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

Question

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?

Charleencharlemagne

I'm not a python person or a MySql person, but my first though was to look for unit tests for this, unfortunately they didn't reveal anything useful- code.google.com/p/owasp-esapi-python/source/browse/esapi/test/… – Dusen

Answer 1

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.

Bastia

Link it dead. Consider updating. – Sybilsybila

This doesn't answer the part of the question where it asks "why?" – Faith

Keep in mind you probably don't want to always escape % and _ since the back slash will get passed literally unless used in a filter/search context, e.g. \% will look like the string \% when using = and % when using like – Lindblad

Answer 2

A guess concerning the backspace character: Imagine I send you an email "Hi, here's the query to update your DB as you wanted" and an attached textfile with

INSERT INTO students VALUES ("Bobby Tables",12,"abc",3.6);

You cat the file, see it's okay, and just pipe the file to MySQL. What you didn't know, however, was that I put

DROP TABLE students;\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b\b

before the INSERT STATEMENT which you didn't see because on console output the backspaces overwrote it. Bamm!

Just a guess, though.

Edit (couldn't resist):

Conney

Thanks Stefano, that fulfills the attribution clause of the CC license. – Conney

Answer 3

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 Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?

Ungenerous

Answer 4

Where user input contains tabulators or backspace characters?

It's quite remarkable a fact that up to this day most users do believe that it's user input have to be escaped, and such escaping "prevents injections".

Granadilla

Answer 5

Java solution:

public static String filter( String s ) {
    StringBuffer buffer = new StringBuffer();
    int i;

    for( byte b : s.getBytes() ) {
        i = (int) b;

        switch( i ) {
            case  9 : buffer.append( "    " ); break;
            case 10 : buffer.append( "\\n"  ); break;
            case 13 : buffer.append( "\\r"  ); break;
            case 34 : buffer.append( "\\\"" ); break;
            case 39 : buffer.append( "\\'"  ); break;
            case 92 : buffer.append( "\\"   );

            if( i > 31 && i < 127 ) buffer.append( new String( new byte[] { b } ) );
        }
    }

    return buffer.toString();
}

Laurettalaurette

Answer 6

couldn't one just delete the single quote(s) from user input?

eg: $input =~ s/\'|\"//g;

Ljubljana

If you're trying to store a name, like O'Leary, you would mess up the person's name. If you're storing a sentence like "Help!", David yelled. you'd want to keep the double-quotes. So yeah, in some cases dumping special characters might be fine, but not in all. – Decussate