Home > DeveloperSection > Forums > Are PDO prepared statements sufficient to prevent SQL injection?
lois waisbrooker
lois waisbrooker

Total Post:70

Points:494
Posted on    April-17-2013 1:59 AM

 PHP PHP 
Ratings:


 1 Reply(s)
 1894  View(s)
Rate this:
Hi All!

Let's say I have code like this:

$dbh = new PDO("blahblah");

$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );
The PDO documentation says

The parameters to prepared statements don't need to be quoted; the driver handles it for you.

Is that truly all I need to do to avoid SQL injections? Is it really that easy?

You can assume MySQL if it makes a difference. Also, I'm really only curious about the use of prepared statements against SQL injection. In this context, I don't care about XSS or other possible vulnerabilities.

Thanks in advance! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    April-17-2013 9:11 AM

Hi Lois!

Prepared statements / parameterized queries are sufficient to prevent 1st order injection on that statement. If you use un-checked dynamic sql anywhere else in your 

application you are still vulnerable to 2nd order injection.

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see 

real 2nd order attacks, as it usually easier to social-engineer your way in.

One way to accomplish a 2nd order injection attack is when a value stored in a database is then used as a literal in a query. This isn't the best example, because it 

could still be stopped by a prepared statement, but the concept still applies.

Let's say you put this value in a text box that's expecting a name (assuming MySQL DB for this question):

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '
A prepared statement will make sure that select query doesn't run at the time of insert, and store the value correctly in the database. But if later on the name is 

retrieved and then used a literal in another query you'll get to see someone else's password. And since the first few names in users table tend to be admins, you may 

have just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

Again, this particular example isn't that great, but I try not to spend too much time thinking up real attacks, and even if I did I wouldn't post it straight up in public.


Don't want to miss updates? Please click the below button!

Follow MindStick