WHERE 子句中的 SQL 加密列

Question

我希望使用对称密钥应用 SQL 列级加密。创建数据库主密钥、证书和对称密钥所需的初始步骤似乎很简单，我已经成功测试了使用对称密钥加密/解密数据。

然而，一旦数据被加密，我不知道如何最好地查询它。例如

SELECT PlainTextA, PlainTextB, PlainTextC 
WHERE CONVERT(varchar, DECRYPTBYKEY(EncyptedColumn)) = @SearchTerm

肯定会导致全表扫描？

我认为可能有效的另一个选择是首先对搜索条件进行加密，

SELECT PlainTextA, PlainTextB, PlainTextC 
WHERE EncyptedColumn = ENCRYPTBYKEY(KEY_GUID('KeyName'), @SearchTerm)

但这不起作用，因为生成的加密值总是不同的。

任何建议将不胜感激。

Answer 1

典型的方法是存储加密值和该值的单向哈希值。当您寻找特定值时，您会寻找哈希值。通过这种方式，您可以高效地查询，而不必解密每一行来找到您感兴趣的值：

create table Table (
EncryptedColumn varbinary(max),
HashValue binary(20),
PlainA int,
PlainB varchar(256),
PlainC Datetime);

create index ndxTableHash on Table(HashValue);

select PlainA, plainB, PlainC
from table
where HashValue = HashBytes('SHA1', @searchTerm);

理论上，您可能会遇到一次哈希冲突，偏执狂安全您对解密列添加双重检查：

select PlainA, plainB, PlainC
from table
where HashValue = HashBytes('SHA1', @searchTerm)
and DecryptByKey(..., EncryptedColumn) = @searchTerm;

另请参阅 对加密数据建立索引 和 SQL Server 2005 ：搜索加密数据。

Answer 2

您可以选择的一种选择是向表中添加一列（或者使用一个包含计算列的 WITH SCHEMABINDING 视图，并为其建立索引），并使用搜索值的单向哈希值。它不必是强哈希 - 像 CHECKSUM 这样简单的东西就可以工作。然后，您对查找中的搜索值进行散列，并通过已索引的散列对其进行过滤。这样，您就可以公开可搜索和可索引的内容，而无需实际公开值本身。

但是，如果有另一种方法可以直接执行此操作，我很想知道它是什么:)

Answer 3

另一种选择是使用包含解密值列的视图并根据它查找记录。

SELECT PlainTextA, PlainTextB, PlainTextC from TheView 
WHERE DecryptedColumn = @SearchTerm