Database Injection alert has become a pain in the back lately, after the resent SQL injection against MySQL main website, I start researching about Data Injection myself and I found a lot information that might be helpful for many my fellow designers and coders.
What’s A Data Injection?
If it’s the first time you heard about Database Injection let me explain it. It is refer to an attempt to gather information from the database using any form in your website. For example, if you have a log in system obviously you have a User and Password form when submitted your visitors has access to privilege data.
How the attempt is done? I can explain it using PHP/MySQL samples. I will explain this as you have some knowledge in MySQL and PHP. Normally when you code a php program you need to set a variable with the MySQL query then execute an mysql_query function to process it. For example
<?php $sql="SELECT * FROM `database_table` WHERE `user` = '".$user."' AND `password` = '".$password."';"; $qry = mysql_query($sql); $rev = mysql_num_rows($qry); ?>
At first this is a simple SQL method to select the information needed, if the query result is more that 0 then you execute your code. Now, if I want to hack the system then I need to change the query. PHP doesn’t allow the execution of more than two queries at a time, what this means if the variable has two queries the mysql_function will execute the last query after the semicolon symbol.
<?php $sql = "SELECT * FROM `database_table` WHERE `user` = '".$user."' AND `password` = '".$password."'; SELECT * FROM `database_table`;" ; $qry = mysql_query($sql); $rev = mysql_num_rows($qry); ?>
In this case the query will only execute the second query where there is no WHERE specifications. Knowing this, if I could change the variable value to make it look like it has two queries then I can make it execute the query I want and retrieve the information that I want. This could be done through the fields of a form.
As you see you need to insert a closing single quote to close the original quoted statement then closing the first query with a semicolon and start the query you want to execute. Then you need to finish the query with an statement that require opening a quote with a single quote that’s going to be closed with the original variable value that was created first, for example, what I entered in the field will change the $sql variable to:
<?php $sql = "SELECT * FROM `database_table` WHERE `user` = '".$user."'; SELECT * FROM `users` WHERE 1 = 1 OR `user` = 'admin' ' AND `password` = '".$password."';" ; ?>
Now I have the query doing what I want it to do it and the I can even create a new $sql variable or execute another code I want it to. When WHERE 1=1 is used the database will return the value of the first row or all the information in the database. I’m not pretty sure of this but as far as I understand that’s how it works and always return value 1.
Now imagine if instead of SELECT statement is used it is use UNION, UPDATE or even DELETE, what can a hacker do with your information?.
How to prevent this?
One thing I know for sure is that we will never be able to counter attack all hacks done to our website unless you’re a hacker yourself, but every day a new way of hack is created and it’s almost impossible to detect new hacks or keep up the pace.
But not doing nothing against this is extremely dangerous and can make you loose clients if they get sued by an user that his information has been exposed.
One of the things that a hacker prefers in queries is when the query is executed before any field validation. Field validations are necessary and clearly can save you some headaches. The data validation need to be made before the query not after, if the query is done the hack done in the field will exposed the information either the data is validated or not.
Validate your data using the substr_count() function or with JavaScript whether you prefer. Note: Validating the data with JavaScript give you the advantage of processing the data even before it’s send to the server-side script.
For example of a PHP function would be this:
Create a function that process any string used in a MySQL query:
<?php function injection($str){ $bad = array("SELECT","INSERT","UPDATE","UNION","DROP" [... and on and on!]); $result = true; foreach($bad as $b){ if(substr_count($str,$b) > 0){ $result = false; break; } } return $result; } ?>
This is a very weak function but this give you an idea in how to counter attack this kind of injections. A better function would have all possible options or statements either upper or lower case, or even better, using RegExp (regular expressions) to validate the value of a variable.
The Data Injection can be lethal to a website and to your customers and is something you need to take care about if you own a user/log in environment or any kind of web-base network.
PHP Function mysql_real_escape_string
Update for 12/06/2011
Another way to prevent SQL Injection is using the php function mysql_real_escape_string() which is designed specially to filter out any information that’s going to be sent out to your database. Of course you need to prevent XSS intrusion filtering out whatever you output from your database.
The mysql_real_escape_string function can be used as follow:
$sql = sprintf("SELECT * FROM data WHERE user = '%s' AND password = '%s'",
mysql_real_escape_string($_POST['user']),
mysql_real_escape_string($_POST['password'])
);
$qry = mysql_query($sql) or die("Unable to process!");
First I used a preformatted SQL statement with some variables, this variables are filtered out by the escape function. If you’re using another programming language you might want to search for an alternative to this function.
Somethings you need this function requires:
- The mysql connection has to precede it
- Is only intended to filter anything that goes to the database
- Should not be used as a function to filter out what’s retrieved from the database
To prevent HTML intrusion (known as XSS) you need to filter any string that’s going to be output to the user. For more information about XSS visit this link. There’s a lot of information out there… Time to clean up your codes!!!
PHP Function mysqli_real_escape_string
Update for 1/25/2012
As is known that mysql functions are going to be deprecated you can also use mysqli_real_escape_string($conn,$txt) or mysqli->real_escape_string($txt) the only difference is in the procedural process where you need to specify which connection your using:
$conn = mysqli_connect($host,$user,$password,$database); $string = mysqli_real_escape_string($conn,"Filter this text' where 1'");
Better practice is to use the object format:
$db = new mysqli($host,$user,$password,$database);
$db->real_escape_string("Filter this text' where 1'");
Hope this help, happy coding!