Question

如何取BLOB类型的MD5加密值

Answer 1

from ragini.deshpande
Here is the my_encryption package code ...........

CREATE OR REPLACE PACKAGE BODY my_encryption IS
/*
|| Local variable to hold the current encryption key.
*/
ps_encryption_key RAW(32);

/*
|| Local exception to hide Oracle -28231 Error Code.
*/
INTERNAL_BAD_KEY exception;
PRAGMA EXCEPTION_INIT(INTERNAL_BAD_KEY, -28231);

/*
|| Local exception to hide Oracle -28232 Error Code.
*/
INTERNAL_BAD_DATA exception;
PRAGMA EXCEPTION_INIT(INTERNAL_BAD_DATA, -28232);

/*
|| Local function to get the encryption key for a particular case.
*/
FUNCTION get_case_encryption_key(pi_cas_id IN ELS_CASES.ID%TYPE) RETURN RAW IS

/*
|| The key to be returned.
*/
key RAW(16);

/*
|| Cursor to return the case encyption key in encrypted format.
*/
CURSOR c_case_key(b_cas_id ELS_CASES.ID%TYPE) IS
SELECT encryption_key
FROM els_cases
WHERE id = b_cas_id;

BEGIN

OPEN c_case_key(pi_cas_id);
FETCH c_case_key INTO key;
CLOSE c_case_key;
RETURN key;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE NO_CASE;
END;

/*
|| Procedure to initialize package with the master key.
|| The master key will be held elsewhere from the database.
*/
PROCEDURE set_master_key(pi_key IN RAW) IS
BEGIN
IF LENGTHB(pi_key) != 32 THEN
RAISE BAD_KEY;
END IF;
ps_encryption_key := pi_key;
END;

/*
|| Procedure to initialize package with the master key.
|| Always returns 'Y'
|| The master key will be held elsewhere from the database.
*/
FUNCTION set_master_key(pi_key IN RAW) RETURN VARCHAR2 IS
BEGIN
set_master_key(pi_key);
RETURN 'Y';
END;

/*
|| Procedure to initialize package with the case encryption key.
*/
PROCEDURE set_case_key(pi_master_key IN RAW,
pi_cas_id IN ELS_CASES.ID%TYPE) IS
BEGIN
ps_encryption_key := pi_master_key;
ps_encryption_key := decrypt(pi_data=>get_case_encryption_key(pi_cas_id));
END;

/*
|| Function to initialize package with the case encryption key.
|| Always returns 'Y'
*/
FUNCTION set_case_key(pi_master_key IN RAW,
pi_cas_id IN ELS_CASES.ID%TYPE) RETURN VARCHAR2 IS
BEGIN
set_case_key(pi_master_key,pi_cas_id);
RETURN 'Y';
END;

/*
|| Function to encrypt data using the master key. Note the length of
|| pi_data, in bytes, must be at most 2000 bytes and be divisible by 8.
*/
FUNCTION encrypt(pi_data IN RAW) RETURN RAW IS
BEGIN
RETURN dbms_obfuscation_toolkit.DES3Encrypt(input => pi_data,
key => ps_encryption_key);
EXCEPTION
WHEN INTERNAL_BAD_DATA THEN
RAISE BAD_DATA;
WHEN INTERNAL_BAD_KEY THEN
RAISE BAD_KEY;
END;

/*
|| Function to encrypt a BLOB using the current encryption key.
*/
FUNCTION encrypt(pi_blob IN BLOB) RETURN BLOB IS

/*
|| Temporary blob variable to hold the encrypted contents.
*/
result blob;

/*
|| Variable to hold the length of the blob.
*/
blob_length PLS_INTEGER := dbms_lob.getlength(pi_blob);

/*
|| The Oracle encryption routines can only encrypt data whose length is <=2000.
*/
max_chunk_length PLS_INTEGER := 2000;

/*
|| Variable to hold the length of the current chunk that is being encrypted.
*/
chunk_length PLS_INTEGER;

/*
|| Variable to remember which how much of the input blob has been encrypted.
*/
pointer PLS_INTEGER := 1;

/*
|| Variable to hold the next bit of data to be encrypted.
*/
chunk RAW(2000);

/*
|| Variable to hold a pad byte used to pad the last chunk.
*/
pad RAW(1) := utl_raw.substr(utl_raw.cast_to_raw('0'),1,1);

BEGIN

/*
|| Create the temporary blob using the database memory buffer.
*/
dbms_lob.createtemporary(result, TRUE, dbms_lob.call);

/*
|| Loop through the input blob
*/
WHILE (pointer <= blob_length) LOOP

/*
|| Grab at most 2000 bytes from the input blob.
*/
chunk_length := LEAST(max_chunk_length,blob_length-pointer+1);
chunk := dbms_lob.substr(pi_blob,chunk_length,pointer);

/*
|| Pad any chunk (ie the last) so its length is divisible by 8 (another Oracle limitation on encryption)!.
*/
WHILE mod(chunk_length,8) !=0 LOOP
chunk := utl_raw.concat(chunk,pad);
chunk_length := chunk_length+1;
END LOOP;

/*
|| Encrypt the chunk and write it to the end of the temporary blob.
*/
dbms_lob.writeappend(result,
chunk_length,
encrypt(pi_data => chunk)
);

/*
|| Advance the pointer by the length of the last chunk.
*/
pointer := pointer + chunk_length;
END LOOP;

/*
|| All Done!
*/
RETURN result;
END;

/*
|| Function to decrypt data using the master key. Note the length of
|| pi_data, in bytes, must be at most 2000 bytes and be divisible by 8.
*/
FUNCTION decrypt(pi_data IN RAW) RETURN RAW IS
BEGIN
RETURN dbms_obfuscation_toolkit.DES3Decrypt(input => pi_data,
key => ps_encryption_key);
EXCEPTION
WHEN INTERNAL_BAD_DATA THEN
RAISE BAD_DATA;
WHEN INTERNAL_BAD_KEY THEN
RAISE BAD_KEY;
END;

/*
|| Function to decrypt a BLOB using the current encryption key.
*/
FUNCTION decrypt(pi_blob IN BLOB,
pi_size IN PLS_INTEGER) RETURN BLOB IS
/*
|| Temporary blob variable to hold the encrypted contents.
*/
result BLOB;

/*
|| Variable to hold the length of the blob.
*/
blob_length PLS_INTEGER := dbms_lob.getlength(pi_blob);

/*
|| The Oracle encryption routines can only encrypt data whose length is <=2000.
*/
max_chunk_length PLS_INTEGER := 2000;

/*
|| Variable to hold the length of the current chunk that is being encrypted.
*/
chunk_length PLS_INTEGER;

/*
|| Variable to remember which how much of the input blob has been encrypted.
*/
pointer PLS_INTEGER := 1;

BEGIN

/*
|| Create the temporary blob using the database memory buffer.
*/
dbms_lob.createtemporary(result, TRUE, dbms_lob.call);

/*
|| Loop through the input blob
*/
WHILE (pointer <= blob_length) LOOP

/*
|| Grab at most 2000 bytes from the input blob.
*/
chunk_length := LEAST(max_chunk_length,blob_length-pointer+1);

/*
|| Decrypt the chunk and write it to the end of the temporary blob.
*/
dbms_lob.writeappend(result,
chunk_length,
decrypt(pi_data => dbms_lob.substr(pi_blob,
chunk_length,
pointer
)
)
);

/*
|| Advance the pointer by the length of the last chunk.
*/
pointer := pointer + chunk_length;
END LOOP;

/*
|| Remove the padding bytes that were added when the data was encrypted.
*/
dbms_lob.trim(result,pi_size);

/*
|| All Done!
*/
RETURN result;
END;

/*
|| Procedure to clear session state of stored keys.
*/
PROCEDURE CLEAR IS
BEGIN
ps_encryption_key:=null;
END;

END;
/

and here is the PL/sql I run before running the sql stmt

DECLARE

mkey LONG RAW;

BEGIN

mkey := UTL_RAW.CAST_TO_RAW ('&&key');

my_encryption.set_master_key(mkey);

my_encryption.set_case_key(mkey,&&case_id);

END;

mkey is a 16 digit key .
and the encrypted_contents I'm trying to decrypt is a BLOB.

select my_encryption.decrypt(encrypted_contents,file_size),mime_type
from my_drafts where id = &&draft_id;

I hope this makes sense .

Ragini