Suggestion for improving MySQL and backslash escape handling

[Alok Menghrajani]

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

[Lukas Eder]

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

--
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.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

A small correction...

2014-10-06 13:12 GMT+02:00 Lukas Eder <lukas...@gmail.com>:

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.

I just realised that MySQL actually performs backslash escaping *in addition* to double-apostrophe escaping. I guess there really is room for a simple backslashEscaping boolean property in Settings, then... We'll implement that for jOOQ 3.5.0

[Alok Menghrajani]

Hello,

On Mon, Oct 6, 2014 at 4:12 AM, Lukas Eder <lukas...@gmail.com> wrote:
> 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.

I agree MySQL should get their act together and stick to the standard.
A coworker however brought up a concern. Given that MySQL's default
behavior is to escape backslashes, he thinks not escaping backslashes
might be less tested and have issues. For example, he pointed me to:
http://bazaar.launchpad.net/~mysql/connectorj/5.1/view/head:/src/com/mysql/jdbc/PreparedStatement.java#L4781

I don't have enough context to tell how much of an issue this is going
to be, but it's something to keep in mind.

> 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.

Do you want a pull request to support PostgreSQL's 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.

Agree. I was going for something that would be fool-proof and do the
safe thing by default. We can revisit this in the future.

Thanks for fixing this!

[Alok Menghrajani]

On Mon, Oct 6, 2014 at 4:51 AM, Lukas Eder <lukas...@gmail.com> wrote:
> I just realised that MySQL actually performs backslash escaping *in
> addition* to double-apostrophe escaping. I guess there really is room for a
> simple backslashEscaping boolean property in Settings, then... We'll
> implement that for jOOQ 3.5.0

Instead of a backslashEscaping setting, you should perhaps make it
stringEscaping and then have database specific options, e.g.
MySQL_BACKSLASH, PSQL_DOLLARS, etc. It will be less confusing for
users of databases which don't need to care about this and easier for
other set of users to tell which setting applies to them.

[Lukas Eder]

2014-10-06 19:01 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

Hello,

On Mon, Oct 6, 2014 at 4:12 AM, Lukas Eder <lukas...@gmail.com> wrote:
> 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.

I agree MySQL should get their act together and stick to the standard.
A coworker however brought up a concern. Given that MySQL's default
behavior is to escape backslashes, he thinks not escaping backslashes
might be less tested and have issues. For example, he pointed me to:
http://bazaar.launchpad.net/~mysql/connectorj/5.1/view/head:/src/com/mysql/jdbc/PreparedStatement.java#L4781

I don't have enough context to tell how much of an issue this is going
to be, but it's something to keep in mind.

Ugh. Nice discovery! I'll check back again with Morgan and Jess about this. This looks like a problem to me.

Also, Morgan has pointed out an interesting aspect about the NO_BACKSLASH_ESCAPES property with respect to backwards-compatibility, when I asked him if it could be included in MySQL 5.7's new STRICT mode:

http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html#comment-1621690667

In any case, I guess this escaping will stick around for another while, and if jOOQ can abstract it entirely, that would be a great value proposition for jOOQ/MySQL users, also in terms of security.

> 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.

Do you want a pull request to support PostgreSQL's escaping?

Let's first discuss how this could be implemented. It's not a priority though, so if you don't have an immediate stake, I'd prefer not to include it in jOOQ 3.5.0 anymore.

 

> 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.

Agree. I was going for something that would be fool-proof and do the
safe thing by default. We can revisit this in the future.

Thanks for fixing this!

We certainly should. I really wonder if there is any way through standard JDBC API to access the NO_BACKSLASH_ESCAPES property. I haven't found it in Connection.getClientInfo(), nor in INFORMATION_SCHEMA.SESSION_VARIABLES... I also don't find it in @@SESSION.sql_mode

[Lukas Eder]

In fact, after I re-visited your pull request and how this feature actually works, I believe that backslash escaping might really be the right setting here, as it is complementary to double-apostrophe escaping, i.e. a double-apostrophe ('') is considered the same as a backslash-escaped apostrophe (\'). This isn't true with PostgreSQL's dollar-escaping, where you have to choose either one...

[Lukas Eder]

Agree. I was going for something that would be fool-proof and do the
safe thing by default. We can revisit this in the future.

Thanks for fixing this!

We certainly should. I really wonder if there is any way through standard JDBC API to access the NO_BACKSLASH_ESCAPES property. I haven't found it in Connection.getClientInfo(), nor in INFORMATION_SCHEMA.SESSION_VARIABLES... I also don't find it in @@SESSION.sql_mode

The com.mysql.jdbc.ConnnectionImpl class contains this logic to expose the noBackslashEscapes flag:

            if (this.serverVariables.containsKey("sql_mode")) {

                int sqlMode = 0;

                String sqlModeAsString = this.serverVariables.get("sql_mode");

                try {

                    sqlMode = Integer.parseInt(sqlModeAsString);

                } catch (NumberFormatException nfe) {

                    // newer versions of the server has this as a string-y list...

                    sqlMode = 0;

                    if (sqlModeAsString != null) {

                        if (sqlModeAsString.indexOf("ANSI_QUOTES") != -1) {

                            sqlMode |= 4;

                        }

                        if (sqlModeAsString.indexOf("NO_BACKSLASH_ESCAPES") != -1) {

                            this.noBackslashEscapes = true;

                        }

                    }

                }

                if ((sqlMode & 4) > 0) {

                    this.useAnsiQuotes = true;

                } else {

                    this.useAnsiQuotes = false;

                }

            }

It looks like the setting is only exposed if it is active. The variable can be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO mode, we would need to check this flag prior to query execution (lazily, only the first time we actually encounter an inlined string variable).

Another option would be to add another setting value called DEFAULT, which means OFF for most databases and ON for MySQL / MariaDB. If the setting is not specified, we'll actually use this DEFAULT as it aligns with MySQL's own default. This would then be the sensible default for new users, to keep them from running into this issue when they start using jOOQ.

What do you think?

[Alok Menghrajani]

>> > I prefer this not to be strictly tied to backslash escaping, as other
>> > databases (such as PostgreSQL) may implement additional means of
>> > escaping.
>>
>> Do you want a pull request to support PostgreSQL's escaping?
>
>
> Let's first discuss how this could be implemented. It's not a priority
> though, so if you don't have an immediate stake, I'd prefer not to include
> it in jOOQ 3.5.0 anymore.

I don't think it's a priority, just something we may potentially need
in the future.

> We certainly should. I really wonder if there is any way through standard
> JDBC API to access the NO_BACKSLASH_ESCAPES property. I haven't found it in
> Connection.getClientInfo(), nor in INFORMATION_SCHEMA.SESSION_VARIABLES... I
> also don't find it in @@SESSION.sql_mode

If you do SELCT @@GLOBAL.sql_mode, @@SESSION.sql_mode, you can see if
NO_BACKSLASH_ESCAPES is set either at a connection level or globally.
If you don't find it in either of these two strings, then you know
it's not set. Unfortunately, this requires a roundtrip to the
database.

I don't know if you can read the value from
http://bazaar.launchpad.net/~mysql/connectorj/5.1/view/head:/src/com/mysql/jdbc/ConnectionImpl.java#L3554

Alok

[Alok Menghrajani]

> It looks like the setting is only exposed if it is active. The variable can
> be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT
> @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO mode, we
> would need to check this flag prior to query execution (lazily, only the
> first time we actually encounter an inlined string variable).

batch queries also end up needing this. Do you know what other queries
or features indirectly inline?

> Another option would be to add another setting value called DEFAULT, which
> means OFF for most databases and ON for MySQL / MariaDB. If the setting is
> not specified, we'll actually use this DEFAULT as it aligns with MySQL's own
> default. This would then be the sensible default for new users, to keep them
> from running into this issue when they start using jOOQ.
>
> What do you think?

On one hand, I like the idea of having the default be to escape
backslashes for MySQL users. Besides aligning jOOQ with the database's
default behavior, it reduces the impact of having the wrong setting
(mangled data instead of a SQL injection). On the other hand, it might
impact jOOQ users who turned NO_BACKSLASH_ESCAPES and aren't careful
when upgrading to the newer version.

Alok

[Alok Menghrajani]

On Tue, Oct 7, 2014 at 9:46 AM, Alok Menghrajani <al...@squareup.com> wrote:
>>> > I prefer this not to be strictly tied to backslash escaping, as other
>>> > databases (such as PostgreSQL) may implement additional means of
>>> > escaping.
>>>
>>> Do you want a pull request to support PostgreSQL's escaping?
>>
>>
>> Let's first discuss how this could be implemented. It's not a priority
>> though, so if you don't have an immediate stake, I'd prefer not to include
>> it in jOOQ 3.5.0 anymore.
>
> I don't think it's a priority, just something we may potentially need
> in the future.

Sorry, I take that back. I just looked into how dollar quoted strings
work and there is probably no safe way to sanitize such an input.
Thankfully, the issue isn't the same as backslash, so it's best to
simply ignore dollar quoted strings.

Alok

[Lukas Eder]

Yes, I've seen that. That's another option. In particular, the JDBC driver will already cache these values.

Unfortunately, that's vendor-specific API, and thus needs to be accessed via reflection. Besides, depending on the connection pool being used, the "raw connection" might not be available to jOOQ. We've had such issues with Oracle's JDBC driver, which we need to access in order to create array objects.

[Lukas Eder]

2014-10-07 19:49 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

> It looks like the setting is only exposed if it is active. The variable can
> be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT
> @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO mode, we
> would need to check this flag prior to query execution (lazily, only the
> first time we actually encounter an inlined string variable).

batch queries also end up needing this. Do you know what other queries
or features indirectly inline?

I'm not sure what you mean...? How do batch queries need this?

 

> Another option would be to add another setting value called DEFAULT, which
> means OFF for most databases and ON for MySQL / MariaDB. If the setting is
> not specified, we'll actually use this DEFAULT as it aligns with MySQL's own
> default. This would then be the sensible default for new users, to keep them
> from running into this issue when they start using jOOQ.
>
> What do you think?

On one hand, I like the idea of having the default be to escape
backslashes for MySQL users. Besides aligning jOOQ with the database's
default behavior, it reduces the impact of having the wrong setting
(mangled data instead of a SQL injection). On the other hand, it might
impact jOOQ users who turned NO_BACKSLASH_ESCAPES and aren't careful
when upgrading to the newer version.

Yes, this is why we usually keep a section for incompatible changes in the release notes. This change will not be shipped with patch releases, only with a minor release.

From how I've perceived the discussion between Morgan Tocker and Bill Karwin (on the previously linked blog article by Morgan), I suspect that few people actually turned that flag on. So the risk / benefit ratio is probably in favour of breaking compatibility this time - in particular given that the setNString() method that you've pointed out to me also assumes that the flag is not set. 

[Lukas Eder]

2014-10-07 19:51 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

Sorry, I take that back. I just looked into how dollar quoted strings
work and there is probably no safe way to sanitize such an input.
Thankfully, the issue isn't the same as backslash, so it's best to
simply ignore dollar quoted strings.

Except that apostrophes shouldn't be doubled within dollar quoted strings. This isn't really a problem when jOOQ inlines strings, but it is a problem when jOOQ parses plain SQL. Perhaps, though, that's more of a PostgreSQL-related bug, rather than a new setting. 

[Alok Menghrajani]

On Tue, Oct 7, 2014 at 11:23 PM, Lukas Eder <lukas...@gmail.com> wrote:

2014-10-07 19:49 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

> It looks like the setting is only exposed if it is active. The variable can
> be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT
> @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO mode, we
> would need to check this flag prior to query execution (lazily, only the
> first time we actually encounter an inlined string variable).

batch queries also end up needing this. Do you know what other queries
or features indirectly inline?

I'm not sure what you mean...? How do batch queries need this?

 

Sorry, I wasn't very clear. Given the following table:

create table jooqtest(title varchar(255), primary key(title));
insert into jooqtest (title) values ("hello"),("world"),("foo"),("bar");

And a batch query:

 

create.batch(

          create.update(table("jooqtest")).set(field("title"), value("aaa")).where(field("title").eq("something\\' or 1=1 ORDER BY title DESC LIMIT 1-- ")),

          create.update(table("jooqtest")).set(field("title"), value("bbb")).where(field("title").eq("whatever")))

       .execute();

In theory, the batch query shouldn't do anything. However, the batch request is prone to SQLi and one of the rows (in this case "hello" which is the first row) gets overwritten with "aaa".

I'm assuming the batch feature internally calls inline. My question was: are there other types of queries or other jOOQ features which call inline?

Yes, this is why we usually keep a section for incompatible changes in the release notes. This change will not be shipped with patch releases, only with a minor release.

From how I've perceived the discussion between Morgan Tocker and Bill Karwin (on the previously linked blog article by Morgan), I suspect that few people actually turned that flag on. So the risk / benefit ratio is probably in favour of breaking compatibility this time - in particular given that the setNString() method that you've pointed out to me also assumes that the flag is not set. 

:) for favoring security over backwards compatibility!

[Lukas Eder]

2014-10-09 2:38 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

On Tue, Oct 7, 2014 at 11:23 PM, Lukas Eder <lukas...@gmail.com> wrote:

2014-10-07 19:49 GMT+02:00 Alok Menghrajani <al...@squareup.com>:

> It looks like the setting is only exposed if it is active. The variable can
> be obtained from SHOW VARIABLES LIKE 'sql_mode', or via SELECT
> @@SESSION.sql_mode FROM DUAL. I suspect that if we support an AUTO mode, we
> would need to check this flag prior to query execution (lazily, only the
> first time we actually encounter an inlined string variable).

batch queries also end up needing this. Do you know what other queries
or features indirectly inline?

I'm not sure what you mean...? How do batch queries need this?

 

Sorry, I wasn't very clear. Given the following table:

create table jooqtest(title varchar(255), primary key(title));
insert into jooqtest (title) values ("hello"),("world"),("foo"),("bar");

And a batch query:

 

create.batch(

          create.update(table("jooqtest")).set(field("title"), value("aaa")).where(field("title").eq("something\\' or 1=1 ORDER BY title DESC LIMIT 1-- ")),

          create.update(table("jooqtest")).set(field("title"), value("bbb")).where(field("title").eq("whatever")))

       .execute();

In theory, the batch query shouldn't do anything. However, the batch request is prone to SQLi and one of the rows (in this case "hello" which is the first row) gets overwritten with "aaa".

I see, you're probably right, there is an issue. Nice catch! I'm not aware of how MySQL serialises static batch statements to the server. Will the trailing comment ("--") really result in the subsequent statements to be commented out? But even if it wasn't you could probably also just write:

    "something\\'; drop database bobby_tables; --"

 

I'm assuming the batch feature internally calls inline.

Yes, DSLContext.renderInlined() is used to generate the SQL. This will then inline all bind values.

 

My question was: are there other types of queries or other jOOQ features which call inline?

There are equals() and hashCode(), which are not security-relevant, and the new Result.formatInsert(), which generates a set of INSERT statements from a Result. There are probably a couple of other places, where inlining is performed because the server doesn't accept bind values in that particular place - e.g.

Unproblematic, because inlining numbers:

- DB2's / Ingres's OFFSET .. FETCH FIRST .. ROWS ONLY clause

- Informix's SKIP .. FIRST .. clause

- SQL Servers's Sybase's TOP .. START AT .. clause

Possibly problematic:

- If the one of the following (dialect: max number of bind variables per statement) thresholds is surpassed, we inline ALL bind values: (MS Access: 768), (Sybase ASE: 2000), (Ingres: 1024), (SQL Server: 2100), (SQLite: 999). Details here: https://github.com/jOOQ/jOOQ/issues/1520 and here: http://blog.jooq.org/2013/04/28/rare-uses-of-a-controlflowexception/

- The org.jooq.impl.BatchMultipe use-case that you've discovered

On a quick glance, these are the only cases where inlining is enforced - unless of course, you enforce it yourself via Settings.statementType or via explicit inlining

Yes, this is why we usually keep a section for incompatible changes in the release notes. This change will not be shipped with patch releases, only with a minor release.

From how I've perceived the discussion between Morgan Tocker and Bill Karwin (on the previously linked blog article by Morgan), I suspect that few people actually turned that flag on. So the risk / benefit ratio is probably in favour of breaking compatibility this time - in particular given that the setNString() method that you've pointed out to me also assumes that the flag is not set. 

:) for favoring security over backwards compatibility!

Well, with jOOQ, we still break a bit less clients than MySQL would :) Compare:

- http://stackoverflow.com/questions/tagged/mysql (~300k questions)

- http://stackoverflow.com/questions/tagged/jooq (~300 questions)

[b.cou...@gmail.com]

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

[b.cou...@gmail.com]

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

[Lukas Eder]

Hello,

Yes, as we discovered earlier in this thread, there is a cache for the noBackslashEscapes value in the JDBC driver:

https://groups.google.com/d/msg/jooq-user/jCd7LjCs2vw/L1gYJfKgUxAJ

Thanks for pointing this out. Yes, the "SET @@SESSION.sql_mode = [...]" SQL statement you've referred to is going to change the session setting in the server only, not also in the client. The JDBC driver will not be aware of this change, if it is using this cache.

jOOQ will always bind values the way you bind them to jOOQ. Most of this discussion is about what happens when bind values are inlined by jOOQ.

Cheers,

Lukas

--