1

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, [email protected] != [email protected]

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

Improve this question
3
  • 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.
    – Jeroen Mostert
    Commented Sep 19, 2018 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
    Commented Sep 20, 2018 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.
    – Jeroen Mostert
    Commented Sep 20, 2018 at 7:10

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.