php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Doc Bug #77954 Binding value as PDO::PARAM_INT has different behaviour in PHP 7.2
Submitted: 2019-05-01 08:59 UTC Modified: 2019-05-01 09:14 UTC
Votes:5
Avg. Score:3.8 ± 0.7
Reproduced:4 of 5 (80.0%)
Same Version:3 (75.0%)
Same OS:3 (75.0%)
From: f dot bosch at genkgo dot nl Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.2.17 OS: Ubuntu
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: f dot bosch at genkgo dot nl
New email:
PHP Version: OS:

 

 [2019-05-01 08:59 UTC] f dot bosch at genkgo dot nl
Description:
------------
When binding a value against a MySQL PDO statement, the behaviour between PHP 7.1 and PHP 7.2 differs. When you bind a value as integer while passing a float, this was no problem with PHP7.1. However, it became a problem in PHP7.2.

The problem is when executing the following. It inserts 0.5 in PHP7.1, and 0 in PHP7.2. I have tested this against MariaDB (10.1 and 10.2), not against MySQL. Another person also found difference in behaviour: https://stackoverflow.com/questions/54413798/pdoparam-int-behaviour-in-php-7-1-php-7-2.

$stmt = $pdo->prepare('INSERT INTO tbl (val) VALUES (?)');
$stmt->bindValue(1, 0.5, PDO::PARAM_INT);

See also this pull request in the Laravel Framework for this behaviour change: https://github.com/laravel/framework/pull/28379.

Test script:
---------------
<?php
$pdo = new PDO('mysql:dbname=floattest;host=127.0.0.1', 'root', '');

$create = $pdo->prepare('CREATE TABLE IF NOT EXISTS tbl (val DECIMAL(9,2))');
$create->execute();

$del = $pdo->prepare('DELETE FROM tbl');
$del->execute();

$ins = $pdo->prepare('INSERT INTO tbl (val) VALUES (?)');
$ins->bindValue(1, 0.5, PDO::PARAM_INT);
$ins->execute();

$sel = $pdo->prepare('SELECT * FROM tbl');
$sel->execute();
var_dump($sel->fetchColumn(0));

Expected result:
----------------
0.5

Actual result:
--------------
0

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2019-05-01 09:02 UTC] nikic@php.net
-Status: Open +Status: Feedback
 [2019-05-01 09:02 UTC] nikic@php.net
Why do you think the previous behavior was correct? It seems right to me that if an integer binding is requested it should be bound as an integer.
 [2019-05-01 09:10 UTC] f dot bosch at genkgo dot nl
-Status: Feedback +Status: Open
 [2019-05-01 09:10 UTC] f dot bosch at genkgo dot nl
You are right. I totally agree that the previous behaviour was not correct. Since I could not find any reason why the behaviour change was there, no backward-incompatibility notice in this regard, I was wondering if this is intensional. This could affect quite some people, especially those using the Laravel framework and/or only its database component.

If you no idea, please just close the report.
 [2019-05-01 09:14 UTC] nikic@php.net
-Type: Bug +Type: Documentation Problem
 [2019-05-01 09:14 UTC] nikic@php.net
This should be due to bug #73234 / https://github.com/php/php-src/commit/32b6154a61fae820386527f3019f8c5937fc5d27.

Changing this to a doc problem as this probably should indeed be mentioned in the upgrading guide.
 [2019-08-30 09:45 UTC] oleg at andreyev dot lv
This fix actually triggered another issue:
- Let's say we have foo table and it's big
- We have an SQL `SELECT * FROM foo WHERE bar IN (:param)`
- We bindValue('param', 'baz', \PDO::PARAM_INT) // INT is a mistake obviously 
- As result PDO will send `SELECT * FROM foo WHERE bar IN (0)` which is a valid query and it's causing MySQL (probably because of type-conversion) to return all rows that 

I think we should add some checks to `pdo_parse_params` and make sure that type of value is correct otherwise throw error.
 [2019-11-05 16:18 UTC] kawewong at gmail dot com
Not sure is my problem a bug here.

$sql = 'SELECT * FROM `my_table` WHERE `id` = :id AND `deleted` = :deleted LIMIT 0, 1';
$Sth = $this->Db->PDO($this->connectKey)->prepare($sql);
$Sth->bindValue(':deleted', 0);
$Sth->bindValue(':id', '1');
$Sth->execute();
$result = $Sth->fetchObject();// always return false in PHP 7.2+ but return correct result in PHP 7.1-

`$result` expect result is object.
Actual in PHP 7.2+: `false`.
Actual in PHP 7.1-: object.

From https://www.php.net/manual/en/pdostatement.bindvalue.php page, the `$data_type` argument default value is `PDO::PARAM_STR`.
So, if I add `PDO::PARAM_STR` to third argument (for PHP 7.2+) the result will be return correctly.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Oct 16 00:01:27 2024 UTC