php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80653 MySQL BIGINT UNSIGNED value in prepared statement treated incorrectly
Submitted: 2021-01-21 18:47 UTC Modified: 2021-01-30 09:54 UTC
From: gman dot n at xrbr dot com Assigned: dharman (profile)
Status: Not a bug Package: MySQLi related
PHP Version: 7.4.14 OS: Ubuntu 20.04.1 LTS
Private report: No CVE-ID: None
 [2021-01-21 18:47 UTC] gman dot n at xrbr dot com
Description:
------------
When using MySQLi prepared statement, everything works as expected when using only a single BIGINT UNSIGNED column by itself:

Using one column only: "... WHERE `val_hash`=?"
test value A: 8969302881072144 => result: works.
test value B: 13326067295508650029 => result: works.

The query returns the expected result in both cases. However, when adding a second column, which in my scenario is a TINYINT column, the SELECT query will work for test value A, but not yield any results for test value B. This occurs probabaly because the test value B ist a BIGINT UNSIGNED value is higher than PHP_INT_MAX.

Using two columns: "... WHERE `val_type`=? AND `val_hash`=?"
test value A: 8969302881072144 => result: works.
test value B: 13326067295508650029 => result: DOES NOT WORK.

Therefore, there must be a bug that messes up the BIGINT UNSIGNED value when higher than PHP_INT_MAX.

Test script:
---------------
Full script: https://pastebin.com/g4ZCG3sB

Shorted version:

<?php

$connection = new \mysqli(...);

$statement1 = $connection->prepare('SELECT `val_id`, `val_type` FROM `copy_pw_values` WHERE `val_type`=? AND `val_hash`=?');

$type3 = 3;
$hash3 = '13326067295508650029';
$statement1->bind_param('is', $type3, $hash3);
$statement1->execute();
echo $statement1->get_result()->num_rows;
// Returns 0, should return 1.

$type4 = '3';
$hash4 = '13326067295508650029';
$statement1->bind_param('ss', $type4, $hash4);
$statement1->execute();
echo $statement1->get_result()->num_rows;
// Returns 0, should return 1.

Expected result:
----------------
1111
11
11


Actual result:
--------------
1100
11
11


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-01-21 19:16 UTC] dharman@php.net
-Status: Open +Status: Feedback
 [2021-01-21 19:16 UTC] dharman@php.net
I can't reproduce it. I tried with PHP 7.3, 7.4.14 and 8.0. I tried on Windows and Linux. I tried it with MySQL and MariaDB. I even tried it online, but I always get the same output: https://phpize.online/?phpses=ff46fef22cb57979f1280d43f0ad2729&sqlses=null&php_version=php7&sql_version=mysql57

1111
11
11

Are you sure there isn't something else in your environment that causes the wrong output?
 [2021-01-21 19:33 UTC] gman dot n at xrbr dot com
-Status: Feedback +Status: Open
 [2021-01-21 19:33 UTC] gman dot n at xrbr dot com
Thanks for checking this out! Using your phpize.online link, when I change to MySQL 8.0 it does no longer work. I immediately checked and I'm using MySQL 8.0.22-0ubuntu0.20.04.3 (should have added that in the beginning, sorry!).

So is it an issue with PHP or MySQL?

Updated link:
https://phpize.online/?phpses=ff46fef22cb57979f1280d43f0ad2729&sqlses=null&php_version=php7&sql_version=mysql80
 [2021-01-21 21:24 UTC] kieran at supportpal dot com
This issue looks very similar to one that I'm experiencing.

Here's a reproducer:
https://phpize.online/?phpses=10da649bcd37236fa6f1498f6adcdba0&sqlses=aa6be73c673b7971675e7fc46729579d&php_version=php8&sql_version=mysql80

Change `int unsigned` to `int` and the query works:
https://phpize.online/?phpses=eab1ced5f425974278c22777c0e79f58&sqlses=aa6be73c673b7971675e7fc46729579d&php_version=php8&sql_version=mysql80

The issue only occurs on MySQL 8 (not 5.x versions), and affects all PHP versions. 

Emulating prepared statements seems to be a workaround to the problem.
 [2021-01-21 21:43 UTC] dharman@php.net
Thanks, I can see that there is a problem. I have been looking into the issue for the past hour and I still haven't narrowed it down, but I have some more findings I would like to keep a record of here. It looks like the only affected version is MySQL 8.0.22. I am 90% sure that the bug is on MySQL side...

What I have found out so far is:
- It affects all PHP versions
- Engine is irrelevant
- As you have noticed the type (both column and binding) of the second column impacts the result
- Only certain numbers are affected. There is a pattern, but it's unclear to me what that pattern is. The bigger the numbers get the more irregular the pattern becomes. e.g. numbers between 13326067295508630 and 13326067295508660 (00 fail, 11-success):
11	13326067295508630
00	13326067295508631
11	13326067295508632
11	13326067295508633
11	13326067295508634
00	13326067295508635
11	13326067295508636
11	13326067295508637
11	13326067295508638
00	13326067295508639
11	13326067295508640
11	13326067295508641
11	13326067295508642
00	13326067295508643
11	13326067295508644
11	13326067295508645
11	13326067295508646
00	13326067295508647
11	13326067295508648
11	13326067295508649
11	13326067295508650
00	13326067295508651
11	13326067295508652
11	13326067295508653
11	13326067295508654
00	13326067295508655
11	13326067295508656
11	13326067295508657
11	13326067295508658
00	13326067295508659

-----------------------------
- The type used in binding makes huge difference, regardless of what it is or which parameter it is. For example, this works fine:

$type3 = 30;
$statement1->bind_param('ss', $hash3, $type3);
$statement1->execute();
echo $statement1->get_result()->num_rows;
$type4 = 30;
$statement1->bind_param('ss', $hash3, $type4);
$statement1->execute();
echo $statement1->get_result()->num_rows;

but this does not

$type3 = 30;
$statement1->bind_param('si', $hash3, $type3);
$statement1->execute();
echo $statement1->get_result()->num_rows;
$type4 = 30;
$statement1->bind_param('ss', $hash3, $type4);
$statement1->execute();
echo $statement1->get_result()->num_rows;

This leads me to believe that the problem is on MySQL side with the way the optimizer handles type casting in prepared statements. MySQL 8.0.22 has changed the way that prepared statements are parsed and optimized. This looks like an unintentional bug. The only explanation I have for this erratic behaviour is that there is a bug in the new way that MySQL handles PS and type casting. I see no way that mysqli or mysqlnd could produce such bug. 

Therefore, I would kindly ask you to report it to Oracle (https://bugs.mysql.com/) as soon as possible.
 [2021-01-21 22:54 UTC] gman dot n at xrbr dot com
Done: https://bugs.mysql.com/bug.php?id=102338
 [2021-01-22 10:58 UTC] kieran at supportpal dot com
Suggest to close. MySQL have verified a regression in 8.0.22+
 [2021-01-22 11:41 UTC] dharman@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: dharman
 [2021-01-22 11:41 UTC] dharman@php.net
Reported upstream with MySQL.
 [2021-01-22 13:45 UTC] cmb@php.net
-Status: Closed +Status: Not a bug
 [2021-01-22 13:45 UTC] cmb@php.net
Should be not a bug, then. :)
 [2021-01-30 09:54 UTC] gman dot n at xrbr dot com
There is a new comment in the MySQL bug tracker ( https://bugs.mysql.com/bug.php?id=102338 ) regarding this issue:


[29 Jan 12:51] Georgi Kodinov
Posted by developer:
 
Can I please get the exact sequence of libmysql C API calls resulting from the above PHP snippet to reproduce the bug?
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Wed Oct 27 08:03:36 2021 UTC