Edit report at https://bugs.php.net/bug.php?id=63281&edit=1
ID: 63281
Comment by: jim dot gibbs at onelifemedia dot com
Reported by: jim dot gibbs at onelifemedia dot com
Summary: PDO: Multiple queries using multiple bindParams
yields unexpected results
Status: Wont fix
Type: Bug
Package: PDO related
Operating System: Mac
PHP Version: 5.4.7
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2012-10-16 15:08:29] larue...@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 14:42:19] larue...@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-15 18:18:17] 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')
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=63281&edit=1
