Every now and then Stack Overflow provides fantastic opportunities to learn something new. One user asked whether SQL Server’s DECRYPTBYPASSPHRASE can be implemented with Entity Framework Core 2.2 so they can fetch encrypted strings in SQL.
We like the challenge
This post led us to quickly implement a PoC for EF2.2. We’ve got a Model defined like so:
public partial class Model
{
public int Id { get; set; }
public byte[] Encrypted { get; set; } // apparently encrypted data is stored in `VARBINARY`, which translates to `byte[]`, so I had to tweak it here
[NotMapped] // this is still required as EF will not know where to get the data unless we tell it (see down below)
public string Decrypted { get; set; } // the whole goal of this exercise here
public Table2 Table2 { get; set; }
}And a Concrete Repository to access the DB:
public IEnumerable<Model> GetAllById(int id)
{
// you will need to uncomment the following line to work with your key
//_dbContext.Database.ExecuteSqlCommand("OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';", SymmetricKeyName, SymmetricKeyPassword);
var filteredSet = Set.Include(x => x.Table2)
.Where(x => x.Id == id)
.Where(x => x.Table2.IsSomething)
.Select(m => new Model
{
Id = m.Id,
//Decrypted = EF.Functions.DecryptByKey(m.Encrypted), // since the key's opened for session scope - just relying on it should do the trick
Decrypted = EF.Functions.Decrypt("test", m.Encrypted),
Table2 = m.Table2,
Encrypted = m.Encrypted
}).ToList();
// you will need to uncomment the following line to work with your key
//_dbContext.Database.ExecuteSqlCommand("CLOSE SYMMETRIC KEY {1};", SymmetricKeyName);
return filteredSet;
}now, defining EF.Functions.Decrypt is the key here. We basically have to do it twice:
- as extension methods so we can use then in LINQ and
- as EF Expression tree nodes.
What EF then does, for each method call it discovers, it checks internal list of IMethodCallTranslator and if it discovers a match – it defers the function to SQL. Otherwise it will have to be run in C#. So all the plumbing we’re going to have to do next is basically needed to inject TranslateImpl into that list.
The IMethodCallTranslator itself
public class TranslateImpl : IMethodCallTranslator
{
private static readonly MethodInfo _encryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Encrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(string) });
private static readonly MethodInfo _decryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Decrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(byte[]) });
private static readonly MethodInfo _decryptByKeyMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.DecryptByKey),
new[] { typeof(DbFunctions), typeof(byte[]) });
public Expression Translate(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Method == _encryptMethod)
{
var password = methodCallExpression.Arguments[1];
var value = methodCallExpression.Arguments[2];
return new EncryptExpression(password, value);
}
if (methodCallExpression.Method == _decryptMethod)
{
var password = methodCallExpression.Arguments[1];
var value = methodCallExpression.Arguments[2];
return new DecryptExpression(password, value);
}
if (methodCallExpression.Method == _decryptByKeyMethod)
{
var value = methodCallExpression.Arguments[1];
return new DecryptByKeyExpression(value);
}
return null;
}
}I ended up implementing three expression stubs: DecryptByKey, DecryptByPassphrase and EncryptByPassphrase, for example:
public class DecryptByKeyExpression : Expression
{
private readonly Expression _value;
public override ExpressionType NodeType => ExpressionType.Extension;
public override Type Type => typeof(string);
public override bool CanReduce => false;
protected override Expression VisitChildren(ExpressionVisitor visitor)
{
var visitedValue = visitor.Visit(_value);
if (ReferenceEquals(_value, visitedValue))
{
return this;
}
return new DecryptByKeyExpression(visitedValue);
}
protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is IQuerySqlGenerator))
{
return base.Accept(visitor);
}
visitor.Visit(new SqlFragmentExpression("CONVERT(VARCHAR(MAX), DECRYPTBYKEY("));
visitor.Visit(_value);
visitor.Visit(new SqlFragmentExpression("))"));
return this;
}
public DecryptByKeyExpression(Expression value)
{
_value = value;
}
}All in all, it ends up being a pretty trivial string building exercise after all.
But the question remained
Can we port it forward? When we tried applying the same code base to version 3 of the framework we found that code has changed quite a bit. For one, Expressions were no longer an option.
Good news
Good news was that the approach was still valid. Custom Method Call translation is still a thing in EF Core 3. Even better news, the functionality seems to have gotten a bit more polished in the new version and now it’s a bit easier to do.
Solution
In EF Core 3, we’ve got to deal with ISqlExpressionFactory to get most of the job done. It gets passed around to plugins and has convenient methods to generate required expressions:
public class TranslateImpl : IMethodCallTranslator
{
private readonly ISqlExpressionFactory _expressionFactory;
private static readonly MethodInfo _encryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Encrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(string) });
private static readonly MethodInfo _decryptMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.Decrypt),
new[] { typeof(DbFunctions), typeof(string), typeof(byte[]) });
private static readonly MethodInfo _decryptByKeyMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.DecryptByKey),
new[] { typeof(DbFunctions), typeof(byte[]) });
public TranslateImpl(ISqlExpressionFactory expressionFactory)
{
_expressionFactory = expressionFactory;
}
public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)
{
var args = new List<SqlExpression> { arguments[1], arguments[2] }; // cut the first parameter from extension function
if (method == _encryptMethod)
{
return _expressionFactory.Function(instance, "EncryptByPassPhrase", args, typeof(byte[]));
}
if (method == _decryptMethod)
{
return _expressionFactory.Function(instance, "DecryptByPassPhrase", args, typeof(byte[]));
}
if (method == _decryptByKeyMethod)
{
return _expressionFactory.Function(instance, "DecryptByKey", args, typeof(byte[]));
}
return null;
}
}this is way easier than the previous version!
Talk is cheap. Show me the code
Working code can be found on my GitHub, check out the difference between implementations for both frameworks.