Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.glorb.com!wn51feed!worldnet.att.net!attbi_s52.POSTED!not-for-mail From: Steve Jorgensen Newsgroups: comp.databases.oracle.server,alt.php.sql,alt.comp.databases,comp.databases.ms-sqlserver,comp.lang.java.databases Subject: Re: searching for encrypted fields in data columns Message-ID: References: <4042F2DF.6080800@hotmail.com> X-Newsreader: Forte Free Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 38 NNTP-Posting-Host: 24.20.228.102 X-Complaints-To: abuse@comcast.net X-Trace: attbi_s52 1078359171 24.20.228.102 (Thu, 04 Mar 2004 00:12:51 GMT) NNTP-Posting-Date: Thu, 04 Mar 2004 00:12:51 GMT Organization: Comcast Online Date: Thu, 04 Mar 2004 00:12:55 GMT Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:256246 comp.databases.ms-sqlserver:109574 comp.lang.java.databases:61753 On Mon, 01 Mar 2004 00:22:55 -0800, sffan@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.