Description: ------------ When using a non-english locale (see setlocale() function) where the decimal seperator is a comma, the binding of floats into a SQL statement fails for these reasons. 1. The float is converted to it's string representation when binding. 2. The string representation of the float isn't quoted either. The result is that the SQL statement contains one value too many compared to the number of fields for each binded parameter that has a floating value. The temporary workaround is to call this function on every float passed as bind parameter to the execute() method: number_format($number, 2, '.', ''); (assuming you want your numbers rounded to 2 decimal places) F.y.i. I'm using the DB_mysql 'driver'. Reproduce code: --------------- setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $percent = 10.3 * 2; // string representation: 20,6 $sql = 'INSERT INTO tmp (percent) VALUES (?)'; $sth = $dbh->prepare($sql); if (DB::isError($sth)) { // handle error } $res = $dbh->execute($sth, array($percent)); if (DB::isError($res)) { // Definately handle error here // because SQL statement is sent to the database as: // INSERT INTO tmp (percent) VALUES (20,6) } Expected result: ---------------- I expected it to work and I expected to see nothing. Actual result: -------------- This is sent to the database: INSERT INTO tmp (percent) VALUES (20,6) instead of this: INSERT INTO tmp (percent) VALUES (20.6)

Which version of MySQL are you using? And what happens if you try the same maneuver direclty using mysql_query(). I have a feeling it won't work because the SQL standard uses commas to separate columns.

It of course works with mysql_query() because there the user is responsible for manually building the SQL statement (using number_format on floats etc). Since the DB package supports parameter binding (through emulation sometimes), it should be the responsibility of the DB package to detect the types of these scalar parameters (strings, floats, ints, etc) and 'bind' them correctly independent of the current locale setting. In the case of the DB_mysql class (and perhaps others) where parameter binding is emulated, floats should be formatted with the number_format() function so that they appear in the correct SQL format. The same probably applies other scalar types for example booleans or ints >= 1000 where in many locales the thousands seperator is a ".".

Thanks for your elaborate explanations. Now how about answering my questions? Which version of MySQL are you using? What happens if you run it directly through mysql_query()? Here's a test script: <?php setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $con = mysql_connect('localhost', 'ptuser', 'pts8ft'); $db = mysql_select_db('ptdb', $con); mysql_query('CREATE TABLE blah (a FLOAT)'); mysql_query('INSERT INTO blah (a) VALUES (1,5)'); echo mysql_error($con) . "\n"; $res = mysql_query('SELECT * FROM blah'); $row = mysql_fetch_row($res); echo $row[0] . "\n"; mysql_query('DROP TABLE blah'); ?>

This is the error I get running your sample code: Column count doesn't match value count at row 1 ...which is the same kind of error I get using the DB classes. This is my mysql version string (sorry I overlooked your question the 1st time): mysql Ver 14.7 Distrib 4.1.7, for pc-linux (i686)

I looked through the SQL standard found nothing about internationalizing the column and/or decimal separators. Decimal places must be marked by "." and columns separated by ",". It is not DB's place to implement features that subvert the standard.

I think there's a misunderstanding here. I don't expect DB to place floats in SQL statements in localized formats. A float in an SQL string should always have this format: /^\d+(\.\d+)$/ That's if it is part of an SQL string, i.e. not binded. When you really bind a float, you're not binding a string representation of your float, but the float itself. That's why in low-level bind functions you have to specify the data type. Doing this avoids all problems with localization. The DB classes (well at least DB_mysql) don't use real parameter binding internally. Externally they do: the execute() and query() methods let the user believe he/she is really passing bind parameters to the database when in fact the DB class simply replaces the question marks in the SQL statement with safe string representations of the data (side node: PHP automatically converts all scalar types into their string representation as soon as the are glued into part of another string). This is exactly where it goes wrong, as soon as string representations are made of non-string variables, the result is locale dependent, and things can start going (really) wrong. The solution is: 1. Know the data type for each column. I think the DB classes fetch this metadata from the database, but I'm not sure yet. If not, calling gettype() on each parameter will indicate if they are non-strings. 2. For each non-string type parameter force it into the correct SQL format. That means for floats, call number_format($value, $precision, '.', '') or something similar on them. Another example based on your previous example: This example uses mysqli and goes to show that when a PHP float variable is not automatically converted into a string by PHP that the insert statement works. Note that when using mysqli's real binding that one must indicate the data type (in this case "d"). This example prints "1.5". <?php setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $host = 'localhost'; $usr = 'me'; $pwd = 'secret'; $dbname = 'test'; $value = 1.5; // string representation is 1,5 ! $link = mysqli_connect($host, $usr, $pwd, $dbname); mysqli_query($link, 'CREATE TABLE blah (a FLOAT)'); $stmt = mysqli_prepare($link, 'INSERT INTO blah (a) VALUES (?)'); mysqli_stmt_bind_param($stmt, 'd', $value); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); $res = mysqli_query($link, 'SELECT * FROM blah'); $row = mysqli_fetch_row($res); echo $row[0] . "\n"; mysqli_query($link, 'DROP TABLE blah'); mysqli_close($link); ?>

Here's another example based on my previous example. This is just to show that the same code without the real binding but placing the float into a string will fail for the reasons I've already explained. <?php setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $host = 'localhost'; $usr = 'me'; $pwd = 'secret'; $dbname = 'test'; $value = 1.5; // string representation is 1,5 ! $link = mysqli_connect($host, $usr, $pwd, $dbname); mysqli_query($link, 'CREATE TABLE blah (a FLOAT)'); $sql = 'INSERT INTO blah (a) VALUES (' . $value . ')'; print "About to execute: $sql\n"; if (!mysqli_query($link, $sql)) { // this will croak now! die('Query failed as expected: ' . mysqli_error($link)); } $res = mysqli_query($link, 'SELECT * FROM blah'); $row = mysqli_fetch_row($res); echo $row[0] . "\n"; mysqli_query($link, 'DROP TABLE blah'); mysqli_close($link); ?>

Just set status back to "Open".

Changed from bug to feature request. Changed summary from "Binding floats with non-english locale fails" to "provide prepare/execute support for mysqli."

uhh, I think the point is still being missed here. It's not about mysqli. It's about DB in general. The bind emulation in DB simply does not work correctly. As a matter of fact I discovered it using the DB_mysql class which has nothing to do with mysqli. As I see it it is clearly a bug, not "Bogus" or a "feature request". I'm only trying to help but am losing hope here.

This is not a bug with DB. What you want can only be done by binding variables directly to the prepared statements using a given DBMS's native API. At this time, only the Oracle and Interbase drivers in DB have native prepared statement support.

Hmm... I guess another way to interpret your report is a request for DB to double check data types submitted and reformat them to suit the SQL standards. If that's the case, then please understand that I'll mark that aspect as "won't fix." It's not DB's job to baby sit or encourage programmers to submit non-SQL-standard data.

Well that then is another misinterpretation. There are more scalar types in PHP than just 'string'. A float type cannot be formatted by the programmer. A float remains a float (it is not a string and is not influenced by locale settings)and is passed that way as a bind parameter to the DB's execute method. It's within DB's execute method that the float type is converted to a string type. You can blame the programmer for that, but the way I see it it's the DB's problem as it does the converting from float to string. Anyway, nevermind, I've given up on DB and attempting to get the crux of the problem through to you.

Feel free to say or do whatever you want. It's not DB's problem. The same issue will arise with any DBMS you deal with in PHP unless you're directly binding variables to prepared statements. The example below uses the mysqli_query() interface directly, but the same issue comes up. <?php setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $value = 1.5; echo $value . "\n"; $link = mysqli_connect($host, $usr, $pwd, $dbname); mysqli_query($link, 'CREATE TABLE blah (a FLOAT)'); mysqli_query($link, 'INSERT INTO blah (a) VALUES (' . $value . ')'); echo mysqli_error($link) . "\n"; $res = mysqli_query($link, 'SELECT * FROM blah'); $row = mysqli_fetch_row($res); echo $row[0] . "\n"; mysqli_query($link, 'DROP TABLE blah'); ?> output: 1,5 Column count doesn't match value count at row 1

Well I'll say it once more: In PHP, when you place a float in string context then you are converting it to a string and applying locale formatting. It's not the programmer using DB doing this, it's DB. It's happening within DB when you call the execute() or query() methods. Your example: $value = 12345.67; // Here it's a float, really, believe me, gettype() will return 'double' for this. You can do arithmatic on it for it is still really a float. A float is not a string. By float I don't mean string representation of a float, I really mean float. print 'INSERT INTO blah (a) VALUES (' . $value . ')'; // Here the float $value is being converted to a string since you are using it in string context, and when a float is converted to a string in PHP, locale formatting takes place automatically. This happens IN DB because the DB code can't bother with checking the types of the scalar data it's 'binding'. The solution (for DB) lays in the PHP functions gettype(), is_float, is_int, sprintf('%F',..), number_format(). This is the check and the formatting DB should be doing: if (gettype($value) == 'double') { $value = floatToStringNoLocale($value); } function floatToStringNoLocale($float) { $precision = strlen($float) - strlen(intval($float)); // For all PHP versions: return rtrim(number_format($float, $precision, '.', ''),0); // or for PHP versions from PHP 4.3.10 and PHP 5.0.3: //return rtrim(sprintf("%.${precision}F",$float),0); }

For those who do believe this is a bug and understand the problem, I've included the patch below to fix it. This is the test script to test PEAR DB before and after patching: <?php setlocale(LC_NUMERIC, 'nl_NL@euro', 'nl_NL', 'nl', 'nl_NL.ISO_8859-2', 'nl_NL.ISO_8859-1', 'Dutch'); $dbh = .... connect to database here. $value = 1.5; // string representation is 1,5 ! print "Create table.\n"; @$dbh->execute('CREATE TABLE blah (a FLOAT)'); print "About to prepare.\n"; $sth = $dbh->prepare('INSERT INTO blah (a) VALUES (?)'); print "About to execute.\n"; $dbh->execute($sth, array($value)); // passing a float here. $res = $dbh->query('SELECT * FROM blah'); $row = $res->fetchRow(DB_FETCHMODE_ORDERED); print_r($row); print "Drop table.\n"; $dbh->execute('DROP TABLE blah'); ?> This is the somewhat smarter quoteSmart() function after patching PEAR/DB/common.php version 1.103: function quoteSmart($in) { if (is_int($in)) { return $in; } elseif (is_double($in)) { // From Craig Manley: // Make sure that you don't end up with locale dependent string representations of floats. // Do this by converting the float type into a string type here and apply SQL standard formatting. return rtrim(number_format($in, strlen($in) - strlen(intval($in)), '.', ''),0); } elseif (is_bool($in)) { return $in ? 1 : 0; } elseif (is_null($in)) { return 'NULL'; } else { return "'" . $this->escapeSimple($in) . "'"; } } This is the diff patch for PEAR/DB/common.php version 1.103: d349 1 a349 1 if (is_int($in)) { a350 6 } elseif (is_double($in)) { // From Craig Manley: // Make sure that you don't end up with locale dependent string representations of floats. // Do this by converting the float type into a string type here and apply SQL standard formatting. return rtrim(number_format($in, strlen($in) - strlen(intval($in)), '.', ''),0);

Look at the example in my last comment. This is not DB's problem. It is a problem with ANY _query_ you send to ANY DBMS in PHP. And it's not a PHP bug either. Now, if DB want's to be so kind as to remove programmers' obligation to provide properly formatted input, that's the discussion we're having now. Your idea of adding functionality to quoteSmart() is interesting. I'll consider it further. Changed summary from "provide prepare/execute support for mysqli" to "have quoteSmart() convert floats to strings."

No offence, but it's very clear to me (and probably other readers) that you just don't have a clue what I'm talking about. Perhaps you should re-read and think over everything I' ve said. Anyway, what you do or not do about this bug is up to you. I'm using ADODB from now on. It's designed better and has better support.

Bye. [Change summary from "DB's float to string conversion is not locale independent." back to "have quoteSmart() convert floats to strings" and chang back to Feature Request.]

Daniel, I have to disagree here... AFAIK there have been some commits to php-src to avoid/circumvent (or even just make the behavior consistent) this problem... If a locale is set that has a comma as decimal separator, floats that are concatenated to strings will change it's literal representation, i.e. 1.5 becomes 1,5 (!) $ php -r 'setlocale(LC_ALL, "de_DE"); echo "". 1.5 ."\n"; Will output 1,5 -- and as those string concatenation happens inside the code of DB, it is DB's responsability to maintain the literal represantation of the variables value (or for what reason is quoteSmart() called smart?). My 0.02 <whatever>, Mike

Ok, so just in case this does get fixed, this is the corrected float to string conversion function (my previous one wasn't 100% correct: function floatToStringNoLocale($float) { $precision = strlen($float) - strlen(intval($float)); if ($precision) { $precision--; // don't count decimal seperator } return number_format($float, $precision, '.', ''); } The alternative of simply replacing comma's with dots is not 100% safe because some locales such as Farsi use neither of these seperators but others instead.

I ran into the same problem as the original submitter. I wrote my code without using setlocale. After adding a setlocale(LC_ALL, 'nl_BE') my original code which contained db->execute would not work anymore. I received this error message: "DB Error: value count on row". Applying the function provided by glide solved the problem. I have to agree with glide, I didn't expect my float to be turned into a string that would not be understandable by mysql before submission to the database.

Another implementation could be that PEAR DB changed to a "known-good" locale (for decimal separator purposes), e.g. $original_locale = setlocale(LC_NUMERIC,0); setlocale(LC_NUMERIC, 'C'); .. execute stuff .. setlocale(LC_NUMERIC, $original_locale); (though I don't know whether C is available under Windows or not) - Peter

I have exactly the same issue. Not fixing it means that you're basically requiring programmers to be running locale en_* when they call the DB API... which is not a very happy situation.

I also ran into this issue. This is a 1-year old bug. I see there is a patch. Can somebody explain why the bug is still open ?

I also have to agree that this is a bug. A working script in the US locale will suddenly break when run on another locale because of this. Since the db package takes care of abstracting the generation of the complete SQL statement, it should also abstract the proper formatting of parameters. The Java JDBC api uses a setFloat() function to handle this, and does not require the user to convert floats differently depending on what locale is set. PHP can do it even more transparently, as glider suggested. It seems to me that any of the patch provided by glider and Peter is an adequate solution. We should not have to put float conversions functions all over the place when there is a natural single place to put it. I believe properly handling locales is also part of an api's responsibilities. Still hoping for a fix...

>It's not DB's job to baby sit or encourage programmers >to submit non-SQL-standard data. danielc, would you elaborate on what you mean by "non-SQL-standard data"? The documentation isn't very explicit about which PHP data types are allowed in the data array for $db->execute($st, $data). Are floats assumed to be submitted as PHP strings? > What you want can only be done by binding variables > directly to the prepared statements using a given DBMS's > native API. I don't understand why we don't want to mimic this behaviour in the emulated API?

The current state of things is that floats can't be passed (are not supported) as the '$params' parameter of the function 'query'. The current documentation says that $params is an 'array, string or numeric data to be used in execution of the statement ...' That is incorrect. floats are numeric. try this: echo is_numeric(1.5); the result is obvious. Please patch this bug or change the documentation in such a way that it would be obvious that floats can't be passed as $params and should be manually converted to strings by the user.

I have commited this along with support for scientific notation to MDB2.

I incorporated the patch into DB: --- common.php 14 Jul 2005 02:42:18 -0000 1.138 +++ common.php 18 May 2006 12:10:03 -0000 @@ -424,8 +424,14 @@ */ function quoteSmart($in) { - if (is_int($in) || is_double($in)) { + if (is_int($in)) { return $in; + } elseif (is_double($in)) { + $precision = strlen($in) - strlen(intval($value)); + if ($precision) { + --$precision; // don't count decimal seperator + } + return number_format($in, $precision, '.', ''); } elseif (is_bool($in)) { return $in ? 1 : 0; } elseif (is_null($in)) {

This is silly, please fix this bug. Some people is offenced a little _too_ much and doesn't see their own faults. I feel with you glideraerobatics. I don't know if I had bothered trying to explain the issue so many times. Just stepped into this problem myself, guess I'll have to manually use your patch for the moment... Just to state this explicitly: PEOPLE THAT USES LOCALE SETTINGS WHERE THE DECIMAL SEPERATOR IS COMMA WILL NOT BE ABLE TO USE PEAR::DB'S PREPARED STATEMENTS WITH FLOAT DATATYPES Come'on ppl, this bug is _OLD_, you have received a patch, patch it in!

Actually the fix seems to be buggy. I have updated MDB2 sources and tests accordingly: http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/Datatype/Common.php?r1=1.69&r2=1.70&diff_format=u http://cvs.php.net/viewcvs.cgi/pear/MDB2/tests/MDB2_datatype_testcase.php?r1=1.6&r2=1.7&diff_format=u

This bug made us upgrade from DB to MDB2 as it is still not fixed in the current PEAR::DB release. :-( Please could the PEAR::DB team comment on the many postings that clearly state that this is a bug? Why do you not patch PEAR::DB as proposed?

I also had this error and asked about it in the pear-general mailing list. Maybe Mark Wiesemanns comment can be helpful (the entire text is in http://news.php.net/php.pear.general/25698): "It's a bug in DB that a user of DB_Table some weeks ago noted (and he said that he will open a bug report for DB; MDB2 is not affected because of smarter logic for handling of decimals). If you want to open a bug report: The error is in executeEmulateQuery() in DB/common.php. The string concatenation in line 1003 $realquery .= $this->quoteSmart($value); is responsible for the error (quoteSmart() has the right value, but the concatenation breaks the query)."

This bug has been fixed in CVS. If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET). If this was a problem with the pear.php.net website, the change should be live shortly. Otherwise, the fix will appear in the package's next release. Thank you for the report and for helping us make PEAR better. Assuming I'm right in interpreting this rather long bug as "float values should be quoted with the . character as the decimal point in all cases, regardless of locale", then this should be fixed.

FYI, roughly the same bug cropped up in PHP's PostgreSQL module but this solution was dismissed as incorrect. https://bugs.php.net/bug.php?id=46408