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

Question

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:

• \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 (Ctrl+Z). See note following the table.
• \\ A backslash (\) character.
• \% A % character.
• \_ A _ character.

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 1

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' ) );

Solution 2

An obligatory addendum from 2020:

Dealing with characters was proven to be inefficient and obsoleted

You must use prepared statements and forget about escaping, "dangerous characters" or any of that business.

Using parameterized queries is considered the only proper way to protect from SQL injections, for the reasons provided in the original answer below:

Which characters are actually capable of causing SQL injection in mysql

There are no such characters.

It is not "characters" that cause the SQL injections. But improper formatting. Any character, depends on the circumstances, could be either "dangerous" or absolutely harmless. Limiting your protection to some subset is a dangerous delusion that will actually lead to SQL injection sooner or later.

There are two wrong statements in your question that led you to a confusion:

1. We all know that we should use ... the appropriate replacement rules in order to prevent sql injection in our applications.

This statement is wrong. Not replacement but formatting. The difference is essential. Replacement alone does not protect from injections, while formatting does. Note that every distinct part of the query require different formatting which being useless for any other part. Say, there is another character, essential for injection protection - a backtick (`). But you didn't list it because it has nothing to do with string literals.

2. the ' (single quote), \ (backslash), and " (double quote) all need to be escaped in order to prevent injection

That's a gravely wrong statement. Escaping do not prevent injections. These characters need to be escaped in order to format strings and has absolutely nothing to do with injections. While it is true that properly formatted query part is invulnerable. But the truth is - you have to format dynamical query parts just for sake of it, to follow the syntax rules and not because of whatever injections. And you will have your query impenetrable just as a side effect.

Now you can see why your last statement,

why all of these other characters are vulnerable enough to be escaped via mysql_real_escape_string, as it is not immediately obvious to me.

is wrongly put:
It is string formatting rules require these characters, not whatever "vulnerability". Some of them are escaped just for convenience, some for readability, some for the obvious reason of escaping a delimiter. That's all.

To answer recent questions from comments:

I really want an answer to this, as PHP's mysql_real_escape_string does not quote these literals either.

Again: although in the mind of average PHP user mysql_real_escape_string() is strongly connected to whatever scaring injection, in reality it doesn't. There are no "dangerous" characters. Not a single one. There are some service characters with special meaning. They have to be escaped in some circumstances, depends on the context.

Thus, there is no connection between characters escaped by this function, and whatever "danger". The moment you start thinking that mysql_real_escape_string()'s purpose is to escape "dangerous" characters, you are indeed putting yourself in a danger. While as long as you are using this function only to escape strings (and doing it unconditionally) - you may consider yourself safe (of course if you don't forget to format all other literals too, using their respective formatting rules)

I want to know if the "%" character can lead to anything more than extra results in a LIKE clause.

No.