Escape Array of Strings for IN Statement PDO MYSQL

Question

Instead of running a loop to update values in a table I'd like to use the IN statement (assuming it's faster?).

I have an array of values:

$array (
    1 => Tom
    2 => Bob
    3 => Sally's String
    4 => Pesce is Italian for "fish"
   )

I'm using a loop because I can singly prepare each string to account for potentially bad characters:

$sql = "UPDATE table SET data = 1 WHERE my_string = ?";
$s = pdoObject->prepare($sql);

foreach($array as $string){
    $s->execute(array($string));
}

I'd love to use the IN statement (again, assuming it's faster, please tell me if i'm wrong). The problem is, creating an IN statement would cause some errors given the different types of characters present in my array of strings. e.g;

$inString = '"'.implode('","',$array).'"';
// $inString would be "Tom","Bob","Sally's String","Pesche is Italian for "fish"";

Is there a proper way to prepare this type of query? Or an "execute many" type function? I will typically see arrays of strings anywhere from 5 - 50 items long.

###### WHY THIS QUESTION IS UNIQUE ######

My question is unique to question Can I bind an array to an IN() condition? because I am looking to bind an array of strings not integers.

What type of errors? You have it in an array, right? Recommend changing from starting array at element 1 to 0. If it still doesn't work, what about something like `/'`? — user1032531, Apr 20 '15 at 23:59
@user1032531 I put in an example of why a generated IN string would cause errors. — Howard Zoopaloopa, Apr 21 '15 at 00:04
You can do something like `col IN (?, ?, ?, ...)` where you have one `?` for each value in the array. Then bind/execute that. See: http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Jonathan Kuhn, Apr 21 '15 at 00:04
The reference question isn't just about binding *integers*. All the answers there work for strings too. (That's kind of the point of parameter binding. The data type shouldn't matter.) — mario, Apr 21 '15 at 00:28
@mario The solution is the same, yes. But the reason I'm asking is different. If I had an array of integers I could have just created a string like $inString = implode(',',$intArray); — Howard Zoopaloopa, Apr 21 '15 at 00:31
Sure you *could* have. But you *shouldn't* ;) → In that integer case (which your question isn't about) you would've been better off still with correctly binding the list as string instead of interpolating it. There's `FIND_IN_SET()` for integer lists. — mario, Apr 21 '15 at 00:35

pala_ · Accepted Answer · 2015-04-21T00:17:59.397

It's not as pleasant as you'd like. You have to build up the array of ? placeholders. Something like this:

<?php
  $array = array('Tom', 'Bob', 'Sally\'s String', 'Pesce is Italian for "fish"');
  $placeholders = implode(',', array_fill(0, count($array), '?'));

  $sql = "UPDATE table SET data = 1 WHERE my_string IN ( $placeholders )";
  // $sql now contains "UPDATE table SET data = 1 WHERE my_string IN ( ?,?,?,? )"
  $s = $pdo->prepare($sql);
  $s->execute($array);
?>

this way, each individual string is bound and escaped individually, while still giving you the single in query that you were looking for. change $s->execute($array) to $s->execute(array_values($array)) if those values are actually in an associative array.

Just an addition to @pala_ anwser...I had a situation where I needed to add an AND operator with another IN condition. I did the same as you wrote but in order to execute() I had to merge the two arrays in one in order to execute(). — mirta, Apr 09 '19 at 20:39

Escape Array of Strings for IN Statement PDO MYSQL

1 Answers1