Why does PDO manual say that SQL injection is still possible with PDO?

Question

http://php.net/manual/en/pdo.prepared-statements.php

If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

What are the possible scenarios where some of the input is unescaped? Is that even possible if all the other input goes into the database using PDO?

I'm thinking of the scenario where other input is processed with mysql_* functions and not escaped with mysql_real_escape_string. Is there anything else that could be a threat?

Thanks a lot. Regards

Answer 1

It means you cannot use untrusted values directly e.g. as a column or table name - or as a LIMIT parameter.

For example, this is safe:

$query = "SELECT * FROM tbl WHERE col = ?";

while these aren't:

$query = 'SELECT * FROM tbl WHERE col = ? LIMIT ' . $_GET['limit'];
$query = 'SELECT * FROM tbl WHERE ' . $_GET['field'] . ' = ?';
$query = "SELECT * FROM tbl WHERE col = ? AND othercol = '" . $_GET['other'] . "'";
$query = 'SELECT * FROM ' . $_GET['table'] . ' WHERE col = ?';

Basically, prepared statements' placeholders are meant to be used in places where you would have used an escaped value within single quotes in a classical query.

In case you wonder why databases usually do not support placeholders for things like table names: Besides the fact that dynamic table/column names are not that common, the database engine usually optimizes a prepared statement when it's prepared. This however cannot be done properly without knowing exactly which tables/columns are accessed.

Answer 2

Consider this:

$sql = "SELECT * FROM ".$_GET['tablename']." WHERE somecol = ?";

Because I populated the table name with un-escaped user input, it would be possible to pass in for example public_table p LEFT JOIN hidden_table h ON h.id = p.id and get results you didn't want me to, even though you have escaped the value passed to the somecol comparison.

The point is that while prepared statements safely escape any user input you pass to a ? in the query, they can't escape data that already existed in the string before you passed it to prepare().