Encrypted Fields & Full Text Search, Best Approach?

Question

I've got some fields that store notes and sensitive information that I'd like to encrypt before it makes its way into the database.

Right now, I use a SQL Full-Text Search to search these fields. Obviously encrypting this data is going to throw off my search results.

What's the best way to encrypt these fields, but still allow searching?

Answer 1

There is NO database supporting encrypted index so you have to sacrifice some security to achieve this.

You can index partial data in clear and find the real data from your application. For example, if you want store credit-card number. You can have an index of last 4-digit. The number of cards sharing the same last 4 digit are limited so you can afford to decrypt each one and check the whole number.

Answer 2

It's not going to be easy. What you're describing is rarely implemented in commercial databases, although there are some theoretical results in the field. I'd suggest that you go to google scholar and start looking for papers on the subject.

Here are a few references to get you started:

• Dawn Xiaodong Song, David Wagner, and Adrian Perrig. Practical techniques for searches on encrypted data.
• R. Brinkman, L. Feng, J. Doumen, P.H. Hartel, and W. Jonke. Efficient Tree Search in Encrypted Data. In Security In Information Systems, pages 126-135, 2004.
• D Boneh, G Di Crescenzo, R Ostrovsky, G Persiano. Public Key Encryption with keyword Search
• P Golle, J Staddon, B Waters. Secure Conjunctive Keyword Search over Encrypted Data.

Answer 3

Another option is to store the soundex of the encrypted data. You can then search on the soundex value and get close without decrypting the data.

Answer 4

Oracle's 10g Release 2 (or later) may support this functionality. From their website here:

http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html

"A new feature in Oracle Database 10g Release 2 lets you do just that: You can declare a column as encrypted without writing a single line of code. When users insert the data, the database transparently encrypts it and stores it in the column. Similarly, when users select the column, the database automatically decrypts it. Since all this is done transparently without any change to the application code, the feature has an appropriate name: Transparent Data Encryption (TDE)."

The idea is that no one can see the clear text in the database, but a select statement would work as normal. This might help with your searching if Oracle is an option?

Update: there is another option here:

http://www.critotech.com/index.htm

for MySQL databases, but it seems quite expensive.