Bug #102338 Prepared statements treat integer values incorrectly (using PHP MySQLi)
Submitted: 21 Jan 22:53 Modified: 22 Jan 8:04
Reporter: G Man Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:8.0.22 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: regression

[21 Jan 22:53] G Man
Description:
Please see https://bugs.php.net/bug.php?id=80653

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.

Additional comments by dharman at php.net :
- It looks like the only affected version is MySQL 8.0.22.
- It affects all PHP versions.
- Engine is irrelevant.
- 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
...

How to repeat:
Test script by dharman at php.net :
https://phpize.online/?phpses=ff46fef22cb57979f1280d43f0ad2729&sqlses=null&php_version=php...

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
[22 Jan 8:04] MySQL Verification Team
Hello!

Thank you for the report and test case.

regards,
Umesh
[22 Jan 8:17] MySQL Verification Team
-- Looks like regression. Issue seen since 8.0.22+

C:\php>php test.php
Current PHP version: 8.0.1
MySQL Server: 8.0.22
array(0) {
}

C:\php>php test.php
Current PHP version: 8.0.1
MySQL Server: 8.0.23
array(0) {
}
-- 5.6.50, 5.7.33 and 5.7.20 - looks fine

C:\php>php test.php
Current PHP version: 8.0.1
MySQL Server: 5.6.50
array(1) {
  [0]=>
  array(2) {
    ["b"]=>
    int(892373780064030720)
    [0]=>
    int(892373780064030720)
  }
}

C:\php>php test.php
MySQL Server: 5.7.33
array(1) {
  [0]=>
  array(2) {
    ["b"]=>
    int(892373780064030720)
    [0]=>
    int(892373780064030720)
  }
}

C:\php>php test.php
Current PHP version: 8.0.1
MySQL Server: 8.0.21-commercial
array(1) {
  [0]=>
  array(2) {
    ["b"]=>
    int(892373780064030720)
    [0]=>
    int(892373780064030720)
  }
}
[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?