Decrypting data using EF Core 3

Question

I am trying to find out how to use EF Core to decrypt data that is column encrypted with a Symmetric Key. In SQL Server, I would use these commands to get my data:

OPEN SYMMETRIC KEY My_Key DECRYPTION BY CERTIFICATE myCert;
SELECT  CONVERT(char, DecryptByKey(soc_sec_no))
FROM MyDatabase WHERE arid_identifier=0001882

How can I do this from EF Core 3? I see a lot of articles on how to do this from Entity Framework 6 (non-core) and I did find this question, but the code did not work for me.

Answer 1

I started with https://github.com/tkhadimullin/ef-core-custom-functions/tree/feature/ef-3.1-version .

I've put the changes on https://github.com/xanatos/ef-core-custom-functions/tree/feature/ef-3.1-version . Note that there are three commits, one for EF Core 3.1, one for EF Core 3.1.11 (no changes necessary), one for EF Core 5.0.2 (some changes necessary).

Sadly there were various small bugs in the code that I had to correct. The part about OPEN SYMMETRIC KEY in fact wasn't tested and was commented out by the author.

Changes necessary:

class Repo:

A transaction is needed to keep together the various SQL commands. The indexes of the parameters are wrong. I've added some protection against injection attacks vs the symmetric key name and password (probably useless because these two things should be very protected)

public IEnumerable<Model> GetAllById(int id)
{
    // Transaction to keep together the two ExecuteSqlRaw with the query
    DbContext.Database.BeginTransaction();

    DbContext.Database.ExecuteSqlRaw($"DECLARE @Open NVARCHAR(MAX) = N'OPEN SYMMETRIC KEY ' + QUOTENAME(@p0, '[') + ' DECRYPTION BY PASSWORD = ' + QUOTENAME(@p1, '''') + N';'; EXEC sp_executesql @Open", 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.Decrypt(SymmetricKeyPassword, m.Encrypted).ToString(),
            Decrypted = EF.Functions.DecryptByKey(m.Encrypted2).ToString(), // since the key's opened for session scope - just relying on it should do the trick
            Table2 = m.Table2,
            Encrypted = m.Encrypted,
        }).ToList();

    DbContext.Database.ExecuteSqlRaw($"DECLARE @Close NVARCHAR(MAX) = N'CLOSE SYMMETRIC KEY ' + QUOTENAME(@p0, '[') + ';'; EXEC sp_executesql @Close", SymmetricKeyName);

    DbContext.Database.CommitTransaction();

    return filteredSet;
}

class TranslateImpl:

Fixed some problems with the handling of arguments (2 arguments aren't always present, and if there is only one the method crashes)

public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)
{
    if (method == _encryptMethod)
    {
        var args = new[] { arguments[1], arguments[2] }; // cut the first parameter from extension function
        return _expressionFactory.Function(instance, "ENCRYPTBYPASSPHRASE", args, typeof(byte[]));
    }

    if (method == _decryptMethod)
    {
        var args = new[] { arguments[1], arguments[2] }; // cut the first parameter from extension function
        return _expressionFactory.Function(instance, "DECRYPTBYPASSPHRASE", args, typeof(byte[]));
    }

    if (method == _decryptByKeyMethod)
    {
        var args = new[] { arguments[1], }; // cut the first parameter from extension function
        return _expressionFactory.Function(instance, "DECRYPTBYKEY", args, typeof(byte[]));
    }

    return null;
}

script dbSchema.sql

The script can now encrypt text in two different ways (passphrase or symmetric key)

CREATE SYMMETRIC KEY [TestKeyWithPassword] WITH ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = 'TestPassword'
GO
OPEN SYMMETRIC KEY [TestKeyWithPassword] DECRYPTION BY PASSWORD = 'TestPassword'
GO
INSERT [dbo].[Models] ([Id], [Encrypted], [Table2Id]) 
VALUES (
    1, 
    --ENCRYPTBYPASSPHRASE('TestPassword', 'Encrypted with Passphrapse'), 
    ENCRYPTBYKEY(key_GUID('TestKeyWithPassword'), 'Encrypted with Symmetric Key With Password'), 
    1)

Big note about .ToString()

There is a .ToString() after the Decrypt/DecryptByKey. It is translated to a CONVERT(VARCHAR(100), ...) by EF Core. If you want something different you'll have to create some DBFunctions to do it (it is quite simple, take the example from DbFunctionsExtensions.Encrypt, done in my git)

Big note about encrypting on save

In all of this, I'm not sure how you would implement the encryption part. While you have some control on the SELECT generated by EF Core, you have much smaller control on the INSERT/UPDATE.