在绑定字符串时，使用带有PDO的WHERE IN（...）不起作用

Question

I have a query that looks like this:

  UPDATE table SET column = UNIX_TIMESTAMP()
  WHERE id IN (:idString)

Where idString is a string of comma separated ids and is passed to execute() in an array. To my surprise, when this query is executed, only the row with the first id in idString is updated.

After banging my head against the wall for a while, I finally decided to try it like this:

  UPDATE table SET column = UNIX_TIMESTAMP()
  WHERE id IN (' . $idString . ')

The second query works as expected.

Why won't the query work when I bind the string of ids using PDO?

Answer 1

In SQL, the string

'1,2,3,5,12'

Is a single value, and casting it in a numeric context, it will just have the value of the leading digits, so just the value 1.

This is much different from the set of multiple values:

'1', '2', '3', '5', '12'

Any time you use bound parameters, whatever you pass as the parameter value becomes just one single value, even if you pass a string of comma-separated values.

If you want to pass a set of multiple values to parameters in your SQL query, you must have multiple parameter placeholders:

UPDATE table SET column = UNIX_TIMESTAMP()
WHERE id IN (:id1, :id2, :id3, :id4, :id5)

Then explode your string of values and pass them as an array:

$idlist = array('id1' => 1, 'id2' => 2, 'id3' => 3, 'id4' => 5, 'id5' => 12);
$pdoStmt->execute($idlist);

For cases like this, I would recommend using positional parameters instead of named parameters, because you can pass a simple array instead of an associative array:

$pdoStmt = $pdo->prepare("UPDATE table SET column = UNIX_TIMESTAMP()
    WHERE id IN (?, ?, ?, ?, ?)");
$idlist = explode(",", "1,2,3,5,12");
$pdoStmt->execute($idlist);

---

@mario adds a comment that you can use FIND_IN_SET(). That query would look allow you to pass one string formatted as a comma-separated string of values:

$pdoStmt = $pdo->prepare("UPDATE table SET column = UNIX_TIMESTAMP()
    WHERE FIND_IN_SET(id, :idString)");
$pdoStmt->execute(["idString" => "1,2,3,5,12"]);

However, I usually don't recommend that function because it spoils any chance of using an index to narrow down the search. It will literally have to examine every row in the table, and during an UPDATE that means it has to lock every row in the table.