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
 [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-2024 The PHP Group
All rights reserved.
Last updated: Mon Dec 30 14:01:28 2024 UTC