The background,

Sometimes, some sensitive information of users will be saved in our database, such as mobile phone number, bank card and other information. If these information is saved in plain text, it is not safe. If a hacker breaks into the database, or a former employee exports the data, it could lead to the leak of sensitive data. So we need to find a way to solve this problem.

Second, solutions

Since Mybatis is used as the database persistence layer in our system, we decide to use Mybatis TypeHandler or Plugin to solve the problem.

  • TypeHandler: We need to manually specify TypeHandler on certain columns to choose which TypeHandler to use or to infer from the @mappedjDBCTypes and @mappedTypes annotations.

    • <result column="phone" property="phone" typeHandler="com.huan.study.mybatis.typehandler.EncryptTypeHandler"/>
Copy the code

  • Plugin: Intercepts select, INSERT, UPDATE, delete statements in the system, and obtains parameters before SQL execution and data after SQL execution.

After consideration, it is decided to use TypeHandler to encrypt and decrypt data.

3, requirements,

We have a customer table customer, which contains fields such as customer phone number and customer address, among which, customer phone number needs to be encrypted and saved in the database.

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

2, in the query of customer information, automatically decrypt the customer phone number.

Fourth, the realization of ideas

1, write an entity class, all this entity class data are expressed to need encryption and decryption

public class Encrypt {
    private String value;

    public Encrypt(a) {}public Encrypt(String value) {
        this.value = value;
    }

    public String getValue(a) {
        return value;
    }

    public void setValue(String value) {
        this.value = value; }}Copy the code

2, write an encryption and decryptionTypeHandler

  • Encrypts data when setting parameters.
  • When retrieving records from the database, the data is decrypted.
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;

/** * Encrypt and decrypt TypeHandler **@authorHuan. 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);

    /**
     * 设置参数
     */
    @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));
    }

    public Encrypt decrypt(String value) {
        if (null == value) {
            return null;
        }
        return newEncrypt(SecureUtil.aes(KEYS).decryptStr(value)); }}Copy the code

Note ⚠ ️ :

  1. @MappedTypes: indicates what Java types the handler handles.
  2. @MappedJdbcTypes: indicates the Jdbc type processed by the processor.

3, SQL statement writing method

<? The 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>Copy the code

There’s no special way to write it in SQL.

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

mybatis.type-handlers-package=com.huan.study.mybatis.typehandler
Copy the code

5. Write background code

  1. Provide an add method
  2. Provides a method to query by phone number

Background code is relatively simple, directly check gitee.com/huan1993/sp… Post a screenshot of the Mapper layer.

6. Test results

According to the test results, when adding data, the data (phone) that needs to be encrypted is already encrypted in the database, and when querying, the encrypted data is automatically decrypted.

Five, implementation code

Background code: gitee.com/huan1993/sp…

6. Reference documents

1, mybatis.org/mybatis-3/z…

2, github.com/mybatis/spr…