How to prevent php code from sql injection

Prevent PHP Website From SQL Injection

I am presenting a small article which explains basics of SQL Injection with an example that shows SQL Injection, and provides methods to prevent from these attacks.

SQL Injection, wow sounds great..but its a serious issue for any web application. We've all heard the term. But what exactly it is?

One fine day PHP got infected with some disease, so PHP's daddy Rsamus Lerdorf called up some doctors to cure the disease of PHP. So then the doctors came and gave an injection called SQL Injection. Hahahahah..ROFL..Its not SQL Injection. I am just kidding. Don't take it serious. Now lets see what actually SQL Injection is?

What is SQL Injection?

As the name suggests, this is an attack which can be done with SQL queries. Many web developers are unaware of how an attacker can attack with the SQL queries. SQL Injection can be done on a web application which doesn't filter the user inputs properly and trusts whatever the user provides. The idea of SQL injection is to make the application to run undesired SQL queries.

Many PHP developers don’t think about this, assuming that PHP and mySQL take care of everything to make your code bullet proof. This is unfortunately not the case – or is it? PHP has built-in functions which allow you to sanitize and secure your code and queries, but they’re not automatic – you have to call them manually.

What can we do with SQL Injections?

Insert malicious data into the MySQL table
Retrieve all the MySQL table data without any authentication
Delete the MySQL table data.

Now let's see what an SQL Injection attack is and how to prevent it in our application with an example.

For better understanding of this issue, lets take one login form. We all know what a login form contains. Simply it contains username, password and a submit filed in general. When submitted, the form values are passed through the $_POST variable and received and processed.

Before going to the example first of all create a table in the database "test" for testing purpose. Lets see the table structure.

SQL Code:

CREATE TABLE IF NOT EXISTS `member` (
  `username` varchar(25) NOT NULL,
  `password` varchar(25) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Dump some duplicate data for testing.

INSERT INTO `member` (`username`, `password`) VALUES ('phphunger', '123456');

login.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SQL Injection Demo by PHP Hunger</title>
</head>
<body>
<form name="frm_login" method="post" action="sqlinjection.php">
Name : <input name="name" /><br />
Password : <input name="pwd" /><br/>
<input type="submit" value="Login" />
</form>
</body>
</html>

An attack happens when a hacker or intruder enters some malicious code in values that you don’t plan for and through that, tricks SQL into doing all the evil things. Lets take the sqlinjection.php form which processes the given input values by the user.

sqlinjection.php

<?php
$Connect = mysql_connect("localhost","root",""); 
if (!$Connect) {
 echo "Database Connection Failure";
 exit;
} 
mysql_select_db("test", $Connect); 
$name = $_POST['name'];
$pwd  = $_POST['pwd'];
$str_sql = "SELECT * from member WHERE " .
           "username='" . $name . "' AND " .
           "password='" . $pwd . "'";
$result=mysql_query($str_sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
echo "Login Success...";
}else {
echo "Wrong Username or Password";
}
?>

Assume if the attacker entered the credentials as phphunger and 123456 in the previously mentioned form, the SQL statement would look like this:

SELECT * FROM member WHERE username=`phphunger` AND password=`123456`;

But assume what if the attacker entered something different..what if for the password field, they entered the following:

123456' OR 1='1'

The SQL statement would then look like this:

SELECT * FROM member WHERE username='phphunger' AND password='123456' OR 1='1';

The 1=1 would always be true, so by these techniques the hacker can get all information without knowing of username also and can delete the records also.

So the million dollar question awaits...

How to prevent php code with SQL Injections?

There are a variety of ways to prevent SQL Injections. Lets discuss some below.

Method 1 : Using Escaping

Sanitizing your $_POST variables is very easy, and is a good beginning towards developing secure code. In PHP, I recommend at the very least of using the PHP's pre-defined function called mysql_real_escape_string().

mysql_real_escape_string() : Escapes special characters in a string for use in an SQL statement.

$name = mysql_real_escape_string($_POST['name']);

$pwd = mysql_real_escape_string($_POST['pwd']);

Method 2 : Using Parameterized Statements

To prevent against SQL Injection attacks, user input must not directly be embedded in SQL statements. Instead, you can use parameterized statements(Prepared statements) to check user input and storage procedure.

Huh..these are some of the preventive measures you should consider for preventing your application with SQL Injections. As a thumb of rule, i always validate my user data before submitting to the SQL Server.

My Advice:

Never trust any kind of input, especially that which comes from the client side, even though it comes from a select box, a hidden input field or a cookie. Mainly never ever trust your user. Keep this in mind. Happy coding...