PHP - PDO not taking imploded array as question mark parameter for IN clause in SELECT query

Question

I have a problem with a question mark parameter in a prepared statement using PDO. My Query class looks like this (for now, I'm still adding functionality like data limits, custom parameters filtering and automatic detection of supported statements for the driver being used):

// SQL query
class Query {
    public $attributes;

    // constructor for this object
    public function __construct() {
        if ($arguments = func_get_args()) {
            $tmp = explode(" ", current($arguments));

            if (in_array(mb_strtoupper(current($tmp)), ["ALTER", "DELETE", "DROP", "INSERT", "SELECT", "TRUNCATE", "UPDATE"], true)) {
                // classify the query type
                $this->attributes["type"] = mb_strtoupper(current($tmp));

                // get the query string
                $this->attributes["query"] = current($arguments);

                // get the query parameters
                if (sizeof($arguments) > 1) {
                    $this->attributes["parameters"] = array_map(function ($input) { return (is_array($input) ? implode(",", $input) : $input); }, array_slice($arguments, 1, sizeof($arguments)));
                }

                return $this;
            }
        }
    }
}

This is the code fragment which executes the query:

$parameters = (!empty($this->attributes["queries"][$query]->attributes["parameters"]) ? $this->attributes["queries"][$query]->attributes["parameters"] : null);

if ($query = $this->attributes["link"]->prepare($this->attributes["queries"][$query]->attributes["query"], [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY])) {
    if ($query->execute((!empty($parameters) ? $parameters : null))) {
        return $query->fetchAll(\PDO::FETCH_ASSOC);
    }
}

And this is how I call it in my test code:

$c1->addQuery("lists/product-range", "SELECT * FROM `oc_product` WHERE `product_id` IN (?);", [28, 29, 30, 46, 47]);

if ($products = $c1->execute("test2")) {
    foreach ($products as $product) {
        print_r($product);
    }
}

The problem I have is I just see the first product (it's a test against a vanilla OpenCart installation) with id 28. As you can see in my code, if the passed parameter is an array, it gets automatically detected by the lambda I have in place in the Query class constructor, so it gets rendered as a string like 28,29,30,46,47.

Is there a missing parameter in PDO setup I'm missing? Or maybe there's some bug or platform limit in what I'm doing? I know there's some limitations on what PDO can do in regards to arrays, and that's why I pre-implode all arrays for them to be passed like a simple string.

There's some procedures I've seen here in SO which, basically, composes the query string like WHERE product_id IN ({$marks}), where $marks is being dynamically generated using a procedure like str_repeat("?", sizeof($parameters)) but that's not what I'm looking for (I could resort to that in case there's no known alternative, but it doesn't look like a very elegant solution).

My development environment is composed of: Windows 7 x64, PHP 5.4.13 (x86, thread-safe), Apache 2.4.4 (x86) and MySQL 5.6.10 x64.

Any hint would be greatly appreciated :)

Triparted

Well, I can't see where anything is executes. First, an instance of "Query" is never created. Second: Are these codefragments inside of "Query", or outside? Why are so many arrays used - I mean, this is supposed to be OOP, isn't it, make use of it by implementing properties. And I have no idea why the "setLimit" function is shown, but also never used. – Nickelsen

Sorry, I didn't wanted to copy the setLimit() method. Forgot to cleain it up. Those properties you mention are implemented with the attributes property which, in fact, is an associative array. Serves well the purpose and it avoids having empty members when some property is not applicable or not included at construction time. The Query class is complete per se. There's four classes for database management: Manager, Connection, Query and QueryGroup. The execution fragment belongs to the Connection class, in this case (so it can control both single and grouped queries). – Bifocals

Ok, then edit your question. And when I remove mentally the setLimit function, I really don't know why the class "Query" is mentioned at all because I still do not see where the constructor or an instance of it is used. – Nickelsen

Answer 1

A ? placeholder can only substitute for a single literal. If you want an IN clause to accept an arbitrary number of values, you must prepare a new query for each possible length of your array.

E.g., if you want to select ids in array [1, 2], you need a query that looks like SELECT * FROM tbl WHERE id IN (?,?). If you then pass in a three-item array, you need to prepare a query like SELECT * FROM tbl WHERE id IN (?,?,?), and so on.

In other words, you cannot know with certainty what query you want to build/create until the moment you have the data you want to bind to the prepared statement.

This is not a PDO limitation, it is fundamental to how prepared queries work in SQL databases. Think about it--what datatype would the ? be in SQL-land if you said IN ? but had ? stand in for something non-scalar?

Some databases have array-types (such as PostgreSQL). Maybe they can interpret IN <array-type> the same way as IN (?,?,...) and this would work. But PDO has no way of sending or receiving array-type data (there is no PDO::PARAM_ARRAY), and since this is an uncommon and esoteric feature it's unlikely PDO ever will.

Note there is an extra layer of brokenness here. A normal database, when faced with the condition int_id = '1,2,3,4' would not match anything since '1,2,3,4' cannot be coerced to an integer. MySQL, however, will convert this to the integer 1! This is why your query:

$pstmt = $db->prepare('SELECT * FROM `oc_product` WHERE `product_id` IN (?)');
$pstmt->execute(array('28,29,30,46,47'));

Will match product_id = 28. Behold the insanity:

mysql> SELECT CAST('28,29,30,46,47' AS SIGNED INTEGER);
+------------------------------------------+
| CAST('28,29,30,46,47' AS SIGNED INTEGER) |
+------------------------------------------+
| 28                                       |
+------------------------------------------+
1 rows in set (0.02 sec)

Pantechnicon

Ok, I get your point. still... I don't get why PDO isn't treating my string as a single variable. Your point looks pretty valid if you pass [1, 2] as an array to the execute() method but I'm handing over an array in which the only position (0) is a string containing 28,29,30,46,47. So, if I'm understanding it, it doesn't matter. I must do some pre-processing of the query string and substitute that single question mark with as many as parameters are in the passed string? – Bifocals

You misunderstand what a query binding is. id IN (1,2) is the same as id = 1 OR id = 2. What you are doing is id IN ('1,2') (a single string), which is the same as id = '1,2' (i.e. ID is equal to the string '1,2'), which is very much not what you intend!! – Pantechnicon

In other words, column IN (?) is always pointless--it means the same as column = ?. IN is for when you have a list of values and only one of them needs to match. Since ? only replaces literals, you need one ? per item in the list. – Pantechnicon

Ok, now I understand it. Substitution makes my string goes like '...', regardless of its size. Thanks for the explanation :) – Bifocals

@JulioMecaHansen, also MySQL is confusing matters because of its type coersion rules. When comparing a string with an int, '1,2' is cast to the int 1, which is why you get one match instead of none. (See output of SELECT CAST('1,4' AS SIGNED); This is insane and is just one small part of why MySQL and PHP belong together in the "fractal of bad design" campground. – Pantechnicon

Found a solution. Not the ideal one, I think, but it does the job very well... it scans every question mark, decomposes the query string, re-adapts the parameters array and then re-composes the query string to its final form. Thank you very much for your valuable hints :) – Bifocals

'('.implode(',',array_fill(0,sizeof($myarray),'?')).')'; – Crowl

Answer 2

Lambda detects an array and creates coma delimited string from it, and passed argument is treated as string, so the query looks like:

SELECT * FROM tbl WHERE id IN('1,2,3,4')

'1,2,3,4' is one string value for SQL.

If you are expecting only numerical values, you can omit adding them as parameters and simply put them in the query:

$a = [28, 29, 30, 46, 47];
$s = "SELECT * FROM tbl WHERE id IN(".implode(',', array_map('intval', $a)).")";

For different data types, you have to add as many parameter placeholders as you need, and bind every parameter separately.

Smokeless

The Query class gets called from the Connection class (which manages the database connection) with the addQuery() method, in which you supply the same arguments you supply to the sprintf() function. The query string first, and then as many parameters as you need to specify. That's why composing the query string in a 'manual' fashion is possible but not what I'm looking for. – Bifocals