MySQL Prepared statements with a variable size variable list

Question

How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:

SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33) 

The IN clause will have a different number of ids each time it is run.

I have two possible solutions in my mind but want to see if there is a better way.

Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.

Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.

Answer 1

I can think of a couple solutions.

One solution might be to create a temporary table. Do an insert into the table for each parameter that you would have in the in clause. Then do a simple join against your temporary table.

Another method might be to do something like this.

$dbh=new PDO($dbConnect, $dbUser, $dbPass); $parms=array(12, 45, 65, 33); $parmcount=count($parms);   // = 4 $inclause=implode(',',array_fill(0,$parmcount,'?')); // = ?,?,?,? $sql='SELECT age, name FROM people WHERE id IN (%s)'; $preparesql=sprintf($sql,$inclause);  // = example statement used in the question $st=$dbh->prepare($preparesql); $st->execute($parms); 

I suspect, but have no proof, that the first solution might be better for larger lists, and the later would work for smaller lists.

---

To make @orrd happy here is a terse version.

$dbh=new PDO($dbConnect, $dbUser, $dbPass); $parms=array(12, 45, 65, 33); $st=$dbh->prepare(sprintf('SELECT age, name FROM people WHERE id IN (%s)',                           implode(',',array_fill(0,count($parms),'?')))); $st->execute($parms); 

---

Answer 2

There is also the FIND_IN_SET function whose second parameter is a string of comma separated values:

SELECT age, name FROM people WHERE FIND_IN_SET(id, '12,45,65,33')