1

Is there a way to encrypt a field in a database and still have useful indexes on it?

For example, in the medical arena you need to encrypt patient information. If I do this on a patient name field, is there a way to still be able to have indexes on the decrypted value?

I'm thinking of using AES_ENCRYPT() on the field, but would really like to know if there is a trick to do the indexing on the decrypted value, not on the field's value (which would be encrypted).

James Oravec
  • 19,579
  • 27
  • 94
  • 160

1 Answers1

0

AES_ENCRYPT() and AES_DECRYPT() are functions. So the question in more general terms is:

Can MySQL do indexing on functions?

As of MySQL 5.6 the answer is no, although you can see this in other sql engines. For example oracle has done it since 8i and MS SQL has done it since 2000.

It looks like this might be possible in Maria DB 5.2 (https://mariadb.com/kb/en/mariadb/virtual-computed-columns/), which is a community version fork of MySQL.

References:

Community
  • 1
  • 1
James Oravec
  • 19,579
  • 27
  • 94
  • 160
  • 1
    If you think that AES functions will make the data more secure, keep in mind that the code is now the critical point of vulnerability, since the 'key' must be accessed in your code. – Rick James May 01 '15 at 03:10