MySQL 5 and NO_BACKSLASH_ESCAPE bit

at 2006-06-06 in Announcements by friebe (0 comments)

From the MySQL manual:

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients. This capability enables each application to tailor the server's operating mode to its own requirements.
Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

In case the NO_BACKSLASH_ESCAPE bit is set in the sqlmode server variable, escaping strings in strings is not accomplished by backslashes (\) but rather by doubling the quotes. Quoting with backslashes will lead to "errorcode 1064: Statement failed: You have an error in your SQL syntax...".

The rdbms.MySQLConnection class has been fixed to respect this value.

How to query the sql_mode variable
By issuing the following SQL query:

  show variables like "sql_mode";

one can retrieve information about the mode "bitfield". The result is a comma-separated string containing one or more of the values listed in the MySQL manual.

Note: The MySQL manual suggests using one of the following SQL queries:
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
These will not work in MySQL servers older than 4.1, which acquit them with ERROR 1193: Unknown system variable 'sql_mode'!

A few examples:

5.0.19-standard-log: NO_BACKSLASH_ESCAPES
4.0.25-standard-log: 0
4.1.13-standard-log / 4.1.19: (empty string)

Further reading

MySQL bug #10214
http://bugs.mysql.com/bug.php?id=10214
MySQL manual, 5.2.5. The Server SQL Mode
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
SQL-Injection-Lücke in MySQL gestopft
http://www.heise.de/newsticker/meldung/73804 (in German)

You can subscribe to the XP framework's news by using RSS syndication.

MySQL 5 and NO_BACKSLASH_ESCAPE bit

Subscribe

Categories

Related