Escape Array of Strings for IN Statement PDO MYSQL [duplicate]

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.

Answer 1

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.