Is htmlspecialchars enough to prevent an SQL injection on a variable enclosed in single quotes?

Question

Although many sources quote the htmlspecialchars function with ENT_QUOTES to be not enough to prevent SQL injection, none of them provide a proof of the concept. I cannot think of any possibility myself.

Let us consider the following example:

$username = htmlspecialchars($_GET['name'], ENT_QUOTES, 'UTF-8');
$sql = "SELECT * from user WHERE name='$username'";
mysql_query($sql,...);

Can any one provide an example, OTHER than ones covered by the case when SQL injection gets around mysql_real_escape_string()?

Answer 1

The character that htmlspecialchars fails to encode the critical character \0 (NUL byte), \b (backspace), as well as the \ character.

In order to exploit this, you need a statement with multiple injection points. With this you can escape the closing delimiter of one string literal and thus expand it up to the next starting delimiter of the next string literal. Three string literals each with an injection point can then be transformed into two string literals.

For example:

SELECT * from user WHERE (name='$login' OR email='$login') AND password='$password'

Now with the following values:

login:    ) OR 1=1 /*\
password: */--

The resulting statement looks like this:

SELECT * from user WHERE (name=') OR 1=1 /*\' OR email=') OR 1=1 /*\') AND password='*/--'

Which is equivalent to:

SELECT * from user WHERE (name=') OR 1=1 /*\' OR email=') OR 1=1