Best way to encrypt searchable text fields in DB?
Posted: Thu Feb 24, 2011 6:18 pm
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
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