php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #48856 emulated prepared statements allow multiple parameters of the same name
Submitted: 2009-07-08 20:04 UTC Modified: 2021-09-28 12:25 UTC
Votes:11
Avg. Score:4.7 ± 0.6
Reproduced:10 of 10 (100.0%)
Same Version:3 (30.0%)
Same OS:8 (80.0%)
From: dhammari at q90 dot com Assigned: cmb (profile)
Status: Closed Package: PDO related
PHP Version: 5.2.10 OS: Linux 2.6.27-gentoo-r8
Private report: No CVE-ID: None
 [2009-07-08 20:04 UTC] dhammari at q90 dot com
Description:
------------
My PDO Statement seems to bind multiple parameters of the same name even though the PDO->Prepare documentation indicates that this should fail: "You cannot use a named parameter marker of the same name twice in a prepared statement." Nevertheless, my SQL statement that is reusing the same parameter is getting through and returning a valid result set from a MySQL engine.

PHP Version: 5.2.9-pl2-gentoo
System: Linux 2.6.27-gentoo-r8

Reproduce code:
---------------
<?php

// CREATE TABLE `testError` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Name` VARCHAR( 50 ) NOT NULL , `Description` TEXT NOT NULL);
// INSERT INTO `testError` (`id` , `Name` , `Description`) VALUES ('1', 'Binds Both Parameters', 'Seems to bind both parameters'), ('2', 'Binds All Parameters', 'Seems to bind all parameters');
    
    $pdo = new PDO($_SESSION["API_DB_dsn"], $_SESSION["API_DB_username"], $_SESSION["API_DB_password"]);
    $sql = "SELECT * FROM testError WHERE id >= :myParameter AND LENGTH(name) > :myParameter AND 1 = :myParameter";
    $params = array("myParameter" => 1);
    $statement = $pdo->prepare($sql);
    foreach($params as $key => $value){
        $statement->bindParam(":".$key, $value);
    }
    $statement->debugDumpParams();
    $success = $statement->execute();
    if(!$success){
        echo("\n<p style='color:red;'>SQL FAILED</p>\n");
        var_dump($pdo->errorInfo());
        var_dump($statement->errorInfo());
    }
    else{
        echo("\n<p style='color:green;'>SQL SUCCEEDED</p>\n");
        $result = $statement->fetchALL(PDO::FETCH_ASSOC);
        var_dump($result);
    }

?>

Expected result:
----------------
I expect to see the following error:

Invalid parameter number: number of bound variables does not match number of tokens

SQL FAILED

array
  0 => string '00000' (length=5)

array
  0 => string 'HY093' (length=5)


Actual result:
--------------
Instead, I get the following:

SQL SUCCEEDED

array
  0 => 
    array
      'id' => string '1' (length=1)
      'Name' => string 'Binds Both Parameters' (length=21)
      'Description' => string 'Seems to bind both parameters' (length=29)
  1 => 
    array
      'id' => string '2' (length=1)
      'Name' => string 'Binds All Parameters' (length=20)
      'Description' => string 'Seems to bind all parameters' (length=28)


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-09-23 16:17 UTC] sjoerd@php.net
Bjori, do you know why this was in the documentation?
 [2009-09-23 17:29 UTC] bjori@php.net
No idea. Its been like this for almost 4years..
Dan? Was this originally a limitation in PDO?
 [2009-09-23 18:07 UTC] dhammari at q90 dot com
Hi Bjori,

It seems to me that the ability to use the same token for multiple binds has arisen fairly recently. I recall earlier releases of php 5 berating me for attempting to reuse a token in this manner as recently as several months ago.

I have looked up documentation on this behavior before, and the articles I have found seem adamant that multiple binding from a single token should be disallowed. For example, please take a look at these previous bug reports from 2005 and 2007:

http://bugs.php.net/bug.php?id=33886
http://bugs.php.net/bug.php?id=40417

The discussions in these articles seem to reach a consensus that every time a variable in a prepared PDO statement is bound, it should be bound to a unique token.

While I think it is useful to bind multiple parameters to a single token, I don't want to start writing code that relies on this feature until I am assured that this is the intended behavior. Otherwise, my code would not be future proof and could be invalidated by an upcoming release. I would appreciate it if you could clarify the intended behavior and update php's documentation accordingly.

Sincerely,
Dan Hammari
 [2009-09-24 02:59 UTC] dbs@php.net
Yes, it was a limitation back then - and while it might work for some PDO drivers, I believe the problem was that it was not guaranteed to work with all PDO drivers - and therefore discouraged. It would be best for us to talk to a current PDO developer to get the real deal on the current situation.
 [2009-11-16 13:42 UTC] dbs@php.net
I've referred this bug to the PDO discussion list, as there are ongoing discussions about the future design of PDO and this bug seems to be pertinent to that discussion.
 [2014-01-01 12:50 UTC] felipe@php.net
-Package: PDO related +Package: PDO Core
 [2015-09-26 01:33 UTC] a791446794 at 163 dot com
I want this feature too!
When sql has subquey with the same condition,it is fairly that use the same :token will be maintainable and efficient.
 [2017-10-24 07:11 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: dbs +Assigned To:
 [2017-10-24 08:29 UTC] kalle@php.net
-Package: PDO Core +Package: PDO related
 [2017-10-24 18:51 UTC] adambaratz@php.net
-Summary: PDO_Statement->bindParam binds multiple parameters of the same name +Summary: emulated prepared statements allow multiple parameters of the same name -Package: PDO related +Package: PDO Core
 [2017-10-24 18:51 UTC] adambaratz@php.net
You're allowed to reuse parameters with emulated prepares. I believe that's specifically what's being called out here. The easiest thing to do, honestly, would be to update the documentation to that end. Otherwise, this would have be changed for the next minor rev since it would technically be a BC break. Other drivers have built-in inflexibility here, so I wouldn't suggest making any other changes.

Moving this to PDO Core since that's where the emulation code lives.
 [2017-10-24 23:49 UTC] kalle@php.net
-Package: PDO Core +Package: PDO related
 [2021-09-28 12:25 UTC] cmb@php.net
-Status: Open +Status: Closed -Type: Bug +Type: Documentation Problem -Assigned To: +Assigned To: cmb
 [2021-09-28 12:25 UTC] cmb@php.net
> You're allowed to reuse parameters with emulated prepares. I
> believe that's specifically what's being called out here. The
> easiest thing to do, honestly, would be to update the
> documentation to that end.

I aggre. The documentation now says[1]:

| You cannot use a named parameter marker of the same name more
| than once in a prepared statement, unless emulation mode is on.

So this is resolved.  See also feature request #76647 regarding
implementation improvements.

[1] <https://www.php.net/manual/en/pdo.prepare.php>
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Oct 13 14:01:27 2024 UTC