Please let me know if any of my assumptions are incorrect.

Say I had a database, where all of the rows of a database column is encrypted at the application level using a single passphrase, "SECRET", using password based key derivation:

My Database

Both the random salt and IV ensure that encrypting the same data for two different rows will produce different encrypted values. However, if an attacker has read access to my database and is able to brute-force the encryption of a single row, they would then know my "SECRET" passphrase and could decrypt all of the encrypted rows.

Does this mean that the main benefit in using a random salt and IV for each row is that it hides the frequency of values of my encrypted data? For example, the downside to not using a random salt and IV would be that an attacker could look at my database and see "50 rows have value X, 49 rows have value Y, and 1 row has value Z" but would not know what X, Y, and Z actually are unless they could also crack the encryption? Are there more reasons to use a random salt/IV per row?

Say I needed to query my database for the values that are encrypted. Does using a random salt and IV make the query "Give me the rows that have value N" impossible? How can I protect my data with encryption and still be able to query on it, and what kinds of common threats would I then be vulnerable to?

  • If you downvote, I'd appreciate some feedback as to why!
    – Steven
    May 19, 2017 at 19:13
  • No downvote here, but your title talks about row-encrypted data while your text is about column-encrypted data.
    – SDsolar
    May 19, 2017 at 20:54
  • I don't believe the text is talking about column-encrypted data, because each ROW of the column is encrypted separately
    – Steven
    May 22, 2017 at 13:52

2 Answers 2


First off, it isn't just frequency you are protecting. If the attacker cracked value X in your example, he now knows all 50 rows. Let's say the rows are users, and the encrypted value is a password. Attacker has now just popped all 50 users at once. That's more valuable than just knowing 50 entries were the same.

Random salt and IV per row doesn't make your query impossible, per se, but does make it really difficult without doing a lot of other gyrations. Look up the term "homomorphic encryption" which offers a number of ways to perform some searches over encrypted data. In practice, this isn't really scalable, or done in production, but it isn't impossible.


"How much security" to sacrifice for functionality, is really a question only you can answer. What is your threat model? What's the value of the data being protected? Security has to be in service of your business goals, not an end unto itself. It has a cost which must be less than the value it brings. If the attacker gets control of your search function, they get all your data any way.

In practice, if your data can fit in memory, your best bet may be to decrypt the data, search in memory over the plain text, and leave the data encrypted on disk. If you can't, you may have to leak partial information by breaking the stored data up into searchable-sized subchunks.

You don't want to give up the masking nature of the same plain text encrypting to different cipher texts if your protected information is something like passwords, or credit card numbers. Anything an attacker can run a forward encryption on can be used for a known plaintext attack.

Example: Baddie (with read access to your database - but not the secret) signs up for your service as a legit user, and provides "password" as her password. She then pops the database, finds her own record, and now knows all the other users who also picked "password." Very bad.

  • 1
    Thank you for the quick reply! Your example at the end was really helpful for understanding why the same plain text encrypting to different cipher texts is important, IF the attacker has some way of introducing rows. For your first paragraph, if the attacker cracked value X in my example, wouldn't the attacker have ALL 100 rows, and not just the 50 rows that are value X, since they would have had to have cracked the "SECRET" passphrase? And then they would have all of the same information that I use to decrypt arbitrary rows?
    – Steven
    May 19, 2017 at 18:27
  • You are welcome. If they did it by cracking the "SECRET" value, sure, they get all 100. But what if you used different secrets for some rows? I was just limiting my answer to what is strictly known. Given all the same inputs, the same output reveals all identical outputs. Also, assume they didn't crack "SECRET" but just guessed "password" and let you encrypt for them. They now know the other 50 "password" rows but not SECRET or the other 50 Y and Z values. Recall, she is "cracking" (guessing) plaintext of X, not "SECRET" in my example attack.
    – JesseM
    May 19, 2017 at 18:40
  • For this specific question, I am using one single passphrase "SECRET" for all rows. Sorry if that wasn't clear. Another scheme that I could consider would be to use reversible encryption with a random salt/IV AND store the value hashed with an irreversible HMAC function. I could then use the HMAC function to query rows for matches, and then decrypt that row using the key and salt/IV? If some of my fields are low entropy (booleans, integers, enums), the hashes produced by the HMAC function would be easily crackable without salting?
    – Steven
    May 19, 2017 at 18:46
  • Always be wary of rolling your own crypto solutions. ;-) But yeah, without salting, your boolean values HMAC are just as discernible as plain text. It really depends on your business case and threat model. Full disclosure, my employer offers a product with technology which might be of interest: voltage.com/technology/data-encryption/…
    – JesseM
    May 19, 2017 at 19:00

Yes, the main purpose of using a IV/salt is to ensure that the same input to an encryption/hash function does not yield the same output. If you do not use a random IV/salt, the same encrypted/hashed value will show up the same way in your database.

Querying encrypted data is not impossible, just very, very slow. If you need to use the encrypted field in a where, join, or an order by clause, you will have to use a function to read the IV/salt and then decrypt the value on a row by row basis.

You must log in to answer this question.

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