RE: [PHP] Storing Social Security Number WAS: Encryption/Decryption Question

 

> -----Original Message-----
> From: tedd [mailto:[email protected]] 
> Sent: Thursday, August 12, 2010 8:32 AM
> To: Bastien Koert
> Cc: [email protected]
> Subject: Re: [PHP] Storing Social Security Number WAS: 
> Encryption/Decryption Question
> 
> For searching standard fields, it's a piece of cake to use %LIKE%. 
> For example, let's say the investigator has a piece of paper that has 
> the number "393" on it and want's to search the database for all 
> phone numbers that contain "393" -- he could use %LIKE% and that 
> would produce 517-393-1111, 393-123-4567, 818-122-4393 and so on. 
> That's neat!
> 
> However, if the field is encrypted, then how do you preform a partial 
> search on that? You can't encrypt the search string and use that 
> because you need the entire string. So, how do you solve that problem?
> 
> If you hash the number of store the hash, then you can create a 
> hashed search string and use that. But again it doesn't work for 
> partial %LIKE% searches. For example, I couldn't search for "393" in 
> a SS# -- I would have to search for the complete SS#.
> 
> So, how do you solve the %LIKE% problem with encryption and hashes?

Well, if you can get all the encryption/decryption to take place in SQL,
you can use something like this pseudocode:

SELECT name, 
	 dob, 
	 DECRYPT(ssn) as rawssn
FROM   deadbeats
HAVING rawssn LIKE '%393%';

You can assign an alias and use HAVING instead of WHERE.

http://dev.mysql.com/doc/refman/5.0/en/select.html