Bug #80653 [Opn]: MySQL BIGINT UNSIGNED value in prepared statement treated incorrectly

Edit report at https://bugs.php.net/bug.php?id=80653&edit=1 ID: 80653 User updated by: gman dot n at xrbr dot com Reported by: gman dot n at xrbr dot com Summary: MySQL BIGINT UNSIGNED value in prepared statement treated incorrectly Status: Open Type: Bug Package: MySQLi related Operating System: Ubuntu 20.04.1 LTS PHP Version: 7.4.14 Block user comment: N Private report: N New Comment: Done: https://bugs.mysql.com/bug.php?id=102338 Previous Comments: ------------------------------------------------------------------------ [2021-01-21 21:43:13] 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 21:24:28] 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 19:33:46] 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 19:16:03] 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 18:47:06] 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 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=80653&edit=1