如何在 .select 语句中使用 pgcrypto 和 knex？（Postgres 数据库）

Question

我目前正在使用knex将我的node.js服务器连接到一个postgres数据库，并开始使用pgcrypto加密部分数据。我有一些查询需要更新，想找到最有效的方法来不仅交换我的查询，而且实际查询数据库。当我尝试在knex.select()查询中直接实现PGP_SYM_DECRYPT时，我会收到一个错误，说找不到用户。然而，如果我使用knex.raw()查询，我可以让它正常工作。是否有办法在.select()查询中使用PGP_SYM_DECRYPT，或者通过查询传递密钥，以便自动解密任何加密列？

示例代码：

const user = await knex("n_user AS u")
  .where({
    "u.uuid": uuid,
    "su.site_id": site.id
  })
  .first()
  .join("site_has_user AS su", { "su.user_id": "u.id" })
  .select(
    "u.id",
    "u.uuid",
    "u.mobile_number",
    "u.email",
    "u.first_name",
    "u.last_name",
    "u.department",
    // "u.note", the note is the encrypted data
    "u.disabled",
    "su.role"
  )
.select(
  knex.raw(
    `PGP_SYM_DECRYPT(u.note::bytea, '${process.env.SECRET_KEY}') as note`
  )
);

期望的代码（或其他变体）示例：

const user = await knex("n_user AS u")
  .where({
    "u.uuid": uuid,
    "su.site_id": site.id
  })
  .first()
  .join("site_has_user AS su", { "su.user_id": "u.id" })
  .select(
    "u.id",
    "u.uuid",
    "u.mobile_number",
    "u.email",
    "u.first_name",
    "u.last_name",
    "u.department",
    `PGP_SYM_DECRYPT(u.note::bytea, '${process.env.SECRET_KEY}') as note`,
    "u.disabled",
    "su.role"
  );

有什么想法吗？

- Psyk

Answer 1

您可以像这样在选择器中添加原始代码片段：

  .select(
    "u.id",
    "u.uuid",
    "u.mobile_number",
    "u.email",
    "u.first_name",
    "u.last_name",
    "u.department",
    knex.raw("PGP_SYM_DECRYPT(??::bytea, ?) as note", ['u.note', process.env.SECRET_KEY]),
    "u.disabled",
    "su.role"
  );

在原始语法中，?? 是标识符替换，而 ? 则是值绑定，所以将密钥作为绑定传递给驱动程序，而不是尝试直接将其插入到 SQL 字符串中，从而保证安全性。

- Mikael Lepistö