SQL Injection and its prevention in PHP

What is SQL injection?

SQL injection is one of the common attacks in web industry which uses SQL statements in the web form to execute a query in the wrong way and make attacks like making an unauthorised login or making wrong access to the data in the database. SQL injection usually occurs when users are asked for inputs through the HTML forms.

Let us see a common example of SQL injection. Suppose we have a user login form that asks for their username and password. We use select statement for validating the username  which is stored in the database.



Code:

Proper user input:

$uname= "Harry";

$query= "SELECT * from tbl_users where username='$uname'";

//query that is executed

$query= "SELECT * from tbl_users where username='Harry'";

SQL injected user input:

$uname="' or '1=1";

$query= "SELECT * from tbl_users where username='$uname'";

//query that is executed

$query= "SELECT * from tbl_users where username='' or '1=1'";

 The above SQL statement satisfies the where condition and will allow the intruder to make an unauthorised login into your application.

How to prevent SQL injection?

The root cause for all SQL injection attacks are quotes (' '); therefore it is very important to escape these quotes while getting string inputs from the users. In PHP 4, there were no such functions to escape these quotes apart from setting magic_quotes_gpc ON in the PHP configuration file which does the same task as done by addslashes() function does. Since, the setting was for all files, it would cause a big problem during other tasks as each and every quote would be escaped.

To fix this problem, PHP removed magic quotes from PHP 5.4.0 and introduced a new function called mysql_real_escape_string(). This function replaces troublesome single quote (') with slash and quote (\').

Therefore we can use this function in our above code to prevent SQL injection as follows:

$uname="' or '1=1";

$uname=mysql_real_escape_string($uname); // value of $uname will be \' or \'1=1

$query= "SELECT * from tbl_users where username='$uname'";

//query that is executed

$query= "SELECT * from tbl_users where username='\' or \'1=1'";

This is one of the easiest solution to prevent SQL injection and make your website safe.