如何通过Sequelize原生Raw Query正确更新JSONB类型字段？

问题描述 #

我在Profile模型中定义了JSONB类型的loginPrize字段：

loginPrize: {
    type: DataTypes.JSONB,
    defaultValue : {
        day : 0,
        lastClaimed : dayjs().subtract(1,'d').format(),
        collected : false
    }
},

尝试用Sequelize原生Raw Query更新该字段，代码如下：

let loginPrize = {
    day: prizes.day,
    lastClaimed: dayjs().format(),
    collected: true
}
let stringPrize = JSON.stringify(loginPrize)
await Profile.sequelize?.query(
`update profile set ${prizes.type} = ${prizes.type} + ${prizes.prize} , 
 loginPrize = to_jsonb(${stringPrize}::jsonb) ,where "playerId" = ${id}
 `)

执行时报错："syntax error at or near "{"""，请问该怎么正确实现这个字段的更新？

问题分析&解决办法 #

核心问题 #

1. 直接把JSON字符串拼进SQL，会让SQL解析器把{当成语法符号而非字符串内容，触发语法错误；
2. 手动拼接变量存在SQL注入风险；
3. 原始SQL中where前面多了个逗号，也是语法错误的诱因。

正确写法（推荐参数绑定） #

用Sequelize的参数绑定功能，既避免语法错误，又杜绝SQL注入，还不用手动处理JSON转换：

let loginPrize = {
    day: prizes.day,
    lastClaimed: dayjs().format(),
    collected: true
};

await Profile.sequelize?.query(
    `UPDATE profile 
     SET "${prizes.type}" = "${prizes.type}" + :prize,
         loginPrize = :loginPrize
     WHERE "playerId" = :playerId`,
    {
        replacements: {
            prize: prizes.prize,
            loginPrize: loginPrize, // Sequelize自动处理JSONB类型转换
            playerId: id
        }
    }
);

备选方案（手动处理JSON，不推荐） #

如果非要手动拼接JSON，需要把JSON字符串用单引号包裹，同时转义内部单引号（仍有注入风险）：

let stringPrize = JSON.stringify(loginPrize).replace(/'/g, "''"); // 转义单引号避免SQL语法错误
await Profile.sequelize?.query(
`UPDATE profile 
 SET "${prizes.type}" = "${prizes.type}" + ${prizes.prize},
     loginPrize = '${stringPrize}'::jsonb
 WHERE "playerId" = ${id}`
);

内容的提问来源于stack exchange，提问作者user10596155