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 #1 (513 Votes)
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 engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign
' characters or similar.
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 all access to a database within an 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 are (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.
Answer #2 (44 Votes)
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']) );
Note: you can't use PDO to bind data that goes outside of DDL (Data Definition Language), i.e. this does not work:
$stmt = $dbh->prepare('SELECT * FROM foo ORDER BY :userSuppliedData');
The reason why the above does not work is because
ASC are not data. PDO can only escape for data. Secondly, you can't even put
' quotes around it. The only way to allow user chosen sorting is to manually filter and check that it's either