#35103 [Asn->Fbk]: mysqli bind_result incorrectly handles unsigned int

ID: 35103 Updated by: andrey@php.net Reported By: php at pjberkel dot com -Status: Assigned +Status: Feedback Bug Type: MySQLi related Operating System: * PHP Version: 5CVS-2005-11-04 (cvs) Assigned To: andrey New Comment: Hi, this has been addressed in the 5.1 branch. So far HEAD (6.0) is not patched, neither 5.0. Fixed is that a value from PS if the platform is 32bit and the type is int(11) unsigned and if the value is > MAX_INT a string will be returned. If the value <= MAX_INT an int will be returned. I know it's not nice to have different types but these are the limitations of PHP. In year or 2 most servers will run on 64bit :) Regarding the types returned. mysqli_query() always returns strings which is not that quite efficient in terms of memory consumption but the underlying libmysql functions return strings. It's matter of choice whether this can be optimized (by using more CPU cycles to reduce memory consumption). Previous Comments: ------------------------------------------------------------------------ [2005-11-07 08:34:38] php at pjberkel dot com Thanks for the suggested workaround, while this does patch the problem it will be preferable to have a permanent fix as updating my entire codebase to deal with this problem will be quite time-consuming. Note that mysqli->query() / mysqli->fetch_row() does not appear to suffer from this bug, changing the prepared statement in the example to the following code: $result = $mysqli->query("SELECT id FROM temp"); while ($row = $result->fetch_row()) { var_dump($row[0]); } $result->close(); Shows that the values are correctly returned as variable type string (show by the results below): string(10) "2147483647" string(10) "2147483648" string(10) "2147483649" string(10) "3800001532" string(10) "3900002281" string(10) "4294967295" For the sake of consistency, it would be a good idea for both mysqli->query() and mysqli->prepare() to return the results using the same variable types. ------------------------------------------------------------------------ [2005-11-05 23:05:41] iliaa@php.net How about using float type if the returned value is > MAX_INT? ------------------------------------------------------------------------ [2005-11-04 15:53:27] andrey@php.net if your code is aware that the variable is unsigned you can get the unsigned value by using sprintf() with %u as format specificator andrey@lmy004:~/test> php -r '$a=-2; printf("%d %u\n", $a, $a);' -2 4294967294 However I think it is good idea to make that implicit so mysqli to return a string (on 32bit) and normal int (on 64bit). ------------------------------------------------------------------------ [2005-11-04 12:46:29] php at pjberkel dot com Compiling the current php5 STABLE CVS snapshot version under RHEL 4 yields the same incorrect results as before: using "var_dump" instead of "print" in the original example gives the results: int(2147483647) int(-2147483648) int(-2147483647) int(-494965764) int(-394965015) int(-1) ------------------------------------------------------------------------ [2005-11-04 11:17:12] php at pjberkel dot com Description: ------------ When using "mysqli_stmt_bind_result" to retrieve a 32bit unsigned integer value from a mysql database (version 4.1.13-standard) that is greater than the maximum *signed* value but less than the maximum *unsigned* value (i.e. 2147483647 < int <= 4294967295), the integer is returned incorrectly as a signed value. I did read in the manual that php does not support unsigned integers (http://www.php.net/manual/en/language.types.integer.php), however in this case, mysqli_stmt_bind_result should probably cast the result to a float. Reproduce code: --------------- <?php $mysqli = new mysqli("host", "user", "pass", "db"); $mysqli->query("CREATE TABLE temp (id INT UNSIGNED NOT NULL)"); $mysqli->query("INSERT INTO temp (id) VALUES (2147483647),(2147483648),(2147483649),(3800001532),(3900002281),(4294967295)"); /* BEGIN EXAMPLE OF BUG */ $stmt = $mysqli->prepare("SELECT id FROM temp"); $stmt->execute(); $stmt->bind_result($id); while ($stmt->fetch()) { print $id . "<br>\n"; } $stmt->close(); /* END EXAMPLE OF BUG */ $mysqli->query("DROP TABLE temp"); $mysqli->close(); ?> Expected result: ---------------- 2147483647 2147483648 2147483649 3800001532 3900002281 4294967295 Actual result: -------------- 2147483647 -2147483648 -2147483647 -494965764 -394965015 -1 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=35103&edit=1