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

Edit report at https://bugs.php.net/bug.php?id=80653&edit=1 ID: 80653 Comment by: kieran at supportpal 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: 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. Previous Comments: ------------------------------------------------------------------------ [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