Can we perform wildcard search on encrypted columns in MySQL and Java

user_808409 0
Viewed 544

I'm working on a web application where customers' sensitive data like name, dob, etc need to encrypt and store in the database.

For this, I'm using crypto encryption in java to encrypt the given text and storing the encrypted value in the database.

The following is the piece of code to encrypt the text

import java.security.Key;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.binary.Hex;

String key = "";// key to encrypt/decrypt    
String customerName = "customer name";

Key aesKey = generateMySQLAESKey(key,"UTF-8");
Cipher cipher = Cipher.getInstance("AES");
cipher.init(Cipher.ENCRYPT_MODE, aesKey);
byte[] encrypted = cipher.doFinal(customerName.getBytes("UTF-8"));
String encrtedName = new String(Hex.encodeHex(encrypted));
// storing this encrtedName as varchar in database

An exact search is done by passing with encrypted value in where clause while querying the data from the database

But how can we perform a wildcard search on encrypted columns?

I'm new to cryptography and encryption methods. Please let me know the best approach for this in Java or Mysql Database

aes cryptography security mysql java
2 Answers

This is more a remark than an answer but as a new member, I can only post answers.

Doing computations on encrypted data is linked to what is called "homomorphic encryption". This is a research topic but currently being implemented in the industry. I found this paper which seems to deal with your problem in a secure way: https://eprint.iacr.org/2020/931.pdf

user_149593550

Rule of thumb is... if you want to index information, you don't encrypt it.

Depending on the eventual size of your database, performance can take a hit. 100 rows is doable for a decrypt, check, handle workflow; but for something that spans 10000000 rows... you're better off finding clever ways to use additional tables which emulate database indexing schemes.

For example, before encrypting the information you are about to store in the db, fire off another query that adds the user's DB PK to tables designed to classify users based on DOB subsets ... like people born in the 90s, 80s, etc... Something similar can be applied for the first letter in their family name... etc.

This way you're technically keeping their information safe, and can save some time performance wise while not having to deal with decryption.

Do keep in mind that depending on how valuable that information is, and how many "clues" you leave lying about, someone can technically figure out means to decrypt your stuff.

So don't do something like the same ID showing up in clearly defined tables named PPL_BORN_IN_THE_90S, PPL_MALES, PPL_NAMED_JOHN, etc.

user_31323510
Related