|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #64852 Allow binding arrays in PDO
Submitted: 2013-05-16 05:22 UTC Modified: 2013-12-13 07:53 UTC
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: Assigned: wez (profile)
Status: Wont fix Package: PDO related
PHP Version: 5.5.0RC1 OS: Any
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Bug Type:
New email:
PHP Version: OS:


 [2013-05-16 05:22 UTC]
Binding arrays would be useful in these queries:


Most database systems don't support binding complex data structures on server side but PDO supports client side data binding so it can expand arrays to scalars and use them. Example:

$stmt = $pdo->prepare("SELECT * FROM table WHERE id IN ?");
$stmt->execute([ array('1', '2', '3') ]);

// This will be executed with client side data binding:
// SELECT * FROM table WHERE id IN ('1', '2', '3')

// With server side data binding:
// SELECT * FROM table WHERE id IN (?, ?, ?) -- bind values: '1', '2', '3'

It means that with server side data binding, arrays will be expanded to variable number of placeholders and the elements in the array will be bound.

There is a risk that the same statement would be used with a differently structured array or with non-array. Example:

$stmt = $pdo->prepare("SELECT * FROM table WHERE id IN ?");

// Expands query to: SELECT * FROM table WHERE id IN (?, ?, ?)
$stmt->execute([ array(1, 2, 3) ]);

// This subsequent call should throw.
$stmt->execute([ array(1, 2, 3, 4) ]);

// This subsequent call should also throw.
$stmt->execute([ 1 ]);

This is a very rare usage and throwing an error in this case seems like a good solution.

=== Named parameters ===

Named parameters could expand to name-key pair:

$stmt = $pdo->prepare("SELECT * FROM table WHERE id IN :ids");

// Expands query to: SELECT * FROM table WHERE id IN (:ids_0, :ids_1, :ids_2)
$stmt->execute([ 'ids' => array(1, 2, 3) ]);

However, there could be a collision: "WHERE id = :ids_0 OR id IN :ids". PDO could solve it by some sort of escaping - e.g. by prepending a magic string to all array names or by prepending something else also to all non-array names. Or it could just throw as this would be a rare and easily fixable problem.

=== Nested arrays ===

Expanding arrays should be recursive to support these queries:

$stmt = $pdo->prepare("SELECT * FROM table WHERE (type, type_id) IN ?");

// Expands to SELECT * FROM table WHERE (type, type_id) IN ((?, ?), (?, ?))
$types = array();
$types[] = array('article', 1);
$types[] = array('comment', 11);
$stmt->execute([ $types ]);

=== Braces or no braces ===

Array should expand to comma-delimited, braces-enclosed string. This expansion would support queries "WHERE (type, type_id) IN ?". It unfortunately wouldn't support this query:

INSERT INTO ... VALUES (...), (...), ...

This query needs braces in inner array and no braces in outer array so there's no consistent way to support this type of query.

=== Empty arrays ===

Empty arrays should be treated same as other arrays so they should expand to (). "INSERT INTO table () VALUES ()" is a valid query, at least in MySQL (it inserts a row with all default values). This would cause a syntax error in query "WHERE id IN ()" but that's a good behavior as there's no way to tell database to not match anything. "WHERE id IN (NULL)" would be a solution in this particular query (as NULL doesn't equal to NULL) but "WHERE id NOT IN (NULL)" wouldn't return NULL rows. So empty array must expand to ().

=== Debugging ===

PDO should disclose a method returning the real query sent to the server. It would be useful even without arrays, especially with client-side binding.

=== Implementation ===

Implementation would be tricky as the statement couldn't be prepared until it's executed. It means that PDO wouldn't talk to the database server in prepare() even with server-side binding (this is the current behavior with client-side binding). The query would be both prepared and executed in execute().


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2013-05-16 05:23 UTC]
-Assigned To: +Assigned To: wez
 [2013-05-16 05:23 UTC]
Wez, do you think it's reasonable?
 [2013-05-20 06:16 UTC]
It would be nice, but unfortunately it can't be done in a sane way at the PDO level; there is no 
consistent way to express how to handle data binding to array types and in the absence of that, the 
parameter binding needs to explicitly specify each parameter.

This would require a much more powerful SQL parsing layer to resolve and make it work properly,

This sort of feature is better implemented in a layer on top of PDO.
 [2013-12-13 07:53 UTC]
-Status: Assigned +Status: Wont fix
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Jul 17 22:01:28 2024 UTC