Got it, it's the MySQL JDBC driver that inserts the backslashes, because it doesn't know about the sql_mode change. Using "?sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES" in the connection string solved theproblem.
Barry On Friday, October 17, 2014 4:14:12 PM UTC+1, [email protected] wrote: > > Is "SET @@SESSION.sql_mode = CONCAT(@@SESSION.sql_mode, ',' > ,'NO_BACKSLASH_ESCAPES');" a complete solution? > > I'm encountering problems using this approach with queries that have bound > values. > > I'm creating a batch insert query of records like so: > > InsertQuery<T> q = ctx.insertQuery(record.getTable()); > for (T r : records) { > q.addRecord(r); > } > q.onDuplicateKeyIgnore(true); > ctx.execute(q); > > > *System.out.println(q.getSQL(ParamType.INDEXED));* > insert ignore into `mydb`.`MyTable` (`id`, `text`) values (?, ?) > *System.out.println(q.getSQL(ParamType.INLINED));* > insert ignore into `mydb`.`MyTable` (`id`, `text`) values (1, 'Wasn''t > doesn''t') > > This all looks well. When inlined the apostrophe is escaped correctly. > > But executing this query gives the error "....server version for the right > syntax to use near 't doesn\'t"... > > Why is jOOQ using a backslash to escape apostrophes before it hits the > server? Is this fixed by #3000 or is it a different issue? > > Regards, > Barry > > > On Monday, September 29, 2014 11:53:40 PM UTC+1, Alok Menghrajani wrote: >> >> Hi, >> >> The topic of MySQL and NO_BACKSLASH_ESCAPES has been discussed in the >> past (https://groups.google.com/d/msg/jooq-user/JdU_Qaap94E/cYLgtqPs0PMJ) >> and Lukas suggestion was to either escape strings manually or turn off the >> backslash escaping option at the database level. >> >> I am sorry to to revive such an annoying issue, but I would like to >> suggest a third alternative, and hear your feedback. >> >> Let me first explain why Lukas' suggestions aren't enough: >> 1. Escaping strings manually makes me sad. Unless there is a way to >> register a global hook that gets called every time a string should get >> escaped, someone will eventually forget to manually escape a string and it >> will lead to security issues. We can do better than this! >> 2. There are cases where a database is accessed by multiple frameworks >> and libraries. jOOQ makes the assumption that back slashes are not escaped, >> while other libraries make the exact reverse assumption. In an ideal world, >> all libraries should be configurable in both ways. >> >> MySQL's back slash escaping can be configured in three places: at a >> global level (in the config file or command line when starting the server), >> in the jdbc connection string (by appending >> ?sessionVariables=sql_mode=NO_BACKSLASH_ESCAPES) or once the connection is >> established (using "SET @@SESSION.sql_mode ..."). jOOQ doesn't know what is >> the global setting without doing a SELECT query. jOOQ however can inspect >> the connection string. >> >> My suggestion is therefore to implement the following logic: >> >> 1. Look at the dialect. If it's not MySQL, we are happy and life goes on >> as usual. >> 2. Look at the connection URL, if it sets NO_BACKSLASH_ESCAPES, do >> nothing. >> 3. In all other cases, execute "SET @@SESSION.sql_mode = >> CONCAT(@@SESSION.sql_mode, ',' ,'NO_BACKSLASH_ESCAPES');" >> >> Besides allowing interoperability between different frameworks, >> implementing such a logic would make jOOQ safer when someone fails to >> properly configure their database. >> >> I did some casual tcpdumping and it seems that establishing a MySQL >> connection requires 15 round trips. Executing the SET command takes another >> 4 round trips, so we would slow down the connection setup by ~25%? This >> performance loss is easy to earn back by changing the jdbc connection >> string. >> >> You can see my work in progress here: >> https://github.com/alokmenghrajani/jOOQ/commits/myql_backslash_escape >> (I will create a PR once I get your feedback and also once I figure out >> how to handle ConnectionProvider). >> >> I also plan to expose the behavior change as a new setting, >> BackslashEscapeHandlingType which can be AUTO (behavior == above >> suggestion), ESCAPE_BACKSLASHES (in case you don't want to incur the >> cost of setting NO_BACKSLASH_ESCAPES and you don't want to change the >> connection URL), and DO_NOT_ESCAPE_BACKSLASHES (if you want the current >> behavior, mostly useful if you are already manually escaping backslashes). >> >> A fun alternative would be to hex encode all strings (X'...hex >> bytes...'), and this whole problem magically goes away. >> >> Alok >> > -- 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]. For more options, visit https://groups.google.com/d/optout.
