如何在Rails原生SQL查询中解密attr_encrypted加密的数据？

要直接在原生SQL中获取attr_encrypted加密的字段，需要借助PostgreSQL的pgcrypto扩展，结合attr_encrypted的加密规则构造解密逻辑，具体步骤如下：

1. 启用PostgreSQL的pgcrypto扩展 #

首先确保数据库启用pgcrypto扩展，它提供了加密解密所需的核心函数。

方式一：通过Rails迁移启用 #

创建迁移文件：

class EnablePgcrypto < ActiveRecord::Migration[7.0]
  def change
    enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
  end
end

执行迁移：rails db:migrate

方式二：直接在psql命令行执行 #

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. 构造解密的原生SQL查询 #

attr_encrypted默认将加密数据（如encrypted_email）和初始化向量（如encrypted_email_iv）以Base64编码字符串存储，需先解码为PostgreSQL的bytea类型，再用对应算法解密。

假设你的Rails模型配置如下：

class User < ApplicationRecord
  attr_encrypted :email, key: Rails.application.credentials.attr_encrypted_key, algorithm: 'aes-256-cbc'
end

使用参数化查询避免SQL注入，解密的Rails代码示例：

key = Rails.application.credentials.attr_encrypted_key
decrypted_users = User.find_by_sql([<<~SQL, key])
  SELECT 
    id,
    convert_from(
      decrypt(
        decode(encrypted_email, 'base64'),
        convert_to(?, 'UTF8'),
        decode(encrypted_email_iv, 'base64')
      ), 'UTF8'
    ) AS email
  FROM users
SQL

关键函数说明： #

• decode(encrypted_field, 'base64')：将Base64编码的加密字符串转为bytea类型
• convert_to(密钥, 'UTF8')：将字符串密钥转为bytea类型，匹配attr_encrypted的密钥处理逻辑
• decrypt(加密数据bytea, 密钥bytea, IV bytea)：用AES-CBC算法解密（对应attr_encrypted默认的aes-256-cbc算法）
• convert_from(解密后的bytea, 'UTF8')：将解密后的二进制数据转为UTF8编码的字符串

3. 非默认加密算法的适配 #

如果你的attr_encrypted使用了aes-256-gcm这类非默认算法，需替换为对应解密函数，同时传入认证标签（通常对应encrypted_email_tag字段）：

SELECT 
  id,
  convert_from(
    decrypt_gcm(
      decode(encrypted_email, 'base64'),
      convert_to(?, 'UTF8'),
      decode(encrypted_email_iv, 'base64'),
      decode(encrypted_email_tag, 'base64')
    ), 'UTF8'
  ) AS email
FROM users

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