PDO prepared statements IN clause with named placeholders doesn't work as expected

Question

Let's say in a scenario: $ids = 2,3. But for some reason the records are getting returned as if: $ids = 2.

I believe there's some problem in this line from the complete code, because when I echo $ids, it returns 2,3, but the actual query returns as if there's only one id. I.e. it returns events from one course only (of id 2).

$statement->execute(array("ids" => $ids, 'timestart' => $timestart, 'timeend' => $timeend))) {

my full code:

$ids = array_map(function($item) { return $item->id; }, $entitlementsVOs);
$ids = implode(', ', $ids); //echo shows 2,3

$timestart = isset($_GET['start']) && $_GET['start'] != "" ? $_GET['start'] : null;
$timeend = isset($_GET['end']) && $_GET['end'] != "" ? $_GET['end'] : null;

$statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(:ids) AND timestart >= :timestart AND timestart + timeduration <= :timeend");
$statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));


if($statement->execute(array("ids" => $ids, 'timestart' => $timestart, 'timeend' => $timeend))) {
    return $statement->fetchAll();
} else {
    return null;
}

p.s. manually running this query in mysql workbench returns my two records (1 from course id 2 and the other from course 3) where I substitute in operator with (2,3), but in php execution I get only one record back.

If I hard code the values in php courseid IN(2,3), e.g.:

$statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(2,3) AND timestart >= :timestart AND timestart + timeduration <= :timeend");

I get what I'm expecting, so I believe there's some problem with either implode, or IN operator or $statement->execute.

Edit:

I've read the dupe, but I don't know where to start with the named placeholders to accomplish the same. My question is when I've both named parameters and IN operator, the dupe uses IN operator only with position placeholders.

Edit 2

I've read the second dupe, it's not related, my question uses a mix of both IN and named placeholders, the linked question doesn't address that, however, I've got the solution now in the answer.

You need as many placeholders as you want to bind values in your IN clause! — Rizier123, Apr 30 '15 at 21:04
Your `$ids` are being passed to MySQL as a string and not being converted into a MySQL "array". Effectively you are telling MySQL to do this: `courseid IN('2,3')`, which is just a single value, unlike the desired `courseid IN(2,3)` — MonkeyZeus, Apr 30 '15 at 21:05
@MonkeyZeus thanks for the help, can you please help with an answer while using `$ids` — user2727195, Apr 30 '15 at 21:07
@Rizier123 i've named parameters as well as IN operator, so it's not exact duplicate or please link the pertinent one. — user2727195, Apr 30 '15 at 21:08
`courseid IN($ids)` will work but it will leave you open to SQL injection. You need to add a parameter for each ID like this `courseid IN(:id1, :id2)` — MonkeyZeus, Apr 30 '15 at 21:10
@user2727195 See MonkeyZeus comment: http://stackoverflow.com/questions/29977652/php-prepared-statement-with-in-operator?noredirect=1#comment48075220_29977652 You can also use named placeholders and use an id which you increment for all values which you have in your IN clause — Rizier123, Apr 30 '15 at 21:13
@user2727195 But if you think you have read the dupe carefully and still think it's not a dupe: edit your question and show what and where the difference between your question and the dupe is. OR if you read it, but you are still stuck, then add this to the question, say that you have read it, maybe you understand something more, but that you are still somewhere stuck — Rizier123, Apr 30 '15 at 21:14
i hope this could help, coming to PHP after a long time, lost in here, can someone please write an answer that would work with my problem — user2727195, Apr 30 '15 at 21:14
@MonkeyZeus can you please write the answer in comments, I think I need to deal with the `$ids = implode(', ', $ids);` and `$statement->execute` part — user2727195, Apr 30 '15 at 21:20
@user2727195 So where are you stuck? creating the named placeholders or bind the values? <- Add this to your question and show/say where you are stuck — Rizier123, Apr 30 '15 at 21:23
@Rizier123 I'm stuck at when i've both named params with In operator, not sure how to approach this problem, and I don't see any example around — user2727195, Apr 30 '15 at 21:24
@user2727195 ^ Add this say that you have read the dupe, but you don't know where to start with the named placeholders to accomplish the same — Rizier123, Apr 30 '15 at 21:25
finally solved by concatenation `courseid IN(" . $ids . ") AND`, but as @MonkeyZeus mentioned it's prone to sql injection, actually it's not a user input, ids are retrieved by the system, but question is still open for a sql injection free solution — user2727195, Apr 30 '15 at 21:36
@user2727195 I reopened your question extra to show you how to do the same thing with named placeholders. So where are we now with the question? — Rizier123, May 01 '15 at 17:43

Rizier123 · Accepted Answer · 2015-04-30T22:43:42.037

This should work for you:

So as already said in the comments you need a placeholder for each value which you want to bind into the IN clause.

Here I create first the array $ids which only holds the plain ids, e.g.

[2, 3]

Then I also created the array $preparedIds which holds the placeholders as array, which you then later use in the prepared statement. This array looks something like this:

[":id2", ":id3"]

And I also create an array called $preparedValues which holds the $preparedIds as keys and $ids as values, which you then later can use for the execute() call. The array look something like this:

[":id2" => 2, ":id3" => 3]

After this you are good to go. In the prepared statement I just implode() the $preparedIds array, so that the SQL statement look something like this:

... IN(:id2,:id3) ...

And then you can simply execute() your query. There I just array_merge() your $preparedValues array with the other placeholders array.

<?php

    $ids = array_map(function($item){
        return $item->id;
    }, $entitlementsVOs);

    $preparedIds = array_map(function($v){
        return ":id$v";
    }, $ids);

    $preparedValues = array_combine($preparedIds, $ids);


    $timestart = (!empty($_GET['start']) ? $_GET['start'] : NULL );
    $timeend = (!empty($_GET['end']) ? $_GET['end'] : NULL );


    $statement = $this->connection->prepare("SELECT name AS title, timestart AS start, timestart + timeduration AS end FROM event WHERE courseid IN(" . implode(",", $preparedIds) . ") AND timestart >= :timestart AND timestart + timeduration <= :timeend");
    $statement->setFetchMode(\PDO::FETCH_CLASS, get_class(new EventVO()));

    if($statement->execute(array_merge($preparedValues, ["timestart" => $timestart, "timeend" => $timeend]))) {
        return $statement->fetchAll();
    } else {
        return null;
    }

?>

Also I think you want to put an if statement around your query, since your query will not run if the values of $timestart and $timeend are NULL.

PDO prepared statements IN clause with named placeholders doesn't work as expected

1 Answers1

Linked

Related