Prevention of Sql Injection with PHP

May 22nd, 2009 by divyang.shah § 15

Preventing SQL injection attack is becoming a headache now a days for many application developers and especially for web application developers. Lets explore a scenario where SQL Injection attack is most common and how we can avoid that.

We generally have a login screen with username and password in almost every web application.

Assume that attacker passes values like following:

user name = a’ or ‘t’='t
pass =  a’ or ‘t’='t

Generally we have query like following:
“select * from admin where name = ‘”.$_POST['user_name'].”‘ and psw = ‘”.$_POST['pass'].”‘”;

So after passing the value it will look like:
select * from admin where name = ‘a’ OR ‘t’='t’ and psw = ‘a’ OR ‘t’='t’;

If you see properly this will go true and return the first record of the database and set that into session for login and user will be in your system!! It’s so easy, isn’t  it?

To avoid this embarrassing situation there are many ways:

1) You can set ON magic quotes of your server. It is same as addslashes() function. It will be there in your php.ini file.

(NOTE : This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 6.0.0. Relying on this feature is highly discouraged.)

2) You can manually addslashes like following

$user_name = addslashes($_POST['user_name']);
$pass = addslashes($_POST['pass']);

and then pass these parameters into query like this
“select * from admin where name = ‘”.$user_name.”‘ and psw = ‘”.$pass.”‘”;

so now it will look something like this
select * from admin where name = ‘a’ OR ‘t’='t’ and psw = ‘a’ OR ‘t’='t’;

so now it will match whole word (a’ OR ‘t’='t) with database and will return empty result set.

3) If you want to do it in one query then use mysql_real_escape_string.
“select * from tbladmin where name = ‘”.mysql_escape_string($_POST['username']).”‘ and psw = ‘”.mysql_escape_string($_POST['pass']).”‘”;

mysql_escape_string is more appropriate then addslashes().

you can read that thing here

http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-re…

This will also provide the same result as point 2.

4) Another and best solution is Prepared Statements.

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent SQL injection attack.

mysql> PREPARE stmt_name FROM "SELECT name FROM Country WHERE code = ?";
//Statement prepared

mysql> SET @test_parm = "FIN";
// set the parameter

mysql> EXECUTE stmt_name USING @test_parm;
+---------+
| Name    |
+---------+
| Finland |
+---------+

mysql> DEALLOCATE PREPARE stmt_name;

Read more about prepared statement from this
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

5) If you don’t want to use any of the functions then you can break your query in 2 steps like this

“select * from admin where name = ‘”.$_POST['user_name'].”‘”;

So from this you will get first record from the database. Now check $_POST['pass'] with obtain result.

Like you store the query result into $result variable. So in $result[0]['pass'] you will get original password. So now check
if($result[0]['pass'] == $_POST['pass'])

And then do the remaining process.

So by using any of the above steps you can avoid Sql Injection in your login form.

But the best way is to use point 2 and 3 and 4 because if hacker enters a’;drop table users; select * from data where name like ‘%
in user name field then it will look something like this

select * from users where name = ‘a’;drop table users; select * from data where name like ‘%’;

and it will fire 3 queries at same time and it will drop your users table.

1st, 2nd 3rd and 4th points had good results but as you can see the note in 1st point and 3rd point has more advantage then 2nd point. So the 3rd and 4th points are best solution and for more security purpose you can use 3rd or 4th point together.

Like
$format=”select * from users where `user_password` = ‘%s’ and `user_name` = %s
$query=sprintf($format,mysql_real_escape_string($password), mysql_real_escape_string($user_name) );

Good practice is to use use 3rd or 4th point.

Same way you can use this method at any of the user input forms like in user registration or in add product form. Any of the place where you are taking input from user side you have to check the data first and then store that data into database. And same way you can use the htmlentities() function to avoid html code.

Do you know any more methods using which we can prevent it? Let us know.

Tagged: , ,

§ 15 Responses to “Prevention of Sql Injection with PHP”

What's this?

You are currently reading Prevention of Sql Injection with PHP at Digicorp.

meta

  • Author: divyang.shah
  • Comments: 15 Comments
  • Categories: php

Share