PDO Query with in clause that contain an Array won't work [duplicate]

Question

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?

comma seperated values isn't good design. consider normalizing your database — Funk Forty Niner
– Funk Forty Niner, Commented Dec 31, 2017 at 14:36

Rasclatt · Accepted Answer · 2017-12-31 11:11:00Z

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.

answered Dec 31, 2017 at 11:11

Rasclatt

12.5k3 gold badges28 silver badges33 bronze badges

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

8 Comments

user9158219 Over a year ago

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:)

Rasclatt Over a year ago

Oh, sorry, the [ and ] in newer versions of php is same as using array() so [ = array( and ] = ).

user9158219 Over a year ago

Thanks for have clarified :)

Rasclatt Over a year ago

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: php.net/manual/en/…

user9158219 Over a year ago

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.

|

Collectives™ on Stack Overflow

PDO Query with in clause that contain an Array won't work [duplicate]

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related