Encryption/Decryption in PL/SQL

This may sound dumb and easy. However, I am sharing my experiences with encrypting data because it might be of help to someone.

The Oracle packages that can be used for encryption/decryption varies on the Oracle version.
DBMS_OBFUSCATION_TOOLKIT   – Oracle 8i and above
DBMS_CRYPTO                – Oracle 10.1 and above

I have recently used DBMS_OBFUSCATION_TOOLKIT and will elaborate on it here.

DBMS_OBFUSCATION_TOOLKIT supports DES(Data Encryption Standard), Triple DES and MD5(Not encryption).  The requirement for DES encryption is that the data to encrypt should be multiple 8 bytes which is a requirement of the DES encryption algorithm.

There are ways for overcoming this drawback by using standard padding methodologies. However, if you are using DBMS_OBFUSCATION_TOOLKIT you are unlucky because it doesn’t support any kind of padding. Padding is extremely important especially when you are encrypting/decrypting data for other languages like Java and .NET. We faced situations where data encrypted on a .NET client was different from data encrypted by PL/SQL package. So let’s stop blabbering and get into an example.

We shall create two functions, one that will encrypt data and another that decrypts data.

The function for encrypting data is as follows

create or replace FUNCTION EncryptPassword(PlainText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
hexdata VARCHAR2(2000);
BEGIN

encdata:=dbms_obfuscation_toolkit.DES3Encrypt(input=>utl_raw.cast_to_raw(PlainText),key=>UTL_RAW.CAST_TO_RAW(EncKey));
return  (rawtohex(encdata));

END  EncryptPassword;

The function for decrypting data is as follows

create or replace FUNCTION DecryptPassword(EncryptedText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
BEGIN

encdata:=dbms_obfuscation_toolkit.DES3Decrypt(input=>hextoraw(EncryptedText),key=>UTL_RAW.CAST_TO_RAW(EncKey));
return  (utl_raw.cast_to_varchar2(encdata));

END  DecryptPassword;

Now we shall try out the functions we have just created

First the encryption of the data ‘Test1234’ with the key ‘1234567812345678’

select encryptpassword('Test1234','1234567812345678' ) ENC from dual;

ENC
———————————————-
4EF8091F4F5DCAA2

Now we shall decrypt the data with the same key

select decryptpassword('4EF8091F4F5DCAA2','1234567812345678' ) DEC from dual;

DEC
————————————————
Test1234

Some things to note, the data is of size 8 bytes(Test1234). The key is of size 16 bytes and should be multiples of 8 again. These are certain requirements by the Triple DES algorithm.

There are ways to get around this problem and that is with the support of padding. However it is only supported in DBMS_CRYPTO, but to get it working in DBMS_OBFUSCATION_TOOLKIT, you would have to implement it yourself.

More on .NET and Java encryption and how they interact soon…..

---

Possibly related posts: (automatically generated)

• Homomorphic Encryption
• Changing from Non-IDENTITY to IDENTITY and vice versa