SQL injection happens when user provided input through forms or query string is directly used in a SQL query without any sanitation done to it. For example a badly coded login script would allow an attacker to login without knowing the username/password or login with full rights of admin users. Though this can happen to any web page developed in any language, this issue seems to be affected more PHP pages than .NET applications. Perhaps simplicity of PHP programming and inexperience coders are reasons for this. In any case, keeping following poins in mind while developing PHP sites should help avoid mistakes which can be prevented easily.

Note: these points or things to consider are nothing new and you will find this repeated in various flavors around the web. However these simple and obvious factors are most and easily overlooked.

Validate user input.

Yes I am sure we have heard this again and again however this is the main thing to consider if you want to avoid buggy software. What exactly does “validate user input” mean?

Validating user input means that insuring that the user provided value is in the form expected by the program for it’s intended use.

Does a email address look like a email address? Does the username has characters which you don’t allow in a username? Does the id of the article to display retrieved using query string is actually an integer? And so on. Performing these checks wil greatly reduce the risk of SQL injection and incorrect program behaviour. For example here is how a login page might be coded leaving it open for SQL injection.

$res=mysql_query(“select * from users where user_name = ’{$_POST[‘username’]}’ and password = md5(’{$_POST[‘password’]}’);”);

Providing ”’ or 1=1#” without quotes in the username field on the login screen will enable hackers to gain access to the protected areas of your website. The simple yet often forgotten thing to do here is “validating user input!”.
Here is a regular expression which will make sure that only alphanumeric characters are in the username variable.

if(!preg_match(’/^([A-Za-z0-9]{3,20})$/’,$_POST[‘username’]))
die(“Error!”);

Certainly you will have to modify this code to fit your situation however the example show how you should validate the user provided data always. For validating int values you can run them through intval() function as follows.

$aid=(isset($_GET[‘id’]))?intval($_GET[‘id’]):0;

This will ensure that the id variable is an int if not, 0 will be used instead.
Also note that the $_POST[‘username’] might throw a notice if notices are enabled. For avoiding it see the second example above how I used isset() function to check if the id exists before accessing it.

Sanitize parameters to SQL queries

After verifying that the input is reasonablly secured, next step is to sanitize the variables which will form a part of the SQL queries. The best way here is to use parameterized query if your database or your DB library supports it. From PHP5 onwards, the Improved MySql extension (MYSQLI) is available and you should use it with prepared statements as far as possible. Here is an example of the same login SQL code using mysqli. Note that the code required for connecting to the database etc is not shown.

$stmp=$con->prepare(“select * from users where user_name = ? and password = ? ”);
$stmp->bind_param(“ss”,$_POST[‘username’],md5($_POST[‘password’]));
$stmp->execute();

This example shows how to mark parameters in your query using ”?” sign and how to bind arguments to it using bind_param() function. The first argument to bind_param() function is datatype specifyer which indicates the type of the params to bind. “s” stands for string, “i” stands for int etc. Here we are specifying that both our parameters are string and should be escaped properlly. Do read on about prepared statement in PHP manual and google it around for a bit.

If you can not use mysqli for any reason, you can still make your SQL more secure by using mysql_escape_real_string() oops mysql_real_escape_string().

$res=mysql_query(“select * from users where user_name = ’” . mysql_real_escape_string($_POST[‘username’]) . ”’ and password = ’” . md5($_POST[‘password’]) . ”’”);

Here we are using mysql_real_escape_string() function for properlly escaping the username variable. We don’t need to escape password as we are already hashing it so what ever user provides in password field can not be harmful.
Note that value of magic_quotes_gpc has an impact on forms value received. Ideally you should disable magic_quotes_gpc and use proper validation methods to ensure data security. If not, the php manual page on mysql_real_escape_string() has a nice function showing how to avoid issues.

Summary

The first thing to do while protecting your application against atacks is to properlly validate user input and make sure that user input doesn’t contain any harmful values. Next we should use proper escapeing on these variable using prepared statements or using old mysql_real_escape_string() function. Doing this where ever you accept user input and use it in a query will help you avoid SQL injection attacks.