Best way to encrypt searchable text fields in DB?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Best way to encrypt searchable text fields in DB?

Post by mecha_godzilla »

Hi,

The subject says it all really, but I would like some advice please:

The system I'm developing features 'in motion' encryption - the way this works is that the PHP application handles all of the encryption/decryption routines and MySQL just holds the encrypted data in BLOBs.

Before getting to this point I had originally intended to do part of the decryption process in the database itself (using a stored procedure) but had issues with this because the AES implementation in MySQL seems to be slightly different to that of PHP - this might just be my imagination but I did enough testing to conclude that it wasn't working as expected and the data didn't decrypt properly in MySQL if it was encrypted in PHP beforehand. I also came to the conclusion that it would be better to keep the encryption/decryption routines solely within PHP so that the database (if compromised or backups were ever made) didn't compromise the security arrangements.

However, back to the question...the database holds customer records and at the moment all of the data in these records is encrypted with the exception of the customer's first name and surname (because I need these to be searchable). To avoid the system running like treacle I didn't think it was sensible to retrieve 3,000+ records from the DB, decrypt them in PHP and then search through them, so I've left things as they are for the moment.

One idea I had was to convert copies of the first names and surnames to lowercase letters and then hash them (so that the user's search terms are treated in the same way and a search run against these hashes) but this isn't much help if the user wants to search for just part of the name, or spells the name slightly differently.

Does anyone have any suggestions or examples please?

Mecha Godzilla
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Best way to encrypt searchable text fields in DB?

Post by AbraCadaver »

Having never done this, here are some of my thoughts. I would create stored procedures for encrypting/decrypting, but the key would be passed from PHP to the stored procedure. You could use triggers to encrypt INSERTS and UPDATES and use a stored procedure to search and return from SELECTS.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Best way to encrypt searchable text fields in DB?

Post by John Cartwright »

However, back to the question...the database holds customer records and at the moment all of the data in these records is encrypted with the exception of the customer's first name and surname (because I need these to be searchable). To avoid the system running like treacle I didn't think it was sensible to retrieve 3,000+ records from the DB, decrypt them in PHP and then search through them, so I've left things as they are for the moment.
You will be doing the same thing essentially in MySQL regardless. When you are performing an operation on the column, MySQL will perform a full table scan to determine the actual values prior to applying conditions.

Basically searching encrypted fields is going to be very slow, and exponentially so as your table grows. Basically it comes down to either you want to protect the data (encrypt it), or make it search-able, there generally isn't an in-between "optimized" solution.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: Best way to encrypt searchable text fields in DB?

Post by Mordred »

You can build your own Inverted index with salted hashes. You must be aware that searching may still leak information about the encrypted data (apart from the fundamental thing about returning relevant results ;) ) and can be used in addition to a stolen database to reverse the inverted index so to speak.

About MySQL/PHP AES problems, what mode do you use and how large is your key? IIRC, it would work for up to 128 bit keys, and requires some stupid transformation for larger ones (xoring it over until it's all 128 buts?)
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Best way to encrypt searchable text fields in DB?

Post by mecha_godzilla »

Thanks for all the responses - I'll try and answer them all coherently :)

I'm going to try and keep away from using stored procedures for the moment as I want to keep the encryption/decryption routines completely in PHP - having all the secrets keys in the database *and* all the secret data in there as well is probably asking for trouble ;)

However, I take your point about using them. Although there's obviously a risk that someone will hack into the DB itself my main concern is making sure that plaintext doesn't leak out as a result of an SQL injection, so it might be possible to do a separate encryption/decryption routine in the database - something that's quick and relies on a value in the stored procedure maybe.

I think I'd already come to the conclusion John made that there's no halfway-house - either I encrypt the data or I don't - so I need to run some tests to see how slow this might be.

In answer to Mordred's questions, I'm using the 128 bit standard (I didn't use the 256 one because of the need to recompile MySQL). I think the problem might have been to do with padding - looking back at some of the tests I did a couple of months ago I see that I had to use things like trim() to get rid of the padding and also had some code in there to get rid of "White Medium Square" characters that were padding the strings - by which I mean this character:

http://www.fileformat.info/info/unicode ... ertest.htm

The reason why I made the comment about the AES implementations being different was because I initially developed the encryption/decryption using two separate PHP implementations of AES-128 (as I didn't have access to mcrypt on the server I was working on) and they couldn't decode the other's outputs. If I get a moment I'll test these two libraries again and post the results here in case anyone else is trying to do the same thing.

I'll take a look at that link to inverted index structures - my brain isn't working well enough to quite grasp the concept this evening though :mrgreen:

Thanks,

M_G
Post Reply