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 id's 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 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.

Answer 2

Please take #2 off the table. Prepared statements are the only way you should consider protecting yourself against SQL injection.

What you can do, however, is generate a dynamic set of binding variables. i.e. don't make 100 if you need 7 (or 103).

Answer 3

decent sql wrappers support binding to array values. i.e.

$sql = "... WHERE id IN (?)";
$values = array(1, 2, 3, 4);
$result = $dbw -> prepare ($sql, $values) -> execute ();

Answer 4

If you're only using integer values in your IN clause, there's nothing that argues against constructing your query dynamically without the use of SQL parameters.

function convertToInt(&$value, $key)
{
    $value = intval($value);
}

$ids = array('12', '45', '65', '33');
array_walk($ids, 'convertToInt');
$sql = 'SELECT age, name FROM people WHERE id IN (' . implode(', ', $ids) . ')';
// $sql will contain  SELECT age, name FROM people WHERE id IN (12, 45, 65, 33)

But without doubt the solution here is the more general approach to this problem.

Answer 5

i got my answer from: http://bugs.php.net/bug.php?id=43568 this is my working solution to my problem. Now i can dynamically use as many parameters as i want. They will be the same number as i have in an array or as in this case I am passing the ids from the last query ( which found all the ids where email = '[email protected]') to the dynamic query to get all the info about each of these id no matter how many i end up needing.

$paramtype=implode('',array_fill(0,$NumofIds,'i')); // = ii
//make the array to build the bind_param function
$idAr[] = $paramtype; //'ii' or how ever many ?'s we have
while($statement->fetch()){ //this is my last query i am getting the id out of
$idAr[] = $id; }
//now this array looks like this array:
//$idAr = array('ii', 128, 237);

$query = "SELECT id,studentid,book_title,date FROM contracts WHERE studentid IN ($parameters)";
$statement = $db->prepare($query);
//build the bind_param function
call_user_func_array (array($statement, "bind_param"), $idAr);
//here is what we used to do before making it dynamic
//statement->bind_param($paramtype,$v1,$v2);
$statement->execute();