I am working on a small utility that will store journal entries in a mysql database. To ensure that even the database administrator can't read the contents of these entries I need to use some form of encryption. MySQL has some pretty decent AES encryption functions that I plan to use (AES_ENCRYPT() & AES_DECRYPT() ). The "key" for the encryption will be supplied by the user and never stored permanently so this should be pretty secure. The issue I am having is that searching through your journal/note entries is a pretty firm requirement. I am familiar with using full text indexes to make sure searching is speedy and efficient... but with encryption this is not an option.
Does anyone have any tips for me? I am just getting started with this project and want to know if there are some tricks I can do to make sure the server doesn't slow to a crawl when someone tries to search through 1,000 encrypted journal entries.
Currently I am planning on doing a query that would resemble the following. I have to use simple pattern matching since no fulltext functions work on the BLOB column type required by AES.
SELECT id, date, AES_DECRYPT(content, "user key") as decrypted FROM posts WHERE userhash = "testuser" AND decrypted LIKE "%some pattern%"
I'm not even sure if that is possible or valid syntax... that is just how I am assuming it will have to work. I think this query could at least grab just the user's posts from the "posts" table quickly, but if there were many it looks like it would need to inefficiently decrypt them one at a time and look for the search string.
Is there a more elegant solution that could give me the speed and security I'm looking for? If not, is there anything you can think of that I could do to hide this inefficiency so the server responds to large searches in a reasonable amount of time? Is this even a big deal? If I have like 50 concurrent users with 1000 posts each constantly editing and searching would a mid-range little linux box or shared environment be able to handle it without appearing super sluggish?
