Are PDO prepared statements sufficient to prevent SQL injection?

Question

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.

Answer 1

Personally I would always run some form of sanitation on the data first as you can never trust user input, however when using placeholders / parameter binding the inputted data is sent to the server separately to the sql statement and then binded together. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement.

Answer 2

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 3

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

Parameterised queries work by sending the code and the data separately, so it would never be possible to find a hole in that.

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

Answer 4

As JimmyJ said, you still have to sanitize your data ! With PDO, you can still save something like <?php phpinfo();?> in your database. Ok, this example won't harm anything, but it could be anything else instead of phpinfo.

Answer 5

sorry, wrong place

Answer 6

No, they are not always.

It depends on whether you allow user input to be placed within the query itself. For example:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

would be vulnerable to SQL injections and using prepared statements in this example won't work, because the user input is used as an identifier, not as data. The right answer here would be to use some sort of filtering/validation like:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];
$allowedTables = array('users','admins','moderators');
if (!in_array($tableToUse,$allowedTables))    
 $tableToUse = 'users';

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

The examples I gave may not be all that great, but I hope it shows my point. And situations like these are not impossible. Software like phpMyAdmin have to deal with identifiers.