PHP - PDO does not accept a mounted array as a question question parameter for an IN clause in a SELECT query

Question

I have a problem with a question mark parameter in a prepared statement using PDO. The My Query class looks like this (at the moment I'm still adding features such as data restrictions, filtering custom parameters and automatically detecting supported statements for the driver 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 a piece of code that executes a request:

 $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 here is what 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 is that I just see the first product (this is a test against installing OpenCart with vanilla) with identifier 28. As you can see in my code, if the parameter passed is an array, it is automatically determined by lambda I have a place in the constructor of the Query class therefore it is displayed as a string, for example 28,29,30,46,47 .

Is there a missing parameter in the PDO setup that I am missing? Or maybe there is some kind of error or platform limitation in what I do? I know that there are some restrictions on what PDO can do with regards to arrays, and why I pre-insert all arrays to pass them as a simple string.

Here are some procedures that I saw here in SO, which basically makes up the query string, like WHERE product_id IN ({$marks}) , where $marks dynamically generated using a procedure like str_repeat("?", sizeof($parameters)) , but this is not what I am looking for (I could resort to this if there is no known alternative, but this does not seem like a very elegant solution).

My development environment consists 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 :)

Answer 1

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

For example, if you want to select identifiers in the array [1, 2] , you need a query that looks like SELECT * FROM tbl WHERE id IN (?,?) . If you then pass an array of three elements, you need to prepare a query like SELECT * FROM tbl WHERE id IN (?,?,?) , Etc.

In other words, you cannot know with certainty which request you want to create / create until the moment when you have the data that you want to bind to the prepared statement.

This is not a PDO restriction; it is fundamental to how prepared queries work in SQL databases. Think about it: what type of data would be ? in SQL-land, if you would say IN ? but had ? for something non-scalar?

Some databases have array types (e.g. PostgreSQL). Maybe they can interpret IN <array-type> in the same way as IN (?,?,...) and this will work. But PDO does not have the ability to send or receive data such as an array (no PDO::PARAM_ARRAY ), and since this is an unusual and esoteric function, it is unlikely PDO.

Please note that there is an additional layer of brokenness. A normal database, faced with the condition int_id = '1,2,3,4' , will not correspond to anyone, since '1,2,3,4' cannot be forcibly bound to the whole. MySQL, however, converts this to an integer of 1 ! This is why your request:

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

Will match product_id = 28 . Here's the madness:

 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) 

Answer 2

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

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

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

If you expect only numeric values, you can omit them as parameters and just 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 types of data, you need to add as many bookmarks as you need, and bind each parameter separately.