| Bug #57738 | Escaping binary data: contradiction about required escapes | ||
|---|---|---|---|
| Submitted: | 26 Oct 2010 13:24 | Modified: | 5 Jul 2011 16:32 |
| Reporter: | Evan Jones | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[27 Oct 2010 3:08]
Paul DuBois
Hi, do you have a counterexample to what the manual says?
[27 Oct 2010 11:11]
Evan Jones
Having tested this right now, I think the mysql_real_escape_string documentation is correct: only single quote (') and backslash (\) are required to be escaped. Therefore, the section "Strings" should be updated.
HOWEVER: when using the mysql command line client, you must escape NULL as well, because otherwise the mysql *client* truncates the strings before sending them to the server. Using the C API will NULLs works fine.
Some example commands:
Inserting a \Z (success):
printf "insert into test values (100, 'hello\\x1aworld');" | mysql --user=root --host=127.0.0.1 --port=3306 test
Inserting a " (success: note the \" is for the *shell*; verify the data sent to the command using hexdump -C)
printf "insert into test values (100, 'hello\"world');" | mysql --user=root --host=127.0.0.1 --port=3306 test
inserting a NULL (fails: looking at the TPC dump, the mysql command line client truncates the statement, so it isn't the server that has the issue here):
printf "insert into test values (100, 'hello\\0world');" | mysql --user=root --host=127.0.0.1 --port=3306 test
[5 Jul 2011 11:50]
Paul DuBois
Also related: The description of the QUOTE() function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_quote
[5 Jul 2011 16:32]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
I've updated the text to draw a distinction between characters that must be escaped and those that might need escaping depending on client environment:
If you want to insert binary data into a string column (such as a
BLOB column), you should represent certain characters by escape
sequences. Backslash ("\") and the quote character used to quote the
string must be escaped. In certain client environments, it may also
be necessary to escape NUL or Control+Z. The mysql client truncates
quoted strings containing NUL characters if they are not escaped, and
Control+Z may be taken for END-OF-FILE on Windows if not escaped.
Description: Section "8.1.1. Strings" contradicts "21.9.3.53. mysql_real_escape_string()" with regards to the characters required to be escaped when sending binary data to MySQL. 8.1.1 states that NULL ('\0'), backslash ('\\'), single quote ('\'') and double quote ('"') must all be escaped for binary data. 21.9.3.53. mysql_real_escape_string states "Strictly speaking, MySQL requires only that backslash and the quote character [...] be escaped". How to repeat: Read the manual pages: http://dev.mysql.com/doc/refman/5.5/en/string-syntax.html http://dev.mysql.com/doc/refman/5.5/en/mysql-real-escape-string.html Suggested fix: I *think* that mysql_real_escape_string's documentation is the right one. Although maybe for some connection types that isn't true? I suggest updating 8.1.1 Strings to state that only single quote and backslash *must* be escaped, but that other characters (NULL, double quote, ctrl-z) may cause difficulties when passing the data around, and are also recommended to be escaped.