Extending IDbConnection and getting help from Dapper for tedious tasks

Introduction

Every once in a while, I am faced with writing repetitive code. Since I like staying DRY, I quickly resort to creating (or copying and pasting) a number of extensions, for various tasks. This article will demonstrate how to create a universal log4net extension, as well as generic and secure database extensions to simplify and streamline coded interactions with a database.

Background

The reader is expected to have a good understanding of C# extension methods, log4net, and the Dapper micro-ORM library.

SQL Scripts

In preparation of this exercise, please create the required certificates and table which will contain secure data:

Certificate

--If there is no master key, create one now. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '383927hxJKL969#gif0%34467GRkjl5k3fd117r$$#1946kcj$n44nhdlj'
GO
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Employee Social Security Numbers';
GO 

Encryption Key

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO 

Table

CREATE TABLE [dbo].[Employees](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [SSN] [varbinary](128) NOT NULL
) ON [PRIMARY] 

Using the code

When I first discovered Dapper, I quickly realized the power of micro-ORM for basic and medium complexity tasks. I still use Entity Framework for more complex designs, but I have been recently disappointed with the steady performance degradation of the newer releases. Dapper is easy to use, quick to develop with, yet it still required a degree of boilerplate code to instantiate and dispose of connections. I am a big believer in separating concerns and since I did not want to repeat the same piece of code over and over, I decided to write a few extensions for the work that I repeatedly needed to do:

Let's start with basic extensions first:

public static TReturn Using<T, TReturn>(this T client, Func<T, TReturn> work)
    where T : IDbConnection
{ 
    try
    {
        client.Open();
        TReturn o = work(client);
        client.Close();
        return o;
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
} 

The extension is modeled on a similar concept I employ with WCF clients. It is not a good idea in that case to rely on the state of the client might be in. Similarly, for the IDbConnection, I usually obtain it from a ConnectionPool<SqlConnection>, provided by the Parallel Extensions Extras.

static readonly SqlConnectionStringBuilder Scsb = 
  new SqlConnectionStringBuilder(){
    DataSource = "localhost", 
    InitialCatalog = "Scratch", 
    IntegratedSecurity = true};

static readonly ObjectPool<SqlConnection> ConnectionPool = 
  new ObjectPool<SqlConnection>(
    ()=>new SqlConnection(Scsb.ConnectionString));  

The extension method allows me to execute any Func<TReturn> on any database connection object implementing the IDbConnection interface. It will take care of opening the connection before work is to be performed and closing it immediately after every line in the Func<> body has completed. This allows me to combine multiple Dapper calls using one SqlConnection, but does not imply that such call will be governed by a transaction (however, it is easy to implement). It will also log any warning and error messages using the Log4Net library, and the logger configured for the type T, i.e. SqlConnection.

Because my database iteraction is governed by another try/catch block, I only return the connection to the pool, if the call was executed succesfully and the connection is in a closed state. Otherwise the failed object gets discarded.

try {                
    var connection = ConnectionPool.GetObject();
    connection.Execute( /* ... */ );
    ConnectionPool.PutObject(connection);
} catch (Exception ex) {
    typeof(Program).Log().Error("While populating data", ex);
}

I realize this might be a bit of an overkill, since as compared to the ICommunicationObject, the IDbConnection should never end in a faulted state (technically, it has no state).

The Log() method is another extension, which returns an object implementing ILog interface and can be used on any object to automatically choose a logger configured for a particular type:

using log4net;
public static class LogExtensions
{
    #region Public Methods and Operators
    public static ILog Log<T>(this T t) { return LogManager.GetLogger(typeof(T)); }
    #endregion
} 

To use the new database extensions with Dapper, I can simply pass it a new connection object and use the Dapper Query<T> extension method. In the example below I retrieve multiple Person's records from the database.

var scsb = new SqlConnectionStringBuilder
           {
               DataSource = "SERVER",
               InitialCatalog = "DATABASE",
               IntegratedSecurity = true
           };
const string SelectEmployees = "SELECT TOP 10 [Id], CONVERT(NCHAR(11), DECRYPTBYKEY(SSN)) AS [SSN] FROM dbo.Employees;";
List<Person> result1 = new SqlConnection(scsb.ConnectionString).Using(c => c.Query<Person>(SelectEmployees)).ToList(); 

Dapper will auto-map all columns retrieved from the database to the class properties matching them by name. There are also a number of ways to map them using attributes, which is beyond the scope of this article. Dapper will leave properties with no equivalent columns with their default values.

At this point, it is worth mentioning that Microsoft's SQL Server supports few ways to secure data:

• Symmetric Keys
• Assymmetric Keys
• Certificates
• Transparent Data Encryption (TDE)

However, you must realize that the data is only encrypted in the database, and it gets decrypted before it reaches the transport layer. In other words, data in transfer is sent in plain text mode. If you require higher degree of security, you must resort to communication channel encryption, using SSL certificates. Having encrypted columns guards against a database, or its backup, being taken offline to a standalone server, and other similar attacks.

No single algorithm is ideal for all situations, and guidance on the merits of each is beyond the scope of this article or even SQL Server Books Online. However, the following general principles apply:

• Strong encryption generally consumes more CPU resources than weak encryption.
• Long keys generally yield stronger encryption than short keys.
• Asymmetric encryption is weaker than symmetric encryption using the same key length, but it is relatively slow.
• Block ciphers with long keys are stronger than stream ciphers.
• Long, complex passwords are stronger than short passwords.
• If you are encrypting lots of data, you should encrypt the data using a symmetric key, and encrypt the symmetric key with an asymmetric key.
• Encrypted data cannot be compressed, but compressed data can be encrypted. If you use compression, you should compress data before encrypting it.

Credit: Microsoft SQL Server Books Online

In the example above, I am retrieving the Id column, which is a plain Identity column, as well as an encrypted column, the social security number, which has been secured with a symmetric key generated on the database. If you tried to execute this code as is, the Id column would be returned as requested, but the SSN column would be NULL for all records. The reason for it is that before you can decrypt a column, you must open the certificate which it was secured with.

Using a sample from Microsoft's best practices on this subject, the command would look like so:

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037; 

After you are done with your query, it is recommended to close the certificate using this command:

CLOSE SYMMETRIC KEY SSN_KEY_01;  

This requirement lead me to create another extension method, which can be used to retrieve secured records without a need to specify the prefix- and suffix- SQL for each secure operation:

public static TReturn UsingSecure<T, TReturn>(this T client, Func<T, TReturn> work, string keyName, string certificateName)
    where T : IDbConnection
{
    try
    {
        client.Open();
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1};", keyName, certificateName);
            c.ExecuteNonQuery();
        }
        TReturn o = work(client);
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("CLOSE SYMMETRIC KEY {0};", keyName);
            c.ExecuteNonQuery();
        }
        client.Close();
        return o;
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}  

The differences between the first extension method and a secure version of it is the addition of names of key and certificate to be used during secure interaction. The extension method creates IDbCommand objects, constructing the necessary SQL commands and executing them before and after the actual SQL statement to be called. Now I can request the secure query to be executed, while properly opening and closing the symmetric key used for encryption:

List<Person> result2 = new SqlConnection(scsb.ConnectionString).UsingSecure(c => c.Query<Person>(SelectEmployees), "SSN_Key_01", "HumanResources037").ToList(); 

This time, the Id and the SSN column will contain data.

To support executing SQL commands, which return no data, like UPDATEs or even stored procedures, I have created two additional database extension methods.

public static void Using<T>(this T client, Action<T> work) where T : IDbConnection
{
    try
    {
        client.Open();
        work(client);
        client.Close();
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
}

public static void UsingSecure<T>(this T client, Action<T> work, string keyName, string certificateName) where T : IDbConnection
{
    try
    {
        client.Open();
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1};", keyName, certificateName);
            c.ExecuteNonQuery();
        }
        work(client);
        using (IDbCommand c = client.CreateCommand())
        {
            c.CommandText = String.Format("CLOSE SYMMETRIC KEY {0};", keyName);
            c.ExecuteNonQuery();
        }
        client.Close();
    }
    catch (TimeoutException e)
    {
        client.Log().Warn(e);
        throw;
    }
    catch (SqlException e)
    {
        client.Log().Error(e);
        throw;
    }
    catch (Exception e)
    {
        client.Log().Fatal(e);
        throw;
    }
} 

Now, I can freely query and update secure data with minimal effort:

const string selectWithId = "SELECT CONVERT(NCHAR(11), DECRYPTBYKEY(SSN)) AS [SSN] FROM dbo.Employees WHERE [Id]=@id";

var r = new Random();
string newSSN = String.Format("{0:000}-{1:00}-{2:0000}", r.Next(100, 999), r.Next(99), r.Next(9999));

string oldSSN =
    new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }),
        "SSN_Key_01",
        "HumanResources037").First();

new SqlConnection(scsb.ConnectionString).UsingSecure(
    c => c.Execute(
        "UPDATE dbo.Employees SET SSN = EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) WHERE [Id]=@id;",
        new
        {
            id = 1,
            ssn = newSSN
        }),
    "SSN_Key_01",
    "HumanResources037");

string updSSN =
    new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }),
        "SSN_Key_01",
        "HumanResources037").First(); 

new SqlConnection(scsb.ConnectionString).UsingSecure(
        c => 
  {

    string oldSSN = c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }).First();

    c.Execute(
        "UPDATE dbo.Employees SET SSN = EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) WHERE [Id]=@id;",
        new
        {
            id = 1,
            ssn = newSSN
        });

    string updSSN = c.Query<string>(
            selectWithId,
            new
            {
                id = 1
            }).First();

  }, "SSN_Key_01", "HumanResources037");

public static IDbTransaction NewTransaction(this SqlConnection connection)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(transactionId);
}

try
{
    var connection = ConnectionPool.GetObject();
    connection.UsingSecure(c =>
    {
        var transaction = c.NewTransaction();
        try
        {
            var randomSSN = String.Format("{0:000}-{1:00}-{2:0000}", random.Next(100, 1000),
            random.Next(100),
            random.Next(10000));
        c.Execute(
            "INSERT INTO dbo.Employees ([FirstName],[LastName],[SSN]) VALUES (@firstName, @lastName, EncryptByKey(Key_GUID('SSN_Key_01'),@ssn) )",
            new
            {
                firstName = "John",
                lastName = "Smith",
                ssn = randomSSN                                    
            }, transaction);
        transaction.Commit();
        }
        catch(Exception te)
        {
            transaction.Rollback();
        }
    }, "SSN_Key_01",
        "HumanResources037");
    ConnectionPool.PutObject(connection);
}
catch (Exception ex)
{
    typeof(Program).Log().Error("While populating data", ex);
}

public static IDbTransaction NewTransaction(this SqlConnection connection, IsolationLevel isolationLevel = IsolationLevel.Serializable)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(isolationLevel,transactionId);
} 

public static IDbTransaction NewSnapshotTransaction(this SqlConnection connection)
{
    var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
    return connection.BeginTransaction(IsolationLevel.Snapshot, transactionId);
}

public static T GetIdentity<T>(this IDbConnection connection, IDbTransaction transaction = null)
{
    using (var command = connection.CreateCommand())
    {
        command.Transaction = transaction;
        command.CommandText = "SELECT @@IDENTITY";
        var retval = command.ExecuteScalar();
        return (T)Convert.ChangeType(retval,typeof(T));
    }
}

CREATE TABLE [dbo].[T1](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [C1] [nchar](10) NULL
) ON [PRIMARY]

var identity = connection.Query<long>(
    "INSERT INTO dbo.T1 ([C1]) OUTPUT INSERTED.Id VALUES('a')").First();

Dapper combined with a few extension methods is a true time and effort saver. You can use the code presented here as a base for creating a highly secure coded interaction with the SQL server, or any other database server, which implements the IDbConnection interface. Since the certificates' and keys' names can be made of any ascii-text and stored in encrypted configuration files, it will lead to an environment which will be difficult to penetrate in an attempt to gain access to highly sensitive data.