1

I'm used to doing something like the following for my queries:

$array = array(123,456,10,57,1024,768); //random data
$sql = "select * from table where field in(".implode(',',$array).")";
mysql_query($sql);

This works just fine, and is fast even when the array may have 1,000 or more items to search for(effectively a join against a nonexistent table). For me, this is quite useful because I am joining data from multiple sources together.

However, I know it is somewhat insecure -- SQL injection is possible if I don't escape my incoming data correctly.

I'd like to try to figure out a parametrized equivalent of this; some way to do that sort of "array IN" using more "hardened" code.

It may not be possible to do this directly, it may require a helper function that does the replacements. If so, I'm looking for a good one I can drop into my code, hopefully something that will allow me to just pass an array as a parameter.

While my example is for PHP, I also need to figure this out for Python, as I'm doing the same thing there.

I've read some similar questions here, but haven't seen any (good) answers.

Macrobb
  • 23
  • 3
  • With PDO you can easily extend `PDOStatement` and add a method to build your SQL string for IN clauses. Shown in Barmar's answer. – Xorifelse Mar 03 '16 at 21:30

2 Answers2

5

Using PDO prepared statements:

$placeholders = str_repeat('?, ', count($array)-1) . '?';
$stmt = $pdo->prepare("SELECT * FROM table WHERE field IN ($placeholders)");
$stmt->execute($array);

$placeholders will contain a sequence of ?, ?, ? placeholders, with the same number of ? as the size of the array. Then when you execute the statement, the array values are bound to the placeholders.

Barmar
  • 741,623
  • 53
  • 500
  • 612
0

I have personally done:

$in = substr(str_repeat('?,', count($array)), 0, -1);

$sql = "SELECT * FROM table WHERE field IN ($in)";

This will provide you with ?, for each array element and remove the trailing comma.

MonkeyZeus
  • 20,375
  • 4
  • 36
  • 77