mysql_real_escape_string SQL injection
Understanding how to safely use mysql_real_escape_string function
PHP provides mysql_real_escape_string() to escape special characters in a string before sending a query to MySQL. This function was adopted by many to escape single quotes in strings and by the same occasion prevent SQL injection attacks. However, it can create serious security flaws when it is not used correctly.
Escaped Characters
What exactly does mysql_real_escape_string function do? It prepends a backslash to every special character in the first parameter. Special characters considered are listed below.
Characters escaped by mysql_real_escape_string
0x00 (NULL)
Newline (\n)
Carriage return (\r)
Double quotes (")
Backslash (\)
0x1A (Ctrl+Z)
Correct Usage
Here is a classic and secure way to use this function as input sanitizer for string parameters.
User input (attack attempt).a' OR 'a'='a
Sanitizing input.
$param = mysql_real_escape_string($_GET['n']);
Query generated.
SELECT id FROM products WHERE name='a\' OR \'a\'=\'a'
Since the last example is secured against SQL injections, the query generated will return no result (except if a product is really named as the green segment). What is important to remember here is that you must always enclose the sanitized parameter between quotes when using mysql_real_escape_string() otherwize a SQL injection vulnerability will be created.
How Not To Use
Programmers should be really careful when using mysql_real_escape_string function to sanitize numeric parameters since they are habitually integrated in the query without quotes. The function discussed in this article does not verify data types; it simply escapes some special characters. Here is a code sample that shows how SQL injection could be achieved when mysql_real_escape_string is not correctly implemented.
Malicious user input - A numeric value is expected by the script.9999 OR 1=1
Sanitizing input.
$productid = mysql_real_escape_string($_GET['id']);
Query generated.
SELECT name FROM products WHERE id=9999 OR 1=1
When the query is executed, all products' name are returned because the crafted parameter submitted by the attacker is considered as a part of the SQL segment.
Numeric Parameters and mysql_real_escape_string
As stated earlier in the article, the parameter sanitized by mysql_real_escape_string must be enclosed between quotes in order to avoid SQL injection (no matter the data type). MySQL Server (and other popular DBMS) supports single quotes around numeric values. Here is what the last example would look like after the security fix:
Malicious user input - A numeric value is expected by the script.
9999 OR 1=1
Sanitizing input.
$productid = mysql_real_escape_string($_GET['id']);
Query generated - Attack voided.
SELECT name FROM products WHERE id='9999 OR 1=1'
The last query will return no name.
LIKE Operator Wildcards
There is one last thing to consider when using mysql_real_escape_string to sanitize data; the function does not escape SQL wildcards for LIKE operator. It might seem trivial but in fact it can have a considerable impact on the query's behavior. Since those characters are not escaped, they are considered as classic wildcards by the LIKE operator:
MySQL LIKE Wildcards (MySQL has only 2 wildcards)% Matches an arbitrary number of characters (including zero character).
_ Matches any single character.
To prevent a wildcard match you must escape the corresponding character with a backslash. Here is how it can be done:
User input.
john's ca_
Sanitize data (also escape all MySQL wildcards).
$val = mysql_real_escape_string($_GET['p']);
$val = str_replace("%", "\%", $val);
$val = str_replace("_", "\_", $val);
Generated query (search all products where the description contains exact match of user input).
SELECT * FROM products WHERE description LIKE '%john\'s ca\_%'
Even if most of the time you should escape wildcards characters, there are some cases where you may want the user to use them. Just keep in mind that in those situations, the user could build input strings difficult to match and it might have some performance impact on LIKE operator (available soon). This is not a critical issue, however if an attacker tries to slow down the application or make a DDOS it might be easier with a control over wildcards characters.
Alternative Solutions
Using mysql_real_escape_string is without a doubt a simple way to secure an application against SQL injections, however it is far from the perfect world. Every time this function is used to sanitize data, it calls MySQL's library function. It is not a big deal but if you make a large number of calls to mysql_real_escape_string it will slow down your database server. Moreover if you mistakenly call the function twice on the same data you will end up with incorrect information in your database.
For those reasons, it is suggested to adopt alternative solutions such as parameterized statements or stored procedures as explained in the article about preventing SQL injections in PHP (article available soon).
