Wildcard searching of encrypted data in a MySQL database?

[Vincentas]

Hi everybody,

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).

[pritaeas]

If your application/API runs on the same server as the database, passing the key plain text is not really an issue.