php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #70128 PDO: Support named and question mark placeholders (parameters) in single query
Submitted: 2015-07-24 14:18 UTC Modified: -
From: chealer at gmail dot com Assigned:
Status: Open Package: PDO related
PHP Version: Irrelevant OS:
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please — but make sure to vote on the bug!
Your email address:
MUST BE VALID
Solve the problem:
49 - 35 = ?
Subscribe to this entry?

 
 [2015-07-24 14:18 UTC] 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();
?>


Patches

Add a Patch

Pull Requests

Add a Pull Request

 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sun Dec 15 11:01:25 2019 UTC