Where clause - Decrypt column or hash?

Question

Post reply

Where clause - Decrypt column or hash?

triloba

Mr or Mrs. 500

Points: 582
More actions
July 16, 2015 at 11:14 am

#298891

We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:
Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.
If we write the where clause like
Where DecryptByKey(p.SSN) = @Input_SSN_Param
Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.
Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?
Any other suggestions?
Thanks,
Tom

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

dba-wannabe (7/16/2015)
Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?

This.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

dba-wannabe (7/16/2015)
Where DecryptByKey(p.SSN) = @Input_SSN_Param
Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.

Yes and yes.

Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?
Any other suggestions?

Salt your hash. Otherwise fine.

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

triloba Mr or Mrs. 500 Points: 582 More actions · Answer 3

A salted hash made sense as a way to search for ssn(s).

Each row would hold a unique salt, the salted hash of the ssn and the encrypted ssn.

1. The application exec'ing a stored proc to find a single ssn would:

select <columns>

from dbo.People p

Where p.SaltedHashedSSN = hashbytes('sha1', p.Salt + @input_SSN_Param)

so what does that buy me?

The whole purpose is to protect the SSN's.

If someone gains access to the database, the stored salt allows brute force decrypting of the SSN's.

Thanks,

Tom