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. On Sat, Jan 20, 2024 at 10:08 AM 'Jens Schwarzer' via jOOQ User Group < [email protected]> wrote: > 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! > On Tuesday 16 January 2024 at 17:51:07 UTC+1 [email protected] wrote: > >> Yes, well, that calls QueryPart.toString(), and most QueryPart instances >> know nothing about your Configuration, Settings, SQLDialect, etc. >> >> On Tue, Jan 16, 2024 at 4:56 PM 'Jens Schwarzer' via jOOQ User Group < >> [email protected]> wrote: >> >>> I meant it shows "ABC''DEF" within the debugger >>> On Tuesday 16 January 2024 at 16:52:07 UTC+1 [email protected] wrote: >>> >>>> 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? >>>> >>> -- >>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/jooq-user/4b4c08b1-4a5a-4289-9a03-c3ed98656ca7n%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/4b4c08b1-4a5a-4289-9a03-c3ed98656ca7n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/2b289e41-14a1-4cd6-bd27-1b778e0bfd94n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/2b289e41-14a1-4cd6-bd27-1b778e0bfd94n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4OBxoOLuNXMdLG%2BkkapoNAvek%2B4xeWLjrDzs0gmp_hig%40mail.gmail.com.
