Re: searching for encrypted fields in data columns

On Mon, 01 Mar 2004 00:22:55 -0800, sffan_at_hotmail.com wrote:

>I am new to database programming and was curious how others solve the
>problem of storing encrypted in data in db table columns and then
>subsequently searching for these records.
>The particular problem that I am facing is in dealing with (privacy)
>critical information like credit-card #s and SSNs or business critical
>information like sales opportunity size or revenue in the database. The
>requirement is that this data be stored encrypted (and not in the
>clear). Just limiting access to tables with this data isn't sufficient.
>Does any database provide native facilities to store specific columns as
>encrypted data ? The other option I have is to use something like RC4 to
>encrypt the data before storing them in the database.
>
>However, the subsequent problem is how do I search/sort on these columns
> ? Its not a big deal if I have a few hundred records; I could
>potentially retrieve all the records, decrypt the specific fields and
>then do in process searches/sorts. But what happens when I have (say) a
>million records - I really don't want to suck in all that data and work
>on it but instead use the native db search/sort capabilities.
>
>Any suggestions and past experiences would be greatly appreciated.
>
>much thanks,
>~s

If you use PKI-style encryption, you can make the encryption key public, and the decryption key private. This way, you can encrypt a card number, then do a search for a match on that value. Since the card number will encrypt identically each time, the search will match.

Of course, a fundamental problem with such a system is that, given the limited number of valid card numbers relative to the number your database is likely to contain, a constructing s brute force attack on such a system would not be hard. This would be true of any system that allows searching for a card number, no matter how it actually worked. This situation could be improved if other key information was also encrypted so that even if a match were found, the other necessary card owner information would remain unknown. Received on Wed Mar 03 2004 - 18:12:55 CST