php.net |  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
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: 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
Description:
------------
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);
$stmt->execute();

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

/**************
# MYSQL script for this test script

CREATE TABLE IF NOT EXISTS `dogs` (
  `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 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

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 )

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-10-02 06:11 UTC] requinix@php.net
-Status: Open +Status: Feedback
 [2016-10-02 06:11 UTC] requinix@php.net
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] requinix@php.net
-Status: Feedback +Status: Not a bug
 [2016-10-02 19:51 UTC] requinix@php.net
.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved.
Last updated: Tue Mar 11 11:01:31 2025 UTC