Implementing Fuzzy Search on Encrypted Sensitive Fields in a Spring Boot Backend

When sensitive fields like real name, phone number, or ID number are stored encrypted in a database, traditional SQL LIKE queries cannot be used for fuzzy search; this article addresses that problem and presents practical solutions.

The scenario involves a personnel management system where fields such as name, phone, and ID need fuzzy matching while the underlying data is encrypted for security.

Four possible solutions are discussed: (1) decrypt all data in memory and filter, (2) maintain a plaintext mapping table, (3) perform decryption in the SQL query, and (4) use a tokenized ciphertext mapping table, with the last method being the recommended balance of security and performance.

The recommended approach creates a separate mapping table that stores encrypted tokenized substrings of the sensitive field together with the primary key of the original record. During a fuzzy query, the search keyword is tokenized, encrypted, and matched against this table to retrieve the relevant record IDs.

Environment configuration includes JDK 1.8, Spring Boot 2.3.9.RELEASE, MyBatis Spring Boot Starter 2.1.4, and the Hutool library for encryption utilities.

Key implementation steps:

1. Create the tokenized ciphertext mapping 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 'person phone token mapping table';

2. In the registration flow, decrypt the plaintext phone, generate fixed‑length substrings, encrypt each substring, concatenate them, and store the result in the mapping table:

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

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

// tokenization and encryption
private String keywords(String word, int len) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < word.length(); i++) {
        int start = i;
        int end = i + len;
        if (end > word.length()) break;
        String sub = word.substring(start, 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 search keyword, querying the mapping table for matching IDs, and then retrieving the full records:

public List
getPersonList(String phoneVal) {
    if (phoneVal != null) {
        return this.personDao.queryByPhoneEncrypt(this.encrypt(phoneVal));
    }
    return this.personDao.queryList(phoneVal);
}
select * from sys_person where id in (
        select person_id from sys_person_phone_encrypt
        where phone_key like concat('%', #{phoneVal}, '%')
    )

The article also references similar implementations used by major Chinese e‑commerce platforms (Taobao, Alibaba, Pinduoduo, JD) and provides Maven dependencies for Spring AOP and Hutool.