Secure Your MySQL Data with MyBatis TypeHandler: Automatic Encryption & Decryption

Background

Sometimes sensitive user data such as phone numbers or bank cards are stored in plain text in the database, which is insecure. If a hacker or a former employee obtains the data, it may be leaked.

Solution

Because the project uses MyBatis as the persistence layer, we can use MyBatis TypeHandler or Plugin to encrypt and decrypt data.

TypeHandler : manually specify a TypeHandler for a column, or let MyBatis infer it via @MappedJdbcTypes and @MappedTypes annotations.

Plugin : can intercept select, insert, update, delete statements and obtain parameters before execution and results after execution.

We choose to use a TypeHandler for encryption/decryption.

Requirements

We have a customer table with fields phone and address. The phone field must be stored encrypted.

When adding a customer, automatically encrypt the phone number before saving.

When querying a customer, automatically decrypt the phone number.

Implementation Idea

1. Define an entity class for encrypted data

public class Encrypt {
    private String value;
    public Encrypt() {}
    public Encrypt(String value) { this.value = value; }
    public String getValue() { return value; }
    public void setValue(String value) { this.value = value; }
}

2. Implement a TypeHandler

Encrypt the value when setting a parameter.

Decrypt the value when retrieving a result.

package com.huan.study.mybatis.typehandler;

import cn.hutool.crypto.SecureUtil;
import cn.hutool.crypto.symmetric.AES;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.nio.charset.StandardCharsets;
import java.sql.*;

@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(Encrypt.class)
public class EncryptTypeHandler extends BaseTypeHandler<Encrypt> {
    private static final byte[] KEYS = "12345678abcdefgh".getBytes(StandardCharsets.UTF_8);

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Encrypt parameter, JdbcType jdbcType) throws SQLException {
        if (parameter == null || parameter.getValue() == null) {
            ps.setString(i, null);
            return;
        }
        AES aes = SecureUtil.aes(KEYS);
        String encrypt = aes.encryptHex(parameter.getValue());
        ps.setString(i, encrypt);
    }

    @Override
    public Encrypt getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return decrypt(rs.getString(columnName));
    }

    @Override
    public Encrypt getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return decrypt(rs.getString(columnIndex));
    }

    @Override
    public Encrypt getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return decrypt(cs.getString(columnIndex));
    }

    private Encrypt decrypt(String value) {
        if (value == null) return null;
        return new Encrypt(SecureUtil.aes(KEYS).decryptStr(value));
    }
}

3. Mapper XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.huan.study.mybatis.mappers.CustomerMapper">
    <resultMap id="BaseResultMapper" type="com.huan.study.mybatis.entity.Customer">
        <id column="id" property="id"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
    </resultMap>

    <insert id="addCustomer">
        insert into customer (phone,address) values (#{phone},#{address})
    </insert>

    <select id="findCustomer" resultMap="BaseResultMapper">
        select * from customer where phone = #{phone}
    </select>
</mapper>

4. Configure MyBatis to scan the TypeHandler package

mybatis.type-handlers-package=com.huan.study.mybatis.typehandler

5. Service layer

Provide methods to add a customer and to query a customer by phone number. The MyBatis layer will automatically handle encryption and decryption.

6. Test Result

After inserting, the phone column in the database contains encrypted data; after querying, the value is automatically decrypted.