I'm trying to encrypt and decrypt a CLOB. I have a test CASE below, which utilizes functions to accomplish this task and was taken from the following link.
Error while using DBMS_CRYPTO function to decrypt CLOB data
Once the anonymous block creates the encrypted CLOB I am using an update statement, which calls a decrypt function to ensure it matches the original string.
I've tried initializing the CLOB before encryption with a call to empty_clob() but I can't seem to get this test CASE to decrypt the CLOB and return back the original value, which I started with.
The string being generated to encrypt is small but the test CASE is a little lengthy so I can provide a step by step guide on what I'm doing.
The last step in the test is a dump() of the decrypted CLOB, which shows some unusual characters in the middle of the CLOB that weren't in the input.
While looking at the documentation I did see an example using RANDOMBYTES on a string bur that will not work for me as it would be very unlikely I will get the same random bytes for the encryption and decryption.
I'm testing this in livesql for those who want to emulate my testing environment.
I was hoping someone could tell me where the problem is and provide the necessary fixes I need to get this functioning properly.
Thanks in advance to all who respond and for your time, patience and expertise.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE TABLE encryption_values
(
NAME VARCHAR2(100 BYTE),
VALUE NVARCHAR2(100)
);
INSERT INTO encryption_values
SELECT 'key' NAME,
RAWTOHEX ('52AB32;^$!ER94988OPS3W21') VALUE
FROM DUAL
UNION
SELECT 'iv' NAME, RAWTOHEX ('TY54ABCX') 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 (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 encryption_values
WHERE name = 'key';
SELECT VALUE
INTO v_iv
FROM encryption_values
WHERE name = 'iv';
dbms_lob.createtemporary(lb_variable, true);
DBMS_CRYPTO.ENCRYPT(
dst => lb_variable,
src => p_clob,
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 (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 := 16;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
begin
SELECT VALUE
INTO v_key
FROM encryption_values
WHERE name = 'key';
SELECT VALUE
INTO v_iv
FROM encryption_values
WHERE name = 'iv';
dbms_lob.createtemporary(l_blob, true);
-- base-64 string has line breaks; those could give an inaccurate result to the decode function below, so they are replaced with NULL.
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;
dbms_lob.createtemporary(lb_variable, true);
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 decrypt_clob;
/
CREATE table t(
seq_num integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
hash_pk VARCHAR2(1000) not NULL PRIMARY KEY,
c CLOB,
encrypted VARCHAR2(1) DEFAULT 'N',
create_date DATE DEFAULT SYSDATE
);
/
CREATE or REPLACE FUNCTION HASH_SHA512 (
psINPUT IN VARCHAR2
) RETURN VARCHAR2 AS
rHash RAW (512);
BEGIN
rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT),
dbms_crypto.HASH_SH512);
RETURN (LOWER (RAWTOHEX (rHash)));
END HASH_SHA512;
/
CREATE OR REPLACE PROCEDURE insert_clob(
p_clob IN CLOB,
p_encrypted VARCHAR2
) AS
l_hash_pk VARCHAR2(1000);
BEGIN
SELECT HASH_SHA512(p_clob) INTO l_hash_pk from dual;
IF (p_encrypted = 'N')
THEN
insert into t (hash_pk, c, encrypted)
select l_hash_pk, p_clob, p_encrypted from dual
where not exists(select 1
from t
where (hash_pk = l_hash_pk));
ELSE
insert into t (hash_pk, c, encrypted)
select l_hash_pk, encrypt_clob (p_clob), p_encrypted from dual
where not exists(select 1
from t
where (hash_pk = l_hash_pk));
END IF;
END;
declare
c1 clob := empty_clob();
c2 varchar2(32000);
BEGIN
for i in 1 .. 3
loop
c2 := 'abcdefghijklmnopqrstuvwxyz ' || i;
dbms_output.put_line(c2);
SELECT CONCAT(c1, c2) INTO c1 FROM DUAL;
insert_clob (c1, 'Y');
c1 := empty_clob();
c2 := NULL;
end loop;
END;
UPDATE t
set c = decrypt_clob(c),encrypted = 'N' where encrypted ='Y';
-- As you can see from the dump() the decrypted CLOB doesn't match the original CLOB.
SELECT c, dump(to_char(c)) from t;
/
C DUMP(TO_CHAR(C))
abcdefgh0QKEmnopqrstuvwxyz 1 Typ=1 Len=28: 97,98,99,100,101,102,103,104,48,81,75,69,109,110,111,112,113,114,115,116,117,118,119,120,121,122,32,49
abcdefgh�klmnopqrstuvwxyz 2 Typ=1 Len=30: 97,98,99,100,101,102,103,104,239,191,189,22,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,32,50
abcdefgh�klmnopqrstuvwxyz 3 Typ=1 Len=30: 97,98,99,100,101,102,103,104,239,191,189,22,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,32,51