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.

Comments

Carl said…
Why not make EncryptionService static? then you don't need dependency injection into Entity objects. (Also Rfc2898DeriveBytes replaces DeriveBytes because it's obsolete)
Mark Kockerbeck said…
Wow, this is a delayed response. But yeah, EncryptionService could easily be static. I opted not to in my case in order to use Moq with my unit tests. I also threw up an old EncryptionService that (you are 100% right) does make use of the obsolete DeriveBytes. Thanks for your comment!
Unknown said…
What if i have to encrypt something like "FirstName" "LastName" of User/Person. And then would like to query them using string operations such as 'like'. I wouldn't be able to do it untill i load my entire table in the memory.

Popular posts from this blog

Require comment / message on all SVN commits for Visual SVN Server

Fluent NHibernate - Incorrect syntax near the keyword 'Group'

Deleting Cookies (or Managing Cookie Domains) in ASP.NET