insert value of type 'inet' into postgresql table - how?

[Peter Ertl]

Hello folks,

I have a table that contains a INET column:

CREATE TABLE backend_registration (

  id                 BIGSERIAL                   NOT NULL PRIMARY KEY,

  password_hash      TEXT                        NOT NULL,

  confirmation_token TEXT,

  created_at         TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),

  created_ip         INET);

However I can't get how to insert an address into column 'created_ip'

factory.insertInto(REGISTRATION)

.set(REGISTRATION.EMAIL, "f...@bar.com")

.set(REGISTRATION.PASSWORD_HASH, "2304820984309238409237428739")

.set(REGISTRATION.CREATED_IP, "::1")

.execute();

^ this does not work and I get the following error:

>> ERROR: column "created_ip" is of type inet but expression is of type character varying

>>  Hint: You will need to rewrite or cast the expression.

Is there a way to solve this issues?

Should type INET be supported by jooq natively?

Cheers

Peter

[Lukas Eder]

Hello,

>> ERROR: column "created_ip" is of type inet but expression is of type character varying

>>  Hint: You will need to rewrite or cast the expression.

Is there a way to solve this issues?

The easiest workaround is to write this:

factory.insertInto(REGISTRATION)

.set(REGISTRATION.EMAIL, "f...@bar.com")

.set(REGISTRATION.PASSWORD_HASH, "2304820984309238409237428739")

.set(REGISTRATION.CREATED_IP, field("?::inet", String.class, "::1"))

.execute();

By using plain SQL, you can cast bind values to custom data types

Should type INET be supported by jooq natively?

It probably should, I have created #2092 for this:

https://github.com/jOOQ/jOOQ/issues/2092

So far, I'm not quite sure how to handle these dialect-specific data types, that are not really compatible with any of java.sql.Types' types. Specifically, Postgres and the Postgres JDBC driver have their ways of dealing with these non-standard situations.

Any implementation hint for #2092 is very welcome

Cheers

Lukas

[Lukas Eder]

Hmm, thinking about it. Similar to introducing support for UUID types by mapping Fields to java.util.UUID, the expected Java type associated with Postgres' INET type would be java.net.InetAddress.

Any thoughts?

2013/1/4 Lukas Eder <lukas...@gmail.com>

[Peter Ertl]

Thanks for the hint ... works nicely :-)

Maybe you should use VARCHAR as the SQL data type and convert the value using CAST(? as inet) and CAST(? as text) when sending / receiving an inet address from the database.

InetAddress makes sense for mapping into java world, alternatively java.lang.String could make sense, too

Be aware that InetAddress might cause unwanted DNS lookups (eventually depending on OS) when instantiating the object.

[Lukas Eder]

Thanks for the hint ... works nicely :-)

Good to know

 

Maybe you should use VARCHAR as the SQL data type and convert the value using CAST(? as inet) and CAST(? as text) when sending / receiving an inet address from the database.

I think I've seen other databases supporting similar types. It may make sense to introduce a more formal type support.

Note that jOOQ needs the extra type information to be able to know when to cast. With VARCHAR / String, that is not possible. Also, converters won't help in this case.

 

InetAddress makes sense for mapping into java world, alternatively java.lang.String could make sense, too

See above, a distinct DataType / Class is needed to know how to properly cast it.

 

Be aware that InetAddress might cause unwanted DNS lookups (eventually depending on OS) when instantiating the object.

I was thinking about using this method here:

http://docs.oracle.com/javase/6/docs/api/java/net/InetAddress.html#getByAddress(byte[])

According to the Javadocs, the method doesn't perform any lookups and doesn't block...

Another option is to introduce a minimal INET implementation in org.jooq.types, similar to unsigned integers and interval data types.

[Peter Ertl]

just for the records...

there is not only INET but also CIDR and MACADDR for network stuff in postgresql:

http://www.postgresql.org/docs/9.2/static/datatype-net-types.html

the data type supports quite a number of operations (one reason for using these types) so having a dedicated data type might be necessary to support these properly in jOOQ:

http://www.postgresql.org/docs/9.2/static/functions-net.html

using getByAddress() should indeed be safe

[Lukas Eder]

Hello, and I am sorry for the delay.

Thanks for the additional insight in Postgres' understanding of network address types. I'll consider these things when implementing #2092:

https://github.com/jOOQ/jOOQ/issues/2092

Unfortunately, I can't promise that this be a high priority, though.

Cheers

Lukas

2013/1/4 Peter Ertl <pete...@googlemail.com>

[i.gag...@tecnoteca.com]

Hi, in the make a select or insert o update so...

String query="INSERT INTO NAME_TABLE (NAME_FIELDS1, NAME_FIELDS2) VALUES ('" + value_field1 + "'::inet, '"" + value_field2 + "'::inet)";

 

[Lukas Eder]

Please pay attention when you inline values into your SQL statements as you run into substantial risk of SQL injection!

http://blog.jooq.org/2013/11/05/using-sql-injection-vulnerabilities-to-dump-your-database/

2014-01-31 <i.gag...@tecnoteca.com>:

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[aar....@gmail.com]

I had the same scenario for the CIDR field rather than INET  (the same can be done for it with a differnet field such as INETAddress)

In my case i binding the cidr data type to SubnetUtils.SubnetInfo from apache-commons-net

package my.package;


import org.apache.commons.net.util.SubnetUtils;
import org.jooq.Binding;
import org.jooq.BindingGetResultSetContext;
import org.jooq.BindingGetSQLInputContext;
import org.jooq.BindingGetStatementContext;
import org.jooq.BindingRegisterContext;
import org.jooq.BindingSQLContext;
import org.jooq.BindingSetSQLOutputContext;
import org.jooq.BindingSetStatementContext;
import org.jooq.Converter;
import org.jooq.impl.DSL;


import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Types;
import java.util.Objects;


/**
 * We're binding <T> = Object (unknown JDBC type), and <U> = SubnetUtils.SubnetInfo (user type)
 * @author aaron.axisa
 */
public class PostgresCIDRSubnetInfoBinding implements Binding<Object, SubnetUtils.SubnetInfo> {


    @Override
    public Converter<Object, SubnetUtils.SubnetInfo> converter() {
        return CidrConverter.getInstance();
    }


    /**
     * The converter does all the conversion
     */
    private static class CidrConverter implements Converter<Object, SubnetUtils.SubnetInfo> {


        private static CidrConverter INSTANCE = new CidrConverter();


        private CidrConverter() {
            // we want spring to override the static INSTANCE when it launches
            INSTANCE = this;
        }


        /**
         * @return The singleton instance for {@link CidrConverter}
         */
        public static CidrConverter getInstance() {
            return INSTANCE;
        }


        @Override
        public SubnetUtils.SubnetInfo from(final Object t) {
            // convert the Postgres data type to json
            return t == null ? null : new SubnetUtils(t.toString()).getInfo();
        }


        @Override
        public Object to(final SubnetUtils.SubnetInfo u) {
            // convert the json to the Postgres data type
            return u == null ? null : u.getAddress()+u.getCidrSignature(); // TODO
        }


        @Override
        public Class<Object> fromType() {
            return Object.class;
        }


        @Override
        public Class<SubnetUtils.SubnetInfo> toType() {
            return SubnetUtils.SubnetInfo.class;
        }


    }


    // Rending a bind variable for the binding context's value and casting it to the cidr type
    @Override
    public void sql(BindingSQLContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::cidr");
    }


    // Registering VARCHAR types for JDBC CallableStatement OUT parameters
    @Override
    public void register(BindingRegisterContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }


    // Converting the SubnetInfo to a String value and setting that on a JDBC PreparedStatement
    @Override
    public void set(BindingSetStatementContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null));
    }


    // Getting a String value from a JDBC ResultSet and converting that to a SubnetInfo
    @Override
    public void get(BindingGetResultSetContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }


    // Getting a String value from a JDBC CallableStatement and converting that to a JsonElement
    @Override
    public void get(BindingGetStatementContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }


    // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
    @Override
    public void set(BindingSetSQLOutputContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }


    // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
    @Override
    public void get(BindingGetSQLInputContext<SubnetUtils.SubnetInfo> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

And then in your pom file for the generation plugin add the following:

<customTypes>
 <customType>
 <name>SubnetInfo</name>
 <type>org.apache.commons.net.util.SubnetUtils.SubnetInfo</type>
 <binding>my.package.PostgresCIDRSubnetInfoBinding</binding>
 </customType>
 </customTypes>


 <forcedTypes>
 <forcedType>
 <name>SubnetInfo</name>
 <types>CIDR</types>
 </forcedType>
 </forcedTypes>

[Lukas Eder]

Thank you very much for documenting this here. That's greatly appreciated!

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[aar....@gmail.com]

I  will add one note however
there seems to be a minor bug/corner case in the SubnetInfo class (they seem to have noticed it but only fixed half way atm)

As of 3.5 common-net they deprecated getAddressCount in favour of getAddressCountLong to be able to get the amount of addresses a netmask covers. This was due to the fact that using a netmask of /0 results in a number larger than the max int value. 

While all that is well and good, they did not change the toString() to use getAddressCountLong instead, and hence that Binding is still susceptible to that corner case.  If you have a guarantee that you won't have fully open netmasks go ahead and use it, but otherwise i would create a simple class yourself for it.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.