PHP PDO MySQL IN (?,?,?

Question

I want to write a MySQL statement like:

SELECT * FROM someTable WHERE someId IN (value1, value2, value3, ...)

The trick here is that I do not know ahead of time how many values there will be in the IN().

Obviously I know I can generate the query on the go with string manipulations, however since this will run in a loop, I was wondering if I could do it with a PDO PreparedStatement.

Something like:

$query = $PDO->prepare('SELECT * FROM someTable WHERE someId IN (:idList)');
$query->bindValue(':idList', implode(',', $idArray));

Is that possible?

Tomalak · Accepted Answer · 2010-05-11 21:58:04Z

6

This is not possible the way you try it. You must have a separate placeholder for every parameter you want to pass in, everything else would defy the purpose of parameters (which is separating code from data).

$ids = array(2, 4, 6, 8);

// prepare a string that contains ":id_0,..,:id_n" and include it in the SQL
$plist = ':id_'.implode(',:id_', array_keys($ids));
$sql   = "SELECT * FROM someTable WHERE someId IN ($plist)";
// prepare & execute the actual statement
$parms = array_combine(explode(",", $plist), $ids);
$stmt  = $PDO->prepare($sql);
$rows  = $stmt->execute($parms);

If you were allowed to pass in an array of values to a single parameter during bind, you would effectively be allowed to alter the SQL statement. This would be a loophole for SQL injection - nothing could guarantee that all array values would be innocent integers, after all.

edited May 11, 2010 at 21:58

answered May 11, 2010 at 21:51

Tomalak

339k68 gold badges547 silver badges635 bronze badges

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

1 Comment

Tomalak Over a year ago

@nute: Well, if it works... Maybe PDO has a special feature for this (I don't know). I recommend reading the documentation on this very thoroughly since it really is a potential entry point for SQL injection if there are less parameters defined than bits of data you put in.

Collectives™ on Stack Overflow

PHP PDO MySQL IN (?,?,?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related