This is the fourth post in my SQL Server Database Encryption with NHibernate Series. Check out the others posts here:

• Part 1 of 5: Summary
• Part 2 of 5: Create a database certificate and symmetric key for encryption
• Part 3 of 5: Configure domain and NHibernate mappings to decrypt and read an encrypted value
• Part 4 of 5: Configure domain and NHibernate mappings to encrypt and write a plain-text value
• Part 5 of 5: Wrapping Up

Writing Encrypted Values via NHibernate

Although reading an encrypted value with the Formula function is straight-forward, encrypting a value with database encryption and writing the encypted value to the database via NHibernate is more complex.

The crux of using database encryption is that the SQL server must perform the encryption on the plain text value via the EncryptByKey TSQL command. Unfortunately, NHibernate does not provide users with a mapping function (similar to Formula) when writing data back to the database. As a result, we must implement a custom User Type, intercept the ADO.NET SqlCommand text NHibernate generate by default for database writes, and dynamically modify the command.

Implementing a Custom User Type

Thanks to Darrell Mozingo and his post on creating a generic NHibernate User Type Base Class, creating custom User Types is easy. I started off by using Darrell’s BaseImmutableUserType class to create my custom user type called EncryptedString:

public class EncryptedString : BaseImmutableUserType
{
public override object NullSafeGet(IDataReader rs, string[] names, object owner)
{
// this returns null, because this is not meant to be a readable field
return null;
}

public override void NullSafeSet(IDbCommand cmd, object value, int index)
{
NHibernateUtil.String.NullSafeSet(cmd, value, index);
}

public override SqlType[] SqlTypes
{
get { return new[] {new SqlType(DbType.String)}; }
}
}

When using the BaseImmutableUserType case class, you need to override three methods: SqlTypes, NullSafeGet, and NullSafeSet.

SqlTypes is the function that NHibernate calls to determine the SQL data types mapped to your custom User Type. My implementation maps a single column to my custom User Type, hence I return a single DBType.String.

NullSafeGet is the function NHibernate calls when reading the value from the database for your custom User Type.

Side Note: Initially, you may think that NullSafeGet is the perfect place to perform decryption via our previously used dbo.DecryptSsn() user defined function; however, after further investigation, NullSafeGet gives the user access to an open IDataReader object. Because the IDataReader is already open, NHibernate has already sent a TSQL SELECT command to the database, pulling the encrypted value back from the database without passing it through any sort of decryption function. As a result, I chose to have NullSafeGet always return NULL, ignoring the IDataReader. Later in this post, you will see how I unify this custom User Type for writing and the Formula mapped field for reading into a single domain property.

NullSafeSet is the function NHibernate calls when writing the custom User Type value to the database. The function exposes an IDbCommand (an ADO.NET SqlCommand object in our case), the mapped column value, and parameter index the mapped column value is associated with in the IDbCommand’s Parameter collection.

Similar to reading an encrypted value from the database by calling a user defined function, we will need to dynamically modify the IDbCommand CommandText to wrap the object value being written to the database in a user defined function that encrypts the value with our symmetric key. Once the modification has occurred, you pass the modified IDbCommand to NHibernateUtil.String.NullSafeSet.

For example, a typical CommandText generated by NHibernate for an object insert would be:

INSERT INTO dbo.Cat (Id, Ssn) VALUES (@p0, @p1);

Our goal is to modify the CommandText to be:

INSERT INTO dbo.Cat (Id, Ssn) VALUES (@p0, dbo.Encrypt(@p1));

To accomplish the above, I refactored the NullSafeGet function override to the following. Note, that you have to take into account the location of the index parameter as being in the last parameter or first/middle parameter.

public override void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (cmd.Parameters.Count - 1 == index)
{
cmd.CommandText =
cmd.CommandText.Replace(
String.Format("@p{0})", index),
String.Format("dbo.Encrypt(@p{0}))", index));
}
Else
{
cmd.CommandText =
cmd.CommandText.Replace(
String.Format("@p{0},", index),
String.Format("dbo.Encrypt(@p{0}),", index));
}
NHibernateUtil.String.NullSafeSet(cmd, value, index);
}

Based on the above code, we would assume the dbo.Encrypt user defined function calls EncryptByKey and returns the encrypted value. However, there is another requirement that can easily be overlooked. When using EncryptByKey, the associated symmetric key must be first opened (as shown in an above code snippet).

A second assumption about the dbo.Encrypt user defined function is that the symmetric key is opened in the function immediately prior to calling EncryptByKey. Unfortunately, user defined functions cannot be used to perform actions that modify the database state, and opening a symmetric key modifies the database state. As a result, we need to ensure the symmetric key is opened prior to our call to dbo.Encrypt. One way to ensure the key is opened in our current database connection and immediately prior to calling dbo.Encrypt is to prepend the open statement to the beginning of the IDbCommand CommandText. The code snippet below shows the refactored NullSafeSet function to include the opening of the symmetric key:

public override void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (cmd.Parameters.Count - 1 == index)
{
cmd.CommandText =
String.Format("OPEN SYMMETRIC KEY kzFreshStartSymmetricKey DECRYPTION BY CERTIFICATE kzFreshStartCert;{0}",
cmd.CommandText.Replace(
String.Format("@p{0})", index),
String.Format("dbo.Encrypt(@p{0}))", index)));
}
Else
{
cmd.CommandText =
String.Format("OPEN SYMMETRIC KEY kzFreshStartSymmetricKey DECRYPTION BY CERTIFICATE kzFreshStartCert;{0}",
cmd.CommandText.Replace(
String.Format("@p{0},", index),
String.Format("dbo.Encrypt(@p{0}),", index)));
}
NHibernateUtil.String.NullSafeSet(cmd, value, index);
}

Now that we have completed modifying the IDbCommand CommandText, let’s look at the contents of the dbo.Encrypt user defined function:

CREATE FUNCTION [dbo].[Encrypt] (@plainText varchar(MAX))
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE @encryptedValue varbinary(MAX)
SELECT @encryptedValue = EncryptByKey(
KEY_GUID('kzFreshStartSymmetricKey'),
CAST(@plainText AS varbinary(MAX)));
RETURN @encryptedValue
END

The final two pieces of writing an encrypted value to the database is to refactor the domain and mapping classes to use the custom User Type create above:

public class Cat {
public virtual Guid Id { get; set; }
public virtual string SsnRead { get; set; }
public virtual string SsnWrite { get; set; }
}

public class CatMap : ClassMapping {
public CatMap() {
Id(x => x.Id, m => m.Generator(Generators.GuidComb));
Property(x => x.SsnRead, m => m.Formula(“dbo.DecryptSsn(Id)”));
Property(x => x.SsnWrite, m => { m.Type(); m.Column(“Ssn”); });
}
}