WHERE子句中的SQL加密列 [英] SQL Encrypted Columns in WHERE Clause

问题描述

我正在寻找使用对称密钥进行SQL列级加密的方法。创建数据库主密钥，证书和对称密钥所需的初始步骤似乎很简单，我已经成功使用对称密钥测试了加密/解密数据。

I am looking to apply SQL column level encryption using symmetric keys. The initial steps needed to create the Database Master Key, Certificates and Symmetric Keys seems straight forward and I have tested encrypting/decrypting data using Symmetric Keys successfully.

但是，一旦数据是加密的，我不知道如何最好地查询它。例如，

However, once the data is encrypted I don't know how best to query it. E.g.

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

肯定会导致全表扫描吗？

would surely result in a full table scan?

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

Another option I thought might work is encrypting the search criteria first e.g.

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

但这不起作用

任何建议将不胜感激。

推荐答案

典型的方法是存储加密值和该值的单向哈希。当您寻找特定值时，您将寻找散列。这样，您可以高效地查询，而不必解密每行以查找您感兴趣的值：

The typical way is to store both the encrypted value and a one-way hash of the value. When you seek a specific value, you would seek the hash. This way you can query efficiently, w/o having to decrypt every row in order to find the value you're interested:

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);

从理论上讲，您可以在蓝月亮中遇到一次哈希冲突，以防偏执在解密的列上添加仔细检查：

In theory, you can have a hash conflict once in a blue moon, to be paranoid-safe you add a double check on the decrypted column:

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

另请参见为加密数据编制索引和 SQL Server 2005：搜索加密的数据。

这篇关于WHERE子句中的SQL加密列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！