如何使用Entity Framework在Always Encrypted列上模拟通配符搜索

介绍 (Introduction)

The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title.

这篇文章的标题应该是“如何使用Always Encrypted实现通配符搜索功能，使确定性加密更安全，以及使用SqlBulkInsert加载初始数据” ，但正如您所了解的那样，标题太长了。

A number of years ago I built a web application with “Always Encrypted” as VARBINARY columns, before Microsoft offered this feature out of the SQL Server Box. So in case the database server would be compromised by hackers, no client details could be revealed.

几年前，在Microsoft从SQL Server Box中提供此功能之前，我用“始终加密”作为VARBINARY列构建了一个Web应用程序。 因此，如果数据库服务器受到黑客的威胁，则不会透露任何客户端详细信息。

Just like Microsoft my Data Access Layer, built in .NET did the encryption and decryption, transparent for other code that accessed it. And I had the same challenge how to search on encrypted columns.

就像Microsoft一样，我的内置于.NET的数据访问层也进行了加密和解密，对于访问它的其他代码而言是透明的。 对于如何搜索加密列，我也遇到了同样的挑战。

So with (maybe more than average) interest I was looking forward to know how the Always Encrypted feature of SQL Server 2016 works.

因此，以（也许超过平均水平）的兴趣，我很想知道SQL Server 2016的始终加密功能是如何工作的。

And I noticed that Microsoft implements the searching differently from what I did at the time. What I did different (using randomized encryption and search columns) was my inspiration for this article.

而且我注意到Microsoft实施搜索的方式与当时不同。 我所做的不同（使用随机加密和搜索列）是本文的灵感。

But first a little introduction on the feature itself.

但是首先对功能本身进行一些介绍。

Always Encrypted is a client-side encryption technology in which data is automatically encrypted not only when it is written but also when it is read by an approved application. It’s an end-to-end encryption, therefore SQL server only sees (and stores) the encrypted version of the data.

Always Encrypted （ 始终加密）是一种客户端加密技术，其中，不仅在写入数据时，而且在批准的应用程序读取数据时，都会自动对数据进行加密。 这是一种端到端加密 ，因此SQL Server仅查看（并存储）数据的加密版本。

This means that your client application needs to use an Always Encrypted enabled driver to communicate with the database.

这意味着您的客户端应用程序需要使用启用了始终加密的驱动程序来与数据库进行通信。

At this time, the available Always Encrypted enabled-drivers are:

目前，可用的始终加密启用驱动程序是：

• the .NET Framework Data Provider for SQL Server, which requires installation of .NET Framework version 4.6 on the client computer
  SQL Server的.NET Framework数据提供程序，需要在客户端计算机上安装.NET Framework 4.6版
• the JDBC 6.0 driver
  JDBC 6.0驱动程序
• the Windows ODBC driver
  Windows ODBC驱动程序

For more information and to download the drivers see Always Encrypted client development (on MSDN).

有关更多信息和下载驱动程序，请参阅Always Encrypted客户端开发（在MSDN上） 。

To be honest I hate to replicate MSDN or other sites or blog posts when I do not have to. So as an introduction I kindly refer to:

老实说，我讨厌在不需要时复制MSDN或其他站点或博客文章。 因此，作为介绍，我谨提及：

• Always Encrypted (Database Engine) (MSDN) 始终加密（数据库引擎）（MSDN）
• New Features in SQL Server 2016 – Always encrypted (SQLShack) SQL Server 2016的新功能–始终加密（SQLShack）

如何使用Entity Framework在Always Encrypted列上模拟通配符搜索 (How to mimic a wildcard search on Always Encrypted columns with Entity Framework)

The challenges with searching on a column that is Always Encrypted are twofold:

在始终加密的列上进行搜索面临的挑战是双重的：

• can only search on an exact value, you cannot do a wildcard search. 只能搜索精确值 ，而不能进行通配符搜索。
• you have to do a sacrifice in the area of security: you have to use deterministic encryption, which always produces the same encrypted value for a given input value. When the number of distinct values in a column is low (for instance true/false or a domain value with a limited number of values), you can imagine this can be dangerous: you could guess values by comparing the same encrypted value of other rows. Also brute force to encrypt all possible values so you can compare the encrypted values with the ones in the database is a possible threat. So you do not really want that. 您必须在安全性方面做出牺牲 ：必须使用确定性加密，该加密对于给定的输入值始终会产生相同的加密值。 当一列中不同值的数量较少（例如，真/假或值的数量有限的域值）时，您可以想象这很危险：您可以通过比较其他行的相同加密值来猜测值。 同样，强行加密所有可能的值，以便将加密后的值与数据库中的值进行比较也是一种潜在的威胁。 因此，您并不是真的想要那个。

Coping with these limitations is not ‘easy’. For instance to implement wildcard search functionality you have to to find out how the business users would like to search.

应对这些局限并非易事。 例如，要实现通配符搜索功能，您必须找出业务用户希望如何搜索。

So if you have encrypted credit card numbers in your database, and the business users want to be able to search on the last 4 digits, you have to “do something” for that.

因此，如果您在数据库中已加密了信用卡号，并且业务用户希望能够搜索最后4位数字 ，则必须为此“做些事情” 。

If the business users want to be able to search on expiration month and year of a credit card, but a security requirement is to avoid deterministic encryption because of the limited number of distinct values for those two columns (only 12 month numbers, maybe only 5 to 10 years that are still relevant), the same counts: you have to “do something” for that.

如果业务用户希望能够搜索信用卡的到期月份和年份 ，但是安全要求是避免确定性加密，因为这两个列的不同值的数量有限（只有12个月的数字，也许只有5个月的数字）到10年仍然有意义），同样的道理：您必须为此“做某事” 。

But what is “something”? Okay, to be straight with you, you will need an extra search column to implement each of these search requirements.

但是什么是“东西”？ 好的，为了与您保持直接联系， 您将需要一个额外的搜索列来实现所有这些搜索要求。

Follow the demo and I can show you how this works.

跟随演示，我可以向您展示其工作原理。

为演示准备数据库 (Preparing a database for the demo)

First here are my preparation scripts to set up a demo using a CreditCard table.

首先，这里是我的准备脚本，用于使用CreditCard表设置演示。

If you have read the other resources mentioned above you should be able to understand what these scripts do. Also there is some comment in the scripts.

如果您已经阅读了上面提到的其他资源，那么您应该能够理解这些脚本的作用。 脚本中也有一些注释。

010_create_column_master_key.sql:

010_create_column_master_key.sql：

 
--\
---) Use a separate database [MSSQL_E12_AlwaysEncryptedDemo] for the demo,
---) create this database if it does not exist yet.
--/
IF NOT EXISTS
    (
    SELECT 1 FROM sys.databases WHERE name = 'MSSQL_E12_AlwaysEncryptedDemo'
    )
    EXEC('CREATE DATABASE [MSSQL_E12_AlwaysEncryptedDemo]');
GO
 
USE [MSSQL_E12_AlwaysEncryptedDemo]
GO
 
--\
---) Create a column master key, if it does not exist yet.
---) Do not use this script for your production environment! This is unsafe, as these keys are now public.
---) Generate your keys yourself, e.g. with SQL Server Management Studio.
--/
IF NOT EXISTS ( 
    SELECT 1 FROM sys.column_master_keys
    WHERE name = 'CMK_AlwaysEncrypted'
    )
BEGIN
    CREATE COLUMN MASTER KEY [CMK_AlwaysEncrypted]
    WITH
    (
	    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	    KEY_PATH = N'CurrentUser/My/ECF3CBAA957FEA693002BE905768C39E65CDE7D0'
    )
END
GO
 

020_create_column_encryption_keys.sql:

020_create_column_encryption_keys.sql：

 
USE [MSSQL_E12_AlwaysEncryptedDemo]
GO
 
--\
---) Create a column encryption keys, 
---) for each column that needs to be encrypted a separate key.
---) Do not use this script for your production environment! This is unsafe, as these keys are now public.
---) Generate your keys yourself, e.g. with SQL Server Management Studio.
--/
IF NOT EXISTS ( 
    SELECT 1 FROM sys.column_encryption_keys
    WHERE name = 'CEK_CreditCard_CardNumber'
    )
BEGIN
    CREATE COLUMN ENCRYPTION KEY [CEK_CreditCard_CardNumber]
    WITH VALUES
    (
	    COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted],
	    ALGORITHM = 'RSA_OAEP',
    	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006500630066003300630062006100610039003500370066006500610036003900330030003000320062006500390030003500370036003800630033003900650036003500630064006500370064003000BB08A0C879710C99104A22D20A3E6554260C06C0D1449ECB71780B1D98B98CDD1281576E9A274F46765243182EEB343E96AF31D081E64D5687FE99A7C29F606D8C48CAB5CB8974BD4DEB13160F267C18B809888E6951650313208E4FAB2966B281295A6A64E59EEB4C9D00E9EFF842CFC353D0AB63613248F431A9F781F70CCCD6158D1F7D775ECED7F5532ED51B91D9741B0707BF402E13C6092278F85D6DDB35E052EB93C34654E0C67E62176CFEB9DE4C4E9B1B1A781D811DD5FA062327808E3E9E419290B801A5C63AC5BCC6DD45C5DFEEF8696379824362A90321A6063E4074B0F1533A5CEA4C53A730BD1B43C919DBCB4B8767C470E526CD83B0DE2DFF2FA5DD5923D2D177CB86431DE425384F5533425BCF6E1D72562588380E464CE349611187313D426DEACD81C1B24C7844F82DE5CC9A7C70A45CC97871972B2A44EBCD2DC384517CA