How to Perform Fuzzy Search on Encrypted Fields in Java Applications

Preface

Have you ever wondered how to fuzzy‑search data that is stored encrypted in a database? Using a plain SQL LIKE clause will not work because the stored values are ciphertext. This article shows how to solve that problem.

Scenario Analysis

Consider a personnel‑management feature where fields such as name, ID number, phone number, address, and registration date are stored encrypted. Users need to perform fuzzy searches on name, phone, and ID number.

Because the encrypted values differ from the plaintext keywords, a query like select * from sys_person where real_name like '%Zhang%' works, but select * from sys_person where phone like '%0537' fails since the phone column contains ciphertext.

Implementation Options

Four approaches are discussed:

1. Decrypt‑then‑search

Load all rows, decrypt the sensitive fields in memory, and filter with the fuzzy keyword. This is simple but can cause memory overflow on large datasets and is not recommended for production.

2. Plaintext mapping table

Create a separate table that stores the decrypted values together with the primary key of the main table. Fuzzy search is performed on this mapping table, and matching primary keys are used to retrieve the original rows. This defeats the purpose of encryption and is therefore discouraged.

3. Decrypt in the database query

Use a database function to decrypt the column on‑the‑fly, e.g.,

select * from sys_person where AES_DECRYPT(phone,'key') like '%0537'

. This is easy to implement but prevents index usage and may suffer from algorithm incompatibility between the application and the database.

4. Tokenized ciphertext mapping table (recommended)

Create a tokenized ciphertext table. When a sensitive field is inserted or updated, split the plaintext into overlapping substrings (e.g., every 4 characters), encrypt each token, and store the concatenated ciphertexts together with the record ID. During a fuzzy search, encrypt the search keyword, perform a LIKE query on the tokenized ciphertext column, retrieve matching IDs, and then fetch the full rows from the main table.

Images illustrate the tokenization before and after encryption.

Environment Setup

JDK 1.8, IntelliJ IDEA 2020.1

Spring Boot 2.3.9.RELEASE

mybatis-spring-boot-starter 2.1.4

Dependency Configuration

The example uses Spring AOP and Hutool for symmetric encryption.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.3</version>
</dependency>

Code Implementation

1. Create the tokenized ciphertext table:

create table if not exists sys_person_phone_encrypt (
    id bigint auto_increment comment 'primary key' primary key,
    person_id int not null comment 'foreign key to person table',
    phone_key varchar(500) not null comment 'encrypted phone token'
) comment 'phone token ciphertext mapping';

2. When inserting a person, encrypt the phone number, generate 4‑character tokens, encrypt each token, concatenate them, and store the result in the mapping table.

public Person register(Person person) {
    this.personDao.insert(person);
    String phone = this.decrypt(person.getPhoneNumber());
    String phoneKeywords = this.phoneKeywords(phone);
    this.personDao.insertPhoneKeywords(person.getId(), phoneKeywords);
    return person;
}

private String phoneKeywords(String phone) {
    return this.keywords(phone, 4);
}

private String keywords(String word, int len) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < word.length(); i++) {
        int end = i + len;
        if (end > word.length()) break;
        String sub = word.substring(i, end);
        sb.append(this.encrypt(sub));
    }
    return sb.toString();
}

public String encrypt(String val) {
    byte[] key = SecureUtil.generateKey(SymmetricAlgorithm.DES.getValue(), "fanfu-csdn".getBytes()).getEncoded();
    SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.DES, key);
    return aes.encryptBase64(val);
}

public String decrypt(String val) {
    byte[] key = SecureUtil.generateKey(SymmetricAlgorithm.DES.getValue(), "fanfu-csdn".getBytes()).getEncoded();
    SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.DES, key);
    return aes.decryptStr(val);
}

3. Perform fuzzy search by encrypting the keyword and querying the tokenized table, then retrieving the matching persons.

public List<Person> getPersonList(String phoneVal) {
    if (phoneVal != null) {
        return this.personDao.queryByPhoneEncrypt(this.encrypt(phoneVal));
    }
    return this.personDao.queryList(phoneVal);
}

<select id="queryByPhoneEncrypt" resultMap="personMap">
    select * from sys_person where id in (
        select person_id from sys_person_phone_encrypt where phone_key like concat('%', #{phoneVal}, '%')
    )
</select>

Diagram images (tokenization before and after encryption, and a sample fuzzy query) are included to illustrate the process.