[SOLVED] PDO sql injections

[RichardRotterdam]

I was reading an article about prepared queries using the PDO class

http://www.devshed.com/c/a/PHP/Working-with-Prepared-Queries-with-PDO-Objects-in-PHP-5/1/

And it made me wonder about something. Just take a look at the following code

<?php
$name="banana";
$dbh=new PDO('mysql:host=localhost;dbname=webshop','********','***********');
$pquery=$dbh->prepare('SELECT * FROM product WHERE product_name=:name and category_id=:id');
$pquery->execute(array(
':name'=>$name,
':id'=>'1'
));
$result=$pquery->fetchAll();

print_r($result);
?>

 

in the where clause you see that the variable :name isn't between quotes

WHERE product_name=:name

 

the follwoing code changes :name to  "banana" and :id to 1

<?php
$pquery->execute(array(
	':name'=>$name,
	':id'=>'1'
));
?>

With this i tried to do an sql injection to see if it is secure and I couldnt get any error.

 

Now for my question.

Does using this prepared query mean I dont have to escape the string to prevent sql injections?

[GKWelding]

Although I'm not familiar with PDO myself, I know for a fact that prepared queries DO NOt protect against SQL injection. I promise you that.

 

Even if it does you should still sanitize all data JUST IN CASE... Adding mysql_real_escape_string() or addslashes(trim()) (<--- although I would use real_escape_string over this) isn't a big deal.

[discomatt]

mysql_real_escape_string() won't work with PDO... you'll have to establish a second connection to the DB using mysql_connect()

 

The code you use _SHOULD_ protect you from SQL injection ( prepared statements are supposed to be injection-safe ) but to be sure, simply try a basic injection yourself

[R0bb0b]

I don't see a difference between this:

<?php
$pquery=$dbh->prepare("SELECT * FROM product WHERE product_name='bananna' and category_id=1 and 1=1");
?>

 

and this:

<?php
$pquery=$dbh->prepare('SELECT * FROM product WHERE product_name=:name and category_id=:id');
$pquery->execute(array(
   ':name'=>$name,
   ':id'=>'1 and 1=1'
));
?>

 

they would produce the same results

[discomatt]

you're dealing with static values in the first, and variable data in the second. That's a BIG difference

 

Also, you may find injection easier with OR 1=1

 

Also also, your second query would execute to ( assuming $name = 'banana' )

 

SELECT * FROM product WHERE product_name='banana' and category_id='1 and 1=1'

 

One again, big difference

[R0bb0b]

OK, but I think you get the point.  How are you going to validate that ':id' is an integer if you do validate that ':id' is an integer.  I don't see how this protects against sql injection.

[RichardRotterdam]

ok using the following code

<?php
$name=$_GET['name'];
$id=$_GET['id'];
try{
   	$dbh=new PDO('mysql:host=localhost;dbname=xxx','xxx','xxx');
   	$pquery=$dbh->prepare('SELECT * FROM product WHERE product_name=:name and category_id=:id');
$pquery->execute(array(
	':name'=>$name,
	':id'=>$id
));
   	$result=$pquery->fetchAll();
   	// displays data for 'Alejandro'
   	print_r($result);


}catch(PDOException $e) {
   	echo 'Error : '.$e->getMessage();
   	exit();
}
?>

 

now i tried to do an injection by calling the following url

 

index.php?name=banana&id=1 or 1=1

and also tried

index.php?name=banana&id=monkey or 1=1

 

hmm no errors seems safe to me

[R0bb0b]

That's just one example where there are dozens.  My guess is that somewhere in the PDO class mysql_real_escape_string() is already being used.  It must single quote the values too, otherwise that should work.

[RichardRotterdam]

only one way to find out try a lot of injections and i tried using single quotes and double quotes for the injection

[discomatt]

OK, but I think you get the point.  How are you going to validate that ':id' is an integer if you do validate that ':id' is an integer.  I don't see how this protects against sql injection.

 

That's just one example where there are dozens.  My guess is that somewhere in the PDO class mysql_real_escape_string() is already being used.  It must single quote the values too, otherwise that should work.

 

Now you're starting to get it. Executing a raw query and using prepared statments properly are two totally different things

 

This article is still worth a read

http://www.webappsec.org/projects/articles/091007.shtml

 

Prepared statements won't protect you from injection in your initial query... so be careful when using variables there.

[RichardRotterdam]

after reading this

http://de2.php.net/pdo-prepare

you can see that the variables do get escaped automatically.

however it is totally true that doing a check with php is wise when working with numbers. I'll still use is_numeric for this purpose

[discomatt]

after reading this

http://de2.php.net/pdo-prepare

you can see that the variables do get escaped automatically.

however it is totally true that doing a check with php is wise when working with numbers. I'll still use is_numeric for this purpose

 

You should typecast ( use: (int)$var instead of just $var ) or use if ( ctype_digit((string)$var) ) or use $var = intval( $var ) over is_numeric()... which IMO isn't strict enough.

[RichardRotterdam]

thnx for that i didn't knew that