Bug #73224 [Fbk->Nab]: Bug when binding values that are compared with columns with KEY constraints
Edit report at https://bugs.php.net/bug.php?id=73224&edit=1
ID: 73224
Updated by: requinix@php.net
Reported by: uzbgi at student dot kit dot edu
Summary: Bug when binding values that are compared with
columns with KEY constraints
-Status: Feedback
+Status: Not a bug
Type: Bug
Package: PDO MySQL
Operating System: Linux - Debian 3.2.73-2
PHP Version: 7.0.11
Block user comment: N
Private report: N
New Comment:
.
Previous Comments:
------------------------------------------------------------------------
[2016-10-02 14:45:58] 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 14:10:43] 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 06:11:21] 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 05:38:19] 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 )
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=73224&edit=1
Thread (5 messages)