ARE PDO PREPARED STATEMENTS SUFFICIENT TO PREVENT SQL INJECTION?

lois waisbrooker

Total Post:70

Points:494
Posted by  lois waisbrooker
PHP 
PHP
 2318  View(s)
Ratings:
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! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Are PDO prepared statements sufficient to prevent SQL injection?

    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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!