4

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

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
alexx0186
  • 1,557
  • 5
  • 20
  • 32
  • possible duplicate of [Are PDO prepared statements sufficient to prevent SQL injection?](http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection) – meze May 17 '12 at 12:27

3 Answers3

8

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.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • It's not just identifiers though; the same is obviously true for values which aren't passed as parameters (as @DaveRandom's answer suggests). – eggyal May 17 '12 at 12:26
  • +1 this is better than mine. IIRC, you couldn't use prepared statements to safely escape your second unsafe case, as it only works for values and not object names. If you really must allow user input in field/table/database/etc names, you need to backquote them and manually do a `strpos()` type thing to detect malicious input. I think. Although I could be wrong. – DaveRandom May 17 '12 at 12:27
  • Thought that's obvious - I've added an example for it anyway. @DaveRandom: Yeah, but if you need dynamic column names you better use whitelisting anyway. – ThiefMaster May 17 '12 at 12:27
  • Hi everyone, thanks for your responses. So I guess the safe way of doing it is to replace `".$_GET['other'] ."` by `?` and pass `$_GET['other']` as a parameter? Thanks – alexx0186 May 17 '12 at 12:31
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().

DaveRandom
  • 87,921
  • 11
  • 154
  • 174
0

It means don't be lured into thinking PDO is magic pill...if you don't use prepared statements, you will still be vulnerable.

blockhead
  • 9,655
  • 3
  • 43
  • 69