How to protect your site from SQL injection

sqlinjection

What is SQL injection?

When someone is trying to run the unintended SQL query against your database is called SQL injection. There can be different intention to use SQL injection, some want to delete or modify the content of your database, while other may want to get the sensible data like passwords.

For example, if you have a login page in your application. If the user’s can’t remember their password, you also have a password retrieval page (retrieve_pwd.php) where the user can enter their email id and have the password send by email. After getting the correct mail id, the user is taken to the following page.

 

In this example there is a SQL statement like this in the retrieve_pwd.php page

$query=”SELECT * FROM tbl_user WHERE email='”$email”‘ “;

The intended behavior of this SQL is to select the user with the email “try@try.com”. However people with bad intentions can radically change this behavior by sending some additional information to the page. It can be possible by changing the URL.

Changing the URL to

retrieve_pwd.php?action=send&email=’;  DELETE FROM tbl_user WHERE 1 or email=’;

Suddenly gives the SQL a totally different meaning

$query= “SELECT * FROM tbl_user WHERE email= ‘”$email”‘ ; DELETE FROM tbl_user WHERE 1 or email='”;

So by using this one can delete the content of the table. Also by adding some other informations,  it is possible to access the restricted pages.

How to protect your PHP scripts from SQL injections?

1. Using function mysql_real_escape_string() , we can prevent the SQL injection completely.

The basic principle of the function is,  any unescaped special characters is automatically escaped, which means that backslashes is automatically added in a following way to the special characters: \R00, \n, \r, \, ‘, ” and \x2n.

By using this, there is no possible to inject extra SQL into the MYSQL queries.

Example code 

There are two possible ways to use mysql_real_escape_string(), one is by using PHP global function and another one is to use the mysql_real_escape_string() in each and everywhere while getting a input fields.

function protectSql($value)

{

return mysql_real_escape_string($value);

}

To ensure that no SQL injection is possible, you just need to add the following code each place where a POST value is used as input for at MySQL query, before the values are inserted into a SQL query.

$username=protectSql($_POST[‘uname’]);

Another way is without calling function, directly we can place mysql_real_escape_string() where a POST value is used as a input.

$username=mysql_real_escape_string($_POST[‘uname’]);

2.Using magic quotes function, we can prevent SQL injection.

Using magic quotes, all single_quotes, double_quotes and NULL characters can be escaped automatically by adding backslashes on it.

Three magic quotes directives are:

magic_quotes_gpc – It attacks HTTP request gps(GET, POST, COOKIE), it cant be set at runtime and defaults to on in PHP. We can also return the cuurent configuration settings by using get_magic_quotes_gpc

magic_quotes_runtime –  If enabled, most function return the data from an external source and that includes text file and database etc. Can be set at runtime, by default to off in PHP. We can also set and get the current active congiguration of the magic_quotes_runtime using set_magic_quotes_runtime() and get_magic_quotes_runtime().

magic_quotes_sybase – If enabled, the single_quotes are escaped with the single_quotes instead of adding backslashes. If on, it completely overrides the magic_quotes_gpc. If both the directives are enabled, only the single_quotes are escaped with ”. Hence, double_quotes and NULL characters are remain as same/unescaped.

Example code:

$blnMagicQuotesOn = get_magic_quotes_gpc();
if (isset($_REQUEST[‘name’])) {
$this->strName = trim($_REQUEST[‘name’]);
if ($blnMagicQuotesOn) {
$this->strName = stripslashes($this->strName);
}
}

3. Using htmlspecialchars

htmlspecialchars()- converts the special character into HTML entities.

If you use htmlspecialchars() while getting the user input, then it performs the translation for it. For example, if you use ampersand(&) it translated that into ‘&’.

Example code

<?php

$name=htmlspecialchar(trim($_REQUEST[‘name’]));

?>

Leave a Comment