PDO Prepare Not Replacing Named Placeholders

Question

I have tried using named placeholder to fill in the data as shown here:

    $STH = $DBH->prepare("SELECT mixes.* FROM mixes JOIN(SELECT id FROM mixes WHERE id NOT IN ( :noredo_ids )  ORDER BY RAND() LIMIT 1) ips on mixes.id = ips.id"); 
    $STH->bindParam(':noredo_ids', $_GET["noredo"]);
    $STH->setFetchMode(PDO::FETCH_ASSOC); 
    $STH->execute();

As well as trying

    $arr2["ids"] = $_GET["noredo"];

    $STH = $DBH->prepare("SELECT mixes.* FROM mixes JOIN(SELECT id FROM mixes WHERE id NOT IN ( :ids )  ORDER BY RAND() LIMIT 1) ips on mixes.id = ips.id"); 
    $STH->setFetchMode(PDO::FETCH_ASSOC); 
    $STH->execute($arr2);

But neither of those are working. But when I try to manually put the string in instead of using placeholders, it does work:

        $arr2["ids"] = $_GET["noredo"];

    $STH = $DBH->prepare("SELECT mixes.* FROM mixes JOIN(SELECT id FROM mixes WHERE id NOT IN (". $arr2['ids'] .")  ORDER BY RAND() LIMIT 1) ips on mixes.id = ips.id"); 
    $STH->setFetchMode(PDO::FETCH_ASSOC); 
    $STH->execute();

Should I manually escape the string? Am I missing something obvious? Thanks!

http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991146 — Your Common Sense, May 03 '14 at 18:35

score 0 · Answer 1 · answered Nov 25 '14 at 22:50

I am not certain since PHP's documentation on named placeholders for prepared statements is kinda vague about this. http://php.net/manual/en/pdostatement.bindparam.php

But if $_GET['noredo'] is an array of IDs you need to first implode(',',$_GET['noredo']) before passing into the prepared statement, I do not believe that the placeholder replacement is smart enough to flatten the array into a comma separated list acceptable for use in IN().

And in PHP docs, the replacement is a single value and not an array of values, so (and this is where I'm fuzzy) I don't believe that it looks for :named_placeholder in the array you pass it.

But then again, I've only used the ? placeholder for prepared statements...

score 0 · Answer 2 · answered Nov 25 '14 at 22:58

A query parameter always takes the place of one single scalar value in an SQL expression.

So if you expect $_GET["noredo"] to be an array or a comma-separated list of values, what you're doing won't work. The query will be run as if you did this:

WHERE id NOT IN ( '1,2,3,4' )

A quoted string value that contains a comma-separated list is not the same as a series of comma-separated values. It's one string, and in a numeric context SQL will convert '1,2,3,4' into the scalar number 1.

So you need to use multiple placeholders if you want to bind multiple values in an IN() predicate.

$id_array = (array) $_GET["noredo"];

$placeholders = implode(",", array_fill(0,count($id_array),"?"));

$STH = $DBH->prepare("SELECT mixes.* FROM mixes JOIN(SELECT id FROM mixes 
    WHERE id NOT IN ( $placeholders )  ORDER BY RAND() LIMIT 1) ips on mixes.id = ips.id"); 

$STH->execute($id_array);

score -1 · Answer 3 · answered May 03 '14 at 18:08

-1

The second example doesn't work because you used $arr2["ids"] instead of $arr2[":ids"] as far as I can tell. That is, you still have to use the full parameter name.

Also, you should make sure that the values you try to insert are properly sanitized and cannot cause a syntax error.

answered May 03 '14 at 18:08

Nikolas Grottendieck

2,548
1
24
24

I tried changing it to $arr2[":ids"], but it is still not working. – defiant91 May 03 '14 at 18:18

PDO Prepare Not Replacing Named Placeholders

3 Answers3