Req #71885 [Com]: PostgreSQL has questin mark operator. This collide with PDO placeholder

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: 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. Previous Comments: ------------------------------------------------------------------------ [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. ------------------------------------------------------------------------ [2016-03-24 07:20:17] miki at epoch dot co dot il I missed rowan answer. So I guess ATTR_EMULATE_PREPARES is irrelevant ------------------------------------------------------------------------ [2016-03-24 07:16:03] 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-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. ------------------------------------------------------------------------ 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