How to ignore question mark as placeholder when using PDO with PostgreSQL

Question

Note:

This question can be considered as duplicate of this Question. It does point to the same problem with PDO. But its workaround solution is a bit different as the target differ. I will post there the workaround for JSONB and the link to the PHP ticket.

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.

Is there a proper way to configure PDO to ignore question mark as placeholders?

I will post a workaround bellow. Hoping there is a better way

Edit I add a ticket at PHP bug tracing system

Answer 1

This is a workaround to my problem. It solve the problem by using PostgreSQL function to replace the ? operator.

I don't really like it because it does not make PDO more compliance to PostgreSQL. But I found no real solution.

CREATE FUNCTION json_key_exists(JSONB,TEXT) RETURNS BOOLEAN LANGUAGE SQL STABLE AS $f$
    SELECT $1 ? $2
$f$;

And now I can use the query:

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

The workaround was suggested by the fabulous RhodiumToad from freenode #postgresql

Edit

As @Abelisto suggested, there is no need to create the function above as jsonb_exists(jsonb, text) is avialabe

Answer 2

Since PHP 7.4, supports for escaping question mark have landed.

(...) question marks can be escaped by doubling them (...). That means that the “??” string would be translated to “?” when sending the query to the database, whereas “?” is still going to be interpreted as a positional parameter placeholder.

In your example, you can use:

$sql = "SELECT * FROM post WHERE locations ?? :location;";

Answer 3

Ok, the simplest way is to create the new operator with the same options, like:

-- Operator: ~!@#%^&(jsonb, text)

-- DROP OPERATOR ~!@#%^&(jsonb, text);

CREATE OPERATOR 
  ~!@#%^& -- Change it to any other non-conflicted symbols combination
(
  PROCEDURE = jsonb_exists,
  LEFTARG = jsonb,
  RIGHTARG = text,
  RESTRICT = contsel,
  JOIN = contjoinsel);
COMMENT ON OPERATOR ~!@#%^&(jsonb, text) IS 'key exists';

(Original script was generated by pgAdmin)

And use it in usual way like

SELECT * FROM post WHERE locations ~!@#%^& :location;