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.

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
– user1032531, Commented Apr 20, 2015 at 23:59
@user1032531 I put in an example of why a generated IN string would cause errors. — Howard Zoopaloopa
– Howard Zoopaloopa, Commented Apr 21, 2015 at 0:04
You can do something like col IN (?, ?, ?, ...) where you have one ? for each value in the array. Then bind/execute that. See: stackoverflow.com/questions/920353/… — Jonathan Kuhn
– Jonathan Kuhn, Commented Apr 21, 2015 at 0: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
– mario, Commented Apr 21, 2015 at 0: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
– Howard Zoopaloopa, Commented Apr 21, 2015 at 0:31

pala_ · Accepted Answer · 2015-04-21 00:17:59Z

7

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.

edited Apr 21, 2015 at 0:17

answered Apr 21, 2015 at 0:01

pala_

9,0101 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mirta Over a year ago

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().

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related