Mark Biek Mark Biek - 1 year ago 92
PHP Question

Are PDO prepared statements sufficient to prevent SQL injection?

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 Source

Prepared statements / parameterized queries are generally 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 is usually easier for attackers to social-engineer their way in.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design — which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql — prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

*It turns out that MySql/PHP is (okay, were) just dumb about handling parameters when wide characters are involved, and there is still a rare case outlined in the other highly-voted answer here that can allow injection to slip through a parameterized query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download