|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35103 mysqli bind_result() incorrectly handles unsigned int
Submitted: 2005-11-04 11:17 UTC Modified: 2005-12-02 09:49 UTC
From: php at pjberkel dot com Assigned: andrey (profile)
Status: Closed Package: MySQLi related
PHP Version: 5.1CVS-2005-12-02 (cvs) OS: *
Private report: No CVE-ID: None
 [2005-11-04 11:17 UTC] php at pjberkel dot com
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 (, however in this case, mysqli_stmt_bind_result should probably cast the result to a float.

Reproduce code:

$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)");

$stmt = $mysqli->prepare("SELECT id FROM temp");
while ($stmt->fetch()) {
	print $id . "<br>\n";

$mysqli->query("DROP TABLE temp");


Expected result:

Actual result:


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-04 15:53 UTC]
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-05 23:05 UTC]
How about using float type if the returned value is > MAX_INT?
 [2005-11-07 08:34 UTC] 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()) {

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-09 14:44 UTC]
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).

 [2005-11-10 04:36 UTC] php at pjberkel dot com
I compiled the latest CVS snapshot from the 5.1 branch (php5-200511100130) and can confirm that the problem has been fixed for 32bit unsigned INT values.

However, I also did some further testing using the unsigned BIGINT data type (which contain 64bit integer values) and discovered the same problem exists for unsigned values larger than the maximum signed 64bit value:
(9223372036854775807 < int <= 18446744073709551615)

(BTW I'm using as a reference page for MySQL numerical types.)

Changing the following two lines in the original reproduce code fragment:

$mysqli->query("CREATE TABLE temp (id BIGINT UNSIGNED NOT NULL)");
$mysqli->query("INSERT INTO temp (id) VALUES (9223372036854775807),(9223372036854775808),(18446744073709551614),(18446744073709551615)");

Expected result:
string(19) "9223372036854775807"
string(19) "9223372036854775808"
string(20) "18446744073709551614"
string(20) "18446744073709551615"

Actual result:
string(19) "9223372036854775807"
string(20) "-9223372036854775808"

I don't want to push the envelope too much on this as I guess it would be extremely rare for anyone to encounter this bug, but if there is a quick, easy solution then it's probably a good idea to fix the unsigned BIGINT problem too.

Any plans to backport this to the 5.0.x branch?

 [2005-11-21 21:52 UTC]
I have a fix for this problem as well as speedup of the current code that handles unsigned ints on 32bit but I will push it after 5.1.0 is released because it's a bit too late in the release cycle.
 [2005-11-30 17:26 UTC]
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
Thank you for the report, and for helping us make PHP better.

PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Jun 16 15:01:28 2024 UTC