Is there any way to search within an encrypted column in SQL Azure?

Question

Suppose I want to search for the word "book" in the sentence "There is a book on the table." and I need to store the sentence in encrypted form. On a standalone SQL Server, you can use symmetric key encryption where SQL Server holds the key and it can search for phrase matches within encrypted columns and return only the rows that match. But I want to use SQL Azure, which doesn't support symmetric keys. How can I do this? What workarounds can I use to find a phrase within a longer encrypted varchar string?

If I wanted to search for the entire sentence, instead of just a word, I could easily do the encrytion/decryption at the application level in C#. I could encrypt the whole sentence in C#, which might return "1234567890" and ask SQL Server to find a perfect match of the encrypted string. That works for finding the whole value in the column. The problem for me is that I need to search on just a part of the whole string (the word "book"). If I encrypt "book" in C#, suppose the encryption algorithm returns "asdf". If I encrypt the entire sentence, and the algorithm returns "1234567890" then my substring "asdf" won't be found. If I ask SQL to find strings LIKE "asdf" it won't find that in the full-sentence encrypted string, unless I can find an algorithm where substrings are always found within the larger encrypted sentences (where "book" encrypted becomes "3456" within the whole sentence which becomes "1234567890").

I really want to use SQL Azure but I need a way to search for string LIKE matches within an encrypted larger string. I'm even open to using CLR stored procedures if that helps, but I'm not sure that it will. Any suggestions?

Answer 1

The only way you are going to be able to do this is by decrypting the sentence, then searching for the word in the decrypted sentence.

If you are trying to avoid decrypting the sentence, another option would be to create an "Index" table and at the time when the original sentence get encrypted, scan for indexable words and add these words to your Index table, along with some foreign key to the table that holds your sentences. Then when you need to search for a word, just match the word to your Index table.

Answer 2

Slight variation from icemanmind's ideas:

You could use an indexing pattern like Lucene, and build your index in memory from your table's records which would need to be decrypted first: https://code.msdn.microsoft.com/windowsazure/Azure-Library-for-83562538

The other, as it relates to indexing in a separate table, would be to index the hash value of individual words of your sentence, and use the hashes as your index. This has the advantage of keeping your words secure if that's important.