Bug #46408 [Com]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1
ID: 46408
Comment by: alec at smecher dot bc dot ca
Reported by:alec at smecher dot bc dot ca
Summary:Locale number format settings can cause
pg_query_params to break with numerics
Status: Wont fix
Type: Bug
Package:PostgreSQL related
Operating System: *
PHP Version:5.*, 6
Assigned To:yohgaki
Block user comment: N
Private report: N
New Comment:
Pull request filed at https://github.com/php/php-src/pull/186
Previous Comments:
[2012-09-06 18:03:28] alec at smecher dot bc dot ca
I suggest this patch.
diff -u -r php-5.4.6/ext/pgsql/pgsql.c php-5.4.6-mod/ext/pgsql/pgsql.c
--- php-5.4.6/ext/pgsql/pgsql.c 2012-08-14 21:26:05.0 -0700
+++ php-5.4.6-mod/ext/pgsql/pgsql.c 2012-09-06 10:59:45.0 -0700
@@ -23,6 +23,7 @@
/* $Id$ */
#include
+#include
#define PHP_PGSQL_PRIVATE 1
@@ -1736,7 +1737,15 @@
} else {
zval tmp_val = **tmp;
zval_copy_ctor(&tmp_val);
+
+ // PSQL requires . for radix; convert to string,
+ // avoiding problems with doubles and locales
+ // using , as a radix character instead
+ // (see https://bugs.php.net/bug.php?id=46408)
+ char *current_locale = setlocale(LC_NUMERIC,
"C");
convert_to_string(&tmp_val);
+ setlocale(LC_NUMERIC, current_locale);
+
if (Z_TYPE(tmp_val) != IS_STRING) {
php_error_docref(NULL TSRMLS_CC,
E_WARNING,"Error converting parameter");
zval_dtor(&tmp_val);
[2012-04-18 12:44:31] claude dot pache at gmail dot com
@yohgaki (and others)
I think that, the root of the problem is the way PHP uses
the locale information, which I consider deeply broken.
Here are the details:
In my understanding, the locale information is useful only
for *output*, i.e. for messages destined to the user.
They should not be used for any internal conversion
from one type to the other, unless the result is
destined to output.
The problem is, that PHP uses the locale for any
automatic conversion from number to string.
This behaviour is ok in the following case:
echo "Three and a half is: " . $number;
However, in the following cases, this is NOT correct,
because the resulting string must not be localised:
* constructing a JSON object (I hope that json_encode()
does NOT use internal number-to-string conversion);
* using bcmath package (I have personnaly be bitten by
this misfeature);
* construct a SQL request (the present case);
* etc.
In all these cases, you have to do one of the following options:
(1) never use any locale other than en_US
(and re-implement manually the locale feature);
(2) carefully check the type of each and every parameter
and explicitely perform a correct conversion when needed,
e.g. using number_format(..., '.', '');
(3) fix PHP to NOT use locale for number-to-string conversion
unless it is explicitely asked for
(side note: historically, there has been a similar
problem with the "magic quote" misfeature);
(4) modify the modules bcmath, postgresql, etc,
so that they circumvent the mentionned PHP misfeature,
i.e., they do the option (2) above at your place.
In my dreams, the option (3) would be implemented,
but pragmatically, I think that option (4)
has more chance to be implemented rapidly, if ever.
I think that alec asked precisely the option (4) to be implemented.
(Personnally, I have opted for option (1).)
Claude
P.S. The option (4) might seem a non-optimal hack.
However, do not forget that programming languages
and API should be adapted to the needs of the programmers,
and not the other way round.
[2012-04-18 03:13:12] yohg...@php.net
You misunderstand how libpq/PostgreSQL works.
If you think you can make proper patch for this, clone git source and send pull
request.
No one will stop you from that.
------------------------
[2012-04-18 02:58:26] alec at smecher dot bc dot ca
I believe pg_query_params is broken until this is resolved, but it looks like
we're not going to agree on it. I hope someone else can speak up if they do
think this is a bug.
Since we disagree on the approach any
Bug #46408 [Com]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1
ID: 46408
Comment by: alec at smecher dot bc dot ca
Reported by:alec at smecher dot bc dot ca
Summary:Locale number format settings can cause
pg_query_params to break with numerics
Status: Wont fix
Type: Bug
Package:PostgreSQL related
Operating System: *
PHP Version:5.*, 6
Assigned To:yohgaki
Block user comment: N
Private report: N
New Comment:
I suggest this patch.
diff -u -r php-5.4.6/ext/pgsql/pgsql.c php-5.4.6-mod/ext/pgsql/pgsql.c
--- php-5.4.6/ext/pgsql/pgsql.c 2012-08-14 21:26:05.0 -0700
+++ php-5.4.6-mod/ext/pgsql/pgsql.c 2012-09-06 10:59:45.0 -0700
@@ -23,6 +23,7 @@
/* $Id$ */
#include
+#include
#define PHP_PGSQL_PRIVATE 1
@@ -1736,7 +1737,15 @@
} else {
zval tmp_val = **tmp;
zval_copy_ctor(&tmp_val);
+
+ // PSQL requires . for radix; convert to string,
+ // avoiding problems with doubles and locales
+ // using , as a radix character instead
+ // (see https://bugs.php.net/bug.php?id=46408)
+ char *current_locale = setlocale(LC_NUMERIC,
"C");
convert_to_string(&tmp_val);
+ setlocale(LC_NUMERIC, current_locale);
+
if (Z_TYPE(tmp_val) != IS_STRING) {
php_error_docref(NULL TSRMLS_CC,
E_WARNING,"Error converting parameter");
zval_dtor(&tmp_val);
Previous Comments:
[2012-04-18 12:44:31] claude dot pache at gmail dot com
@yohgaki (and others)
I think that, the root of the problem is the way PHP uses
the locale information, which I consider deeply broken.
Here are the details:
In my understanding, the locale information is useful only
for *output*, i.e. for messages destined to the user.
They should not be used for any internal conversion
from one type to the other, unless the result is
destined to output.
The problem is, that PHP uses the locale for any
automatic conversion from number to string.
This behaviour is ok in the following case:
echo "Three and a half is: " . $number;
However, in the following cases, this is NOT correct,
because the resulting string must not be localised:
* constructing a JSON object (I hope that json_encode()
does NOT use internal number-to-string conversion);
* using bcmath package (I have personnaly be bitten by
this misfeature);
* construct a SQL request (the present case);
* etc.
In all these cases, you have to do one of the following options:
(1) never use any locale other than en_US
(and re-implement manually the locale feature);
(2) carefully check the type of each and every parameter
and explicitely perform a correct conversion when needed,
e.g. using number_format(..., '.', '');
(3) fix PHP to NOT use locale for number-to-string conversion
unless it is explicitely asked for
(side note: historically, there has been a similar
problem with the "magic quote" misfeature);
(4) modify the modules bcmath, postgresql, etc,
so that they circumvent the mentionned PHP misfeature,
i.e., they do the option (2) above at your place.
In my dreams, the option (3) would be implemented,
but pragmatically, I think that option (4)
has more chance to be implemented rapidly, if ever.
I think that alec asked precisely the option (4) to be implemented.
(Personnally, I have opted for option (1).)
Claude
P.S. The option (4) might seem a non-optimal hack.
However, do not forget that programming languages
and API should be adapted to the needs of the programmers,
and not the other way round.
[2012-04-18 03:13:12] yohg...@php.net
You misunderstand how libpq/PostgreSQL works.
If you think you can make proper patch for this, clone git source and send pull
request.
No one will stop you from that.
------------------------
[2012-04-18 02:58:26] alec at smecher dot bc dot ca
I believe pg_query_params is broken until this is resolved, but it looks like
we're not going to agree on it. I hope someone else can speak up if they do
think this is a bug.
Since we disagree on the approach any patch I write to correct it will be
rejected.
I'll add a comment to the manual page for pg_query_params to document this.
-
Bug #46408 [Wfx]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1
ID: 46408
User updated by:alec at smecher dot bc dot ca
Reported by:alec at smecher dot bc dot ca
Summary:Locale number format settings can cause
pg_query_params to break with numerics
Status: Wont fix
Type: Bug
Package:PostgreSQL related
Operating System: *
PHP Version:5.*, 6
Assigned To:yohgaki
Block user comment: N
Private report: N
New Comment:
I believe pg_query_params is broken until this is resolved, but it looks like
we're not going to agree on it. I hope someone else can speak up if they do
think this is a bug.
Since we disagree on the approach any patch I write to correct it will be
rejected.
I'll add a comment to the manual page for pg_query_params to document this.
Previous Comments:
[2012-04-18 02:26:48] yohg...@php.net
BTW, you are reading PostgreSQL manual wrong.
libpq's functions never care about data types, but the server is.
If you are curious still, try to make patch that meets the requirement I've
wrote.
[2012-04-18 02:19:38] yohg...@php.net
IIRC, MDB2 cares data types. (which I think it's a design problem for loosely
type langs.) Therefore, it may change behavior according to locale.
Anyway, It's not a matter of argument. It's the way it works. As I explained
repeatedly, ALL params are passed as string and types are NEVER cared in C API.
To know the data type, module should issue an additional query to get meta
data.
Additional query for each query is severe performance hit.
Therefore, it is a PHP programmer's responsibility (or Perhaps, PostgreSQL. If
they would like to change behavior via client environment vars. I guess they
would not.)
If you could submit patch that works for all data types and does not require
additional query, I'll review it.
--------------------
[2012-04-18 01:30:38] alec at smecher dot bc dot ca
Your example of storing a string-formatted double in a varchar column strikes
me as an unusual case involving a type mismatch, and can be worked around
clearly and logically by passing "$number" in rather than $number.
If this isn't going to be fixed, there should at least be documentation in the
manual for pg_query_params: "If binding numeric data for use as numeric data,
make sure you first cast it to a string using '.' for a decimal separator." But
that makes no sense at all to me.
Meanwhile, this same issue has been solved elsewhere as I've suggested.
Gnome: https://bugzilla.gnome.org/show_bug.cgi?id=650016
Pear::DB: http://pear.php.net/bugs/bug.php?id=3021
It's also implemented in these other cases with specific number formatting code
as I believe it should:
Pear::MDB2:
http://svn.php.net/viewvc/pear/packages/MDB2/trunk/MDB2/Driver/Datatype/Common.php?view=markup#l1498
mysqli: https://github.com/php/php-src/blob/master/ext/mysqli/mysqli_api.c#L102
"PostgreSQL users are supposed to pass/recieve data via strings *always* with
C/C++ API." -- this is also incorrect, if I understand you properly. See the
paramTypes column documented here
http://www.postgresql.org/docs/9.1/static/libpq-exec.html for the PQexecParams
function that you're using.
[2012-04-17 21:36:47] yohg...@php.net
PostgreSQL users are supposed to pass/recieve data via strings *always* with
C/C++ API.
Programmers are responsible how it's passed.
As as mentioned already, system will not know how numbers should be formatted
for certain column. The code even don't care the column types of tables.
Think,
create table test (a text);
and
insert into test (a) values ("number");
The number format is decided by PHP programmer, not pgsql module.
[2012-04-17 20:15:02] alec at smecher dot bc dot ca
That's not correct. In the supplied example, the data is provided to
pg_query_params as numeric data. The number to string conversion is done within
pg_query_params (here, I think:
https://github.com/php/php-src/blob/master/ext/pgsql/pgsql.c#L1739).
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=46408
--
Edit this bug report at https://bugs.php.net/bug.php?id=46408&edit=1
Bug #46408 [Wfx]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1
ID: 46408
User updated by:alec at smecher dot bc dot ca
Reported by:alec at smecher dot bc dot ca
Summary:Locale number format settings can cause
pg_query_params to break with numerics
Status: Wont fix
Type: Bug
Package:PostgreSQL related
Operating System: *
PHP Version:5.*, 6
Assigned To:yohgaki
Block user comment: N
Private report: N
New Comment:
Your example of storing a string-formatted double in a varchar column strikes
me as an unusual case involving a type mismatch, and can be worked around
clearly and logically by passing "$number" in rather than $number.
If this isn't going to be fixed, there should at least be documentation in the
manual for pg_query_params: "If binding numeric data for use as numeric data,
make sure you first cast it to a string using '.' for a decimal separator." But
that makes no sense at all to me.
Meanwhile, this same issue has been solved elsewhere as I've suggested.
Gnome: https://bugzilla.gnome.org/show_bug.cgi?id=650016
Pear::DB: http://pear.php.net/bugs/bug.php?id=3021
It's also implemented in these other cases with specific number formatting code
as I believe it should:
Pear::MDB2:
http://svn.php.net/viewvc/pear/packages/MDB2/trunk/MDB2/Driver/Datatype/Common.php?view=markup#l1498
mysqli: https://github.com/php/php-src/blob/master/ext/mysqli/mysqli_api.c#L102
"PostgreSQL users are supposed to pass/recieve data via strings *always* with
C/C++ API." -- this is also incorrect, if I understand you properly. See the
paramTypes column documented here
http://www.postgresql.org/docs/9.1/static/libpq-exec.html for the PQexecParams
function that you're using.
Previous Comments:
[2012-04-17 21:36:47] yohg...@php.net
PostgreSQL users are supposed to pass/recieve data via strings *always* with
C/C++ API.
Programmers are responsible how it's passed.
As as mentioned already, system will not know how numbers should be formatted
for certain column. The code even don't care the column types of tables.
Think,
create table test (a text);
and
insert into test (a) values ("number");
The number format is decided by PHP programmer, not pgsql module.
------------------------
[2012-04-17 20:15:02] alec at smecher dot bc dot ca
That's not correct. In the supplied example, the data is provided to
pg_query_params as numeric data. The number to string conversion is done within
pg_query_params (here, I think:
https://github.com/php/php-src/blob/master/ext/pgsql/pgsql.c#L1739).
[2012-04-17 20:06:10] yohg...@php.net
>PostgreSQL is hard-coded to parse numeric values using "." as the decimal
>point
(see
>https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.
>c in set_var_from_str for implementation).
The number *may* be a string, what we should do then?
Is it "3.5" or "3,5"? Only the PHP programmer who is writing the code knew
which
should be. Therefore, this won't fix.
[2012-04-17 19:52:49] alec at smecher dot bc dot ca
PostgreSQL is hard-coded to parse numeric values using "." as the decimal point
(see
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.c
in set_var_from_str for implementation).
This agrees with the SQL92 grammar for a numeric literal:
http://savage.net.au/SQL/sql-92.bnf.html#exact%20numeric%20literal
...so by my reading PHP needs fixing.
[2012-04-17 19:33:10] yohg...@php.net
I've tried different locale see if it works and it doesn't.
I also found this
http://www.postgresql.org/docs/8.4/static/locale.html
So , cannot replaced by .
There is other case transparent usage is not allowed. Money is one of them.
http://www.postgresql.org/docs/8.4/static/datatype-money.html
If postgres is going to support conversion, then it is going to work, since it
requires server side settings to work. (i.e. Database initialization, table
definition) It is not a bug or feature that should be fixed in PHP.
It's possible to do locale conversion in module, it would not be a good idea in
general.
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=46408
--
Edit this bug report at https://bugs.php.net/bug.php?id=46408&edit=1
Bug #46408 [Wfx]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1 ID: 46408 User updated by:alec at smecher dot bc dot ca Reported by:alec at smecher dot bc dot ca Summary:Locale number format settings can cause pg_query_params to break with numerics Status: Wont fix Type: Bug Package:PostgreSQL related Operating System: * PHP Version:5.*, 6 Assigned To:yohgaki Block user comment: N Private report: N New Comment: That's not correct. In the supplied example, the data is provided to pg_query_params as numeric data. The number to string conversion is done within pg_query_params (here, I think: https://github.com/php/php-src/blob/master/ext/pgsql/pgsql.c#L1739). Previous Comments: [2012-04-17 20:06:10] yohg...@php.net >PostgreSQL is hard-coded to parse numeric values using "." as the decimal >point (see >https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric. >c in set_var_from_str for implementation). The number *may* be a string, what we should do then? Is it "3.5" or "3,5"? Only the PHP programmer who is writing the code knew which should be. Therefore, this won't fix. ------------------------ [2012-04-17 19:52:49] alec at smecher dot bc dot ca PostgreSQL is hard-coded to parse numeric values using "." as the decimal point (see https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.c in set_var_from_str for implementation). This agrees with the SQL92 grammar for a numeric literal: http://savage.net.au/SQL/sql-92.bnf.html#exact%20numeric%20literal ...so by my reading PHP needs fixing. [2012-04-17 19:33:10] yohg...@php.net I've tried different locale see if it works and it doesn't. I also found this http://www.postgresql.org/docs/8.4/static/locale.html So , cannot replaced by . There is other case transparent usage is not allowed. Money is one of them. http://www.postgresql.org/docs/8.4/static/datatype-money.html If postgres is going to support conversion, then it is going to work, since it requires server side settings to work. (i.e. Database initialization, table definition) It is not a bug or feature that should be fixed in PHP. It's possible to do locale conversion in module, it would not be a good idea in general. ------------------------ [2012-04-17 16:22:31] alec at smecher dot bc dot ca yohgaki, I'm not sure I'm following, but to be clear: the bug entry is not about whether the output is 3,5 or 3.5. The bug is that PostgreSQL throws a syntax error when passing a floating-point number into a parameterized query. See RhodiumToad's quoted comment. You're quoting the following psql query: select 123456789.12345::text::numeric; What I'm trying to point out is this: select 123456789,12345::text::numeric; [2012-04-17 08:21:43] yohg...@php.net It seems it does not work that way. http://www.postgresql.org/docs/8.4/static/locale.html Check that PostgreSQL is actually using the locale that you think it is. The LC_COLLATE and LC_CTYPE settings are determined when a database is created, and cannot be changed except by creating a new database. Other locale settings including LC_MESSAGES and LC_MONETARY are initially determined by the environment the server is started in, but can be changed on-the-fly. You can check the active locale settings using the SHOW command. You may get currency as your locale setting, but not numbers. yohgaki@[local] ~=# select 123456789.12345::text::money; money -- EUR12.345.678.912.345,00 yohgaki@[local] ~=# select 123456789.12345::text::numeric; numeric - 123456789.12345 PHP's pgsql is getting all data as string. If there is a method that returns numbers as you expected, then you'll get your outputs needed. Can do that in PHP. The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=46408 -- Edit this bug report at https://bugs.php.net/bug.php?id=46408&edit=1
Bug #46408 [Wfx]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1 ID: 46408 User updated by:alec at smecher dot bc dot ca Reported by:alec at smecher dot bc dot ca Summary:Locale number format settings can cause pg_query_params to break with numerics Status: Wont fix Type: Bug Package:PostgreSQL related Operating System: * PHP Version:5.*, 6 Assigned To:yohgaki Block user comment: N Private report: N New Comment: PostgreSQL is hard-coded to parse numeric values using "." as the decimal point (see https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/numeric.c in set_var_from_str for implementation). This agrees with the SQL92 grammar for a numeric literal: http://savage.net.au/SQL/sql-92.bnf.html#exact%20numeric%20literal ...so by my reading PHP needs fixing. Previous Comments: [2012-04-17 19:33:10] yohg...@php.net I've tried different locale see if it works and it doesn't. I also found this http://www.postgresql.org/docs/8.4/static/locale.html So , cannot replaced by . There is other case transparent usage is not allowed. Money is one of them. http://www.postgresql.org/docs/8.4/static/datatype-money.html If postgres is going to support conversion, then it is going to work, since it requires server side settings to work. (i.e. Database initialization, table definition) It is not a bug or feature that should be fixed in PHP. It's possible to do locale conversion in module, it would not be a good idea in general. ---- [2012-04-17 16:22:31] alec at smecher dot bc dot ca yohgaki, I'm not sure I'm following, but to be clear: the bug entry is not about whether the output is 3,5 or 3.5. The bug is that PostgreSQL throws a syntax error when passing a floating-point number into a parameterized query. See RhodiumToad's quoted comment. You're quoting the following psql query: select 123456789.12345::text::numeric; What I'm trying to point out is this: select 123456789,12345::text::numeric; [2012-04-17 08:21:43] yohg...@php.net It seems it does not work that way. http://www.postgresql.org/docs/8.4/static/locale.html Check that PostgreSQL is actually using the locale that you think it is. The LC_COLLATE and LC_CTYPE settings are determined when a database is created, and cannot be changed except by creating a new database. Other locale settings including LC_MESSAGES and LC_MONETARY are initially determined by the environment the server is started in, but can be changed on-the-fly. You can check the active locale settings using the SHOW command. You may get currency as your locale setting, but not numbers. yohgaki@[local] ~=# select 123456789.12345::text::money; money -- EUR12.345.678.912.345,00 yohgaki@[local] ~=# select 123456789.12345::text::numeric; numeric - 123456789.12345 PHP's pgsql is getting all data as string. If there is a method that returns numbers as you expected, then you'll get your outputs needed. Can do that in PHP. [2012-03-31 05:43:17] yohg...@php.net I guess setting locale to database locale resolve this issue. [2010-05-14 12:13:40] lewis at peppermind dot de This issue also appears with pg_execute(), when passing float values to the bind array, with a locale that uses comma as decimal separator (such as de_DE and most other european locales). The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=46408 -- Edit this bug report at https://bugs.php.net/bug.php?id=46408&edit=1
Bug #46408 [Wfx]: Locale number format settings can cause pg_query_params to break with numerics
Edit report at https://bugs.php.net/bug.php?id=46408&edit=1 ID: 46408 User updated by:alec at smecher dot bc dot ca Reported by:alec at smecher dot bc dot ca Summary:Locale number format settings can cause pg_query_params to break with numerics Status: Wont fix Type: Bug Package:PostgreSQL related Operating System: * PHP Version:5.*, 6 Assigned To:yohgaki Block user comment: N Private report: N New Comment: yohgaki, I'm not sure I'm following, but to be clear: the bug entry is not about whether the output is 3,5 or 3.5. The bug is that PostgreSQL throws a syntax error when passing a floating-point number into a parameterized query. See RhodiumToad's quoted comment. You're quoting the following psql query: select 123456789.12345::text::numeric; What I'm trying to point out is this: select 123456789,12345::text::numeric; Previous Comments: [2012-04-17 08:21:43] yohg...@php.net It seems it does not work that way. http://www.postgresql.org/docs/8.4/static/locale.html Check that PostgreSQL is actually using the locale that you think it is. The LC_COLLATE and LC_CTYPE settings are determined when a database is created, and cannot be changed except by creating a new database. Other locale settings including LC_MESSAGES and LC_MONETARY are initially determined by the environment the server is started in, but can be changed on-the-fly. You can check the active locale settings using the SHOW command. You may get currency as your locale setting, but not numbers. yohgaki@[local] ~=# select 123456789.12345::text::money; money -- EUR12.345.678.912.345,00 yohgaki@[local] ~=# select 123456789.12345::text::numeric; numeric - 123456789.12345 PHP's pgsql is getting all data as string. If there is a method that returns numbers as you expected, then you'll get your outputs needed. Can do that in PHP. [2012-03-31 05:43:17] yohg...@php.net I guess setting locale to database locale resolve this issue. [2010-05-14 12:13:40] lewis at peppermind dot de This issue also appears with pg_execute(), when passing float values to the bind array, with a locale that uses comma as decimal separator (such as de_DE and most other european locales). [2009-07-26 18:59:12] jerico dot dev at gmail dot com @jani: When I pass in a double, I expect pg_query_params() to prepare it in a way that can be understood by the database independent of my locale settings. AFAIK the implementation of pg_query_params() is also inconsistent with that of the mysql driver which correctly accepts double typed parameters independent of locale. I guess you were not entirely serious when you proposed that one should switch the locale before using pg_query_params(), were you? [2008-11-21 13:09:19] j...@php.net I guess it's an issue always if extension does 'convert_to_string()'. Easily avoided in code: Only do setlocale() prior to outputting stuff. And then restore the locale right after output. :) The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=46408 -- Edit this bug report at https://bugs.php.net/bug.php?id=46408&edit=1
#46408 [Opn]: Locale number format settings can cause pg_query_params to break with numerics
ID: 46408 User updated by: alec at smecher dot bc dot ca 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: Thanks, lsmith and RhodiumToad. FYI, this bug also exists in PDO (I can post reproduce code if it's helpful). Previous Comments: [2008-11-18 22:59:44] [EMAIL PROTECTED] 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-11-10 17:59:56] alec at smecher dot bc dot ca [EMAIL PROTECTED], please have a look at PostgreSQL's number parsing function (set_var_from_str in src/backend/utils/adt/numeric.c, e.g. at <http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/numeric.c?rev=1.114>). It's hard-coded to expect a period as the decimal separator. Long story short, if you supply a floating point value to pg_query_params and you're using a PHP locale that formats floats with a comma, pg_query_params will generate SQL that will cause a parse error. IMO, the use of a locale-specific float-to-string conversion in PHP's implementation of pg_query_params is 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 21:35:51] alec at smecher dot bc dot ca Sorry if I've missed something in the documentation, but this definitely looks like a bug to me. I expect pg_query_params, when given a double, to communicate it to the database properly regardless of PHP's number format settings. Could I have a little more feedback? The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/46408 -- Edit this bug report at http://bugs.php.net/?id=46408&edit=1
#46408 [Bgs]: Locale number format settings can cause pg_query_params to break with numerics
ID: 46408 User updated by: alec at smecher dot bc dot ca Reported By: alec at smecher dot bc dot ca Status: Bogus Bug Type: PostgreSQL related Operating System: Debian testing PHP Version: 5.2.7RC2 New Comment: [EMAIL PROTECTED], please have a look at PostgreSQL's number parsing function (set_var_from_str in src/backend/utils/adt/numeric.c, e.g. at <http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/numeric.c?rev=1.114>). It's hard-coded to expect a period as the decimal separator. Long story short, if you supply a floating point value to pg_query_params and you're using a PHP locale that formats floats with a comma, pg_query_params will generate SQL that will cause a parse error. IMO, the use of a locale-specific float-to-string conversion in PHP's implementation of pg_query_params is the bug. Previous Comments: ---- [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 21:35:51] alec at smecher dot bc dot ca Sorry if I've missed something in the documentation, but this definitely looks like a bug to me. I expect pg_query_params, when given a double, to communicate it to the database properly regardless of PHP's number format settings. Could I have a little more feedback? [2008-10-28 21:30:50] [EMAIL PROTECTED] Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php -------------------- [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: --- 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
#46408 [Bgs]: Locale number format settings can cause pg_query_params to break with numerics
ID: 46408 User updated by: alec at smecher dot bc dot ca Reported By: alec at smecher dot bc dot ca Status: Bogus Bug Type: PostgreSQL related Operating System: Debian testing PHP Version: 5.2.7RC2 New Comment: 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> Previous Comments: [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 21:35:51] alec at smecher dot bc dot ca Sorry if I've missed something in the documentation, but this definitely looks like a bug to me. I expect pg_query_params, when given a double, to communicate it to the database properly regardless of PHP's number format settings. Could I have a little more feedback? [2008-10-28 21:30:50] [EMAIL PROTECTED] Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php ---- [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: --- 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
#46408 [Bgs]: Locale number format settings can cause pg_query_params to break with numerics
ID: 46408 User updated by: alec at smecher dot bc dot ca Reported By: alec at smecher dot bc dot ca Status: Bogus Bug Type: PostgreSQL related Operating System: Debian testing PHP Version: 5.2.7RC2 New Comment: 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. Previous Comments: [2008-10-28 21:35:51] alec at smecher dot bc dot ca Sorry if I've missed something in the documentation, but this definitely looks like a bug to me. I expect pg_query_params, when given a double, to communicate it to the database properly regardless of PHP's number format settings. Could I have a little more feedback? [2008-10-28 21:30:50] [EMAIL PROTECTED] Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php [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: --- 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
#46408 [Bgs]: Locale number format settings can cause pg_query_params to break with numerics
ID: 46408 User updated by: alec at smecher dot bc dot ca Reported By: alec at smecher dot bc dot ca Status: Bogus Bug Type: PostgreSQL related Operating System: Debian testing PHP Version: 5.2.7RC2 New Comment: Sorry if I've missed something in the documentation, but this definitely looks like a bug to me. I expect pg_query_params, when given a double, to communicate it to the database properly regardless of PHP's number format settings. Could I have a little more feedback? Previous Comments: [2008-10-28 21:30:50] [EMAIL PROTECTED] Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.php.net/manual/ and the instructions on how to report a bug at http://bugs.php.net/how-to-report.php [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: --- 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
#46408 [NEW]: Locale number format settings can cause pg_query_params to break with numerics
From: alec at smecher dot bc dot ca Operating system: Debian testing PHP version: 5.2.7RC2 PHP Bug Type: PostgreSQL related Bug description: Locale number format settings can cause pg_query_params to break with numerics 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: --- 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 bug report at http://bugs.php.net/?id=46408&edit=1 -- Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=46408&r=trysnapshot52 Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=46408&r=trysnapshot53 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=46408&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=46408&r=fixedcvs Fixed in CVS and need be documented: http://bugs.php.net/fix.php?id=46408&r=needdocs Fixed in release: http://bugs.php.net/fix.php?id=46408&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=46408&r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=46408&r=needscript Try newer version: http://bugs.php.net/fix.php?id=46408&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=46408&r=support Expected behavior: http://bugs.php.net/fix.php?id=46408&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=46408&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=46408&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=46408&r=globals PHP 4 support discontinued: http://bugs.php.net/fix.php?id=46408&r=php4 Daylight Savings:http://bugs.php.net/fix.php?id=46408&r=dst IIS Stability: http://bugs.php.net/fix.php?id=46408&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=46408&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=46408&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=46408&r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=46408&r=mysqlcfg
