title: Implementing 3DES encryption/decryption in PLSQL to handle values encrypted by VB.net
body: <p>We are being asked to decrypt a stream of data using PLSQL which is being encrypted by a VB.net process on a different platform. </p>
  
  <p>The VB code is below:</p>
  
  <pre><code>Public Function TruncateHash(ByVal key As String,ByVal length As Integer) As Byte()
  
              Dim sha1 As New SHA1CryptoServiceProvider
  
              ' Hash the key.
              Dim keyBytes() As Byte =  System.Text.Encoding.Unicode.GetBytes(key)
              Dim hash() As Byte = sha1.ComputeHash(keyBytes)
  
              ' Truncate or pad the hash.
              ReDim Preserve hash(length - 1)
              Return hash
          End Function
  
        Imports System.Security.Cryptography
  
          Public Function test_encrypt(ByVal plaintext As String, ByVal key As String, ByVal seed As String) As String
  
              Dim TripleDes As New TripleDESCryptoServiceProvider
              TripleDes.Key = TruncateHash(key, TripleDes.KeySize \ 8)
              TripleDes.IV = TruncateHash(seed, TripleDes.BlockSize \ 8)
  
              ' Convert the plaintext string to a byte array.
              Dim plaintextBytes() As Byte = System.Text.Encoding.Unicode.GetBytes(plaintext)
  
              ' Create the stream.
              Dim ms As New System.IO.MemoryStream
              ' Create the encoder to write to the stream.
              Dim encStream As New CryptoStream(ms, TripleDes.CreateEncryptor(), System.Security.Cryptography.CryptoStreamMode.Write)
  
              ' Use the crypto stream to write the byte array to the stream.
              encStream.Write(plaintextBytes, 0, plaintextBytes.Length)
              encStream.FlushFinalBlock()
  
              ' Convert the encrypted stream to a printable string.
              Return Convert.ToBase64String(ms.ToArray)
  
          End Function
  
          Public Function TEST_decrypt(ByVal encryptedtext As String, ByVal key As String, ByVal seed As String) As String
  
              Dim TripleDes As New TripleDESCryptoServiceProvider
              TripleDes.Key = TruncateHash(key, TripleDes.KeySize \ 8)
              TripleDes.IV = TruncateHash(seed, TripleDes.BlockSize \ 8)
  
  
              ' Convert the encrypted text string to a byte array.
              Dim encryptedBytes() As Byte = Convert.FromBase64String(encryptedtext)
  
              ' Create the stream.
              Dim ms As New System.IO.MemoryStream
              ' Create the decoder to write to the stream.
              Dim decStream As New CryptoStream(ms, TripleDes.CreateDecryptor(), System.Security.Cryptography.CryptoStreamMode.Write)
  
              ' Use the crypto stream to write the byte array to the stream.
              decStream.Write(encryptedBytes, 0, encryptedBytes.Length)
              decStream.FlushFinalBlock()
  
              ' Convert the plaintext stream to a string.
              Return System.Text.Encoding.Unicode.GetString(ms.ToArray)
          End Function
  </code></pre>
  
  <p>This appears to have been pulled mostly from an <a href="https://msdn.microsoft.com/en-us/library/ms172831.aspx" rel="nofollow">MSDN article</a>.</p>
  
  <p>How can this type of encryption/decryption be implemented in PLSQL using Oracle 11g Enterprise Edition? I have had a stab at it using the code below but the values I'm getting are completely different. I don't know how to interpret the padding/truncation element of the TruncateHash function.</p>
  
  <pre><code>DECLARE 
  
  g_value VARCHAR2 (30 BYTE) DEFAULT 'hello world';
  g_key   VARCHAR2 (30 BYTE) DEFAULT 'password';
  g_seed  VARCHAR2 (30 BYTE) DEFAULT 'seed';
  
  l_enc   VARCHAR2 (2000 BYTE) DEFAULT NULL;
  
      FUNCTION get_hash (p_in     VARCHAR2)
      RETURN RAW
      AS
      l_hash  RAW(2000);
      BEGIN
  
      l_hash  :=  dbms_crypto.hash(utl_raw.cast_to_raw(p_in), dbms_crypto.hash_sh1);
  
      RETURN l_hash;
  
      END get_hash;  
  
  FUNCTION get_enc_val (
       p_in_val      IN   VARCHAR2,
         p_key         IN   VARCHAR2,
         p_iv          IN   VARCHAR2 := NULL
      )
         RETURN VARCHAR2
      IS
         l_enc_val    RAW (4000);
         l_enc_algo   PLS_INTEGER;
        l_in         RAW (4000);
        l_iv         RAW (4000);
        l_key        RAW (4000);
        l_ret        VARCHAR2 (4000);
     BEGIN
        l_enc_algo := dbms_crypto.encrypt_3des_2key;
        l_in := utl_raw.cast_to_raw(p_in_val);
        l_iv := get_hash (p_iv);
        l_key := get_hash (p_key);
        l_enc_val :=
           dbms_crypto.encrypt (src      =&gt; l_in,
                                KEY      =&gt; l_key,
                                iv       =&gt; l_iv,
                                typ      =&gt;   l_enc_algo
                                            + dbms_crypto.chain_cbc
                                            + dbms_crypto.pad_pkcs5
                               );
        l_ret := RAWTOHEX (l_enc_val);
        RETURN l_ret;
     END;
  
  
  BEGIN
  
  l_enc := get_enc_val(g_value, g_key, g_seed);
  
  dbms_output.put_line(utl_raw.cast_to_varchar2(l_enc));
  END;
  </code></pre>
  
  <p>The expected encrypted value of "hello world" is "S3UPpm51c919L+C+PqkuxS7avQt5IjXV". </p>