The problem is that IV should be unique for each message/field. But that makes querying impossible or very slow.
If IV is same for all fields, some querying is possible, but that is not how AES is supposed to be used.
How bad is having a constant IV (for all fields) per column?
If you want to use constant IV for deterministic (exact) lookups. Make sure you use AES in SIV mode which is resistant to IV reuse or CBC mode with an HMAC tag. Its slower than GCM unfortunately but one of the only secure options when you want to use deterministic option.
This stuff is hard to get right and can bite you in subtle and unexpected ways.
Is authentication needed for field encryption?
EDIT: I should add that is for fields with unique values. So, constant key & IV (per column), but unique data for each field.
Depends on what kinds of data you're encrypting but if its anything to do with money or health data authenticity is a must.
But how can attacker control what plaintext will become, if he doesn't have a key? Wouldn't he be limited to either a random value or a value from another field?
Since IV is constant. It doesn't need to be stored in DB and can be treated like a key. So, attacker (with an access to DB) can't change IV for a server app reading from the DB.
How do the following methods compare when using constant IV?
AES-CBC + HMAC (encrypt then MAC) vs AES-GCM-SIV vs AES-SIV
(The linked article talks about CTR + HMAC, but CBC + HMAC is also affected.)
An attacker can reveal the keystream, but not the AES key. Still catastrophic.
And AES-SIV is a lot stronger than CBC with deterministic IV, since CBC reveals if two messages start with the same sequence of 16-byte blocks, while SIV only reveals if the messages are identical.
---
There is another interesting option: Create two columns, one using randomized authenticated encryption and one using an HMAC. Then you can use the HMAC column for equality lookups.
One technique for using AES (assuming you're using AES in a secure mode) and still being able to search is to compute a corresponding hash digest based on a small predefined length of the plaintext. Then you can search on that and get back all the rows that start with that. The actual field may still not be what you're looking for but you've narrowed it down to a much smaller subset. Then you just decrypt the field from the subset of rows and return the ones that match. It's definitely inefficient and still has issues but an improvement over a constant IV for all rows.
It seems to me that some type of data might allow constant IV and still be secure.
From what I understand, it is important (in CBC mode) that the combination of key + IV + first block of plaintext is unique.
So, if key and IV are constant, but data is unique it is still secure. For example, social security number is unique and nine digits, which means it fits into 128-bit block. Using constant IV to encrypt SSN should be secure, right?
Email can also be used as unique identifier. But length of an email can exceed 16 bytes, so we don't have a guarantee that first block of plaintext will be unique (as two different emails can have equal first 16 bytes). So, it's not secure to encrypt email address with constant IV.
But if we would use a 16 bytes long hash of an unique email as an IV, there would be very low chance of (IV + first block data) collision. Probably secure enough?
> So, if key and IV are constant, but data is unique it is still secure. For example, social security number is unique and nine digits, which means it fits into 128-bit block. Using constant IV to encrypt SSN should be secure, right?
I think you meant CBC mode? Yes, IV + Key + first block = unique ciphertex. But if you made IV constant, then anyone with the same first block as someone else would result in the same ciphertext first block because all three inputs are now the same.
SSN is unique(ish?) but the range is absolutely small. Do you allow your users to input their own data? If that's the case, then they can create a whole bunch of accounts that enumerates some range of SSNs and look for anyone with the same ciphertext for that field.
Small range of unique values is good argument. But wouldn't hash field (used for lookup) expose the same information?
Is there any good online resource focused on field encryption techniques in databases?
Yes absolutely which is why you pick a small substring as the input to the hash function.
For example, for the SSN field, you can use the first 2 digits as the input. Even if you get a digest match, the fields themselves may not actually be the exact same value. SSN, however, given its small range is kind of hard to secure. You might be able to do something like the email address plus SSN as the input into the digest function.
I don't have any online resources specific to this topic but I did find this book to be very accessible for someone like me who's an engineer:
https://www.amazon.com/gp/product/1593278268/ref=ppx_yo_dt_b...
I find encrypting data in DB challenging, as you often still need to run queries on that data in a performant way.