How to prevent PDO from interpreting a question mark as a placeholder?

Question

For detecting the existence of a key in a hstore, I need to run a query like this:

SELECT * FROM tbl WHERE hst ? 'foo'

However, that gives me a PDOException:

PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound: SELECT * FROM tbl WHERE hst ? 'foo'

Is there any way to escape the question mark so PDO won't pick it up as a placeholder? I've tried with up to four backslashes, as well as a double question mark (??), but nothing seems to persuade PDO to leave the question mark alone.

Tautologize

Answer 1

Use the function call form. According to the system catalogs, the hstore ? operator uses the exist function:

regress=# select oprname, oprcode from pg_operator where oprname = '?';
 oprname | oprcode 
---------+---------
 ?       | exist
(1 row)

so you can write:

SELECT * FROM tbl WHERE exist(hst,'foo');

(Personally I'm not a big fan of hstore's operator-centric design and documentation, I think it discards the useful self-documenting properties of a function based interface without any real benefit and I usually use its function calls rather than its operators. Just because you can define operators doesn't mean you should.)

Verrazano

i am using this for now, but unfortunately there is not a function replacement for ?& and ?| operators. I guess i can write my own... sucks. – Gnarly

Eh? There has to be a function backing any operator. Sure enough, plugging them into the above, ?& is exists_all and ?| is exists_any. – Verrazano

When substituting the ? operator for use with JSONB data types, replace EXIST() with JSONB_EXISTS(). – Winterwinterbottom

Answer 2

I had the same problem when searching on JSONB data. The full question is here

SELECT * FROM post WHERE locations ? :location;

The workaround on PostgreSQL 9.5 is similar:

SELECT * FROM post WHERE jsonb_exists(locations, :location);

I also opened a ticket at PHP bug tracing system

Update

As Diabl0 mentioned, the proposed solution work but does not use the index. Tested with:

CREATE INDEX tempidxgin ON post USING GIN (locations);

Hatching

Please do not post your answer for all the questions of this kind. instead, mark them as a duplicate. – Kulda

@YourCommonSense I mentioned that the workaround (solution) is different and worthwhile emphasizing. I have no idea what do you mean by all. It is focused and defined in the appropriate places. – Hatching

This solution as it works has one huge downside - querying using jsonb functions don't use indexes which may lead to significant performance downgrade. – Rutabaga

Answer 3

As of PHP 7.4 there's now ?? to escape ? as an operator

so you can rewrite

SELECT * FROM tbl WHERE hst ? 'foo'

as

SELECT * FROM tbl WHERE hst ?? 'foo'

source: https://www.php.net/manual/en/pdo.prepare.php

pull request which added it https://github.com/php/php-src/pull/4217

Bullnose

Answer 4

I suggest you disable PDO native prepared statement so question marks will be ignored:

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

Percheron

Whichever way you set this parameter, PDO does some statement parsing, and misinterprets the ?. – Exsanguinate

This answer works for me, though it didn't on a version of PHP I was using before. Tried to change downvote to upvote, won't let me unless you revise ;) – Winterwinterbottom

This solution is in the right direction to a "no surprise" pure SQL player. However it seems to depend on both PHP version, and the driver used; and it is not clear (from PHP documentation) what exactly we loose be removing emulation (here too the answer seems to be driver-dependent). Here it works with PHP 7.2 + PostgreSQL 14. – Gradygrae

For PostgreSQL: the PHP code tells that ATTR_EMULATE_PREPARES implies an internal emulate that (paradoxily) implies PDO_PLACEHOLDER_NONE (that I understand as "do emulate neither ? nor :param"). So for PostgreSQL, counter-intuitively, activating emulation deactivates all kind of preprocessing by PDO; thus the ? gets passed to the PQ library (PostgreSQL client library) which does the right thing (use ? as an operator, not a param placeholder). – Gradygrae