oracle - 使用 DBMS_CRYPTO 函数解密 CLOB 数据时出错
问题描述
我的任务是执行两个 Oracle 功能,实现以下目标:
- 将 CLOB 作为输入并使用 AES-256 对其进行加密并返回 Encrypted CLOB
- 将 Encrypted CLOB 作为输入,使用 AES-256 解密并返回 Decrypted CLOB
CLOB 数据问题很大,函数应该处理好这个问题。
我能够通过第一个函数,它运行良好,即用大量数据加密 CLOB:
create or replace
function F_ENCRYPT_CLOB (ac_input IN CLOB) return CLOB is
l_clob CLOB;
lb_variable BLOB;
v_key RAW (320);
v_encryption_type PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5;
v_iv RAW (320);
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER;
l_step PLS_INTEGER := 1998;
begin
SELECT VALUE
INTO v_key
FROM algparameters
WHERE name = 'key';
SELECT VALUE
INTO v_iv
FROM algparameters
WHERE name = 'iv';
dbms_lob.createtemporary(lb_variable, true);
sys.DBMS_CRYPTO.ENCRYPT(
dst => lb_variable,
src => ac_input,
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 F_ENCRYPT_CLOB;
但是我在使用类似步骤解密先前加密的值时遇到了问题:
create or replace
function F_DECRYPT_CLOB (ac_input IN CLOB) return CLOB is
lb_variable CLOB;
l_blob BLOB;
v_key RAW (320);
v_encryption_type PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5;
v_iv RAW (320);
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER;
l_raw RAW(32767);
l_amt NUMBER := 7700;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
l_step PLS_INTEGER := 7700;
begin
SELECT VALUE
INTO v_key
FROM algparameters
WHERE name = 'key';
SELECT VALUE
INTO v_iv
FROM algparameters
WHERE name = 'iv';
dbms_lob.createtemporary(l_blob, true);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(ac_input) - 1 )/l_amt) LOOP
DBMS_LOB.read(ac_input, l_amt, l_offset, l_temp);
l_offset := l_offset + l_amt;
l_raw := UTL_ENCODE.base64_decode(l_temp);
DBMS_LOB.append (l_blob, TO_BLOB(l_raw));
END LOOP;
dbms_lob.createtemporary(lb_variable, true);
sys.DBMS_CRYPTO.DECRYPT(
dst => lb_variable,
src => l_blob,
typ => v_encryption_type,--dbms_crypto.des_cbc_pkcs5,
key => v_key,
iv => v_iv
);
return lb_variable;
end F_DECRYPT_CLOB;
它抛出的错误是:
ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
ORA-06512: at "SN_PRE_STAGE_415.F_DECRYPT_CLOB", line 33
06502. 00000 - "PL/SQL: numeric or value error%s"
*原因:
*行动:
解决方案
错误来自第 33 行,即:
l_raw := UTL_ENCODE.base64_decode(l_temp);
该base64_decode
函数需要一个 RAW 参数,因此您可以转换您现在拥有的字符串:
l_raw := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(l_temp));
IE
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(ac_input) - 1 )/l_amt) LOOP
DBMS_LOB.read(ac_input, 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;
现在它正在抛出这个
ORA-28817: PL/SQL 函数返回错误。
ORA-06512:在“SYS.DBMS_CRYPTO_FFI”,第 110行 ORA-06512:在“SYS.DBMS_CRYPTO”,
第 64 行
ORA-06512:在“STACKOVERFLOW.F_DECRYPT_CLOB”,第 39 行
您的 base-64 字符串有换行符;那些正在丢弃解码。您可以使用较小的块大小,使用l_amt = 64
, 并跳过换行符:
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(ac_input) - 1 )/(l_amt + 2)) LOOP
DBMS_LOB.read(ac_input, l_amt, l_offset, l_temp);
l_offset := l_offset + l_amt + 2;
l_clob
但是通过一个新变量一次性剥离它们可能更简单,更有效:
create or replace
function F_DECRYPT_CLOB (ac_input IN CLOB) return CLOB is
lb_variable CLOB;
l_clob CLOB;
l_blob BLOB;
...
begin
...
dbms_lob.createtemporary(l_blob, true);
l_clob := replace(replace(ac_input, 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;
...
end F_DECRYPT_CLOB;
/
在全:
create or replace
function F_DECRYPT_CLOB (ac_input IN CLOB) return CLOB is
lb_variable CLOB;
l_clob CLOB;
l_blob BLOB;
v_key RAW (320);
v_encryption_type PLS_INTEGER := DBMS_CRYPTO.AES_CBC_PKCS5;
v_iv RAW (320);
l_dest_offset PLS_INTEGER := 1;
l_src_offset PLS_INTEGER := 1;
l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
l_warning PLS_INTEGER;
l_raw RAW(32767);
l_amt NUMBER := 7700;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
l_step PLS_INTEGER := 7700;
begin
SELECT VALUE
INTO v_key
FROM algparameters
WHERE name = 'key';
SELECT VALUE
INTO v_iv
FROM algparameters
WHERE name = 'iv';
dbms_lob.createtemporary(l_blob, true);
l_clob := replace(replace(ac_input, 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;
dbms_lob.createtemporary(lb_variable, true);
sys.DBMS_CRYPTO.DECRYPT(
dst => lb_variable,
src => l_blob,
typ => v_encryption_type,--dbms_crypto.des_cbc_pkcs5,
key => v_key,
iv => v_iv
);
return lb_variable;
end F_DECRYPT_CLOB;
/
推荐阅读
- animation - 单击矩形时使用 svg 动画降低矩形的不透明度
- sql - SQL Server:每个 GROUP BY 表达式必须至少包含一个不是外部引用的列
- php - 如何使用 php 和 fpdf 创建发票 pdf?
- javascript - 使用 datalist 打开一个新选项卡
- java - 如何将两个地图添加到列表中
- c# - 使用 ItemTemplate 扩展 ListBox
- javascript - 如何放大当前光滑幻灯片的图像?
- reactjs - 如何将多个文档添加到 Firebase 的集合中?
- javascript - 如何延迟 CDK 覆盖中的打开?
- python - 有没有一种更快的方法可以将列表相互“过去”?