26

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.

Improve this question
4
  • I suspect I would avoid both of the solutions you suggest. Prepared statements make securing your code far easier. And your first solution seems pretty inefficient and wasteful if you have a small list. Commented Nov 29, 2008 at 9:40
  • I agree I thought those ideas might stimulate others minds. :) Commented Nov 29, 2008 at 12:17
  • For SQL Server, see Parameterizing an SQL IN clause? Commented Apr 4, 2012 at 12:02
  • For modern implementations of mysqli prepared statements with variadic bound parameters see: Use an array in a mysqli prepared statement: WHERE .. IN(..) query and mysqli bind_param for array of strings Commented Apr 3, 2022 at 20:09

7 Answers 7

Reset to default
25

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

i like your second suggestion. do it and forget about it until performance is an issue. at that point it might be worth investigating the first option.
If only I'd have thought of that! Your first solution sounds like the exact thing I was looking for.
I've used pattern #2 frequently. Perl's DBI has a prepare_cached() function, so if you queries with similar numbers of placeholders, it will reuse statement handles. Not sure about PHP though..
Creating a temporary table would be overkill unless the data set is huge. The sample code is overly complicated (half of those variables can be eliminated and the code can be greatly simplified by doing more of the work in-line). But the basic idea is a good one, using implode(',',array_fill(0,count($params),'?')) to generate the "?"s, then just pass the $params as the data to bind.
@orrd, meh... When I composed this answer I was trying to make verbose so it would be easy to understand. I agree, that it can be simplified, but I don't think the simplification you suggested would make the answer easier to follow, or have any significant impact on performance. In any case, I'll update my answer and add a compact version for you.
|
10

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')
Improve this answer

1 Comment

Problem with this is: it won't use the primary index, defaulting to a full table scan while executing FIND_IN_SET for every row.
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 ();
Improve this answer

3 Comments

I actually do not know about any native PHP database access library for MySQL (neither mysql, mysqli nor PDO) that allows for binding parameters of the array type.
back when i was developing in php few years ago, adodb did a really good job for me. i think you should check it out.
Any framework that does this is doing it by expanding the list and interpolating it into the SQL query before the prepare(). It's not the same as bound parameters.
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).

Improve this answer

3 Comments

what? that doesn't make sense. He is using prepared statements, but he's dynamically setting the number of placeholders.
In scenario #1, he was statically defining the query to take 100 parameters, in #2, he was not using a prepared statement. My suggestion was to dynamically build the query with bindings, which is the same thing you're saying.
whoops. I was reading #2 from stackoverflow.com/questions/327274/…. Sorry!
2

I got my answer from: http://bugs.php.net/bug.php?id=43568.
This is my working mysqli 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.

<?php $NumofIds = 2; //this is the number of ids I got from the last query
    $parameters=implode(',',array_fill(0,$NumofIds,'?')); 
    // = ?,? the same number of ?'s as ids we are looking for<br />
    $paramtype=implode('',array_fill(0,$NumofIds,'i')); // = ii<br/>
    //make the array to build the bind_param function<br/>
    $idAr[] = $paramtype; //'ii' or how ever many ?'s we have<br/>
    while($statement->fetch()){ //this is my last query i am getting the id out of<br/>
        $idAr[] = $id;  
    }

    //now this array looks like this array:<br/>
    //$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();
?>
Improve this answer

1 Comment

If this snippet is building a new query based on the result set of an earlier query, this is generally a symptom of a missed opportunity to use a query with a JOIN and reduce total trips to the database.
1

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.

Improve this answer

1 Comment

Why is the solution to "how do X with a prepared statement?" to dynamically build a different query? If you are using prepared statements to re-use cached query plans, then you have undermined that. If you are doing it to prevent SQL injection, well, that is different.
0

I had a similiar problem today and I found this topic. Looking at the answers and searching around the google I found a pretty solution.

Although, my problem is a little bit more complicated. Because I have fixed binding values and dynamic too.

This is the mysqli solution.

$params = array()
$all_ids = $this->get_all_ids();

for($i = 0; $i <= sizeof($all_ids) - 1; $i++){
    array_push($params, $all_ids[$i]['id']);
}

$clause = implode(',', array_fill(0, count($params), '?')); // output ?, ?, ?
$total_i = implode('', array_fill(0, count($params), 'i')); // output iiii

$types = "ss" . $total_i; // will reproduce : ssiiii ..etc

// %% it's necessary because of sprintf function
$query = $db->prepare(sprintf("SELECT * 
                                FROM clients    
                                WHERE name LIKE CONCAT('%%', ?, '%%') 
                                AND IFNULL(description, '') LIKE CONCAT('%%', ?, '%%')
                                AND id IN (%s)", $clause));

$thearray = array($name, $description);
$merge    = array_merge($thearray, $params); // output: "John", "Cool guy!", 1, 2, 3, 4

// We need  to pass variables instead of values by reference
// So we need a function to that
call_user_func_array('mysqli_stmt_bind_param', array_merge (array($query, $types), $this->makeValuesReferenced($merge)));

And the function makeValuesreferenced:

public function makeValuesReferenced($arr){
    $refs = array();
    foreach($arr as $key => $value)
        $refs[$key] = &$arr[$key];
    return $refs;
}

Links for getting this 'know-how': https://bugs.php.net/bug.php?id=49946, PHP append one array to another (not array_push or +), [PHP]: Error -> Too few arguments in sprintf();, https://www.php.net/manual/en/mysqli-stmt.bind-param.php#89171, Pass by reference problem with PHP 5.3.1

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.