LinQ Statements not working on Encrypted Fields

[daniels7]

Hi,

I'd love to use this component, but I sadly got one problem.

I'm unable to use LinQ-Statements on Encrypted fields

Example: var user = Entities.FirstOrDefault(s=> s.Email==identity) ?? Entities.FirstOrDefault(s => s.Username == identity);

(Email and Username are encrypted with your extension).

This query will always return null. If I query all objects by using Entities.AsEnumerable(); and then query those I get the record I'm looking for but it requires to fetch all records from the DB and I'd like to avoid that.

The problem seems to be that every Encryption results in a different output. If I'm using the same query 6 times and watch the log output it will query for 6 different things despite identity being the same all the time.

Is there any solution on that?

Greetings Daniel

[nhontran]

I got the same problem, the root cause is it is using the dynamic IV when doing the encryption, so the same text value is returned different encrypted string value for each time:

Ex: 1st time encrypt: "Testing 1" -> "SQAxADMAVgAyAEkAMQAzAN1BInMOIejjeXaydFezA2c=" 2nd time encrypt: "Testing 1" -> "SQAxADMAVgAyAEkAMQAzAL0S+K2XF/nLAG/qdq6V+b4="

When you do the query, the input is encrypted and compared with the database encrypted string -> it returns "not found" as not matched.

[hasansahinnn]

The key generated by the provider is different each time. Therefore, encrypted data is not the same. For Linq Statements, I've had the same problem. When I look at the logs, it automatically sends the encrypted data in the sql query. you don't need to do anything extra. I used this class ;

` public class Encryption : IEncryptionProvider { private string privateKey = "secretkeysecretkeysecretkeysecretkeysecretkey";

region Utilty

    private byte[] EncryptTextToMemory(string data, byte[] key, byte[] iv)
    {
        using (var ms = new MemoryStream())
        {
            using (var cs = new CryptoStream(ms, new TripleDESCryptoServiceProvider().CreateEncryptor(key, iv), CryptoStreamMode.Write))
            {
                var toEncrypt = Encoding.Unicode.GetBytes(data);
                cs.Write(toEncrypt, 0, toEncrypt.Length);
                cs.FlushFinalBlock();
            }

            return ms.ToArray();
        }
    }

    private string DecryptTextFromMemory(byte[] data, byte[] key, byte[] iv)
    {
        using (var ms = new MemoryStream(data))
        {
            using (var cs = new CryptoStream(ms, new TripleDESCryptoServiceProvider().CreateDecryptor(key, iv), CryptoStreamMode.Read))
            {
                using (var sr = new StreamReader(cs, Encoding.Unicode))
                {
                    return sr.ReadToEnd();
                }
            }
        }
    }

    #endregion

    public string Encrypt(string text)
    {
        try
        {
            if (string.IsNullOrEmpty(text) || text == "null")
                return string.Empty;

            using (var provider = new TripleDESCryptoServiceProvider())
            {
                provider.Key = Encoding.ASCII.GetBytes(privateKey.Substring(0, 16));
                provider.IV = Encoding.ASCII.GetBytes(privateKey.Substring(8, 8));

                var encryptedBinary = EncryptTextToMemory(text, provider.Key, provider.IV);
                return Convert.ToBase64String(encryptedBinary);
            }
        }
        catch (Exception e)
        {
            return text;
        }
    }

    public string Decrypt(string text)
    {
        try
        {
            if (string.IsNullOrEmpty(text) || text == "null")
                return string.Empty;

            using (var provider = new TripleDESCryptoServiceProvider())
            {
                provider.Key = Encoding.ASCII.GetBytes(privateKey.Substring(0, 16));
                provider.IV = Encoding.ASCII.GetBytes(privateKey.Substring(8, 8));

                var buffer = Convert.FromBase64String(text);
                return DecryptTextFromMemory(buffer, provider.Key, provider.IV);
            }
        }
        catch
        {
            //throw new InvalidTokenException();
            return text;
        }
    }
}

`

[Eastrall]

Hello,

V2 has removed the IV parameter on the AesProvider for security purposes. But now that you all mention this, it might have been a bad design choice to mark it as Obsolete and remove the support of a fixed IV in this case. I will reintroduce the custom IV option in next version, which will allow you to choose between the two options. (Key + IV or Key + Dynamic IV per entry).

[Eastrall]

LINQ statements will be working again with Version 3.0 when PR #25 will be merged. Of course, you will have to use a fixed initialization vector for the AES encryption process to make LINQ statements work as Version 1.1.0.

[Eastrall]

Version 3.0.0 is now available on Nuget and should fix this issue if you use the "fixed IV" option: https://www.nuget.org/packages/EntityFrameworkCore.DataEncryption