Use mybatis’s typehandler to encrypt and decrypt data

Time:2022-5-25

1、 Background

Some sensitive information of users will be saved in our database sometimes,For example:Mobile phone number, bank card and other information are not safe if they are saved in clear text.If:Hackers hack into the database, or resigned personnel export data, which may lead to the leakage of these sensitive data. Therefore, we need to find a way to solve this problem.

2、 Solution

Because we useMybatisAs a database persistence layer, so I decided to useMybatisofTypeHandlerorPluginTo solve it.

  • TypeHandlerWe need to manually specify typehandler on some columns to choose which typehandler to use or@Mappedjdbctypes and @ mappedtypesAnnotation to infer.

    • <result column="phone" property="phone" typeHandler="com.huan.study.mybatis.typehandler.EncryptTypeHandler"/>
      
  • Plugin: can intercept in the systemselect、insert、update、deleteAnd other statements can also obtain the parameters before and data after SQL execution.

After consideration, we decided to use itTypeHandlerTo encrypt and decrypt data.

3、 Demand

We have a customer listcustomer, with the customer’s mobile phone number inside(phone)And customer address(address)And other fields, including customer mobile phone number(phone)It needs to be encrypted and saved to the database.

1. When adding customer information, the customer’s mobile phone number is automatically encrypted and saved in the data.

2. When querying customer information, automatically decrypt the customer’s mobile phone number.

4、 Realization idea

1. Write an entity class. All the data of this entity class represents the data that needs to be encrypted and decrypted

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. Write an encryption and decryptionTypeHandler

  • When setting parameters, encrypt data.
  • Decrypt the data when getting records from the database.
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.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *Encryption and decryption typehandler
 *
 * @author huan. Fu 2021 / 5 / 18 - 9:20 am
 */
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(Encrypt.class)
public class EncryptTypeHandler extends BaseTypeHandler<Encrypt> {

    private static final byte[] KEYS = "12345678abcdefgh".getBytes(StandardCharsets.UTF_8);

    /**
     *Set parameters
     */
    @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);
    }

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

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

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

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

be careful ⚠️:

  1. @MappedTypes: indicates what type of Java the processor handles.
  2. @MappedJdbcTypes: indicates the type of JDBC processed by the processor.

3. Writing method in SQL statement

<?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 type="com.huan.study.mybatis.entity.Customer">
        <id column="id" property="id"/>
        <result column="phone" property="phone"/>
        <result column="address" property="address"/>
    </resultMap>

    <insert>
        insert into customer(phone,address) values (#{phone},#{address})
    </insert>

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

</mapper>

There is no special way to write in SQL.

4. Specify the package path of typehandler in the configuration file

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

5. Write background code

  1. Provides an add method
  2. Provide a method to query according to the mobile phone number

The background code is relatively simple and can be viewed directlyhttps://gitee.com/huan1993/spring-cloud-parent/tree/master/mybatis/mybatis-typehandler-encrypt

Post a screenshot of the mapper layer.

How to write mapper layer

6. Test results

Use mybatis's typehandler to encrypt and decrypt data

Encryption and decryption results of database fields

It can be seen from the test results that when adding data, the data (phone) to be encrypted has been encrypted in the database, and the encrypted data has been automatically decrypted when querying.

5、 Implementation code

Background code:https://gitee.com/huan1993/spring-cloud-parent/tree/master/mybatis/mybatis-typehandler-encrypt

6、 Reference documents

1、https://mybatis.org/mybatis-3/zh/configuration.html#typeHandlers

2、https://github.com/mybatis/spring-boot-starter