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 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();
First I create a random SSN so I can test if the update will be properly executed. Then I retrieve the old value for record identified by Id of 1, so I can compare the old and new values. Next I execute an update statement and finish with retrieving the new value for the same record.
Of course you don't have to create a new connection every time you want to use Dapper or my database extensions. The same code sample can be rewritten like so:
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");
It is important to note, that Dapper will map your anonymous class properties to SQL parameters by name. Dapper will also cache all queries it executes, so the mapping process, either for parameters or results mapped to your POCO classes, takes a performance hit only during the first execution. It is quite impressive IMHO, how much work this micro-ORM saves me in my daily tasks.
Other extensions which you could find useful:
public static IDbTransaction NewTransaction(this SqlConnection connection)
{
var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
return connection.BeginTransaction(transactionId);
}
This extension provides a uniquely named transaction at any point inside the using block.
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);
}
I am free to Commit and Rollback the transaction as the logic dictates.
If you want more control over transaction isolation level, you could use this extension instead:
public static IDbTransaction NewTransaction(this SqlConnection connection, IsolationLevel isolationLevel = IsolationLevel.Serializable)
{
var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
return connection.BeginTransaction(isolationLevel,transactionId);
}
Or if you prefer Snapshot isolation at all times, which significantly reduces blocking and deadlocks:
public static IDbTransaction NewSnapshotTransaction(this SqlConnection connection)
{
var transactionId = Guid.NewGuid().ToString().Replace("-", string.Empty);
return connection.BeginTransaction(IsolationLevel.Snapshot, transactionId);
}
Another extension, which I use only in the simplest of scenarios, returns the @@IDENTITY value.
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));
}
}
Before you jump on the opportunity to use it, I urge you to review Microsoft's documentation on the differences between @@IDENTITY, SCOPE_IDENTITY, and IDENT_SCOPE. Depending on your particular scenario and database content, you might not get the results you'd logically expect ...
But not to worry, Dapper and SQL Server will come to your resuce again. You only need to include the OUTPUT clause in your statement, Let's create a new table to experiment with:
CREATE TABLE [dbo].[T1](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[C1] [nchar](10) NULL
) ON [PRIMARY]
Then, to insert a new record and get its identiy column value, I change my SQL like so:
var identity = connection.Query<long>(
"INSERT INTO dbo.T1 ([C1]) OUTPUT INSERTED.Id VALUES('a')").First();
This provides me with 100% gurantee, that the returned value will be the identity of the new record inserted, even if I execute it from multiple threads amd locations, which the other approaches do not offer:
- @@IDENTIY can be changed by triggers
- SCOPE_IDENTITY works correctly only within "modules" (i.e. stored procedure, batch, etc.)
- IDENT_SCOPE is not limited by scope or session
Summary
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.
