Hello, Thanks for your patience in this matter.
Here's a little feedback. While I understand that this is an annoying issue to MySQL users, I do think that in the long run, backslash escaping should be avoided in MySQL for database interoperability reasons. I trust that MySQL is going to start enforcing a stricter SQL standards compatibility in the future - this was also Oracle's official position mentioned at the Oracle Open World conference. I will be blogging about this pretty soon. For the time being, this is a real issue, of course, and we'll implement #3000 eventually, which allows for alternative string escaping modes: https://github.com/jOOQ/jOOQ/issues/3000 I prefer this not to be strictly tied to backslash escaping, as other databases (such as PostgreSQL) may implement additional means of escaping. Also, I don't think that any interaction with the Connection setting is a good idea, in particular when tampering with such settings. I believe that the suggestion involving calls to "SET @@SESSION.sql_mode..." originates from a local use-case, and it will probably break in more general contexts, e.g. in setups that other users may have. A fun alternative would be to hex encode all strings (X'...hex bytes...'), > and this whole problem magically goes away. Well ;-) There is another related issue when jOOQ's plain SQL APIs need to parse user-provided string literals, which would still need to be solved: https://github.com/jOOQ/jOOQ/issues/3630 Cheers, Lukas 2014-09-30 0:53 GMT+02:00 Alok Menghrajani <[email protected]>: > 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. > -- 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.
