Encrypting CLOBs with DBMS

From: <roemermanOnRecord_at_gmail.com>
Date: 1 Mar 2007 07:44:06 -0800
Message-ID: <1172763846.306960.210360@8g2000cwh.googlegroups.com>

had a lot of problems using DBMS_CRYPTO with the express goal of starting out with an unencrypted clob and ending up with an encrypted clob. Why their stupid algorithm returns blobs instead of clobs is really beyond me. Any way, I could not find my solution online so I thought I'd post it here to help people that have the same problem.

These two procs encrypt and decrypt clobs in a test table, you have to use utl_encode.base64_encode and utl_encode.base64_decode to make the move from clob and blob and vise/versa.

CREATE OR REPLACE
PROCEDURE ENCRYPT_CLOB
( intval IN tst_clob_tbl.id_col%type)
IS
-- This proc gets a clob out of a table, encrypts it, and then puts it
back...

CLOB1 clob; -- select from table
CLOB2 clob; -- temporary clob
BLOB1 BLOB; -- temporary blob

l_key RAW(128); -- encryption key
Amount number := 4000;

Buffer_in RAW(32000);
Buffer_out varchar2(32000); -- allow for expansion len number;
l number;
steps number;
left number;
chunksize number;
chunksize_hex varchar2(4);

offset number;

BEGIN

create temporary clobs and blobs DBMS_LOB.CREATETEMPORARY(BLOB1, TRUE, DBMS_LOB.CALL); DBMS_LOB.CREATETEMPORARY(CLOB2, TRUE, DBMS_LOB.CALL); DBMS_LOB.OPEN(BLOB1, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(CLOB2, DBMS_LOB.LOB_READWRITE);

--create encryption key

l_key := utl_raw.cast_to_raw('abcdefgh');

get the clob out of tst_clob_tbl, use the id that was passed in select clob_value into CLOB1 from tst_clob_tbl where id_col = intval;

--encrypt the clob

sys.dbms_crypto.encrypt(dst => BLOB1,
src=> CLOB1,
typ => dbms_crypto.des_cbc_pkcs5,
key => l_key);

encrypted data is now in BLOB1...but hey, I really wanted a clob

len := DBMS_LOB.GETLENGTH(BLOB1);
dbms_output.put_line('lenght encrypted blob : '||len); steps := floor(len/amount);
left := mod(len,amount); -- this is the remainder chunk smaller than amount

loop though this bad boy and encode the chars back to 64 bit...ughh for i in 1..steps loop offset := 1+((i-1)*amount); buffer_in := dbms_lob.substr(BLOB1,amount,offset);

buffer_out:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer_in));

chunksize := length(buffer_out);

chunksize_hex := lpad(trim(to_char(chunksize,'XXXX')),4,'0000'); dbms_output.put_line(chunksize||' = '||chunksize_hex); dbms_lob.writeappend(lob_loc => CLOB2, amount=> 4, buffer => chunksize_hex);
dbms_lob.writeappend(lob_loc => CLOB2, amount=>chunksize, buffer => buffer_out);

end loop;
if left > 0 then
buffer_in := dbms_lob.substr(BLOB1,left,len-left+1); -- last piece

buffer_out:=utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer_in));

chunksize := length(buffer_out);

chunksize_hex := lpad(trim(to_char(chunksize,'XXXX')),4,'0000'); dbms_output.put_line(chunksize||' = '||chunksize_hex);

dbms_lob.writeappend(lob_loc => CLOB2, amount=> 4, buffer => chunksize_hex);
dbms_lob.writeappend(lob_loc => CLOB2, amount=>chunksize, buffer => buffer_out);

end if;
-- write terminator

buffer_out := 'FFFF';
dbms_lob.writeappend(lob_loc => CLOB2, amount => 4, buffer => buffer_out);

Ohh man what a bunch of work. I wish the crypto package would return clob...thanks oracle
store the encrypted clob back where it came from. update tst_clob_tbl set clob_value=CLOB2 where id_col= intval; commit;
clean up your mess DBMS_LOB.FREETEMPORARY(CLOB2); DBMS_LOB.FREETEMPORARY(BLOB1); end; /

CREATE OR REPLACE
PROCEDURE DECRYPT_CLOB
( intval IN tst_clob_tbl.id_col%type)
IS
-- This proc gets a clob out of a table, decrypts it, and then puts it
back...

CLOB1 clob; -- select from table
CLOB2 clob; -- temporary clob
BLOB1 BLOB; -- tempory blob

l_key RAW(128); -- encryption key
v_amount PLS_INTEGER; -- amount for conversion Buffer_out RAW(16000);
Buffer_in varchar2(32000); -- allow for expansion len number;
l number;
steps number;
left number;

v_offset number;
chunksize number;
chunksize_hex varchar2(4);

v_done boolean := false;

BEGIN

create temporary clobs and blobs DBMS_LOB.CREATETEMPORARY(BLOB1, TRUE, DBMS_LOB.CALL); DBMS_LOB.CREATETEMPORARY(CLOB2, TRUE, DBMS_LOB.CALL); DBMS_LOB.OPEN(BLOB1, DBMS_LOB.LOB_READWRITE); DBMS_LOB.OPEN(CLOB2, DBMS_LOB.LOB_READWRITE);

--create encryption key

l_key := utl_raw.cast_to_raw('abcdefgh');

get the clob out of tst_clob_tb, use the id that was passed in select clob_value into CLOB1 from tst_clob_tbl where id_col = intval;
encrypted data is now in CLOB1 but I cant pass clob in to decrypt so make it a blob
did I mention how much I hate dbms_crypto?

v_offset:=1;

loop through and decode the chars so they will fit in a blob. while not v_done loop

chunksize_hex := dbms_lob.substr(lob_loc => CLOB1,amount => 4,offset => v_offset);
chunksize := to_number(chunksize_hex,'XXXX');

if chunksize_hex <> 'FFFF' then

v_offset:=v_offset+4;

buffer_in := dbms_lob.substr(lob_loc => CLOB1,amount => chunksize, offset => v_offset);
v_amount:=length(buffer_in);

buffer_out :=
utl_encode.base64_decode(utl_raw.cast_to_raw(buffer_in));

v_amount:=utl_raw.length(buffer_out);
begin

dbms_lob.writeappend(lob_loc => BLOB1,
amount => v_amount,
buffer => buffer_out);
exception
when others then
dbms_output.put_line('error in dbms_lob.writeappend'); dbms_output.put_line('v_amount : '||v_amount); raise;
end;
v_offset := v_offset+chunksize;
else
v_done := true;
dbms_output.put_line('done, found terminator'); end if;
end loop;

len := DBMS_LOB.GETLENGTH(BLOB1);
dbms_output.put_line('lenght encrypted blob : '||len);

now that that is over...we can get back to the task at hand
decrypt the clob

sys.dbms_crypto.decrypt(DST => CLOB2,

SRC => BLOB1,
typ => dbms_crypto.des_cbc_pkcs5,
key => l_key);

Put it back where you got it. update tst_clob_tbl set clob_value = CLOB2 where id_col=intval; commit;
clean up your mess DBMS_LOB.FREETEMPORARY(CLOB2); DBMS_LOB.FREETEMPORARY(BLOB1);

end;
/ Received on Thu Mar 01 2007 - 09:44:06 CST

Encrypting CLOBs with DBMS_CRYPTO