Fluent NHibernate + Encrypting Values

I recently created two CLR User Defined Functions for SQL Server 2005 (written in C#) to decrypt & encrypt a given string (based on a hard-coded key).

select dbo.EncryptString('Testing')
-- Returns: Fjfds243qcm43fsdj2343==

select dbo.DecryptString('Fjfds243qcm43fsdj2343==')
-- Returns: Testing

Pretty simple stuff. This will allow the user with enough access to pull encrypted data out of the database and provide the ability to restrict decryption to other SQL users.

Now to implement this in code...

Because it's awesome, Fluent NHibernate has a method called FormulasIs that assists in building out values using SQL.

public class User
{
   public virtual int Id { get; set; }
   public virtual DateTime DateOfBirth { get; set; }
}

public sealed class UserMap : ClassMap
{
  public UserMap()
  {
    WithTable("dbo.[User]");
    Id(x => x.Id, "[ID]");
    Map(x => x.DateOfBirth, "DOB").FormulaIs("dbo.DecryptString(DOB)");
  }
}

This is perfect. Exactly what I needed...until I needed to save the value. Fluent NHibernate will not pass any value to the database for the DateOfBirth property -- it is being set as NULL.

A solution I came up with was to use 2 Properties in the entity. 1 for the developer to use, and 1 for NHibernate to use.

public class User
{
   // Get the default instance using StructureMap
   private readonly IEncryptionService _encryptionService =
                   ObjectFactory.GetInstance<IEncryptionService>();
   public virtual int Id { get; set; }
   public virtual DateTime? DateOfBirth
   {
     get
     {
       return _encryptionService.DecryptObject<DateTime?>(DateOfBirthEncrypted);
     }
     set
     {
       DateOfBirthEncrypted= _encryptionService.EncryptString(value.Value
                                   .ToString("yyyy-MM-dd HH:mm:ss"));
     }
   }
   [Obsolete("Use the 'DateOfBirth' property -- this property is only to be used by NHibernate")]
   public virtual string DateOfBirthEncrypted { get; set; }
}


public sealed class UserMap : ClassMap<User>
{
  public UserMap()
  {
    WithTable("dbo.[User]");
    Id(x => x.Id, "[ID]");
    Map(x => x.DateOfBirthEncrypted, "DOB");
  }
}

And a sample EncryptionService is below:

using System;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace Services
{
 public class EncryptionService : IEncryptionService 
 {
  /// <summary>
  /// Decrypts a string 
  /// </summary>
  /// <param name="encryptedString"></param>
  /// <returns></returns>
  public String DecryptString(string encryptedString)
  {
   if (String.IsNullOrEmpty(encryptedString)) return String.Empty;

   try
   {
    using (TripleDESCryptoServiceProvider cypher = new TripleDESCryptoServiceProvider())
    {
     PasswordDeriveBytes pdb = new PasswordDeriveBytes("ENTERAKEYHERE", new byte[0]);
     cypher.Key = pdb.GetBytes(16);
     cypher.IV = pdb.GetBytes(8);

     using (MemoryStream ms = new MemoryStream())
     {
      using (CryptoStream cs = new CryptoStream(ms, cypher.CreateDecryptor(), CryptoStreamMode.Write))
      {
       byte[] data = Convert.FromBase64String(encryptedString);
       cs.Write(data, 0, data.Length);
       cs.Close();

       return Encoding.Unicode.GetString(ms.ToArray());
      }
     }
    }
   }
   catch
   {
    return String.Empty;
   }
  }

  /// <summary>
  /// Encrypts a string
  /// </summary>
  /// <param name="decryptedString"
  /// <returns></returns>
  public String EncryptString(string decryptedString)
  {
   if (String.IsNullOrEmpty(decryptedString)) return String.Empty;

   using (TripleDESCryptoServiceProvider cypher = new TripleDESCryptoServiceProvider())
   {
    PasswordDeriveBytes pdb = new PasswordDeriveBytes("ENTERAKEYHERE", new byte[0]);

    cypher.Key = pdb.GetBytes(16);
    cypher.IV = pdb.GetBytes(8);

    using (MemoryStream ms = new MemoryStream())
    {
     using (CryptoStream cs = new CryptoStream(ms, cypher.CreateEncryptor(), CryptoStreamMode.Write))
     {
      byte[] data = Encoding.Unicode.GetBytes(decryptedString);

      cs.Write(data, 0, data.Length);
      cs.Close();

      return Convert.ToBase64String(ms.ToArray());
     }
    }
   }
  }

  /// <summary>
  /// Decrypts a given value as type of T, if unsuccessful the defaultValue is used
  /// </summary>
  /// <typeparam name="T"></typeparam>
  /// <param name="value"></param>
  /// <param name="defaultValue"></param>
  /// <returns></returns>
  public T DecryptObject<T>(object value, T defaultValue)
  {
   if (value == null) return defaultValue;

   try
   {
     Type conversionType = typeof(T);

     // Some trickery for Nullable Types
     if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
     {
          conversionType = new NullableConverter(conversionType).UnderlyingType;
     }

     return (T)Convert.ChangeType(DecryptString(Convert.ToString(value)), conversionType);
   }
   catch 
   {
    // Do nothing
   }

   return defaultValue;
  }
 }
}

This will allow the developer to set the property as normal but NHibernate will only access the Encrytped property. The developer never has to worry about knowing which values are encrypted on the database. And better yet, our encryption can all be done in code & we can just delete those CLR UDFs if we don't need them.