How to escape single quotes while creating a query string with jooq?

Question

I am trying to create a jooq query string the following way

DSL.using(SQLDialect.MYSQL)
            .select(
                    ImmutableList.of(DSL.field("Name"))
            .from(DSL.table("Account"))
            .where(DSL.field("Name").eq("Yaswanth's Company"))).toString()

The resultant query string has the single quote escaped with another single quote which is the default mySQL way of escaping single quotes.

"select Name from Account where Name = 'Yaswanth''s Company'"

But I would need the single quote to be escaped with backslash as I am forming the query string for salesforce. (which is called SOQL).

I need the query string this way

"select Name from Account where Name = 'Yaswanth\\'s Company'"

I have looked at the jooq library code and this is hardcoded in the DefaultBinding class

private final String escape(Object val, Context<?> context) {
    String result = val.toString();

    if (needsBackslashEscaping(context.configuration()))
        result = result.replace("\\", "\\\\");

    return result.replace("'", "''");
}

Is there a way for me to override this default behavior via configuration or settings which can be passed by DSL.using(*, *)?

Lukas Eder · Accepted Answer · 2017-02-15 08:17:57Z

Most SQL databases follow the SQL standard of doubling the single quote for escaping, but it certainly makes sense to make this functionality configurable. We'll probably do this for jOOQ 3.10 with #5873.

In the meantime, the best workaround for you is to write your own data type binding for all String types and override the DefaultBinding behaviour when generating the SQL string. Something along the lines of this:

Code generation configuration

Using <forcedTypes/>

<forcedType>
    <userType>java.lang.String</userType>
    <binding>com.example.AlternativeEscapingStringBinding</binding>
    <!-- add other vendor-specific string type names here -->
    <types>(?i:N?(VAR)?CHAR|TEXT|N?CLOB)</types>
</forcedType>

Data type binding

public class AlternativeEscapingStringBinding implements Binding<String, String> {
    ...

    @Override
    public void sql(BindingSQLContext<String> ctx) throws SQLException {
        if (ctx.paramType() == ParamType.INLINED) 
            if (ctx.value() == null)
                ctx.render().sql('null');
            else
                ctx.render()
                   .sql('\'')
                   .sql(ctx.value().replace("'", "\\'"))
                   .sql('\'');
        else
            ctx.render().sql('?');
    }
}

If you're not using the code generator

You can still apply your own data type bindings manually to your fields as such:

DSL.field("Name", SQLDataType.VARCHAR
                             .asConvertedDataType(new AlternativeEscapingStringBinding()));

You'll just have to remember this every time...

Collectives™ on Stack Overflow

How to escape single quotes while creating a query string with jooq?

1 Answer 1

Code generation configuration

Data type binding

If you're not using the code generator

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Code generation configuration

Data type binding

If you're not using the code generator

Comments

Your Answer

Sign up or log in

Post as a guest

Related