Edit report at https://bugs.php.net/bug.php?id=60695&edit=1
ID: 60695
Comment by: anon at anon dot anon
Reported by: nbari at dalmp dot com
Summary: bind_param 'd' appends a 0
Status: Open
Type: Bug
Package: MySQLi related
Operating System: FreeBSD
PHP Version: 5.3.8
Block user comment: N
Private report: N
New Comment:
>The type of column 'name' is text()
Then bind it as 's'.
Previous Comments:
------------------------------------------------------------------------
[2012-01-10 12:08:47] nbari at dalmp dot com
The type of column 'name' is text()
After inserting the data, I query via mysql console directly on a terminal and
notice the 0, so i can confirm it is not an issue with a software displaying or
formatting numbers with 2 decimal places.
I made the same test using 'i' (for integer) and instead of 99.3 the stored
value
was just 99 (no 0 appended), when using 's' is is working but I start noticing
this strange behaivor when the search string was an double.
my search query/code is like this:
$sql = 'select id from test where name=?';
$param = $_POST['name'];
if (is_numeric($param)) {
$param = !strcmp(intval($param), $param) ? (int)$param :
(!strcmp(floatval($param), $param) ? (float)$param : $param);
}
$type = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ?
's' : 'b'));
$stmt->bind_param($type,$param);
$stmt->execute();
$stmt->close();
Initially I though that the 'sanitation' made was affecting the results but
later manually I specified the types and notice that only with doubles 'd' this
was happening.
------------------------------------------------------------------------
[2012-01-10 05:44:34] anon at anon dot anon
What is the type of the column 'name'?
If it's a string, then bind it as 's' instead of 'd'. If it's a double, there's
no problem; it's the same number and the internal bits of a double do not have
a way to represent "93.3" and "93.30" differently -- the bits are identical, so
the issue is the program or code you're using for *displaying* the number is
formatting it with 2 decimal places on purpose.
------------------------------------------------------------------------
[2012-01-10 02:51:01] nbari at dalmp dot com
Description:
------------
---
>From manual page: http://www.php.net/mysqli-stmt.bind-param#refsect1-mysqli-
stmt.bind-param-parameters
---
When inserting a 'double' using prepared statements
d corresponding variable has type double
if the number is on the format n.y example:1.3, or 99.3, an '0' is appended to
the number, storing something like 1.30, or 99.30 instead of just 1.3 or 99.3
Test script:
---------------
$mysqli = new mysqli('localhost','dalmp','test','test');
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$name = 99.3;
if ($stmt = $mysqli->prepare('INSERT INTO test set name=?')) {
$stmt->bind_param('d',$name);
$stmt->execute();
$stmt->close();
}
$mysqli->close();
#this will store 99.30 instead of just 99.3
Expected result:
----------------
do not alter/append the input with a 0 when selecting 'd' as the bind_parameter
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=60695&edit=1
