php - PostgreSQL 参数绑定(bind)与 JSONB 运算符冲突

Question

我正在更新我的 postgreSQL 数据库中的一个 JSONB 字段(使用 PHP Symfony DBAL)，但我也使用参数式数据绑定(bind)来消除 SQL 注入(inject)的风险。

我的查询是这样的:

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);

我的代码是这样的:

$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]
        );

我得到的错误:

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

显然是 JSONB 运算符？被误认为是数据绑定(bind)项。有没有办法解决？

Answer 1

有一个bug report ，这可能会涵盖您的问题。

您可以尝试此处提到的以下解决方案:

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