Menu
I am in the process of building a small web application which will hold around 10 pieces of information for every person inserted. Due to data protection the majority of this information must be encrypted.
Using the CodeIgniter framework and the CodeIgniter encryption class I can encode the information on the application side before storing it in the database. The CodeIgniter encryption class uses PHP's mcrypt function along with the AES_256 cipher.
The problem I have is that I need to allow the users of the application to search the information stored using a wildcard search, Possibly also via an API at a later date.
Any body come across a solution for a similar problem. I've read about the MySQL AES_ENCRYPT and AES_DECRYPT but they still require passing a key back and forth in plain text which I am reluctant to do.
I am currently at the conclusion that if I wish to continue on this route then a full table decryption is my only solution every time a search is made (obviously not good).
563
Decrypt, search, encrypt The most basic approach to searching through encrypted data is to download the data to the client's computer, decrypt it locally, and then search for the desired results in the plaintext data.
For single tablespaces, verify the ENCRYPTION option using INFORMATION_SCHEMA. TABLES and the CREATE OPTIONS settings. A flag field in the INFORMATION_SCHEMA. INNODB_TABLESPACES has bit number 13 set if the tablespace is encrypted.
The MySQL AES_DECRYPT function returns the original string after decrypting an encrypted string. It uses AES(Advanced Encryption Standard) algorithm to perform the decryption. The AES_DECRYPT function returns the decrypted string or NULL if it detects invalid data.
Well, you can't search in decrypted text without decoding it first, that is true.
However, that doesn't mean that there are no ways around this. For example, you could make an inverted index of your data and hash (sha1, md5, crc32, pick one) the keys used for searching. All you have to do then is hash the search terms you're using, look them up in the index and retrieve any record that matches, which will only be a small part of the table instead of the entire thing.
By hashing the data (use a salt!), you avoid storing the data in an unsafe way, while you can still search through the data because you made an index for it. No decryption required until you're actually sure which documents match.
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
