Edit report at https://bugs.php.net/bug.php?id=71885&edit=1
ID: 71885
Updated by: mbeccati@php.net
Reported by: miki at epoch dot co dot il
Summary: PostgreSQL has questin mark operator. This collide
with PDO placeholder
-Status: Open
+Status: Closed
Type: Feature/Change Request
Package: PDO PgSQL
Operating System: NA
PHP Version: Irrelevant
-Assigned To:
+Assigned To: mbeccati
Block user comment: N
Private report: N
New Comment:
Implemented in PHP 7.4 (since beta1).
See https://wiki.php.net/rfc/pdo_escape_placeholders
Previous Comments:
------------------------------------------------------------------------
[2016-07-01 02:30:43] joshuadburns at hotmail dot com
Confirmed, same bug here as well. Ubuntu 14, PHP 5.5, PostgreSQL 9.4.
The "?" is a pretty commonly used operator within PostgreSQL, so this is most definitely
going to become a problem as people begin migrating to newer versions of PostgreSQL.
For those of you stumbling onto this page in the same predicament, here is an in-line solution-- No
stored procedures necessary, still indexable.
Instead of:
SELECT * FROM my_table WHERE my_col ? 'my_key'
Do one of the following, depending on the PostgreSQL data-type you're working with:
SELECT * FROM my_table WHERE EXIST(my_col, my_key)
SELECT * FROM my_table WHERE JSONB_EXISTS(my_col, my_key)
To query for a full list of functions bound to the "?" operator (this will help you find
other functions for data types not covered above) on your database, perform the following query:
SELECT oprname, oprcode FROM pg_operator WHERE oprname = '?'
------------------------------------------------------------------------
[2016-04-12 12:40:23] rasmus at mindplay dot dk
All of the proposed solutions sound rather complex to me.
I would suggest a much simpler approach: just allow us to escape the question mark placeholder to
make PDO ignore it, e.g. as "\?" - consistent with the way it's done by virtually
every other string template facility in PHP.
------------------------------------------------------------------------
[2016-04-04 08:05:13] mbeccati@php.net
There are plenty of alternatives on the postgres side, i.e. using the underlying function, or
defining a new operator. Adapting PDO is not going to be that easy, in comparison.
------------------------------------------------------------------------
[2016-03-24 08:08:12] miki at epoch dot co dot il
PDO has its own advantages. For example the named placeholders and its general abstraction.
If only we could set the placeholder char or disable the question mark it would be perfect.
------------------------------------------------------------------------
[2016-03-24 07:38:51] yohgaki@php.net
I don't maintain PDO pgsql, but I think it cannot workaround unless introducing new place
holder. i.e. Specify meta char like "LIKE" query.
http://www.postgresql.org/docs/9.5/static/functions-matching.html
Obvious workaround is to use pgsql module. One needs native extension to use most out of PostgreSQL.
------------------------------------------------------------------------
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=71885
--
Edit this bug report at https://bugs.php.net/bug.php?id=71885&edit=1