c# - Using EF Core 2.2 to decrypt a string using SQL Server DECRYPTBYKEY

Question

Basically I have a POCO model that has a encrypted string. Using EF core 2.2.

We use DECRYPTBYKEY to decrypt strings using SYMMETRIC KEY.

I am using DBSet.FromSQL to pass in SQL query which calls open symmetric key, get the data including the decrypted value, close symmetric key.

FromSQL only allows you to bring back an entity rather than a string by itself.

I have tried adding an decrypted string value on the model and have tried to then set that in FromSQL query.

This actually populates ok when the repository DBSet does not have any .Include in it.

When the DBSet does have .Include (to filter the DBSet on a foreign key table) there's a runtime error which complains about the decrypted string not being a column on the database table - which of course it isn't. So having .Include is calling the SQL on base table first.

If I put the [NotMapped] attribute on the decrypted string column then when the FromSQL query runs it doesn't populate it.

So how can I use this decrypted string column without using [NotMapped] but with using .Include on the DBSet?

I've added code so you can see the problem more. There's no way to add an implementation of Decrypt on the Model as suggested in one answer. Decrypt method requires the DbSet to call FromSQL. The DbSet originates comes from the ConcreteRepository. There's also no way that I can see to call an adhoc SQL Query to return 1 string.

Snip from Original SQL (SQL Server)

    OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';

    SELECT  * , --other fields
            CONVERT(VARCHAR(60), DECRYPTBYKEY(A.Encrypted)) AS Decrypted
    FROM    dbo.Model A
    JOIN table2 t2 ON ...
    JOIN table3 t3 ON ...

   WHERE A.Id= 123

   CLOSE SYMMETRIC KEY {1};",

Concrete Repository

public async Task<IEnumerable<Model>> GetAllById(int id)
{

            var filteredSet = Set.Where(x => x.Id == id)
               .Include(x => x.Table2)
               .Where(x => x.Table2.IsSomething).ToList();

            var models = filteredSet.Select(f =>
                GetDecryptValue($"Id = {f.Id}");

            return models;

}

Base Repository

protected DbSet<TEntity> Set => _dbContext.Set<TEntity>();

public virtual TEntity GetDecryptValue(string filterCriteria)
        {
            string buildSelectStmt = $"SELECT TOP 1 Encrypted FROM Model";
            string buildSelectStmt2 = $"SELECT *, CONVERT(VARCHAR(MAX), DECRYPTBYKEY(@Value)) AS Decrypted FROM Model";

            buildSelectStmt = $"{buildSelectStmt} WHERE {filterCriteria}";
            buildSelectStmt2 = $"{buildSelectStmt2} WHERE {filterCriteria}";

            string sql = string.Format(@"
                DECLARE @Value NVARCHAR(MAX)
                SET @Value = ({0});
                OPEN SYMMETRIC KEY {1} DECRYPTION BY PASSWORD = '{2}';
                {3};
                CLOSE SYMMETRIC KEY {1};",
                buildSelectStmt, SymmetricKeyName, SymmetricKeyPassword, buildSelectStmt2);

            var result = Set.FromSql(sql);

            return result.FirstOrDefault();
        }

Model

    public partial class Model
    {
        public int Id { get; set; }
        public string Encrypted { get; set; }
        [NotMapped]
        public string Decrypted { get; set; }
    }

Answer 1

So as I hinted in the comment, it is indeed possible to hack into EFCore's pipeline and make it do custom SQL functions. Here's a functional console app that does it.

I will state upfront, I experimented on a database (see SQL script within my repo link) with DECRYPTBYPASSPHRASE function as I didn't have a key. I also only have .net core 2.1 installed. None the less, I am hoping you'd get the gist anyway. With that said, I'll highlight a few points and let you explore the solution further:

I ended up defining my Model 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; }
}

Given I should be able to just select the value without having to make a second roundtrip, I slightly modified your Concrete Repository code:

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: 1) as extension methods so we can use then in LINQ and 2) 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 you will see 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;
    }
}

pretty trivial string building exercise after all. Hopefully this gives you enough fuel to get your solution up and running.

UPD EF Core 3 seems to still support the IMethodCallTranslator, therefore solution above should still apply. UPD2: Indeed, can be done. See my updated repo on github.