dotnet / dotnet/efcore

SQL Server Always Encrypted: rewrite constants/literals in query to parameters

Open
#37,037 4 comments 0 reactions 0 assignees View on GitHub
area-sqlserver
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

When using Always Encrypted, one cannot compare an encrypted column to a literal in SQL:

```c#
context.Patients.Where(p => p.SSN == "123-45-6789");
```

This is because encryption must happen client-side, but the literal is unencrypted. The current workaround for this is for the user to parameterize the value, at which point SqlClient takes care of the client-side encryption:

```c#
var ssn = "123-45-6789";
context.Patients.Where(p => p.SSN == ssn);
```

(this is called out e.g. [here](https://techcommunity.microsoft.com/blog/sqlserver/using-always-encrypted-with-entity-framework-6/384433#community-384433-toc-hId-1950744654))

EF can do this for the user, i.e. detect comparisons of an encrypted column to a constant node, and replace the constant with a parameter. The same would apply for a few others operations (e.g. `Where(b => new[] { 1, 2, 3 }.Contains(b.EncryptedColumn)`). For full end-to-end support, we'd probably have to do the same thing in the update pipeline wherever we currently use literals. This would also require us to know which columns are encrypted (metadata).

In query, we could do this in a postprocessing visitor that's conditional on the connection string containing `Column Encryption Setting=enabled`.

Note that this is all orthogonal to EF actually setting up Always Encrypted in migrations; that's #23970.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.