node.js - 使用 json 数组在 Postgresql 中进行参数化查询

Question

我想使用参数化查询将 array_prepend 调用到 json[] 中。我正在使用 pg-promise npm 包，但这在后台使用了普通的 node-postgres 适配器。

我的查询是:

db.query(`update ${schema}.chats set messages =
  array_prepend('{"sender":"${sender}","tstamp":${lib.ustamp()},"body":$1}',messages) where chat_id = ${chat_id}`
 , message));

与“$1”相同。

它适用于非参数化查询。

以上代码产生:

{ [error: syntax error at or near "hiya"]

这样做的主要原因是为了避免 sql 注入(inject)(文档说它们在使用参数化查询时充分逃逸)。

Answer 1

您的查询中有 2 个问题。

第一个是您正在使用 ES6 模板字符串，同时还使用带有 ${propName} 语法的 sql 格式化。

From the library's documentation :

Named Parameters are defined using syntax $*propName*, where * is any of the following open-close pairs: {}, (), [], <>, //, so you can use one to your liking, but remember that {} are also used for expressions within ES6 template strings.

因此，您要么从 ES6 模板字符串更改为标准字符串，要么简单地切换到不同的变量语法，例如 $/propName/ 或 $[propName]，这避免冲突的方式。

第二个问题正如我之前在评论中指出的那样，在生成正确的 SQL 名称时，使用记录为 SQL Names 的内容.

下面是一种更清晰的查询格式化方法:

db.query('update ${schema~}.chats set messages = array_prepend(${message}, messages) where chat_id = ${chatId}', {
        schema: 'your schema name',
        chatId: 'your chat id',
        message: {
            sender: 'set the sender here',
            tstamp: 'set the time you need',
            body: 'set the body as needed'
        }
    }
);

当不确定您正在尝试执行哪种查询时，查看它的最快方法是通过 pgp.as.format(query, values)，这将为您提供准确的信息查询字符串。

如果你仍然想使用 ES6 模板字符串来做其他事情，那么你可以将字符串更改为:

`update $/schema~/.chats set messages = array_prepend($/message/, messages) where chat_id = $/chatId/`

这只是一个例子，语法很灵活。请记住不要使用 ES6 模板字符串格式将值注入(inject)查询，因为 ES6 模板不知道如何正确格式化 JavaScript 类型以符合 PostgreSQL，只有库知道。