Suppose I have a table with email is stored in one table. I have encrypted it using AES-256 and stored the result in DB Column.

For let's say I have one input email and I want to search whether this email exists in DB or not: Now I see two approaches to it

  • Encrypt the input and search for the encrypted value.
  • Use the concept of Blind Index(using HMAC SHA-256) and store it and then for input also create this HMAC and search it.

Which approach is better and why? For Encrytion we are using Inhouse library which does AES-256 using secret Key/ Salt. Secret key is fetched from AWS KMS(Basically decrypt from AWS KMS)

  • I guess your only input is not-email.
    – kelalaka
    Commented Oct 14, 2020 at 19:44
  • Lets say it is. Then how do we solve this. Commented Oct 14, 2020 at 19:46

3 Answers 3


Perhaps the common two-column approach used for login scenarios may be applicable here.

This is often where users need log in with their email (either because they forgot their username or the site doesn't use them), but their email address is considered confidential data that should not be stored in plaintext.

In this scenario, your "user table" would include two columns:

  • One column containing a peppered hash of the email addresses (such as SHA-256(pepper || email), where "pepper" is just some secret bytes stored on the server or HSM. The term is taken from like how a "pepper" is used to protect passwords)
  • One column containing the actual email addresses, encrypted with a random-IV per-entry (e.g like IV || E(email))

The first column is used to quickly look up user info for login purposes while the second column can be used to retrieve the actual email address whenever needed (e.g., for correspondence purposes).

  • I'm pretty sure that AES is faster than HMAC. While you have the key, why two columns? Just use AES for login purposes, too. Is it really a standard written in some rfc, etc.., or a common approach?
    – kelalaka
    Commented Oct 15, 2020 at 7:13
  • Oh just simply so you don't have to use the less secure ECB or CBC modes with a fixed-IV-for-all-entries to encrypt the email addresses. The two column approach gives you that fast log-in lookup while at the same time also letting you encrypt email addresses in (any of) the more secure random-IV-per-entry modes.
    – ManRow
    Commented Oct 15, 2020 at 8:33
  • No, random IV will prevent you from the first access to the row. It should be deterministic. For the other columns, at least, authenticated encryption is recommended like AES-GCM or better chacha20-poly1305. Depending on the column structure, the authentication can be performed on the row level, too. This is really application specific subject, that is why in the first hand I have asked the OP...
    – kelalaka
    Commented Oct 15, 2020 at 8:35
  • 1
    No -- the random IV is just prepended to the encrypted email address in the second column. The first column just stores hash(email + pepper) for quick lookups to identify which user is trying to log in. You use only the first column for login "identification" -- and that is also its only sole purpose! (Basically, so you can use some more secure random-IV-per-entry mode to actually encrypt the individual email addresses)
    – ManRow
    Commented Oct 15, 2020 at 8:55

Databases currently have native encryption. MySQL calls it Transparent Data Encryption and Oracle calls it the same, Postgres have various options for encryption, IBM DB2 have Native Data Encryption, Redis have data encryption, MongoDB have data at rest and client side field encryption, CouchDB have both encrypted data at rest and field level encryption.

This all means you don't have to create your own way to encrypt data if your database engine already supports that. You will not be more secure writing your own encryption routines.

Reversible encryption is difficult to properly secure because you need to decrypt it, and key management is the most difficult one. If an attacker gets access to it, it's over, no matter how many PBKDF2 rounds you used to generate another key to encrypt every char from the field with a different key, and encrypted the resulting cyphertext.

The attacker can do anything your application can do. If somewhere the application gets a key from Amazon KMS, the attacker can do the same. If the application uses the key to decrypt an index table, so does the attacker. Nothing your application does cannot be done by an attacker.

So either use the database native encryption, or better define your threat model.


E-mail case:

For Encryption, you need to use AES in ECB mode of operation in order to correctly execute the SELECT query. The IV has no use in ECB mode of operation and ECB mode can leak information about the data even the e-mails are unique. If we consider that the data is more than one block, then some part of the data can be matched on the ciphertext - consider that the e-mail that separated exactly at the @ character. The other block-cipher mode of operations uses IV for probabilistic encryption that randomizes the same value into different ciphertext; like CBC and CTR don't allow the SELECT queries.

Using HMAC can be a better solution if the data more than 128-bit size. There is a negligible chance of collision but it will be not important if you use HMAC-SHA256. If you find a collision then you can easily resolve it with other fields, but more importantly, you will be in the Cryptographic news!

The general case:

For both cases, there is a common frequency attack * that can be devastating. With the frequency knowledge of the data, an observer can reveal the ciphertext/HMACed data without needing any key!. Therefore; both solutions can only be secure if the data are unique on the column that they are stored.

For each column is advised to use different keys and those can be constructed from the master key by using HKDF(k,"table_name"+"column_name")

Conclusion: If the data is unique then HMAC-SHA256 is better, if not none.

* The linked answer in the Cryptography.SE gives details about the attack and post some realted articles

  • You do not need to use ECB in order to deterministically encrypt. This can be done with other modes as well.
    – Xander
    Commented Oct 23, 2020 at 2:55
  • @Xander could you properly name a block-cipher mode of operation that can be deterministic that will enable the SELECT query without crippling it like fixed IV/nonce?
    – kelalaka
    Commented Oct 23, 2020 at 7:23
  • CBC or GCM-SIV with a fixed IV.
    – Xander
    Commented Oct 23, 2020 at 13:14
  • 1
    @Xander well those are crippled! CBC will still leak information if the first blocks are equal and can cause the frequency attack. GCM-SIV fixed IV? SIV mode uses all of the messages to remove the nonce-misuse. So, you actually rely only on that. One needs to see the real use case to talk about the lost security case. At first, It might seem good, however, what is the slowdown?
    – kelalaka
    Commented Oct 23, 2020 at 15:57
  • @Xander I did not say that my solution is the solution, I rather compared them and show the problems, indeed, I've commented on another answer to become better and upvoted. Could you write about your experiences so that we can see them?
    – kelalaka
    Commented Oct 23, 2020 at 15:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .