From: vrana
Operating system: Any
PHP version: 5.5.0RC1
Package: PDO related
Bug Type: Bug
Bug description:Allow binding arrays in PDO
Description:
------------
Binding arrays would be useful in these queries:
WHERE id IN ?
INSERT INTO ... VALUES ?
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:
<?php
$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:
<?php
$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:
<?php
$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:
<?php
$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().
--
Edit bug report at https://bugs.php.net/bug.php?id=64852&edit=1
--
Try a snapshot (PHP 5.4):
https://bugs.php.net/fix.php?id=64852&r=trysnapshot54
Try a snapshot (PHP 5.3):
https://bugs.php.net/fix.php?id=64852&r=trysnapshot53
Try a snapshot (trunk):
https://bugs.php.net/fix.php?id=64852&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=64852&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=64852&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=64852&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=64852&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=64852&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=64852&r=support
Expected behavior: https://bugs.php.net/fix.php?id=64852&r=notwrong
Not enough info:
https://bugs.php.net/fix.php?id=64852&r=notenoughinfo
Submitted twice:
https://bugs.php.net/fix.php?id=64852&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=64852&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=64852&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=64852&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=64852&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=64852&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=64852&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=64852&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=64852&r=mysqlcfg
