Is it possible to SELECT an encrypted column like this ?

Question

Post reply

Is it possible to SELECT an encrypted column like this ?

onlo

SSCertifiable

Points: 5080
More actions
June 17, 2008 at 10:19 pm

#123418

Although I know I can select a record like this :
OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert
SELECT AccountID, CandidateID from dbo.Candidate
WHERE CONVERT(VARCHAR, DecryptByKey(eEmail)) = 'bacteric21@yahoo.com.sg'
In order to have better performance, can I select a record with encrypted column like this :
SELECT AccountID, CandidateID, Email from dbo.Candidate
WHERE eEmail = EncryptByKey(KEY_GUID('MySymKey'), 'bacteric21@yahoo.com.sg')
The eEmail column is VARBINARY(300), encrypted column, but no record found.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Dont think you can do this. Each time you encrypt the same value you will get different encrypted values.

"Keep Trying"

onlo SSCertifiable Points: 5080 More actions · Answer 2

"Dont think you can do this. Each time you encrypt the same value you will get different encrypted values."

Yes, in fact, I found this fact just after I sent this email.

So I agreed what you said.

Thanks.

onlo SSCertifiable Points: 5080 More actions · Answer 3

But is my SQL statement a proper way to select an encrypted record :

SELECT AccountID, CandidateID from dbo.Candidate

WHERE CONVERT(VARCHAR, DecryptByKey(eEmail)) = 'bacteric21@yahoo.com.sg'

Although I could select a record successfully, are there other way to select an encrypted record faster ?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

That is the proper way to retrieve encrypted data. I do not know of any other way for this.

"Keep Trying"

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

The main problem here is that you can never benefit from index seeks with encrypted columns. Thus performance is going to be suboptimal. To get around this you can create another column and store an SHA1 hash of the unencrypted value. This is a fixed length value that is repeatably recoverable (i.e. you get the same hash value every time unlike the cert encryption which is salted). Thus index seeks are again possible and retrieval is straight forward.

This mechanism does opens up the possibility of a dictionary attack to retrieve the value. This is very unlikely to occur and takes a LOT of cpu time to crack, but if you are concerned about it you can us a MAC (message authentication code) to work around even that. Search the web to find examples and discussion.

I just addressed a performance problem with a client with the hash work-around.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

onlo SSCertifiable Points: 5080 More actions · Answer 6

Thanks for your reply, but could you give examples of what you said : SHA1 hash of the unencrypted value ? What is that ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

TheSQLGuru (6/19/2008)
This mechanism does opens up the possibility of a dictionary attack to retrieve the value.

Suggestion I've seen to avoid that is to have a salt in a table somewhere (encrypted naturally), and to salt the hash when first storing it and when comparing. It means you're not vulnerable to rainbow tables and a brute force hash and compare is that much more difficult.

Onlo:Look up hashbytes in books online. should get you started.

The important thing about hashbytes is that it is one way and deterministic. Hashing the same value twice produces the same value, however the hash cannot be 'unhashed'. Cintrast with the encryption routines that are nondeterministic and reversable.

So, you can say WHERE HashedColumn = HASHBYTES('SHA1', @ToSearchFor)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

onlo (6/20/2008)
Thanks for your reply, but could you give examples of what you said : SHA1 hash of the unencrypted value ? What is that ?

From my earlier post: "Search the web to find examples and discussion." My search words of - sql server 2005 sha1 hash index seek encryption - brought back several relevant posts. In this day and age those who can search the web effectively are WAY ahead of those that cannot in terms of their ability to acquire knowledge and solve problems. Cultivate that skill!! 😉

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service