|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73224 Bug when binding values that are compared with columns with KEY constraints
Submitted: 2016-10-02 05:38 UTC Modified: 2016-10-02 19:51 UTC
From: uzbgi at student dot kit dot edu Assigned:
Status: Not a bug Package: PDO MySQL
PHP Version: 7.0.11 OS: Linux - Debian 3.2.73-2
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Bug Type:
From: uzbgi at student dot kit dot edu
New email:
PHP Version: OS:


 [2016-10-02 05:38 UTC] uzbgi at student dot kit dot edu
PDOStatement::bindValue does not work properly when the value in the query is compared with a column that uses mysql "KEY constraints" or mysql "FOREIGN KEY constraints". 

We have the mysql query "SELECT * FROM dogs WHERE owner=:owner".
When we call e.g. $stmt->bindValue(':owner', '101.7', PDO::PARAM_INT) the value 101.7 is rouned to 102, if ':owner' is compared with a column that uses a KEY constraint. But this behaviour is wrong.

The same happens also when binding via PDOStatement::bindParam() or PDOStatement::execute().

This problem does ONLY occur for columns that have mysql key constraints.

Test script:
$host   = "YOUR_HOST";
$dbname = "YOUR_DATABASE";
$user   = "YOUR_USERNAME";
$pass   = "YOUR_PASSWORD";

$pdo    = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass );
$stmt   = $pdo->prepare('SELECT * FROM dogs WHERE owner=:owner');

// 101.7 becomes however 102 
$stmt->bindValue(':owner', '101.7', PDO::PARAM_INT);

print_r( $stmt->fetch(PDO::FETCH_ASSOC) );

# MYSQL script for this test script

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `owner` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner` (`owner`)   # <- This is my KEY constraint 

INSERT INTO `dogs` (`id`, `name`, `owner`) VALUES (1, 'Rex', 101), (2, 'Roxy', 102);

Expected result:
Array ()

Actual result:
Array ( [id] => 2 [name] => Roxy [owner] => 102 )


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2016-10-02 06:11 UTC]
-Status: Open +Status: Feedback
 [2016-10-02 06:11 UTC]
What happens when you execute the queries
  SELECT * FROM dogs WHERE owner=101.7
  SELECT * FROM dogs WHERE owner=102
with and without the index?
 [2016-10-02 14:10 UTC] uzbgi at student dot kit dot edu
Thanks, great idea! ;-)
For both of your queries I get a non empty output. So this problem is not a PHP fault.
 [2016-10-02 14:45 UTC] uzbgi at student dot kit dot edu
You can avoid this problem by allowing NULL values for those columns like the column 'owner'. A better solution I couldn't find.
 [2016-10-02 19:51 UTC]
-Status: Feedback +Status: Not a bug
 [2016-10-02 19:51 UTC]
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Apr 20 06:01:28 2024 UTC