lookup encrypted text by clear text without decryption

Question

I've implemented vouchers module in my web project where I use it to generate redeem get etc... vouchers.

since voucher codes are considered sensitive, I've considered to encrypt them using AES-GCM.

but how can I query clear voucher code to encrypted one to check voucher validity in order to redeem later?

I've thought of these two solutions

1. I am thinking to make like a MD5 for the clear code to live in the document, in order to match it with clear code queries?
2. Reversed distribute, where to give the user encrypted voucher codes, and store the clear voucher codes on the DB, when a query is applied to redeem a voucher system decrypt code applied with secret key and iv and looks for a match?

Does those approaches carries less performance overhead than decrypting whole data?, is it safe to consider one of them.

Audience of threats

1. system admins
2. attackers
3. DB data breach

Answer 1

Query with SELECT

• AES with ECB mode can be used as long as the vouchers codes are unique. ECB mode gives you the same results when the key and plaintext are same.
• Another solution is continuing to use AES-GCM to save your vouchers codes. To have the equality on the SELECT statement, use a secure hash function e.g. choose from the SHA3 family. With negligible probability, you will have a collision. If you have a collision store the values, you will be famous. In any case, you will decrypt the AES-GSM after the match of the hash values.

Attacks

As I said in the comment you attack is very broad; I will give answer for single cases;

1. System admins: If you don't trust them, this is a serious issue. They can query everything, even you deploy an HSM. They can insert, delete, etc. Keeping a query log of the Database may help you investigate. Also, there are works as RSA Beehive's that can detect the behavioral change of the users of the system that will can block action. They claimed that "we can prevent the Snowden."

2. Attackers: For the database attack, see the next. If the attacker access the application servers they will be same as a malicious system administrator, probably, a short time.

3. DB data breach: Since the clear vouchers are not in the database, We expect that by the collision-free and the security of AES-GCM the database will be untouched. However, if the database attacker is also an active attacker, you may need some integrity into your rows and even some chaining link for the columns. A simple one as in blow;

r_i  = |data_1|... |data_k| h_i   = hash(data_1,...,data_k)| sign(h)|
r_i+1 =|data_1|....|data_k| h_i+1 = hash(data_1,...,data_k)| sign(h_i+1||h_i|)|