php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #63281 PDO: Multiple queries using multiple bindParams yields unexpected results
Submitted: 2012-10-15 18:18 UTC Modified: 2012-10-16 15:08 UTC
Votes:17
Avg. Score:3.7 ± 1.4
Reproduced:4 of 7 (57.1%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: jim dot gibbs at onelifemedia dot com Assigned:
Status: Wont fix Package: PDO related
PHP Version: 5.4.7 OS: Mac
Private report: No CVE-ID: None
View Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
If you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: jim dot gibbs at onelifemedia dot com
New email:
PHP Version: OS:

 

 [2012-10-15 18:18 UTC] jim dot gibbs at onelifemedia dot com
Description:
------------
Created a prepared statement, with named placeholders.  The prepared statement 
contained 2 queries, and 3 placeholders.  2 of the placeholders were the same, so 
they *should* become the same value.

The resulting query ended up being all three place holders being the same value.

The results below were generated from the mysql log files.

Workaround:

Instead of using the bindParam function, I was able to send the same array to the 
execute function, and it worked.

Test script:
---------------
$query = <<<QUERY
  DELETE FROM `authentication_hashes` WHERE session_key = :session_key;
  INSERT INTO `authentication_hashes` (`session_key`, `generated_hash`) VALUES (:session_key, :generated_hash);
QUERY;

$dbh = new PDO('mysql:host=localhost;port=8888;dbname=foo, $user, $pass, array (PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
$statement = $dbh->prepare($query);
$bind_params = array(':session_key' => $session_key, ':generated_hash' => $generated_hash)
foreach( $bind_params as $key => $value ){
  $statement->bindParam($key, $value);
}
$statement->execute();


Expected result:
----------------
DELETE FROM `authentication_hashes` WHERE session_key = '8675309';
INSERT INTO `authentication_hashes` (`session_key`, `generated_hash`) VALUES 
('8675309', 'cb5606644c1d30a9d8f84cee4234a44455c82448a33f8031434ec42a6d173383')


Actual result:
--------------
DELETE FROM `authentication_hashes` WHERE session_key = 
'3966b45ae07b86de9c74163b093994fbf2a5813a06cbc9902f15e1b38a212940';
INSERT INTO `authentication_hashes` (`session_key`, `generated_hash`) VALUES 
('3966b45ae07b86de9c74163b093994fbf2a5813a06cbc9902f15e1b38a212940', 
'3966b45ae07b86de9c74163b093994fbf2a5813a06cbc9902f15e1b38a212940')

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-10-16 14:42 UTC] laruence@php.net
-Status: Open +Status: Wont fix
 [2012-10-16 14:42 UTC] laruence@php.net
the reason for this is because require a reference variable, then in the loop, 
the variable will be reused, after the loop, the variable will be the last 
assigned one.

you can use bindValue instead, or ...
you can use:
foreach ($params as $key => &$val) ;

this is a complicated problem, since it is a feature of PHP internal(reference). 
so, won't fix.

thanks
 [2012-10-16 15:08 UTC] laruence@php.net
I wrote a blog to explain this: http://www.laruence.com/2012/10/16/2831.html

it's chinese, but I think you can use google translator :)
 [2012-10-16 15:29 UTC] jim dot gibbs at onelifemedia dot com
Thanks for the information.  I read your blog post, and I'm glad that we came to 
the same conclusion.

I guess my only other question would be, are there any drawbacks to sending the 
array to the execute function?  That's the workaround that I'm currently using, 
and it's working just fine.  Of course, I haven't tried any speed/load tests, or 
anything of that ilk, since I'm in the beginning stages of development.
 [2012-11-01 05:39 UTC] rawr at rawr dot com
nihao
 [2012-11-01 16:17 UTC] giblets at mailinator dot com
nihao, mate
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 22:01:27 2024 UTC