php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #38643 PDO/mysql: wrong results in prepared statement when using parameter twice
Submitted: 2006-08-29 15:18 UTC Modified: 2006-08-30 16:19 UTC
From: chris dot vigelius at gmx dot net Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.1.5 OS: Linux
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: chris dot vigelius at gmx dot net
New email:
PHP Version: OS:

 

 [2006-08-29 15:18 UTC] chris dot vigelius at gmx dot net
Description:
------------
If a parameter appears more than once in a parametrized query, unexpected behaviour occurs


Reproduce code:
---------------
$dbh = new PDO(...)
$stmt = $dbh->prepare("SELECT * FROM user WHERE name=:user OR email=:user");
$stmt->execute(array(':user' => 'franz'));

Expected result:
----------------
only the rows are returned where either name or email are set to 'franz'

Actual result:
--------------
always all rows of the table are returned, which is clearly not what one would expect

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-08-29 20:45 UTC] tony2001@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5.2-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.2-win32-latest.zip


 [2006-08-30 11:11 UTC] chris dot vigelius at gmx dot net
the current snapshot does NOT solve the problem - there are 
still all rows returned.
 [2006-08-30 11:12 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.


 [2006-08-30 11:27 UTC] chris dot vigelius at gmx dot net
// prepare the database
$dbh = new 
PDO('mysql:host=localhost;dbname=test', 'test', 'test');
$dbh->exec("DROP TABLE IF EXISTS user");
$dbh->exec("CREATE TABLE user ( name VARCHAR(32), email 
VARCHAR(32), PRIMARY KEY (name) );");
$dbh->exec("INSERT INTO user VALUES 
('fritz', 'fritz@server.com')");
$dbh->exec("INSERT INTO user VALUES 
('otto', 'otto@server.com')");
		
// this will return 2 rows where 0 are expected
$stmt = $dbh->prepare("SELECT * FROM user WHERE name=:user 
OR email=:user");
$stmt->execute(array(':user' => 'franz'));
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
var_dump($rows);
		
// this workaround will return 0 rows as expected
$stmt = $dbh->prepare("SELECT * FROM user WHERE name=:user1 
OR email=:user2");
$stmt->execute(array(':user1' => 'franz', ':user2' 
=> 'franz'));
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
var_dump($rows);
 [2006-08-30 11:31 UTC] chris dot vigelius at gmx dot net
Expected result:
----------------
array(0) { } array(0) { } 

Actual result:
--------------
array(2) { [0]=>  object(stdClass)#4 (2) { ["name"]=>  
string(5) "fritz" ["email"]=>  
string(16) "fritz@server.com" } [1]=>  object(stdClass)#5 
(2) { ["name"]=>  string(4) "otto" ["email"]=>  
string(15) "otto@server.com" } } array(0) { }
 [2006-08-30 16:19 UTC] iliaa@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

This is a limitation of the MySQL prepared statement syntax 
which does not support re-usable identifiers. To make your 
query work you need to set PDO::ATTR_EMULATE_PREPARES to true.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved.
Last updated: Wed Jan 15 10:01:29 2025 UTC