我目前正在使用
示例代码:
有什么想法吗?
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"
);
有什么想法吗?