2

I have this code:

$Array=array();
array_push($Array,"Email1","Email2");
$Array=implode("','",$Array);
$Array="'$Array'";
echo "$Array" //Will output 'Email1','Email2'
$Check=$connection->prepare("SELECT ID FROM USERS WHERE EMAIL IN(:Array)");
$Check->execute(array(
    ':Array' => $Array,
));

This query won't work but if I write:

$Check=$connection->prepare("SELECT ID FROM USERS WHERE EMAIL IN('Email1','Email2')");
$Check->execute(array(
    ':Array' => $Array,
));

This works, but I won't bind the array to avoid SQL Injection. How can I fix it?

Pek
  • 166
  • 1
  • 15
user9158219
  • 23
  • 2

1 Answers1

1

You don't want to bind the imploded list as one element but rather each of the values individually using ? so the end of the statement would be WHERE EMAIL IN (?,?):

$values  = ["Email1","Email2"];
# This should give you ?,?
$bindstr = implode(",",array_fill(0,count($values),'?'));
$query = $connection->prepare("SELECT ID FROM USERS WHERE EMAIL IN({$bindstr})");
# Use the raw values individually in the execute
$query->execute($values);

Hopefully that should get results back you are looking for.

Rasclatt
  • 12,498
  • 3
  • 25
  • 33
  • thanks a lot for your help, could you please exaplain me why in the first row you used the [...],and why did you add in the IN CLAUSE the {..}.Anyway thanks for your help, hope that you'll have a nice day:) – user9158219 Dec 31 '17 at 11:32
  • Oh, sorry, the `[` and `]` in newer versions of php is same as using `array()` so `[` = `array(` and `]` = `)`. – Rasclatt Dec 31 '17 at 11:38
  • Thanks for have clarified :) – user9158219 Dec 31 '17 at 11:40
  • The `{}` inside the string sort of enforces that you are using a variable inside those braces. I could have not used the braces, but it's better to use them. It's for complex strings: http://php.net/manual/en/language.types.string.php#language.types.string.parsing.complex – Rasclatt Dec 31 '17 at 11:43
  • I can't understand a thing how did you bind the values?Is this query 100% secure against sql injection?Thanks for read, and sorry for another nuisance. – user9158219 Dec 31 '17 at 12:03
  • You bind the the values by putting placeholders using question marks (`?`) for where the values would normally be in the statement, then you put the raw array into the `execute()`. – Rasclatt Dec 31 '17 at 12:06
  • So you could have done `WHERE IN (:email1, :email2)` but then you would have to have used the array `$values = [':email1' => 'Email1',':email2'=>'Email2']; $query->execute($values)`. It's just easier to bind question marks as placeholders rather than an associative array. – Rasclatt Dec 31 '17 at 12:08
  • okok understand thanks again for help! – user9158219 Dec 31 '17 at 12:09