MySQL – SQL Injection Prevention June 25, 2010
Posted by Tournas Dimitrios in Mysql.trackback
If you have ever taken raw user input and inserted it into a MySQL database there’s a chance that you have left yourself wide open for a security issue known as SQL Injection. This article will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.
What is sql-injection :
SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
sql-injection example:
Below is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.
Mysql & PHP code :
// a good user's name
$name = "timmy";
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Normal: " . $query . "
";
// user input that uses SQL Injection
$name_bad = "' OR 1'";
// our MySQL query builder, however, not a very safe one
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
// display what the new query will look like, with injection
echo "Injection: " . $query_bad;
The previous code displays:
Normal: SELECT * FROM customers WHERE username = 'timmy'
Injection: SELECT * FROM customers WHERE username = '' OR 1''
The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to timmy.
However, the injection attack has actually made our query behave differently than we intended. By using a single quote (‘) they have ended the string part of our MySQL query
- username = ‘ ‘
and then added on to our WHERE statement with an OR clause of 1 (always true).
- username = ‘ ‘ OR 1
This OR clause of 1 will always be true and so every single entry in the “customers” table would be selected by this statement!
a more serius sql-injection attack:
Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn’t have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.
Mysql & PHP code :
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
// our MySQL query builder really should check for injection
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
// the new evil injection query would include a DELETE statement
echo "Injection: " . $query_evil;
The previous code displays:
SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '
.If you were run this query, then the injected DELETE statement would completely empty your “customers” table. Now that you know this is a problem, how can you prevent it?
injection prevention — mysql_real_escape_string():
Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.
What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.
Lets try out this function on our two previous injection attacks and see how it works.
Mysql & PHP code:
//NOTE: you must be connected to the database to use this function!
// connect to MySQL
$name_bad = "' OR 1'";
$name_bad = mysql_real_escape_string($name_bad);
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection:
" . $query_bad . "";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection:" . $query_evil;
.The previous code displays:
Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''
Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:
- Bad: \’ OR 1\’
- Evil: \’; DELETE FROM customers WHERE 1 or username = \’
And I don’t think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!
[…] processing code. Magic quotes would automatically escape risky form data that might be used for SQL Injection with a backslash . The characters escaped by PHP include: quote ‘, double quote “, […]
[…] earlier post introduces the concept of SQL-injection lets practice a little so that your understand the security risks that you have to face when […]