How can I add custom string preprocessing in JOOQ to sanitize strings?

Rinat Suleimanov 0
Viewed 27

I want to fix the errors, which sometimes happened in my app:

ERROR: invalid byte sequence for encoding "UTF8": 0x00; nested exception is org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0x00

I think right way to solve it is to sanitize all text fields before insertion to database with

this.replace("\\u0000", "")

The question is how to implement the converter or custom binding in one place to force JOOQ invoke this replace function?

jooq
2 Answers

Using jOOQ's Converter or Binding

I'm assuming this is about JSON and PostgreSQL? See also: org.jooq.exception.DataAccessException: unsupported Unicode escape sequence \u0000

It seems that a Converter<JSON, JSON> (or JSONB, respectively) would suffice?

Converter.ofNullable(JSON.class, JSON.class,
    j -> j,
    j -> JSON.json(j.data().replace("\\u0000", ""))
);

And then, attach that everywhere using a forcedType:

<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.17.0.xsd">
  <generator>
    <database>
      <forcedTypes>
        <forcedType>
          <userType>org.jooq.JSON</userType>
          <converter>Converter.ofNullable(JSON.class, JSON.class,
            j -> j,
            j -> JSON.json(j.data().replace("\\u0000", ""))
          )</converter>
          <includeTypes>(?i:json)</includeTypes>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>

Using JDBC

You could, of course, also just proxy JDBC and patch PreparedStatement::setString:

@Override
public void setString(int parameterIndex, String x) throws SQLException {
    if (x == null)
        delegate.setString(parameterIndex, x);
    else
        delegate.setString(parameterIndex, x.replace("\\u0000", "");
}

// Also all the other methods that might accept strings

A simple way to approach this is by using jOOQ's own JDBC proxy utility types, to avoid implementing the entire JDBC API:

This will handle things on a lower level, including when you don't use jOOQ. However, if you're using inline values, then those won't be converted by this approach, as those values are transparent to JDBC

Lukas Eder0

So what I've did to solve the problem:

Added custom binding:

class PostgresStringsBinding : Binding<String, String> {

    override fun converter(): Converter<String, String> {
        return object : Converter<String, String> {
            override fun from(dbString: String?): String? {
                return dbString
            }

            override fun to(string: String?): String? {
                return string?.withEscapedNullChars()
            }

            override fun fromType(): Class<String> {
                return String::class.java
            }

            override fun toType(): Class<String> {
                return String::class.java
            }
        }
    }

    @Throws(SQLException::class)
    override fun sql(ctx: BindingSQLContext<String>) {
        if (ctx.render().paramType() == ParamType.INLINED) ctx.render()
            .visit(DSL.inline(ctx.convert(converter()).value())).sql("::text") else ctx.render().sql("?::text")
    }

    @Throws(SQLException::class)
    override fun register(ctx: BindingRegisterContext<String>) {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR)
    }

    @Throws(SQLException::class)
    override fun set(ctx: BindingSetStatementContext<String>) {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter()).value(), null))
    }

    @Throws(SQLException::class)
    override fun get(ctx: BindingGetResultSetContext<String>) {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()))
    }

    @Throws(SQLException::class)
    override fun get(ctx: BindingGetStatementContext<String>) {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()))
    }

    @Throws(SQLException::class)
    override fun set(ctx: BindingSetSQLOutputContext<String>) {
        throw SQLFeatureNotSupportedException()
    }

    @Throws(SQLException::class)
    override fun get(ctx: BindingGetSQLInputContext<String>) {
        throw SQLFeatureNotSupportedException()
    }
}

and registered it as ForcedType:

                ForcedType()
                    .withUserType("java.lang.String")
                    .withBinding("org.example.jooqbindings.PostgresStringsBinding")
                    .withIncludeExpression(".*")
                    .withIncludeTypes("VARCHAR|TEXT")
Rinat Suleimanov0
Related