php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #60695 bind_param 'd' appends a 0
Submitted: 2012-01-10 02:51 UTC Modified: 2012-02-11 00:12 UTC
From: nbari at dalmp dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.3.8 OS: FreeBSD
Private report: No CVE-ID: None
View Developer Edit
 [2012-01-10 02:51 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-01-10 05:44 UTC] 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 12:08 UTC] 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 23:13 UTC] anon at anon dot anon 
>The type of column 'name' is text()
Then bind it as 's'.
 [2012-01-10 23:44 UTC] nbari at dalmp dot com 
The point is that when using 'd' a 0 shouldn't be appended to the inputed value.

Using 's' works but the 'posible' bug report is related that to the 'd' 

When using 'i' the stored value is like the expected, example 99.3 will store 
only 99 and remove the .3 .

But when using 'd' the stored value instead of been or continue to be like the 
input, in some cases an 0 is appended. 

example: 

99.3 is stored like 99.30
99.31 is stored like 99.31
1.1 is stored like 1.10
1.31 is stored like 1.31

for an unknown reason only doubles with 1 decimal are stored or reformatted to be 
have 2 decimals
 [2012-02-05 07:27 UTC] nbari at dalmp dot com 
the problem seems to only occur with MySQL version 5.1, testing with MySQL 
version >= 5.5 the results are as expected
 [2012-02-11 00:12 UTC] johannes@php.net
-Status: Open +Status: Not a bug
 [2012-02-11 00:12 UTC] johannes@php.net 
this depends on the conversion the MySQL server does. If you want some string value send it as string ... else -> MySQL Server Issue, not PHP
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Fri May 09 04:01:27 2025 UTC