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.
