SQL Encrypted Columns in WHERE Clause

Question

Hi

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)

but this doesn't work as the encrypted value generated is always different.

Any suggestions would be greatly appreciated.

Answer 1

One option you have is add a new column to the table (or have a WITH SCHEMABINDING view with a calculated column in it, and index that) with a one-way HASH of the search value. It doens't have to be a strong hash - something as simple as CHECKSUM will work. Then you hash the search value in your lookup and filter it by the hash, which is indexed. That way, you can expose something searchable and indexable, without actually exposing the value itself.

However, if there's another way to do this directly, I'd love to know what it is :)

Answer 2

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;

Also see Indexing encrypted data and SQL Server 2005: searching encrypted data.