Encrypting CLOBs with DBMS

From: <roemermanOnRecord_at_gmail.com>
Date: 1 Mar 2007 07:27:43 -0800
Message-ID: <1172762863.328128.83370@p10g2000cwp.googlegroups.com>

I 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</
span>

      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); <span style="color: rgb(51, 204,

0);">-- 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:27:43 CST

Encrypting CLOBs with DBMS_CRYPTO