Sequelize中PGP_SYM_DECRYPT与PGP_SYM_ENCRYPT的查询问题

核心问题原因 #

PGP_SYM_ENCRYPT默认会生成随机初始化向量（IV），相同明文每次加密的密文结果不同，所以你用PGP_SYM_ENCRYPT(key, 密钥)生成的密文，和数据库中已存储的同明文加密值几乎不可能匹配，这就是直接加密查询key无效的根本原因。

正确解决思路 #

不要加密查询明文去匹配密文，而是解密数据库中的加密key字段，和传入的明文key做等值对比。

代码示例 #

修改where子句的写法，用Sequelize的where方法实现解密后对比：

return await database.Example.findOne({
  attributes: {
    include: [
      [Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('value'), 'bytea'), process.env.AES_ENCRYPTION_KEY), 'value'],
      [Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('key'), 'bytea'), process.env.AES_ENCRYPTION_KEY), 'key'],
    ]
  },
  where: {
    user_id: id,
    // 解密库中key字段后和明文key对比
    [Sequelize.Op.eq]: Sequelize.where(
      Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('key'), 'bytea'), process.env.AES_ENCRYPTION_KEY),
      key
    )
  }
});

或者用Sequelize.and组合条件的写法：

return await database.Example.findOne({
  attributes: {
    include: [
      [Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('value'), 'bytea'), process.env.AES_ENCRYPTION_KEY), 'value'],
      [Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('key'), 'bytea'), process.env.AES_ENCRYPTION_KEY), 'key'],
    ]
  },
  where: Sequelize.and(
    { user_id: id },
    Sequelize.where(
      Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.cast(Sequelize.col('key'), 'bytea'), process.env.AES_ENCRYPTION_KEY),
      '=',
      key
    )
  )
});

额外注意事项 #

• 确保加密存储时的参数和查询解密一致：如果存储时指定了加密算法（如PGP_SYM_ENCRYPT(key, 'aes256', process.env.AES_ENCRYPTION_KEY)），解密时必须用相同的算法参数。
• 检查字段存储类型：如果数据库中key字段是base64编码的文本（而非bytea），解密前需要先解码：

  Sequelize.fn('PGP_SYM_DECRYPT', Sequelize.fn('decode', Sequelize.col('key'), 'base64'), process.env.AES_ENCRYPTION_KEY)
  

内容的提问来源于stack exchange，提问作者Why so Async