1. Introduction
In business scenarios, we often encounter information such as user mobile phone numbers, ID numbers, bank card numbers, email addresses, passwords, etc., which are sensitive information and need to be stored in the database. Many companies will require encrypted storage of such data in the database.
There are two issues that need to be addressed when desensitizing sensitive data:
- Query operation requires encrypting the query keywords and decrypting the data found from the database.
- Insertion and update operations require the inserted or updated data to be encrypted and then saved to the database.
2. Solution ideas
Use the TypeHandler provided by the mybatis framework to process data in the persistence layer.
Typehandleris an interface provided bymybatis. By implementing this interface, the conversion of jdbc type data and java type data can be realized. We often see varchar to string and bigint to conversion. Long, etc. are handled by mybatis itself by implementing this interface.
Therefore, you can implement a Typehandler yourself to meet your own data processing needs.
Advantages: It is simple to implement and easy to use. The entire use process only requires modification of the xml code
3. Implementation
1. Encryption and decryption method: The encryption and decryption method here directly uses des encryption of hutool.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- hutool-all includes encryption tools -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.15</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
2. Write a custom TypeHandler, inherited from BaseTypeHandler
package com.zsx.common;
import cn.hutool.crypto.symmetric.SymmetricAlgorithm;
import cn.hutool.crypto.symmetric.SymmetricCrypto;
import lombok.extern.slf4j.Slf4j;
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.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Description typeHandler encryption and decryption processor Encrypts or decrypts String type fields
* @author zhousx
* @Data 2023-10-15 13:02
*/
@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(String.class)
public class CryptoTypeHandler extends BaseTypeHandler<String> {<!-- -->
private final byte[] key = {<!-- -->-26, -70, -29, -99, 73, -82, 91, -50, 79, -77, 59, 104, 2, -36 , 50, -22, -39, -15, -57, -89, 81, -99, 42, -89};
private final SymmetricCrypto des = new SymmetricCrypto(SymmetricAlgorithm.DESede, key);
/*
* Processed into ginseng
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {<!-- -->
if (parameter != null) {<!-- -->
//encryption
String encryptHex = des.encryptHex(parameter);
log.info("{} ---encrypted as ---> {}", parameter, encryptHex);
ps.setString(i, encryptHex);
}
}
/*
* Get the return result based on the column name, and the return value can be processed in this method
*/
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {<!-- -->
String originRes = rs.getString(columnName);
if (originRes != null) {<!-- -->
String res = des.decryptStr(originRes);
log.info("{} ---decrypted as ---> {}", originRes, res);
return res;
}
log.info("The result is empty, no need to decrypt");
return null;
}
/*
* Obtain the return result based on the column subscript, and the return value can be processed in this method
*/
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {<!-- -->
String originRes = rs.getString(columnIndex);
if (originRes != null) {<!-- -->
String res = des.decryptStr(originRes);
log.info("{} ---decrypted as ---> {}", originRes, res);
return res;
}
log.info("The result is empty, no need to decrypt");
return null;
}
/*
* Obtain the return result (stored procedure) based on the column subscript, and the return value can be processed in this method
*/
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {<!-- -->
String originRes = cs.getString(columnIndex);
if (originRes != null) {<!-- -->
String res = des.decryptStr(originRes);
log.info("{} ---decrypted as ---> {}", originRes, res);
return res;
}
log.info("The result is empty, no need to decrypt");
return null;
}
}
3. Register custom typeHandler to mybatis
application.yml
server:
port: 8082
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT+8 & characterEncoding=utf8 & useSSL=true
username: root
password: 123456
mybatis:
# The written TypeHandler needs to be registered in mybatis
type-handlers-package: com.zsx.cryptotypehandler.common
mapper-locations: classpath:mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4. Use
Entity class User.java
package com.zsx.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {<!-- -->
private int id;
private String name;
private String phone;
}
dao layer IUserDao.java
package com.zsx.dao;
import com.zsx.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* userMapper
*/
@Mapper
public interface IUserDao {<!-- -->
int insertEncrypt(User user);
List<User> findByName(User user);
List<User> findByPhone(User user);
List<User> findByPhone2(String phone);
}
xml file UserMapper.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.zsx.dao.IUserDao">
<!-- General query mapping results -->
<resultMap id="BaseResultMap" type="com.zsx.entity.User">
<id column="id" property="id" />
<result column="name" property="name"/>
<!-- Fields that need to be decrypted in the result set, plus typeHandler -->
<result column="phone" property="phone" typeHandler="com.zsx.common.CryptoTypeHandler" />
</resultMap>
<!-- The fields that need to be encrypted in the sql parameters are added with typeHandler -->
<insert id="insertEncrypt">
insert into user (id, name, phone)
values (#{id}, #{name}, #{phone,typeHandler=com.zsx.common.CryptoTypeHandler})
</insert>
<select id="findByName" resultMap="BaseResultMap">
select id, name, phone
from user
where name = #{name}
</select>
<!-- The fields that need to be encrypted in the sql parameters are added with typeHandler -->
<select id="findByPhone" resultMap="BaseResultMap">
select id, name, phone
from user
where phone = #{phone,typeHandler=com.zsx.common.CryptoTypeHandler}
</select>
<!-- The fields that need to be encrypted in the sql parameters are added with typeHandler -->
<select id="findByPhone2" resultMap="BaseResultMap">
select id, name, phone
from user
where phone = #{phone,typeHandler=com.zsx.common.CryptoTypeHandler}
</select>
</mapper>
The final test code EncryptTypeHandlerTest.java
package com.zsx;
import com.zsx.dao.IUserDao;
import com.zsx.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class EncryptTypeHandlerTest {<!-- -->
@Autowired
private IUserDao userDao;
@Test
public void testInsert(){<!-- -->
User user = new User();
user.setPhone("11112222333");
user.setName("zsx");
int result = userDao.insertEncrypt(user);
System.out.println(result);
}
@Test
public void testSelectByName(){<!-- -->
User user = new User();
user.setName("zsx");
List<User> userList = userDao.findByName(user);
System.out.println(userList.toString());
}
@Test
public void testSelectByPhone(){<!-- -->
User user = new User();
user.setPhone("11112222333");
List<User> userList = userDao.findByPhone(user);
System.out.println(userList.toString());
}
@Test
public void testSelectByPhone2(){<!-- -->
List<User> userList = userDao.findByPhone2("11112222333");
System.out.println(userList.toString());
}
}
The project structure is as follows
Note:
The results of sql query must be mapped using resultMap, otherwise the automatic decryption of the result field cannot be completed