php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35518 mysqli bind_param() incorrectly handles unsigned int
Submitted: 2005-12-02 10:51 UTC Modified: 2005-12-02 16:29 UTC
From: php at pjberkel dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 6CVS-2005-12-02 (snap) OS: RHEL 4
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: php at pjberkel dot com
New email:
PHP Version: OS:

 

 [2005-12-02 10:51 UTC] php at pjberkel dot com
Description:
------------
This bug report is a follow-up to a previous bug: http://bugs.php.net/bug.php?id=35103

It appears that mysqli_stmt_bind_param suffers from the same unsigned integer problem as mysqli_stmt_bind_result (see bug #35103) when the bind type is set to "i" and the integer value bound is MAX_UNSIGNED_INT < x < MAX_SIGNED_INT.  If the bind type in $stmt->bind_param() is changed to "d" then the reproduce code works as expected.

(Could possibly be related to bug #35428 but the error in this bug report is much more reproducible).  I'm using php5.1-200512020130 + mysql-5.0.16 on RHEL 4. 


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 (3900002281)");

$id_val = 3900002281;

/* BEGIN EXAMPLE OF BUG */
$stmt = $mysqli->prepare("SELECT id FROM temp WHERE id = ?");
$stmt->bind_param("i", $id_val);
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id);
$stmt->close();
/* END EXAMPLE OF BUG */

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

?>

Expected result:
----------------
string(10) "3000000897"


Actual result:
--------------
int(0)


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-12-02 11:12 UTC] tony2001@php.net
>If the bind type in $stmt->bind_param() is
>changed to "d" then the reproduce code works as expected.
Because you are binding a FLOAT?

I can hardly imagine a way to fit a float value in an integer var.
Doesn't look like a bug to me.
 [2005-12-02 14:55 UTC] php at pjberkel dot com
If you check the reproduce code, you'll see that the variable being bound (and also the db column type) is an integer ($id_val = 3900002281) but the problem occurs because the actual integer value is larger than the maximum (signed) 32bit integer value in PHP (while in MySQL it is a valid unsigned 32bit integer value).

Setting the bind type to "d" is a hack / workaround to the problem since double type is presumably 64bit (which will happily store a 32bit unsigned integer value).

The real issue here is that "bind_param("i", $id_val)" should work correctly with integers of any size, not limited to php internal MAX_INT_SIZE limit of 2147483647 (32bit signed integer).

Please let me know if you require further clarification.

Thanks,
Pieter
 [2005-12-02 15:08 UTC] tony2001@php.net
>the variable being bound is an integer ($id_val = 3900002281) 

Hmm.. No?
# php -r '$id_val = 3900002281; var_dump($id_val);'
float(3900002281)

>The real issue here is that "bind_param("i", $id_val)"
>should work correctly with integers of any size, not
>limited to php internal MAX_INT_SIZE limit of 2147483647
>(32bit signed integer).

It's not integer, it's double/float.
Binding floats as integers obviously may not work.
 [2005-12-02 16:29 UTC] php at pjberkel dot com
Ok, so technically you are correct that PHP is storing the value as a float.  My point is that the actual *number* is still an integer and not a float.  I understand that PHP automatically assigns the underlying datatype of a variable based on what is sees fit (which is fine) but in this case it is causing problems.

Consider a typical database application that has integer column types.  In MySQL, both 10000 and 3900002281 are valid (unsigned) 32bit integer values (and thus stored in column type "unsigned int"), while in PHP the former is stored as an "int" while the later as a "float".

Given that an application might expect a particular input value to always be an integer (regardless of its internal representation in PHP) it seems unreasonable and impractical to force the application to determine the internal variable type (at runtime) before binding the variable.

Demonstrating the problem with a small code fragment:

$integers = array(1, 500, 10000, 3800002281, 3900002281); // all valid MySQL unsigned integers
foreach ($integers as $i) {
	$stmt = $mysqli->prepare("SELECT id FROM temp_table WHERE id = ?");
	$stmt->bind_param(is_float($i) ? "d" : "i", $i); // forced to determine varible datatype at runtime
	$stmt->execute();
}

I guess when dealing with large integer values (that might possibly be stored as a float by PHP) it is safe to use the "d" type specifer instead of "i" in bind_parm().  At the very least, the documentation should be updated to make this point a little clearer.

Mental note to self: avoid using unsigned integer columns in mysql! :)
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Tue Nov 29 15:05:53 2022 UTC