Is HTMLspecialchars Enough to Prevent an SQL Injection on a Variable Enclosed in Single Quotes

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

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

SQL Injection Protection - single quotes

Try this input:

abc' OR id <> '

it will lead to following statement:

"SELECT * FROM `Games` WHERE `id` = 'abc' OR id <> ''"

That would return all games instead of only one. If your page allows to show the whole result, then we would definitely see too much...

The way out is to use PDO with prepared statements, are at least the mysqli_real_escape_string() function before inserting the user input into the SQL statement.

SQL-Injection can do a lot more, in the worst case you can even get control over the server. Have a look at this SQL Injection Cheat Sheet

htmlspecialchars_decode shows tags instead of formatting

try this: html_entity_decode(htmlspecialchars_decode($quest,ENT_QUOTES))
and problem will be solved for sure.

Could SQL injection be performed in this case?

mysql_real_escape_string ALONE can't prevent all type of SQL Injection.

Whenever you need escaping, you need it despite of "security", but just because it is required by SQL syntax. And where you don't need it, escaping won't help you even a bit.

The usage of this function is simple: when you have to use a quoted string in the query, you have to escape it's contents. Not because of some imaginary "malicious users", but merely to escape these quotes that were used to delimit a string. This is extremely simple rule, yet extremely mistaken by PHP folks.

This is just syntax related function, not security related.

Depending on this function in security matters, believing that it will "secure your database against malicious users" WILL lead you to injection.

A conclusion that you can make yourself:
No, this function is not enough.

Prepared statements is not a silver bullet too. It covers your back for only half of possible cases. See the important addition I made to the famous question for the details

Am I safe from a MySQL injection?

I think that you are confusing two security issues: SQL injection and cross-site scripting (XSS).

A website is vulnerable to SQL injection when improperly sanitized user input is used in an SQL query that is sent to the SQL database. This code, for example, introduces an SQL injection vulnerability:

mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . $_POST['postmessage'] . "')");

This problem is easy to fix by escaping the user input with a function like mysql_real_escape_string:

mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . mysql_real_escape_string($_POST['postmessage']) . "')");

That's all that you need to do, but the tricky part is remembering to do this for every piece of user input that is used in an SQL statement.

A website is vulnerable to cross-site scripting when user input is used in HTML that is sent to a client. This code, for example, introduces a XSS vulnerability:

echo "<div class='postmessage'>" . $_POST['postmessage'] . "</div>";

A XSS vulnerability is fixed by escaping the user input with a function like htmlspecialchars:

echo "<div class='postmessage'>" . htmlspecialchars($_POST['postmessage']) . "</div>";

Again, this is easy to do, but easily forgotten.

Usually, user input that is placed in a database to be used in sending back HTML at a later time is saved unmodified. That is, only mysql_real_escape_string is used. However, you could escape user input to prevent XSS, and then escape the XSS-safe string to prevent SQL injection:

mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . mysql_real_escape_string(htmlspecialchars($_POST['postmessage'])) . "')");

The benefit is that you don't need to remember to escape values from the database with htmlspecialchars before writing them into HTML. The drawback is that some values may need to be escaped with different functions. For example, a user name would probably be escaped with htmlspecialchars, but a "postmessage" might allow BBcode, Markdown, or a subset of HTML. If you escaped all input to prevent XSS, then you would need to unescape values from the database with, for example, htmlspecialchars_decode.

One problem is that unescaping the escaped string does not always return the original string (unescape(escape($orig)) is not necessarily the same as $orig). Even with htmlspecialchars and htmlspecialchars_decode, using a different quote style will cause this problem. Another example is that if strip_tags is used, then information is removed irrecoverably; you will not be able to undo the strip_tags. Thus, many developers choose to use mysql_real_escape_string only to save values into the database and htmlspecialchars (or whatever) to prepare a string from the database to be used in HTML.

---

Related Topics