Protect MySQL database against SQL Injection in PHP applications

SQL Injection is one of the major security risks of PHP applications if it is not written properly without giving much attention to SQL query code in PHP.
SQL Injection means sometimes hackers can execute SQL statements and crash the database from client browser and steal the valuable data. Sometimes even destroy database completely.

For understanding what is SQL Injection, look at the sample code below.

Code:

<?php $db_name="dev";// Database name $connection=mysql_connect("localhost","root","") or die("I couldn't connect"); $db=mysql_select_db($db_name) or die("I couldn't select your database"); $SQL = �select * from customer where id=�.$_Get[�id�]; Mysql_Query($SQL, $connection); ?>

This script is written for executing a SQL statement to select the entire data from customer table where id is equivalent to the value of the query string that is $_Get[�id�] which is passed through URL query string.


This statement can create problem if hackers change the value of the query string to something like �0;Drop Table Customer;� ? And query will become Select * from Customer where id=0; Drop Table Customer;


Here is another possible way of hacker attack.

Code:

<? $SQL = �select * from Users WHERE Username='$_Get[�uname�]' and Password='$_Get[�pwd�]'�; Mysql_Query($SQL, $connection); ?>

If hackers changed the string '$_Get[�uname�]' and '$_Get[�pwd�]' values to something like ' or ''=', the query will become SELECT * FROM Users WHERE Username='' or ''='' and Password = '' or ''=''

The result of the above statement can be very dangerous.
This query may return all the rows and the hackers can see all the data from the table. Just imagine if hackers see the important data such as credit card details etc...

Here are some of the tips for safe execution of SQL queries.

If the column �id� of the customer table in the first example is a numeric column then following check can be placed in PHP code.

Code:

if (!is_numeric($_GET['id'])) {    // if the id is not numeric stop execution of the page before query \\can run die("The id must be numeric!"); }

PHP has a built in function called is_numeric() which returns true if the value passed in it is a number.

PHP has another built in feature called Magic Quotes. When this is enabled, PHP will automatically escape all backslashes (\), double-quotes ("), single-quotes (') and NULL characters passed through the $_GET and $_POST variables. This should make the strings safe for using against database. By default this feature is enabled in the server. If this is disabled, you may want to make it enabled. To see if magic quotes are enabled, you can use the get_magic_quotes_gpc() function, which will return true if magic quotesare enabled, otherwise false. If this is disabled, you can use mysql_real_escape_string() function to escape special characters in a string. This function can be used only if back end database is MySql. Addslashes() function in php is also used for this purpose. But many Developers recommend mysql_real_escape_string(), because this function is more safer than all even than magic quotes itself.


Mysql_real_escape_string() will substitute single quotes(�) as an escaped quote(\�).

By using it the sql query in above example will look like SELECT * FROM Users WHERE Username='\' or ''='\' and Password = '\' or ''='\'

Here the hacker inputs have been escaped with a backslash \ thus it saves from SQL Injection.

When using the techniques mentioned above, all of your quotes and backslashes will be escaped (meaning extra backslashes will be displayed). When u retrieve those values from the database later in a web page use stripslashes() function in php for remove those extra backslashes.

Here is a best and safe practice code in php.

Code:

<?php // Function for safety function quote_for_safety ($value) { if (get_ magic_quotes_gpc()) {        $value = stripslashes($value);   //if magic quotes is enabled remove those extra slashes } // if the value passed is not integer Quote it if (!is_numeric($value)) { $value = "'" . mysql_real_escape_string($value) . "'"; } return $value; } $db_name="dev";  // Database name $connection=mysql_connect("localhost","root","") or die("I couldn't connect"); $db=mysql_select_db($db_name) or die("I couldn't select your database"); // Make a safe query $SQL = *sprintf("SELECT * FROM users WHERE user=%s AND password=%s", quote_for_safety($_POST['username']), quote_for_safety($_POST['password'])); Mysql_Query($SQL, $connection); ?>

sprintf() is used to format data and put it into a string array. It is equivalent to printf function in �c� language which includes escape sequences and format identifiers.

Hope this article will help you. Please post your comments and suggestions.

Thank you