Edit report at https://bugs.php.net/bug.php?id=71885&edit=1
ID: 71885
Comment by: miki at epoch dot co dot il
Reported by: miki at epoch dot co dot il
Summary: PostgreSQL has questin mark operator. This collide
with PDO placeholder
Status: Open
Type: Feature/Change Request
Package: PDO PgSQL
Operating System: NA
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
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?
Previous Comments:
------------------------------------------------------------------------
[2016-03-23 12:05:46] 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-23 09:46:09] requinix@php.net
Turn off emulated prepares.
------------------------------------------------------------------------
[2016-03-23 09:16:11] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=71885&edit=1