How would one go about doing a case-insensitive search on a column encrypted with Always Encrypted?

The problem arises when you encrypt email addresses for example.
When encrypted, John.Doe@gmail.com != john.doe@gmail.com

Is the best solution truly to duplicate the encrypted column with the ToLower version of the text to search against?

  • 1
    Define "best". At least one alternative I can think of is to hash the ToLower version and store that instead. When the matching rows are returned, further compare them to see if it's really John Doe in all variations we got, or whether Jane came along with. This hash is smaller and reveals less about the column than even a separate encrypted version would, but obviously searching this way is more complicated for clients. Also, you need an expert to tell you if the hash must be cryptographically secure for this purpose. I think you'd be OK with most any hash, but I'm no cryptographer. Sep 19 '18 at 15:00
  • I have come across the hashing approach as well, but the way I understand it, I would have the some problem? It would again just be a case sensitive search. So whether I hash or encrypt the ToLower column, makes no difference, right?
    – Swifty
    Sep 20 '18 at 6:44
  • 1
    Looking up a hash is faster (because it's smaller, and hashing takes less time than encrypting) and it's potentially (slightly) more secure (because a hash reveals less about the plaintext than having two encrypted versions). But there's no structural difference in these approaches, no -- it's still true that the server can only do exact comparisons, so if you want to do inexact comparisons, elements will need to be reduced to equivalence classes somehow. This is a fundamental limitation of making the plaintext unavailable to the server. Sep 20 '18 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.