Bug #10214 mysql_escape_string() should respect NO_BACKSLASH_ESCAPES
Submitted: 27 Apr 2005 21:34 Modified: 5 Aug 2005 23:36
Reporter: Mark Matthews
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Jim Winstead Target Version:

[27 Apr 2005 21: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 15: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 3: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 12:53] Magnus Svensson 
Approved
[1 Jul 2005 1:13] Jim Winstead 
Related to Bug #7374.
[2 Jul 2005 12:03] Konstantin Osipov 
Another review was done by email.
[5 Jul 2005 21: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 23: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>