What's the best way to store and yet still index encrypted customer data?
I'm building an application that needs to store sensitive information, which means the data is encrypted on my database so that a hacker/employee with access to the database cannot decipher the sensitive data. However, it still needs to be searchable (on a certain level).
I understand certain compromises may have to be made. For example, I'm willing to leave some data attributes unencrypted to make them indexable if necessary, but "the main body" must be encrypted.
What are some best practices and approaches for storing sensitive data that needs to be viewable, searchable, and/or sortable by authorized people?
(I was thinking of extracting non stop words from the "body" and putting them in random order in a field before encrypting the body, and then feed that field to a search indexer, I doubt it provides any real security.)
Answer
I'm currently looking for a solution to this same problem.
One of the best ideas I've found, is this article from Raul García, https://docs.microsoft.com/en-us/archive/blogs/raulga/indexing-encrypted-data.
He suggests using a MAC, to create an indexable column. The solution is for MS SQL Server, but it could be applied to another system.