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

> -----Original Message----- > From: tedd [mailto:tedd@sperling.com] > Sent: Thursday, August 12, 2010 8:32 AM > To: Bastien Koert > Cc: php-general@lists.php.net > 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