Wildcard search for encrypted data in MySQL database?

Question

I am creating a small web application that stores about 10 pieces of information for each person added. Due to data protection, most of this information must be encrypted.

Using the CodeIgniter structure and the CodeIgniter encryption class, I can encode information on the application side before storing it in the database. The CodeIgniter encryption class uses the PHP mcrypt function along with the AES_256 cipher.

The problem is that I have to allow application users to search for information stored using wildcard searches, possibly also through the API later.

Any body is faced with a solution to a similar problem. I read about MySQL AES_ENCRYPT and AES_DECRYPT, but they still require passing the key back and forth in plain text, which I reluctantly do.

Currently, I have come to the conclusion that if I want to continue this route, then fully decrypting the table is my only solution every time a search is performed (which is clearly not good).

Answer 1

Well, you cannot search in decrypted text without decrypting it first, it is true.

However, this does not mean that there is no way around this. For example, you can make an inverted index of your data and hash (sha1, md5, crc32, choose one) the keys used to search. All you have to do is hash the search queries you use, look for them in the index, and retrieve any record that matches, and this will be only a small part of the table, and not just that.

By hashing data (use salt!) You avoid storing data in an insecure way, while you can still search for data because you made an index for it. Decryption is not required until you are sure which documents match.