mysqlsecurityescapingsql-injection

Which characters are actually capable of causing SQL injection in MySQL?

We all know that we should use prepared statements or the appropriate replacement/formatting rules in order to prevent sql injection in our applications.

However, when taking a look at MySQL's list of character literals, I noticed that it includes the following characters:

Now, while the % and _ characters need to be escaped in order to prevent injection of unwanted wildcards into LIKE statements, and while the ' (single quote), \ (backslash), and " (double quote) all need to be escaped in order to prevent injection of arbitrary SQL - could having any of these other characters unescaped lead directly to a SQL injection vulnerability that would not otherwise be present? Does anyone have any real world examples of such an exploit?

Let's assume we are building our query like:

SELECT * FROM users WHERE username='$user'

Is there any value for $user where the only unescaped character literals are \b (backspace), \0 (NUL), \n (newline), \r (linefeed), \t (tab) or \Z (Ctrl+Z) that allows the injection of arbitrary SQL into this query?

Solution

  • Considering the below lines from mysql_real_escape_string() manual :

    MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. mysql_real_escape_string() quotes the other characters to make them easier to read in log files.

    SQL injection in MySQL should not be possible with these special characters alone by themselves : \b \0 \n \r \t \Z .

    However String Literals manual states the following but the reasons specified ( or not ) does not relate to SQL injection :

    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.

    Furthermore , in a simple test , irrespective of weather the above listed special characters are escaped or not , MySQL yielded same results . In other words MySQL did not even mind :

    $query_sql = "SELECT * FROM `user` WHERE user = '$user'";

    The above query worked similarly for non-escaped and escaped versions of those above listed characters as put below :

    $user = chr(8);     // Back Space
$user = chr(0);     // Null char
$user = chr(13);    // Carriage Return
$user = chr(9);     // Horizontal Tab
$user = chr(26);    // Substitute
$user = chr(92) .chr(8);    // Escaped Back Space
$user = chr(92) .chr(0);    // Escaped Null char
$user = chr(92) .chr(13);   // Escaped Carriage Return
$user = chr(92) .chr(9);    // Escaped Horizontal Tab
$user = chr(92) .chr(26);   // Escaped Substitute

    Test table and data used in the simple test :

    -- Table Structure

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(10) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- Table Data

INSERT INTO `user` ( `user` ) VALUES
( char( '8' ) ),
( char( '0' ) ),
( char( '10' ) ),
( char( '13' ) ),
( char( '9' ) ),
( char( '26' ) );