SQL Server Always Encrypted 可以保護我們的資料，但同時也帶來了一些不便，比如索引跟內建的預存無法使用，強制使用參數化查詢，這裡列出我已知的開發限制，下次碰到就可以直接避開
接續上篇，https://dotblogs.com.tw/yc421206/2019/05/18/ef6_connect_sql_server_2016_always_encrypted
上篇提到了要如何設定，這裡要談談開發上的限制
本文連結
開發環境
- VS 2017
- SQL Server 2016 Express Localdb
專案位置
https://github.com/yaochangyu/sample.dotblog/tree/master/ORM/EF6/Lab.EF6.AlwaysEncrypt
專案取出來之後，在 SQL Project 部署資料庫並把憑證匯入到 LocalMachine\My
資料結構
加密欄位
開發使用限制
查詢、異動需要使用參數化
在 SSMS 要異動、查詢資料就沒有辦法像以前那樣直接，必須要透過參數化的方式來處理
查詢
這是很常見的查詢語法，可是用了加密 Always Encrypted，就不能直接這樣寫了
USE [Lab.EF6.AlwaysEncrypt]
GO
SELECT
e.Id
,e.Name
,e.Age
,e.CreateAt
,e.ModifyAt
,e.Bonus
,e.Birthday
,e.SequenceId
FROM dbo.Employee e
WHERE e.Name = '小章'
;
GO
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') collation_name = 'SQL_Latin1_General_CP1_CI_AS'
改用參數化
USE [Lab.EF6.AlwaysEncrypt]
GO
DECLARE @Name NVARCHAR(10) = '小章'
SELECT
Id
,Name
,Age
,CreateAt
,ModifyAt
,Bonus
,Birthday
,SequenceId
FROM dbo.Employee
WHERE Name=@Name
新增
USE [Lab.EF6.AlwaysEncrypt]
DECLARE @Id UNIQUEIDENTIFIER = 'E8BECBF2-2AA4-446C-8E5C-FE01E1FBC91C'
,@Name NVARCHAR(10) = 'yao'
,@Age INT = 10
,@CreateAt DATETIME = GETDATE()
,@ModifyAt DATETIME = GETDATE()
,@Bonus NUMERIC(3, 1) = 20.0
,@Birthday DATE = GETDATE()
INSERT INTO dbo.Employee (Id,
Name,
Age,
CreateAt,
ModifyAt,
Bonus,
Birthday)
VALUES (@Id, @Name, @Age, @CreateAt, @ModifyAt, @Bonus, @Birthday)
新增預存
CREATE PROCEDURE dbo.Insert_Employee @Id UNIQUEIDENTIFIER,
@Name NVARCHAR(10),
@Age INT,
@CreateAt DATETIME,
@ModifyAt DATETIME NULL,
@Bonus NUMERIC(3, 1) NULL,
@Birthday DATE NULL
AS
BEGIN
IF EXISTS (SELECT
*
FROM dbo.Employee
WHERE Id = @Id)
UPDATE dbo.Employee
SET Name = @Name
,Age = @Age
,CreateAt = @CreateAt
,ModifyAt = @ModifyAt
,Bonus = @Bonus
,Birthday = @Birthday
WHERE Id = @Id
ELSE
INSERT INTO dbo.Employee (Id,
Name,
Age,
CreateAt,
ModifyAt,
Bonus,
Birthday)
VALUES (@Id, @Name, @Age, @CreateAt, @ModifyAt, @Bonus, @Birthday)
END
GO
對於 EF ，異動資料原本就是參數化，影響不大，查詢欄位要拉出來放在變數裡面，這樣轉譯成 SQL 語法的時候就會變成參數化
[TestMethod] public void 過濾使用參數() { var name = "小章"; using (var dbContext = new TestDbContext()) { var employee = dbContext.Employees .Where(p => p.Name == name) .AsNoTracking() .FirstOrDefault(); Assert.AreEqual(name, employee.Name); } }
無法使用排序
當該欄位的資料已經被加密了，不能排序也是理所當然的
USE [Lab.EF6.AlwaysEncrypt]
GO
SELECT
e.Id
,e.Name
,e.Age
FROM Employee e
ORDER BY e.Name
Msg 33299, Level 16, State 2, Line 4
Encryption scheme mismatch for columns/variables 'Name'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '2' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
沒意外應該會得到上述錯誤，接著看 EF
//無法使用SQL排序 using (var dbContext = new TestDbContext()) { var employees = dbContext.Employees.AsNoTracking().OrderBy(p => p.Name).ToList(); }
Test method Lab.EF6.AlwaysEncrypt.UnitTest.EF6_Solution.無法使用SQL排序 threw exception:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Encryption scheme mismatch for columns/variables 'Name'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '3' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
果然，跟 T-SQL 的錯誤一樣
硬是要做的話就全部撈回來再排序
[TestMethod] public void 無法使用SQL排序() { var expected = new[] { new {Name = "小章", Age = 18}, new {Name = "小英", Age = 23}, new {Name = "小明", Age = 33} }; using (var dbContext = new TestDbContext()) { dbContext.Configuration.LazyLoadingEnabled = false; dbContext.Configuration.ProxyCreationEnaled = false; var employees = dbContext.Employees .AsNoTracking() .ToList() .OrderBy(p => p.Age) ; employees.Should().BeEquivalentTo(expected, option => { option.WithStrictOrdering(); return option; }); } }
無法使用分組
在 SSMS 試了一下沒有辦法針對單一欄位分組
USE [Lab.EF6.AlwaysEncrypt]
GO
SELECT
e.Id
,e.Name
,e.Age
,e.CreateAt
FROM dbo.Employee e
GROUP BY e.Name
'dbo.Employee.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
EF 也是沒有辦法，也是一樣全撈後再分組
[TestMethod] public void 無法使用SQL分組() { var expected = new[] { new {Name = "小明", Age = 33}, new {Name = "小英", Age = 23}, new {Name = "小章", Age = 18} }; using (var dbContext = new TestDbContext()) { dbContext.Configuration.LazyLoadingEnabled = false; dbContext.Configuration.ProxyCreationEnabled = false; var employeeGroups = dbContext.Employees .AsNoTracking() .ToList() .GroupBy(p => p.Age) ; var employee = employeeGroups.First().First(); employee.Should().BeEquivalentTo(expected[0]); } }
PK為加密欄位時，無法直接投影複數導覽屬性
using (var dbContext = new TestDbContext()) { var orders = dbContext.Employees.Select(e => e.Orders).ToList(); }
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Encryption scheme mismatch for columns/variables 'Employee_Id', 'Id'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') and the expression near line '2' expects it to be (encryption_type = 'PLAINTEXT') (or weaker).
為什麼這樣不行??原來轉譯出來的 T-SQL 有 Order By
解法，撈出來再分組
以下這個方法是交集(Inner Join)
[TestMethod] public void 無法直接投影集合_1() { ////無法直接投影集合 //using (var dbContext = new TestDbContext()) //{ // var orders = dbContext.Employees.Select(p => p.Orders).AsNoTracking().ToList(); //} var expected = new[] { new { Name = "小章", Age = 18, Orders = new[] { new {Price = (decimal) 20.00, ProductName = "滑鼠"}, new {Price = (decimal) 18.00, ProductName = "鍵盤"} } } }; using (var dbContext = new TestDbContext()) { dbContext.Configuration.LazyLoadingEnabled = false; dbContext.Configuration.ProxyCreationEnabled = false; var employees = dbContext.Employees .SelectMany(o => o.Orders, (employee, order) => new { employee.Id, employee.Age, employee.Name, Order = new { order.Id, order.Price, order.ProductName } }) .AsNoTracking() .ToList() ; var group = employees.GroupBy(e => new { e.Id, e.Name, e.Age }, e => e.Order, (e, o) => new { e.Id, e.Name, e.Age, Orders = o }) .ToList() ; group.Should() .BeEquivalentTo(expected, option => { option.WithStrictOrdering(); return option; }); } }
自己寫 Outer Join，我已經懶得寫比對了
[TestMethod] public void 無法直接投影集合2() { using (var dbContext = new TestDbContext()) { var employees = (from employee in dbContext.Employees join order in dbContext.Orders on employee.Id equals order.Employee_Id into orders from order in orders.DefaultIfEmpty() select new { employee.Id, employee.Name, employee.Age, Order = order == null ? null : new { order.Id, order.Price, order.ProductName } }).ToList(); var result = new Dictionary<Guid, EmployeeViewModel>(); foreach (var element in employees) { var employee = new EmployeeViewModel { Id = element.Id, Name = element.Name, Age = element.Age.Value }; OrderViewModel order = null; if (element.Order != null) { order = new OrderViewModel { Id = element.Order.Id, ProductName = element.Order.ProductName }; } if (!result.ContainsKey(element.Id)) { result.Add(element.Id, employee); } if (order != null) { result[element.Id].Orders.Add(order); } } } }
無法自訂對應欄位
自訂對應欄位用在加密欄位會噴例外，估計是內部還沒有解密就進行了判斷
using (var dbContext = new TestDbContext()) { var employees = dbContext.Employees .Select(p => new ViewModel.Employee { Id = p.Id, Name = p.Name == "小章" ? "yao" : p.Name, Age = p.Age == null ? 0 : p.Age }) .AsNoTracking() .ToList() ; }
Test method Lab.EF6.AlwaysEncrypt.UnitTest.EF6_Solution.無法使用對應 threw exception:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The data types nvarchar and nvarchar(10) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'yao', column_encryption_key_database_name = 'Lab.EF6.AlwaysEncrypt') are incompatible in the equal to operator
沒有辦法像以前那樣直接把對應結果寫在 Select 區段了，要分兩段寫，必須要解密後再另外投影(Select)
以下是使用匿名型別的方式對應
[TestMethod] public void 無法使用自訂對應_1() { var expected = new[] { new {Name = "小明", Age = 33}, new {Name = "小英", Age = 23}, new {Name = "yao", Age = 18}, new {Name = "小歪", Age = 0} }; using (var dbContext = new TestDbContext()) { var employees = dbContext.Employees .Select(p => new { p.Id, p.Name, p.Age }) .AsNoTracking() .ToList() .Select(p => new { p.Id, Name = p.Name == "小章" ? "yao" : p.Name, Age = p.Age == null ? 0 : p.Age }) ; employees.Should().BeEquivalentTo(expected); } }
具名型別的對應
[Required] [StringLength(10)] public string Name { get { if (this._name == "小章") { this._name = "yao"; } return this._name; } set => this._name = value; }
[TestMethod] public void 無法使用自訂對應_2() { var expected = new[] { new {Name = "小明", Age = 33}, new {Name = "小英", Age = 23}, new {Name = "yao", Age = 18} }; using (var dbContext = new TestDbContext()) { var employees = dbContext.Employees .Select(p => new { p.Id, p.Name, p.Age }) .AsNoTracking() .ToList() .Select(p => new ViewModel.Employee { Id = p.Id, Name = p.Name, Age = p.Age }) ; employees.Should().BeEquivalentTo(expected); } }
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
