Escaping values within IN conditions containing an ' (apostroph)

[Jens Schwarzer]

Hi,

we are currently using an (very) old version of jooq (3.11.12) because of underlying Java Version issues and currently we are facing an issue with escaping values containing one or more ' (apostrophes):

The SQL dialect is set to MYSQL (which usually should automatically activate BackslashEscaping). However it seems that the code still produces double apostrophes for escaping (''). Does anyone faced a similar issue and can give us a hint what could be the root cause?

Here is the code part we are where we see the issue when using an apostroph within the set of values (e.g.: "ABC'DEF")

 List<Object> sqlValues = values.stream()
                .map(v -> sqlField.getDataType().convert(v))
                .collect(Collectors.toList());
      // create the JOOQ condition
      Condition valueCondition = sqlField.in(sqlValues);

When evaluating the valueCondition we see that the included sqlValues contains "ABC''DEF" instead of "ABC\'DEF".

If you need more information, please let me know :)

Thanks in advance for your help!

[Lukas Eder]

What does this mean in code: 

 

When evaluating the valueCondition we see that the included sqlValues contains "ABC''DEF" instead of "ABC\'DEF".

How do you "evaluate" the condition?

[Jens Schwarzer]

I meant it shows "ABC''DEF" within the debugger

[Lukas Eder]

Yes, well, that calls QueryPart.toString(), and most QueryPart instances know nothing about your Configuration, Settings, SQLDialect, etc.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/4b4c08b1-4a5a-4289-9a03-c3ed98656ca7n%40googlegroups.com.

[Jens Schwarzer]

Hi,

sorry for replying late. 

We now tried with the following sample implementation to rule out any issue within our own code: https://github.com/warumono-for-develop/spring-boot-jooq-tutorial/

We updated jooq to 3.18 in there and used the following within the JooqConfiguration.java:

    Settings settings = new Settings()
        .withBackslashEscaping(BackslashEscaping.ON)
        .withStatementType(StatementType.STATIC_STATEMENT);
    jooqConfiguration.set(settings);
    jooqConfiguration.set(SQLDialect.MYSQL);

However we still face the same issue when executing the following code (even the context now shows 'Mysql' within the debugger as dialect):

String sql = context.selectFrom(BOOK).where(BOOK.ISBN.in("abc'def")).getQuery().getSQL();

We get:

select "public"."book"."id", "public"."book"."description", "public"."book"."isbn", "public"."book"."page", "public"."book"."price", "public"."book"."title", "public"."book"."author_id" from "public"."book" where "public"."book"."isbn" in ('abc''def')

I guess we still are doing something wrong any maybe you could give us a hint? Thank you!

[Lukas Eder]

I see now, there's a mismatch of your expectations here. jOOQ won't escape ' with a backslash, even with this setting. The setting is to indicate that backslashes themselves need to be escaped, i.e. instead of '\' you'll now get '\\'

Undesired MySQL behaviour:

set sql_mode = '';

select @@sql_mode;

select '\\';

|\  |

|---|

|\  |

Standard behaviour:

set sql_mode = 'NO_BACKSLASH_ESCAPES';

select @@sql_mode;

select '\\';

|\\  |

|----|

|\\  |

The apostrophes can still be escaped by duplicating them in both modes.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/2b289e41-14a1-4cd6-bd27-1b778e0bfd94n%40googlegroups.com.