php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #71885 PostgreSQL has questin mark operator. This collide with PDO placeholder
Submitted: 2016-03-23 09:16 UTC Modified: 2019-07-25 15:43 UTC
Votes:11
Avg. Score:4.5 ± 0.7
Reproduced:11 of 11 (100.0%)
Same Version:8 (72.7%)
Same OS:6 (54.5%)
From: miki at epoch dot co dot il Assigned: mbeccati (profile)
Status: Closed Package: PDO PgSQL
PHP Version: Irrelevant OS: NA
Private report: No CVE-ID: None
 [2016-03-23 09:16 UTC] miki at epoch dot co dot il
Description:
------------
When I prepare the following query:

SELECT * FROM post WHERE locations ? :location;

The following warning occur:

Warning: PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line xx

The question mark is an valid PostgreSQL operator but PDO condsider it as a placeholder. - http://www.postgresql.org/docs/9.5/static/functions-json.html

I thought of several solutions:

1. Allow to disable question mark as placeholder. Leaving you with named placeholders.
2. Allow to change the placeholder char from a default value (?).
3. Teach pg driver to distinguish when it is a placeholder or an operator.

I think I favor point 2.

I also discussed it here:
http://stackoverflow.com/questions/36173440/how-to-ignore-question-mark-as-placeholder-when-using-pdo-with-postgresql


Patches

Pull Requests

Pull requests:

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-03-23 09:46 UTC] requinix@php.net
-Type: Bug +Type: Feature/Change Request
 [2016-03-23 09:46 UTC] requinix@php.net
Turn off emulated prepares.
 [2016-03-23 12:05 UTC] rowan dot collins at gmail dot com
Enabling or disabling emulated prepares does not work around this issue, it just changes which error messages you see.

In the simplest case, with no actual parameters:

- When emulation is enabled, the ? will be treated as a placeholder by the emulation code itself, and raise an error about the parameter not being filled.
- When emulation is disabled, PDO will still parse it and replace ? with $1, so that Postgres will expect a parameter. (This then gives a syntax error because that's an illegal position for a placeholder.)

In the case where there is a real named parameter alongside the ? operator (as in the OP's example), PDO parses the statement to detect whether named or positional parameters were used (because the driver may need to rewrite the syntax) and will complain that it's found a mixture *before even attempting to prepare it*.

The ability to change (or disable) the placeholder char would fix all these cases, since the ? placeholder is being read by PDO in all cases: either to emulate prepares, or to rewrite to the syntax appropriate for the selected DB driver.
 [2016-03-24 07:16 UTC] miki at epoch dot co dot il
Hi Requinx,

The following code failed:

$sql = "SELECT * FROM post WHERE locations ? :location ORDER BY locations->:location"

$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES ,false);

$stmt = $pdo->prepare($sql); // tried this and version bellow
$stmt = $pdo->prepare($sql, [\PDO::ATTR_EMULATE_PREPARES=>false]);

$stmt->bindValue(':location', 'bar', \PDO::PARAM_STR);
$stmt->execute();

Error when ATTR_EMULATE_PREPARES is false that happen at the line of the prepare():

Warning: PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line 57

Error when ATTR_EMULATE_PREPARES is true that happen at the line of the execute():

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in /path/file.php on line 48

Any idea?
 [2016-03-24 07:20 UTC] miki at epoch dot co dot il
I missed rowan answer.
So I guess ATTR_EMULATE_PREPARES is irrelevant
 [2016-03-24 07:38 UTC] 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.
 [2016-03-24 08:08 UTC] 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-04-04 08:05 UTC] 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-04-12 12:40 UTC] 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-06-30 10:58 UTC] mbeccati@php.net
-Assigned To: +Assigned To: mbeccati
 [2016-07-01 02:30 UTC] 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 = '?'
 [2017-10-24 06:41 UTC] kalle@php.net
-Status: Assigned +Status: Open -Assigned To: mbeccati +Assigned To:
 [2019-07-25 15:43 UTC] mbeccati@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: mbeccati
 [2019-07-25 15:43 UTC] mbeccati@php.net
Implemented in PHP 7.4 (since beta1). 

See https://wiki.php.net/rfc/pdo_escape_placeholders
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Oct 06 01:01:27 2024 UTC