Search filter on encrypted data in MySQL

Question

Query Description: Let say, I have a database table which stores all users' data in encrypted form. I have a functionality in which the Admin can search the user data. Now the problem is, Admin will enter the normal text in the textbox and I have to filter the user list(on every text change) according to the input of Admin. So meanwhile, I have the bunch of data in encrypted form and I have to filter it on the basis of normal text that Admin enters.

What solution I came up till now is, I am decrypting all the data at first and then applying the filter. But I am curious to know, What if I have millions of records in my database then current way seems useless and inefficient.

Can anybody help me to search over encrypted data in the most efficient way?

Any help will be appreciated!

Thanks.

Answer 1

What solution I came up till now is, I am decrypting all the data at first and then applying the filter. But I am curious to know, What if I have millions of records in my database then current way seems useless and inefficient.

You're correct that this is not a scalable solution. If you want a deep dive into this problem, see: Building Searchable Encrypted Databases with PHP and SQL .

What you want to do is:

1. Store encrypted data as-is, using authenticated encryption.
2. Store a blind index of the plaintext alongside the ciphertext.
   • A blind index can be HMAC-SHA256(plaintext, separate_key)
   • Sensitive values should use sodium_crypto_pwhash() or hash_pbkdf2() instead.
   • To save on space, truncate the blind index and treat it as a Bloom filter.

This gives you the best of both worlds: Data encryption that resists both passive and active attacks, but also allows for use in quick SELECT queries.

Update (2019-03-27): Instead of building this yourself, check out CipherSweet which implements this design.