Re: SQL 2005 - Searching Encrypted SSN

Usualy this is done by storing a cryptographic hash (e.g. SHA256) of the
clear text, in a separate column in adition to the encrypted column. The
search is performed on the hash column, not on the encrypted column. The
hash can be used only to find exact matches. The performance penalty is the
cost of computing one hash to be searched for (e.g hash the clear text SSN,
then search the hash value)

You should realize that this schema allows an attacker to validate whether a
known SSN number is in the database or not, since he can compute the hash
and search for it.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx


"Ron Brent" <RonBrent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E9D1C567-4499-4076-A67C-E72E38ABB104@xxxxxxxxxxxxxxxx
Hi,

SQL 2005.
I would like to encrypt the SSN column (the PK).
Currently, I have an application that searches according to SSN (i.e. -
the
user types her SSN for verification, and then her details are retrieved
from
the SQL Server table).
So, the column that is encrypted is the only column that I can use to
search
according to, plus it should be the PK.

Is it possible to do it with the SQL 2005 encryption?
How do I solve the performance issue of searching (select *...) on an
encrypted column?

Many thanks,

Ron


.

Relevant Pages

  • Re: SQL 2005 - Searching Encrypted SSN
    ... search is performed on the hash column, ... cost of computing one hash to be searched for (e.g hash the clear text SSN, ... SQL Service Broker ... Is it possible to do it with the SQL 2005 encryption? ...
    (microsoft.public.sqlserver.security)
  • Re: phpBB 2.06 search.php SQL injection
    ... Greetings BugTraq, ... However, due to the fact that it uses an array function to pull all the relative information and the hash returns a single value without the seperators, it won't acknowledge that a result was returned. ... From what I've tried so far, this doesn't really seem to be a critical vulnerability -- just an SQL injection that would allow you to get maybe the prefix of the forum tables or other insignifcant information. ... >A vulnerability exists in phpBB 2.06 that could allow an attacker to manipulate SQL ...
    (Bugtraq)
  • Re: MD5 Hash with single quote = grief in dao.findfirst
    ... I set the Hash Field size to Text 20. ... I do not get a SQL error. ... >> I have a small function to replace a single quote with two single quotes ... >> I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)
  • Re: query enhancement
    ... Hash: SHA1 ... SQL> create table bla not null, id number not null, ... Let's try the FBI thing (provided ...
    (comp.databases.oracle.server)
  • Re: Creating a list of HASHes
    ... Sinan Unur have provided interesting and useful information, ... I felt that creating a hash that included all the test ... results for each machine would be the simplest since I can use SQL ...
    (comp.lang.perl.misc)