How to disable question mark character binding in SQL query when using PDO?

Question

PHP

The SQL query uses the question mark symbol - ? and PDO tries to bind a value ($1) to it, but in this situation it is completely unnecessary, because the question mark character is an SQL statement for working with the JSONB data type. How to get around this value assignment mechanism?

$statement = "SELECT id FROM public.parameter WHERE variations ?| array[ 'something' ] LIMIT 1 OFFSET 0;";
$sth = $this->pdo->prepare( $statement );
$sth->execute();

Answer 1

To solve this situation, http://stackoverflow.com suggests creating your own operators without a symbol ?and it looks like this:

-- Because PHP::PDO replace symbol '?' into SQL-query we can not use this operator for work with JSONB type.

-- alternative operator for '?'
CREATE OPERATOR [email protected] (LEFTARG = jsonb, RIGHTARG = text, PROCEDURE = jsonb_exists);

-- alternative operator for '?|'
CREATE OPERATOR [email protected]| (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_any);

-- alternative operator for '?&'
CREATE OPERATOR [email protected]& (LEFTARG = jsonb, RIGHTARG = text[], PROCEDURE = jsonb_exists_all);

Now the PHP code above has become working for me (of course, I replaced the operator ?|with[email protected]|

$statement = "SELECT id FROM public.parameter WHERE variations [email protected]| array[ '" . mb_strtolower( $title ) . "' ] LIMIT 1 OFFSET 0;";
$sth = $this->pdo->prepare( $statement );
$sth->execute();

But, friends, this is a crutch!

Answer 2

Replace with exists_any(variations, array[ 'something' ])
stackoverflow.com/questions/16311939/how-to-preven...