#46408 [Opn]: Locale number format settings can cause pg_query_params to break with numerics

ID: 46408 Updated by: jani@php.net Reported By: alec at smecher dot bc dot ca Status: Open Bug Type: PostgreSQL related Operating System: Debian testing PHP Version: 5.2.7RC2 New Comment: I guess it's an issue always if extension does 'convert_to_string()'. Easily avoided in code: Only do setlocale() prior to outputting stuff. :) Previous Comments: ------------------------------------------------------------------------ [2008-11-18 23:16:44] alec at smecher dot bc dot ca Thanks, lsmith and RhodiumToad. FYI, this bug also exists in PDO (I can post reproduce code if it's helpful). ------------------------------------------------------------------------ [2008-11-18 22:59:44] lsmith@php.net <RhodiumToad> lsmith: in a parameterized query it's always wrong to use locale-specific delimiters RhodiumToad is also known as Andrew Gierth and is a highly respected expert on #postgresql on freenode. As such I will reopen the bug .. ------------------------------------------------------------------------ [2008-10-31 18:28:57] alec at smecher dot bc dot ca FYI, there's a discussion of the same bug, which also appeared (in a separate implementation) in the implementation of the Pear::DB package: <http://pear.php.net/bugs/bug.php?id=3021> ------------------------------------------------------------------------ [2008-10-28 22:54:37] alec at smecher dot bc dot ca In case it wasn't clear, the bug IMO is that pg_query_params is using a means to convert doubles to strings that is potentially incompatible with the database. ------------------------------------------------------------------------ [2008-10-28 19:40:39] alec at smecher dot bc dot ca Description: ------------ Using PHP's setlocale function can cause number formats to change (e.g. formatting floats using a comma instead of a decimal, such as 3,5 instead of 3.5). This causes pg_query_params to break when binding floats into the parameter array, as psql doesn't understand comma-formatted numbers. You may need to generate the hr_HR locale on your system in order for number formatting to work as described above once setlocale has been called (see the debug "echo" in the reproduce code). I reproduced the problem with 5.2.6 and 5.2.7RC2. Reproduce code: --------------- <?php ini_set('display_errors', E_ALL); setlocale(LC_ALL, 'hr_HR.utf-8', 'hr_HR'); $number = 3.5; $dbuser = 'putdbusernamehere'; $dbpass = 'putdbpasswordhere'; echo "Three and a half is: " . $number . " (should come out as 3,5)\n"; $conn = pg_connect("host=localhost user=$dbuser password=$dbpass"); $result = pg_query_params($conn, 'SELECT $1::numeric', array($number)); pg_close($conn); ?> Expected result: ---------------- Three and a half is: 3,5 (should come out as 3,5) Actual result: -------------- Three and a half is: 3,5 (should come out as 3,5) Warning: pg_query_params(): Query failed: ERROR: invalid input syntax for type numeric: "3,5" in /home/asmecher/cvs/ojs2-stable/test.php on line 13 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=46408&edit=1