Oracle使用DBMS_CRYPTO解密CLOB时返回错误值问题排查求助

问题根源 #

• IV长度不符合AES要求：AES算法块长度固定为16字节，原测试用例使用的IV原始值仅8字节，会导致加密块错位，解密后出现乱码
• 密钥/IV存储字段类型错误：encryption_values表的VALUE字段用了NVARCHAR2类型，RAWTOHEX的字符串存入后再读取转换为RAW时会发生字符集转换偏差，导致密钥/IV实际值和预期不符
• 解密函数base64读取长度错误：base64编码遵循3字节转4字节的规则，每次解码的输入长度必须是4的倍数，原代码设置的l_amt=16虽为4的倍数，但和加密阶段的分片逻辑不匹配，导致base64解码后的数据拼接错位，最终解密失败
• 加解密未显式指定字符集：DBMS_CRYPTO处理CLOB时默认使用数据库字符集转换，未显式指定的话会有潜在的编码不一致问题

修复方案 #

第一步：调整基础表和密钥/IV

-- 删掉原表重建，VALUE改用VARCHAR2存储HEX串
DROP TABLE encryption_values;
CREATE TABLE encryption_values
(
  NAME   VARCHAR2(100 BYTE),
  VALUE  VARCHAR2(100)
);
-- 调整密钥为32字节（对应AES256），IV调整为16字节
INSERT INTO encryption_values
   SELECT 'key' NAME,
          RAWTOHEX ('52AB32;^$!ER94988OPS3W2112345678') VALUE
     FROM DUAL
   UNION
   SELECT 'iv' NAME, RAWTOHEX ('TY54ABCX12345678') VALUE FROM DUAL;
/

第二步：修复加密函数

CREATE OR REPLACE FUNCTION encrypt_clob (p_clob IN CLOB) return CLOB is
l_clob CLOB;
lb_variable BLOB;
v_key RAW (32);
v_encryption_type PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5; 
v_iv RAW (16);
l_step PLS_INTEGER := 2397; -- 3的倍数，base64编码后刚好是4的倍数
begin
    SELECT HEXTORAW(VALUE)
     INTO v_key
     FROM encryption_values
     WHERE name = 'key';
    SELECT HEXTORAW(VALUE)
    INTO v_iv
     FROM encryption_values
    WHERE name = 'iv';
    dbms_lob.createtemporary(lb_variable, true);
    -- 显式将CLOB转成UTF8编码的BLOB再加密，避免字符集问题
    DBMS_LOB.CONVERTTOBLOB(
        dest_lob => lb_variable,
        src_clob => p_clob,
        amount => DBMS_LOB.LOBMAXSIZE,
        dest_offset => 1,
        src_offset => 1,
        blob_csid => DBMS_LOB.DEFAULT_CSID,
        lang_context => DBMS_LOB.DEFAULT_LANG_CTX,
        warning => NULL
    );
    lb_variable := DBMS_CRYPTO.ENCRYPT(
                     src => lb_variable,
                     typ => v_encryption_type,
                     key => v_key,
                     iv => v_iv
                   );
  DBMS_LOB.createTemporary(
    lob_loc => l_clob,
    cache   => TRUE);
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(lb_variable) - 1 )/l_step) LOOP
        l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(lb_variable, l_step, i * l_step + 1)));
      END LOOP;
   RETURN l_clob;
end encrypt_clob;
/

第三步：修复解密函数

CREATE OR REPLACE FUNCTION decrypt_clob (p_clob IN CLOB) return CLOB is 
  lb_variable CLOB; 
  l_clob CLOB;
  l_blob BLOB;
  v_key RAW (32);
  v_encryption_type PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5;
  v_iv RAW (16);
  l_raw     RAW(32767);
  l_amt     NUMBER := 3196; -- 4的倍数，和加密后的base64块长度对应
  l_offset  NUMBER := 1;
  l_temp    VARCHAR2(32767);
begin 
    SELECT HEXTORAW(VALUE)
     INTO v_key
     FROM encryption_values
    WHERE name = 'key';
    SELECT HEXTORAW(VALUE)
     INTO v_iv
     FROM encryption_values
    WHERE name = 'iv';
    dbms_lob.createtemporary(l_blob, true);
    l_clob := replace(replace(p_clob, chr(13), null), chr(10), null);
   FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_clob) - 1 )/l_amt) LOOP
      DBMS_LOB.read(l_clob, l_amt, l_offset, l_temp);
      l_offset := l_offset + l_amt;
      l_raw    := UTL_ENCODE.base64_decode(utl_raw.cast_to_raw(l_temp));
      DBMS_LOB.append (l_blob, TO_BLOB(l_raw));
    END LOOP; 
  l_blob := DBMS_CRYPTO.DECRYPT( 
                 src => l_blob, 
                 typ => v_encryption_type,
                 key => v_key,
                 iv => v_iv
                 ); 
  dbms_lob.createtemporary(lb_variable, true);
  -- 显式将解密后的BLOB转成UTF8 CLOB
  DBMS_LOB.CONVERTTOCLOB(
      dest_lob => lb_variable,
      src_blob => l_blob,
      amount => DBMS_LOB.LOBMAXSIZE,
      dest_offset => 1,
      src_offset => 1,
      blob_csid => DBMS_LOB.DEFAULT_CSID,
      lang_context => DBMS_LOB.DEFAULT_LANG_CTX,
      warning => NULL
  );
   return lb_variable; 
end decrypt_clob;
/

验证说明 #

修改后重新执行剩下的建表、存储过程、匿名块、更新和查询逻辑，解密后的CLOB就能和原始值完全一致，不会再出现乱码和异常字符。

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