What characters have to be escaped to prevent (My)SQL injections?

Question

Hi,

I'm using MySQL API's function

mysql_real_escape_string()

Based on the documentation, it escapes the following characters:

\0
\n
\r
\
'
"
\Z

Now, I looked into OWASP.org's ESAPI security library and in the Python port it had the following code (http://code.google.com/p/owasp-esapi-python/source/browse/esapi/codecs/mysql.py):

        """
        Encodes a character for MySQL.
        """
        lookup = {
        0x00 : "\\0",
        0x08 : "\\b",
        0x09 : "\\t",
        0x0a : "\\n",
        0x0d : "\\r",
        0x1a : "\\Z",
        0x22 : '\\"',
        0x25 : "\\%",
        0x27 : "\\'",
        0x5c : "\\\\",
        0x5f : "\\_",
        }

Now, I'm wondering whether all those characters are really needed to be escaped. I understand why % and _ are there, they are meta characters in LIKE operator, but I can't simply understand why did they add backspace and tabulator characters (\b \t)? Is there a security issue if you do a query:

SELECT a FROM b WHERE c = '...user input ...';

Where user input contains tabulators or backspace characters?

My question is here: Why did they include \b \t in the ESAPI security library? Are there any situations where you might need to escape those characters?

Answer 1

Blacklisting (identifying bad characters) is never the way to go, if you have any other options.

You need to use a conbination of whitelisting, and more importantly, bound-parameter approaches.

Whilst this particular answer has a PHP focus, it still helps plenty and will help explain that just running a string through a char filter doesn't work in many cases. Please, please see http://stackoverflow.com/questions/110575/do-htmlspecialchars-and-mysqlrealescapestring-keep-my-php-code-safe-from-injec

Answer 2

The MySQL manual page for strings says:

• \0   An ASCII NUL (0x00) character.
• \'   A single quote (“'”) character.
• \"   A double quote (“"”) character.
• \b   A backspace character.
• \n   A newline (linefeed) character.
• \r   A carriage return character.
• \t   A tab character.
• \Z   ASCII 26 (Control-Z). See note following the table.
• \\   A backslash (“\”) character.
• \%   A “%” character. See note following the table.
• \_   A “_” character. See note following the table.