Mysql IN clause parameterization in PHP, Python

Question

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.

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
– Xorifelse, Commented Mar 3, 2016 at 21:30

Barmar · Accepted Answer · 2016-03-03 21:29:39Z

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.

answered Mar 3, 2016 at 21:29

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MonkeyZeus · Accepted Answer · 2016-03-03 21:42:07Z

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.

answered Mar 3, 2016 at 21:42

MonkeyZeus

20.8k4 gold badges41 silver badges83 bronze badges

Collectives™ on Stack Overflow

Mysql IN clause parameterization in PHP, Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related