- Why you want to have something encrypted in CLOB/VARCHAR2?
Encrypted means - non-meaningful streams of bytes, I don`t care.
Encrypted in CLOB/VARCHAR means - non-meaningful streams of
characters, but I need to care about character set of non-meaningful
bytes (extra work).
- If you need encrypted CLOB for any reason, then why not use
DBMS_LOB.CONVERTTOCLOB?
Jan
roemermanOnRecord_at_gmail.com napísal(a):
> 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 Wed Mar 07 2007 - 08:23:23 CST
