Req #70128 [Opn]: PDO: Support named and question mark placeholders (parameters) in single query
Edit report at https://bugs.php.net/bug.php?id=70128&edit=1
ID: 70128
Updated by: cmb@php.net
Reported by: chealer at gmail dot com
Summary: PDO: Support named and question mark placeholders
(parameters) in single query
Status: Open
Type: Feature/Change Request
Package: PDO related
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
First, the documentation is wrong. If native prepares are used,
the driver may well support mixing of named and positional
placeholders[1]. If the driver does not support both styles,
mixing isn't possible, anyway.
Only if you use emulates prepares, mixing is never allowed. I
agree that this limitation is a bit arbitrary.
[1] <https://3v4l.org/6237m>
Previous Comments:
------------------------------------------------------------------------
[2015-07-24 14:18:41] chealer at gmail dot com
Description:
------------
As explained in http://php.net/manual/en/pdo.prepare.php,
"You cannot use both named and question mark parameter markers within the same SQL statement;
pick one or the other parameter style."
Indeed, this can become a "style" issue for complex queries built by several functions,
somewhat forcing applications to choose one or the other.
I believe named parameter markers are generally better, providing better clarity, but they are
sometimes inferior to question mark parameter markers. Consider the broken test script (2 columns
are named "name"). The filterType() function here could easily make itself clever enough
to workaround keeping only named markers, but it would be simpler to use a mix of both Oracle-style
(named) and MySQL-style (question mark) parameters:
function filterType($name, &$parameters) {
$parameters[] = $name;
return '(type=(SELECT id FROM types WHERE name= ?))';
}
Question mark parameter markers have their issues (order matters), and named markers have their
different issues (unicity requirement). The more complex query generation gets, the more complex
using only one parameter style becomes. Therefore, I believe both styles should be allowed in the
same query.
Until this is implemented, I suggest putting the quote from the manual above in a warning box.
Test script:
---------------
<?php
function filterType($name, &$parameters) {
$parameters[':name'] = $name;
return '(type=(SELECT id FROM types WHERE name= :name))';
}
$parameters = array(':name' => 'salsa');
$sql = 'SELECT colour, calories
FROM fruit
WHERE name = :name';
$sql .= ' AND ' . filterType('sauce', $parameters); // Make sure we don't
select something called "salsa" which is not sauce.
$sth = $dbh->prepare($sql);
$sth->execute($parameters);
$sth->fetchAll();
?>
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=70128&edit=1
Thread (2 messages)