Preventing SQL Injection
Many web developers ignore the threat of SQL Injection, or are oblivious to it. It is one of the most serious security threats and can result in a corrupted, amended, viewing of sensitive information or even a deleted database. Thankfully, it is fairly easy to protect websites from SQL injection attacks.
What is a SQL injection attack?
Most modern websites are dynamic and source a lot of the content from databases. The database query is often made, in part, from a variable that is the result of user interaction. This is normally in the form of a $_GET or $_POST. The attacker can inject valid SQL code into the database query via a $_GET or $_POST variable.
Example of a SQL injection
The user has submitted a simple form and the variables are passed to a results page using the GET method. This includes the ID of the relevant result. The results page includes the following code:
$sql = mysql_query(‘SELECT * FROM mytable WHERE id = $id’);
if(!$sql)
{
exit('Error retrieving from database!' . mysql_error()) ;
}
A malicious attacker could insert the following into the idvariable:
Therefore, dropping your table!
The solution
Add the following function to your functions file. If you do not have a functions file, then create a separate php file and add the function. Make sure you include the function in any page that requires it.
{
if( get_magic_quotes_gpc() )
{
$value = stripslashes( $value );
} //check if the mysql_real_escape_string function exists
if( function_exists( "mysql_real_escape_string" ) )
{
$value = mysql_real_escape_string( $value );
} //for PHP version prior to 4.3.0 use addslashes
else
{
$value = addslashes( $value );
}
return $value;
}
The above function checks to see if get_magic_quotes_gpc is turned on. The get_magic_quotes_gpc function will automatically escape POST,GET,COOKIE data, but is very unreliable. We therefore reverse its effect my using the stripslashes function.
The most effective method of protection to date is the mysql_real_escape_string function. Our function checks if it is available, and is so, it is used to escape special characters in the string for use in the SQL query.
Addslashes was used before mysql_real_escape_string was released with PHP version 4.3.0 and should be used if mysql_real_escape_string is not available.
Using the function
Using the original example, we would amend the code so that the user defined variables runs through the sql_secure function before the SQL query.
$safeid = sql_secure($id);
$sql = mysql_query(‘SELECT * FROM mytable WHERE id = $safeid’);
if(!$sql)
{
exit('Error retrieving from the database!'. mysql_error(); }
It is a good practice to validate any variable that it going to be used in SQL query in this kind of way. There are many other ways of achieving this, and you are welcome to try. They should all use the mysql_real_escape_string function.
Disclaimer Please use the function at your own risk! We offer no guarantee that it will prevent a SQL injection attack or any other security breach.
Learn how to write custom Drupal modules
Does Drupal module development make your head explode and drive you crazy?
Why not learn from someone who has paved the way instead?
Sign up to Master Drupal 7 Module Development.
I am 
Post new comment