Bug #10214 mysql_escape_string() should respect NO_BACKSLASH_ESCAPES
Submitted: 27 Apr 2005 19:34 Modified: 5 Aug 2005 21:36
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[27 Apr 2005 19:34] Mark Matthews 
Description:
If the sql_mode has the 'NO_BACKSLASH_ESCAPE' bit set, mysql_escape_string() should use hex escapes when escaping SQL. Otherwise, every client that depends on libmysql will need to have the exact same functionality added.

How to repeat:
N/A.
[5 May 2005 13:49] Jim Winstead 
Strategy: Server will be modified to report SQL_MODE (or maybe just NO_BACKSLASH_ESCAPES) to client. When NO_BACKSLASH_ESCAPES is on, mysql_real_escape_string() will only escape single quotes by doubling them.

mysql_real_escape_string() can't do hex escaping, since it does not return the whole quoted value, but only returns that part of the value that the user puts within the quotes through some other means.
[24 Jun 2005 1:29] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26390
[29 Jun 2005 10:53] Magnus Blåudd 
Approved
[30 Jun 2005 23:13] Jim Winstead 
Related to Bug #7374.
[2 Jul 2005 10:03] Konstantin Osipov 
Another review was done by email.
[5 Jul 2005 19:46] Jim Winstead 
Fixed in 5.0.9.

Note that this requires documentation beyond just the changelog note -- it adds a new value to the server_status field in the MYSQL struct, and changes the behavior of mysql_real_escape_string() when NO_BACKSLASH_ESCAPES mode is enabled on the server.

Further to that, it is probably worth mentioning that mysql_real_escape_string() is not really enough to escape binary data in the face of NO_BACKSLASH_ESCAPES -- one should really use mysql_hex_string() for such data.
[5 Aug 2005 21:36] Mike Hillyer 
Documented in 5.0.9 changelog: 

<listitem><para><literal>mysql_real_escape_string()</literal> API function now respects <literal>NO_BACKSLASH_ESCAPES</literal> SQL mode. (Bug #10214)</para></listitem>

The following was also added to the mysql_real_escape_string() documentation:

<para>When <literal>NO_BACKSLASH_ESCAPES</literal> is on,
<literal>mysql_real_escape_string()</literal> will only escape single quotes by doubling them. If the server has the <literal>NO_BACKSLASH_ESCAPES</literal> SQL mode set, you should not use <literal>mysql_real_escape_string()</literal> for binary data. Instead use <literal>mysql_hex_string()</literal>. See <xref linkend="mysql-hex-string" /></para>