Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 596
Tower Avatar asked Jul 06 '09 13:07

Tower


2 Answers

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):

alt text

like image 113
balpha Avatar answered Oct 06 '22 00:10

balpha


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.
like image 41
Gumbo Avatar answered Oct 06 '22 00:10

Gumbo