MySQL Bugs: #102338: Prepared statements treat integer values incorrectly (using PHP MySQLi)

Bug #102338	Prepared statements treat integer values incorrectly (using PHP MySQLi)
Submitted:	21 Jan 2021 22:53	Modified:	15 Oct 2021 9:55
Reporter:	G Man	Email Updates:
Status:	No Feedback	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

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

Hello!

Thank you for the report and test case.

regards,
Umesh

-- 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)
  }
}

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?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

It seems this was fixed in 8.0.28

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html possibly something to do with the 'Data Type Notes' and MySQL Connectors :shrug: