So today we will discuss about Prevention of SQL Injection Vulnerability and methods of protection against it. Most of the developers came across this issue and want to patch the Vulnerability so this article is based on protection of SQL-i.
MySQL – SQL injection prevention
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 lesson 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 is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).
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:
Display:
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!
More serious sql injection attacks
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;
Display:
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 aMySQL-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: <br />" . $query_bad . "<br />"; $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: <br />" . $query_evil;
Display:
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 = ”
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 = ‘
Video – tut
No comments:
Post a Comment