PostgreSQL param绑定与JSONB运算符冲突

Question

I'm updating a JSONB field in my postgreSQL database (using PHP Symfony DBAL) but I'm also using parameter-style data binding to remove the risk of SQL-injection.

My query looks like this:

UPDATE car SET 
  features = 
    CASE
      WHEN features ? 'exterior' THEN
        JSONB_SET(features, '{exterior, ' || :type || '}', TO_JSONB(:property::TEXT))
      ELSE JSONB_SET(features, '{exterior}', '{' || :type || ': ' || :property || '}'::JSONB)
    END
WHERE id IN (:ids);

My code looks like this:

$stmt = $this->db()->executeQuery($sql,
           ['type' => 'color', 'property' => 'red', 'ids' => [12,32,43,232,3442]],
           ['type' => \PDO::PARAM_STR, 'date' => \PDO::PARAM_STR, 'ibcodes' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
        );

The error I get:

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1" 
LINE 4:             
WHEN features $1 'exterior' THEN        

Obviously the JSONB operator ? is mistaken for a data binding item. Is there any way around this?

Answer 1

There is a bug report, which might cover your issue.

You can try the following solutions mentioned there:

For now I found multiple ways to make it work:

1. use prepared statements (i.e. use :name or ? placeholder, etc.)
2. set the PDO flag PDO::ATTR_EMULATE_PREPARES to true
3. apply pgsql C-Style escape syntax 1, i.e. VALUES(E'\'':1)

About 2): this worked for simple cases but failed in a large application, didn't yet investigate why

About 3): haven't dug deeper whether this can cause more troubles or not