We have a request to encrypt customer personal data (e-mail, address etc.) We use MySQL which does not have any TDE like MS SQL or Oracle. So along with encrypting data we need to preserve functionality for querying this data directly (not LIKE). So something like select * from person where email@example.com'.
The idea here is to use hashing and to make sure encryption is not made redundant by poor hashing function. So if we use bcrypt which has random salt built-in it should be fine. The problem is that with random salt we can't construct the same hash again to be able to run sql queries. If I use bcrypt('firstname.lastname@example.org') and it will return different hash value I can't run select * from person where hash_email=bcrypt('email@example.com'). I can get the same hash value only if I use the same salt (and work factor). But having application-wide salt does not seem to be a great solution. So what can be done about this?
If having one salt value per application is not smart could it be a kind of improvement if we generate, say, 1000 random salt values and store them in the database? If we need to hash email we can do the following:
- get some fast numeric hashing function and calculate, say, m=num_hash(email) mod 1000
- go to salt table take salt where id=m
- hash email with this salt email_hash=bcrypt(salt,email) and store in the database
For searching we can apply the same routine, obtain email_hash and run query. I guess num_hash(email) mod 1000 does not tell a great deal about the e-mail itself. Having 1000 random salts is better than having just one.
Any suggestions would be welcome