Feeds:
Posts
Comments

Archive for the ‘PL/SQL’ Category

Performing encryption and decryption using the same language might be straight forward and many resources can be found in this regard. But when it comes to across languages, mismatches appear even if you are using the same algorithm to encrypt and decrypt. One of the reasons for this is that each language has its own specification that it supports. The second reason is that each language has different default values for its specification that you need to be aware of. The basic specification that should match in both sides i.e. encryption and decryption are:

  • Algorithm
  • Secret Key
  • Secret Key Size
  • Initialization Value (IV)
  • Padding
  • Mode

Lets take the example of .Net and Oracle. We will use C# for encryption and Pl/SQL for decryption. The C# code for encryption can be found here, and the PL/SQL code for decryption can be found in our previous blog Encryption/Decryption in PL/SQL. Here is a copy of the code:

C# Code

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
namespace EncryptConsoleApp
{
 class Program
 {
   static void Main(string[] args)
   {
     string text = "Test1234";
     string key = "5379075357908764";

     byte[] textBytes = new byte[text.Length];
     textBytes = ASCIIEncoding.ASCII.GetBytes(text);

     byte[] keyBytes = new byte[key.Length];
     keyBytes = ASCIIEncoding.ASCII.GetBytes(key);

     byte[] encrptedBytes = Encrypt(textBytes, keyBytes);

     Console.WriteLine("Encrypted Text: " + ByteArrayToHexString(encrptedBytes));
  }

  public static byte[] Encrypt(byte[] clearData, byte[] Key)
  {
    MemoryStream ms = new MemoryStream();
    // Create a symmetric algorithm.
    TripleDES alg = TripleDES.Create();
    alg.Key = Key;

    CryptoStream cs = new CryptoStream(ms,alg.CreateEncryptor(), CryptoStreamMode.Write);
    cs.Write(clearData, 0, clearData.Length);
    cs.Close();

    byte[] encryptedData = ms.ToArray();
    return encryptedData;
  }
 }
} 

PL/SQL Code

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;

If you run the C# code to encrypt Test1234, you will get the following:
Encrypted Text: 5F48C32F78F63971E19764659E3E57F7

Now lets take this output and decrypt it using the PL/SQL code, you should get Test1234 again. Unfortunately, that will not happen. You will see totally different output. Something like ¿ E ¿¿¿(¿u¿¿¿ -¿ or some symbols that are not been displayed in my application.

Now you might be wondering, Why is it so? What went wrong over here? So, lets take a closer look.

The default configurations for the Triple DES algorithm in .Net and Oracle are as follow:

.Net

Oracle

Mode CBC CBC
Padding PKCS7 Not supported
IV in HEX C992C3154997E0FB 0123456789ABCDEF
Secret Key Size 192 bits 192 bits

As you can notice in the above table, the padding and the IV default setting are different in each framework. Thus the result of encryption and decryption will not match. In order to resolve the mismatch we need to change the configuration in C# code as Oracle 9i does not provide any mean to change these configuration. So we will set the value of IV to be the identical to the IV value in Oracle. But what about the padding? It is not supported at all in Oracle 9i. We can not set it to none as then we will be restricted to specific data length. The alternative solution we can think of is to set one of the padding mode that we can manually remove in Oracle. We will set the padding mode in .Net to ANSIX923 . The ANSIX923 padding string consists of a sequence of bytes filled with zeros before the length. In oracle we will check the last two digits i.e. the length and based on that we will remove all the zeros i.e. the padded part. The following example shows how the ANSIX923 mode works. Given a blocklength of 8, a data length of 9, the number of padding octets equal to 7, and the data equal to FF FF FF FF FF FF FF FF FF:

FF FF FF FF FF FF FF FF FF 00 00 00 00 00 00 07

Below is the modified code.

C# Code

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Security.Cryptography;
namespace EncryptConsoleApp
{
 class Program
 {
   static void Main(string[] args)
   {
      string text = "Test1234";

      string key = "5379075357908764";
      string iv = "0123456789ABCDEF";

      byte[] textBytes = new byte[text.Length];
      textBytes = ASCIIEncoding.ASCII.GetBytes(text);

      byte[] keyBytes = new byte[key.Length];
      keyBytes = ASCIIEncoding.ASCII.GetBytes(key);

      byte[] ivBytes = new byte[iv.Length];
      ivBytes = HexStringToByteArray(iv);

      byte[] encrptedBytes = Encrypt(textBytes, keyBytes, ivBytes);

      Console.WriteLine("Encrpted Text: " + ByteArrayToHexString(encrptedBytes));
   }

   public static byte[] Encrypt(byte[] clearData, byte[] Key, byte[] IV)
  {
      MemoryStream ms = new MemoryStream();
      // Create a symmetric algorithm.
      TripleDES alg = TripleDES.Create();
      alg.Padding = PaddingMode.ANSIX923;
      alg.Key = Key;
      alg.IV = IV;

      CryptoStream cs = new CryptoStream(ms,alg.CreateEncryptor(), CryptoStreamMode.Write);
      cs.Write(clearData, 0, clearData.Length);
      cs.Close();

      byte[] encryptedData = ms.ToArray();
      return encryptedData;
   }
 }
}

PL/SQL Code

create or replace FUNCTION DecryptPassword(EncryptedText IN VARCHAR2,EncKey IN VARCHAR2) RETURN VARCHAR2
IS
encdata RAW(2000);
numpad NUMBER;
result VARCHAR2(100);
BEGIN
  encdata:=dbms_obfuscation_toolkit.DES3Decrypt(input=>hextoraw(EncryptedText),key=>UTL_RAW.CAST_TO_RAW(EncKey));

  result :=rawtohex(encdata);
  numpad:=substr(result,length(result)-2);
  result:= substr(result,1,length(result)-(numpad*2));
  result := hextoraw(result);
  result := utl_raw.cast_to_varchar2(result);
  return result;

END DecryptPassword;

Now if you encrypt Test1234 using C# you should get the following output:
Encrypted Text: 109F3C4AD99AE1B0899596AB525D5D59

Lets try to decrypt the output using PL/SQL code. The output is
Test1234

Finally, C# code and PL/SQL code matches. In the same lines any other programming language can be used.

Read Full Post »

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…..

Read Full Post »