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.
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.
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.)