'How can I encrypt column with EF Core & SQL Server?

I have an application using SQL Server and .NET 5, EF Core 5. I need to encrypt some sensitive columns in the database.

I tried methods like Always Encrypted with Secure Enclaves and Symmetric Key encryption.

I couldn't use Always Encrypted due to limitations. ref: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver15#confidential-queries

Then I tried to use Symmetric Key on a demo project:

[Table("TestTableColumnEncrypt")]
public class TestTableColumnEncrypt
{
    public int Id { get; set; }
    public int NumberCol { get; set; }
    public string StrCol { get; set; }
    public decimal DecimalCol { get; set; }
    public int EncryptedNumberCol { get; set; }
    public string EncryptedStrCol { get; set; }
    public decimal EncryptedDecimalCol { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ...
    modelBuilder.Entity<TestTableColumnEncrypt>().Property(x => x.EncryptedNumberCol)
        .HasConversion(x => this.Encrypt(x), y => Convert.ToInt32(this.Decrypt(y)));
    ...
}

public byte[] Encrypt(object value)
{
    var encryptedValue = this.Set<FakeEncryptModel>().FromSqlRaw("sp_OpenKeyAndEncryptColumnValue {0}", value.ToString()).AsEnumerable().FirstOrDefault().EncryptedValue;
    
    return (byte[])encryptedValue;
}

public object Decrypt(byte[] encryptedValue)
{
    var decryptedValue = this.Set<FakeDecryptModel>().FromSqlRaw("sp_DecryptColumnValue {0}", encryptedValue).AsEnumerable().FirstOrDefault().DecryptedValue;

    return decryptedValue;
}

static void Main(string[] args)
{
    TestContext ctx = new TestContext();
        
    if (!ctx.TestTableColumnEncrypts.Any())
    {
        var data = new List<TestTableColumnEncrypt>();

        foreach (var item in Enumerable.Range(1, 5))
        {
            data.Add(new TestTableColumnEncrypt
                {
                    NumberCol = 1 + item,
                    StrCol = "str" + item,
                    DecimalCol = 1M + item,
                    EncryptedNumberCol = 1 + item,
                    EncryptedStrCol = "str" + item,
                    EncryptedDecimalCol = 1M + item
                });
           ctx.Add(data[item - 1]);
       }

       ctx.SaveChanges();
   }
   else
   {
       // var data1 = ctx.TestTableColumnEncrypts.FromSqlRaw("Select * from dbo.TestTableColumnEncrypt").ToList(); //OK
       // var data2 = ctx.TestTableColumnEncrypts.ToList(); //OK
            
       var data3 = ctx.TestTableColumnEncrypts.Where(x => x.EncryptedNumberCol = 2).FirstOrDefault(); //NOT OK
        var data4 = ctx.TestTableColumnEncrypts.Where(x => x.EncryptedNumberCol > 2).FirstOrDefault(); //NOT OK
    }
}
    

I use EncryptByKey and DecryptByKey functions for encryption and decryption.

Inserting the data is successful. It also succeeds when I try to get all the data of the table.

But when I use where conditions with LINQ queries, EF Core encrypts the condition value(2) on conversion process. And the result queries are like:

Equality operation failed. EncryptByKey function is non-deterministic, so it generates always different value for the same plaintext.

SELECT TOP(1) [t].[Id], [t].[DecimalCol], [t].[EncryptedDecimalCol], [t].[EncryptedNumberCol], [t].[EncryptedStrCol], [t].[NumberCol], [t].[StrCol]
FROM [TestTableColumnEncrypt] AS [t]
WHERE [t].[EncryptedNumberCol] = 0x00ACD3D67A73667D4E640251A675D7D60200000022FDE885432CFE38FFBBBE6EC16E276D27817A2B0F73AB863D916F8A1CC2EFB5

SELECT TOP(1) [t].[Id], [t].[DecimalCol], [t].[EncryptedDecimalCol], [t].[EncryptedNumberCol], [t].[EncryptedStrCol], [t].[NumberCol], [t].[StrCol]
FROM [TestTableColumnEncrypt] AS [t]
WHERE [t].[EncryptedNumberCol] > 0x00ACD3D67A73667D4E640251A675D7D6020000008B26CC41884F22FB10231E2BC88AA7B439BC91EF5E71AD8DCA04EFF0C2AF2167

Here is the problem, when I use the Comparison operator with LINQ query, SQL compares the data between two encrypted value, in an other words, it compares the data between two VARBINARY(MAX) value. Therefore, the comparison operation fails.

How can I use comparison operator on encrypted columns in LINQ queries?

Do you have any suggestion for an encryption method that can retrieve data with raw SQL and LINQ queries?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source