35

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?

Improve this question
4
  • 1
    I want to know if the "%" character can lead to anything more than extra results in a LIKE clause. Commented Oct 25, 2013 at 21:00
  • The short answer to your question is, as far as I'm aware, no - such characters (even if unescaped) will not terminate the string literal context and place the server into SQL context; thus SQL injection cannot arise. However, you should be careful that your escaping methodology is aware of the character set that the server is using to decode the string literal's received bytes: if one's escaping is conducted in a different character set, it may be possible for carefully crafted strings to terminate the string literal and inject arbitrary SQL. Commented Oct 26, 2013 at 9:45
  • Starting a like with a % changes the query plan to use a full table scan which is generally bad for performance. So if everything else is handled that could still be used to attack a system. Depending on the interface you use, injecting a ; may allow you to run a second query. It's good policy to limit the permissions of the user the query is run as. Line comment characters like -- in mysql can also cause problems. ex: UPDATE users set access_time = x where id = y; if x is -9999 -- that might be used to update all users access_time. Commented Oct 29, 2013 at 14:00
  • I don't see a comment character listed. This is one of the common ones that get listed in SQL injection attacks. Commented Nov 1, 2013 at 5:09

2 Answers 2

Reset to default
13

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 isn't "characters" that cause the SQL injections, but improper formatting. Any character, depending 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 leading to 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 injection attacks, while formatting does. Note that every distinct part of the query requires different formatting, which in turn is 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.

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

That's a gravely wrong statement. Escaping does not prevent injection attacks. These characters need to be escaped in order to format strings and has absolutely nothing to do with injection attacks. While it is true that a properly formatted query part is invulnerable, the truth is - you have to format dynamic queries anyway, following syntax rules, not because of injection attacks. Your query will be invulnerable to attacks 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 users mysql_real_escape_string() is strongly connected to whatever scary injection attack, in reality it isn'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, depending on the context.

Thus, there is no connection between characters escaped by this function, and whatever "danger". The moment you start thinking that the purpose of mysql_real_escape_string() is to escape "dangerous" characters, you are indeed putting yourself in 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

While I really appreciate you taking the time to answer this, and it's clear that you can be inserting malformatted strings into a sql query if you do not escape these characters, the misunderstanding you think I had was an artifact of my wording and not an actual misunderstanding. I've reworded the question a little and bolded the actual question in the original post. I acknowledge the reason that we format strings is not to prevent injection, but to represent the data correctly in the database, however I am ONLY concerned with the security aspects of this question.
Are injection attacks possible when a program only allows user input that matches the following regex?: /^ *[A-Za-z0-9 ]+ *$/
@ShawnEary injection is not a subject of "input". It's subject of a query formatting. As long as your query is formatted properly, then no injection is possible. In case your query is not formatted properly, any input will make SQL injection possible.
"in case your query is not formatted properly, any input will make SQL injection possible." how can you make SQL injection with input "abcdef", if there are no deliberate backdoors?
9
+25

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' ) );
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.