I have a requirement to store CLOBS both encrypted and in plain text in the same table and column. I have a wrapper function insert_clob() that handles the situation.
For the encryption and decryption I created two functions, which accepts a CLOB as input. The size of the CLOB could be small or huge>32k, which shouldn't be a problem.
I am able to get through the encryption function.
Whether that function is working properly is yet to be determined as I am having issues decrypting the CLOB to verify my results.
After the update step in my test case, I am calling dump() on the decrypted CLOB, which shows some unusual characters in the middle of the CLOB that weren't in the input and therein lies my problem that I can't seem to resolve. I was hoping you could explain the problem(s) (where I went wrong) and provide me with a code fix, which will rectify the issue(s) wherever they are in the test CASE.
Btw, I know there are some issues such as storing the keys in the DB and anyone with the DBA role can see that information. I intend on changing that once I'm satisfied I'm getting the correct results.
I'm testing this in livesql if you want to emulate my testing environment.
Thanks in advance for responding to my question and sharing your time 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)) / 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 := 7700;
l_offset number := 1;
l_temp varchar2 (32767);
l_step pls_integer := 7700;
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
);
-- I saw that base-64 may have line feeds; 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)) / 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;
/
I see similar issues - for example encrypting and decrypting the same value twice can give different results:
with rws as (
select 'abcdefghijklmnopqrstuvwxyz 1' plaintext from dual
connect by level <= 2
), enc as (
select plaintext, encrypt_clob ( plaintext ) cypher_text
from rws
)
select plaintext, decrypt_clob ( cypher_text ) decyphered_text
from enc e;
PLAINTEXT DECYPHERED_TEXT
---------------------------- ---------------------------------------------
abcdefghijklmnopqrstuvwxyz 1 abcdefgh? klmnopqrstuvwxyz 1
abcdefghijklmnopqrstuvwxyz 1 abcdefghijklmnopqrstuvwxyz 1
I'm not sure why. Contact support to get to the bottom of this.
That said, there are several issues with the current approach:
Storing plaintext and ciphertext values in the same columnThis is a recipe for disaster! Due to bugs or mistakes, the encrypted flag is bound to get out-of-sync with the column for some rows at some point. Which could lead to double or triple encrypting them. Cleaning this up could be a major effort.
Storing plaintext at all!If the values are important enough that you want to encrypt them, you should minimize the places you store the unencrypted data. Anytime you store the plaintext in a table, these values will be recorded in redo logs, backups, etc. This greatly increases the attack surface and chance of the data being leaked.
For example, if anyone manages to get a hold of a backup storing plaintext values and restore it they've got the unencrypted data!
Converting the encrypted value back to clob
The dbms_crypto routines return raw/blob data - why bother converting it back to a clob? Just store it as a blob. This is less code, less processing (= faster), and removes a possible reason for the problems you're seeing.
If you're stuck with the method you're currently using and/or making the changes above doesn't resolve the issue, contact support.