07/06/2011

SQL Injection Prevention

by Cyle
Categories: php
Tags: , , ,
Comments: Leave a Comment

Taking a raw user’s input and inserting it into a MySQL database can leave your website open to an attack known as SQL injection. Hopefully this tutorial will help you better understand what SQL injection is and how you prevent it from happening on your site.

What is SQL Injection?

“SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application (like queries).” (quoted from Wikipedia) Basically it is when someone inserts a MySQL statement to be run on your database without you knowing it. It is usually done when a user inputs data through a form. Instead of giving you their name or password in the form they insert a MySQL query command that executes on your database.

A Semi-Serious Example
<?php
// regular user, no attack
$name = "charlie"; 
$query = "SELECT * FROM customers WHERE username = '$name'";
echo "Regular User Query: " . $query . "<br />";
 
// bad user, SQL injection
$name_bad = "' OR 1'"; 
 
// now our query with the bad user's injection
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
 
// echo what the query looks like
echo "SQL Injection: " . $query_bad;
?>

Regular User Query: SELECT * FROM customers WHERE username = ‘charlie’
SQL Injection: SELECT * FROM customers WHERE username = ” OR 1”

The regular user query is fine. It will select everything from customers that has the name charlie. But, the SQL injection query will select every single entry in customers.

By using a single quote (‘) the bad user has ended our query and added OR 1. The single quotes makes it username=' ', which is fine, but when OR 1 is added it becomes username=' ' OR 1. There probably isn’t a username in customers that is blank, but OR 1 in a query is always TRUE! This will return every entry in customers.

A More Serious Example

Just like the last example where the user added an OR clause, he can also add any other query command. He could even delete all the entries.

<?php
// sql injection with a delete
$name_realbad = "'; DELETE FROM customers WHERE 1 or username = '"; 
 
// our query with the sql injection
$query_realbad = "SELECT * FROM customers WHERE username = '$name_realbad'";
 
// display the sql injected query
echo "Injection: " . $query_realbad;
?>

Injection: SELECT * FROM customers WHERE username = ”; DELETE FROM customers WHERE 1 or username = ”The first part of the query does the normal thing, it selects everything from customers where the username is blank. However, the second part is a whole new query in itself and a lot more destructive. It then deletes everything where 1 (always TRUE!). So it deletes our entire contents of customers. Read on to see how we can prevent this from happening.

Injection Prevention

mysql_real_escape_string()

SQL injection has been around for sometime and PHP has done something to prevent these attacks. It is the mysql_real_escape_string() function. Yes it is a pain to type out, but it is well worth it! The mysql_real_escape_string() takes a string that will be used in a MySQL query and returns the same string, but with the injection attempts safely escaped. The function scans the string and when it finds an attempt like a single quote it adds a backslash to escape it out.

mysql_real_escape_string() in Action
<?php
// the bad username
$name_bad = "' OR 1'"; 
 
// using the function to escape the attempts
$name_bad = mysql_real_escape_string($name_bad);
 
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Safe Bad Injection: <br />" . $query_bad . "<br />";
 
// the real bad username
$name_realbad = "'; DELETE FROM customers WHERE 1 or username = '"; 
 
$name_realbad = mysql_real_escape_string($name_realbad);
 
$query_realbad = "SELECT * FROM customers WHERE username = '$name_realbad'";
echo "Safe Real Bad Injection: <br />" . $query_realbad;
?>

Safe Bad Injection:
SELECT * FROM customers WHERE username = ‘\’ OR 1\”

Safe Real Bad Injection:
SELECT * FROM customers WHERE username = ‘\’; DELETE FROM customers WHERE 1 or username = \”

Notice how now all the malicious single quotes are escaped out with a backslash (\). The query now looks for a username that is just simply ridiculous: \' OR 1\' or \'; DELETE FROM customers WHERE 1 or username = \'.

The mysql_real_escape_string() function only works when it is used. It may be a lot to type out, but it is well worth it! Now that you have read this, there is no excuse why it should not be used.


Leave a Reply

Your email address will not be published. Required fields are marked *



user-avatar
Today is Sunday
03/26/2017