I'm trying to implement encryption and decryption for strings as a lead into a larger project where I will have to do the same for CLOBS.
I wanted to get a small working sample first before I take the next step but I'm running into an issue with strings with the last step of my test and was hoping someone could help me out as the last query to decrypt the password is failing and I can't seem to find the issue and would appreciate some help and expertise.
Secondly, once this process is working could it be easily retrofitted to encrypt and decrypt clobs? I was hoping to use as much as the same code as possible with minimal amount of changes to work with CLOBS.
My test CASE is below and I know the password can be seen and I'll address that later on. Btw, I ran this on livesql in case anyone needs to test with the same setup
CREATE TABLE customer (
id NUMBER,
username VARCHAR2(30),
cardnumber NUMBER,
password VARCHAR2(200),
CONSTRAINT customer_pk PRIMARY KEY(id)
);
create sequence customer_seq;
CREATE OR REPLACE PACKAGE encrypt_decrypt
AS
FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/
CREATE OR REPLACE PACKAGE BODY encrypt_decrypt
AS
encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
encryption_key RAW (32) := UTL_RAW.cast_to_raw('HIDEANDSEEKINFO321');
FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC
IS
encrypted_raw RAW (2000);
BEGIN
encrypted_raw := DBMS_CRYPTO.ENCRYPT
(
src => UTL_RAW.CAST_TO_RAW (p_plainText),
typ => encryption_type,
key => encryption_key
);
RETURN encrypted_raw;
END encrypt;
FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC
IS
decrypted_raw RAW (2000);
BEGIN
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => p_encryptedText,
typ => encryption_type,
key => encryption_key
);
RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));
END decrypt;
END;
/
insert into customer(id,username,cardnumber,password) values (customer_seq.nextval,'Fredrik',32435683, encrypt_decrypt.encrypt('kI32432J'));
-- Problem is here
Select id,username,cardnumber,password,encrypt_decrypt(password) decrypted password from customer;