For security and compliance reasons, companies need to encrypt some fields stored in plaintext in the database to prevent unauthorized access and personal information leakage.

Since the project has stopped iterating and the cost of transformation is too high, we chose the MyBatis plugin to implement database encryption and decryption, to ensure that the specified fields can be encrypted when writing data to the database, and the specified fields can be decrypted when reading data.

2.1 System Architecture

2.2 System flow

3.1 Introduction to MyBatis plugin

3.2 Spring-MyBatis execution process

(1) Spring creates sqlSessionFactory through sqlSessionFactoryBean. When using sqlSessionFactoryBean, we usually specify configLocation and mapperLocations to tell sqlSessionFactoryBean where to read the configuration file and where to read the mapper file.

(2) After getting the location of the configuration file and mapper file, call XmlConfigBuilder.parse() and XmlMapperBuilde respectively

r.parse() creates Configuration and MappedStatement. The Configuration class, as the name suggests, stores all the configurations of MyBatis, and the MappedStatement class stores eachSQLThe encapsulation of the statement, MappedStatement is stored in the Configuration object in the form of a map, and the key is the full path of the corresponding method.

(3) Spring scans all Mapper interfaces through ClassPathMapperScanner and creates BeanDefinition objects for them, but since they are essentially unimplemented interfaces, soSspringWill modify the beanClass property of their BeanDefinition to MapperFactorybean.

(4) MapperFactoryBean also implements the FactoryBean interface. Spring will call the FactoryBean.getObject() method to obtain the bean when creating a bean. Finally, a proxy is created for the mapper interface through the newInstance method of mapperProxyFactory. The proxy is created by JDK, and the final proxy is generated. The object is MapperProxy.

(5) All interfaces that call mapper essentially call the MapperProxy.invoke method, and internally call various methods such as insert/update/delete of sqlSession.

MapperMethod.javapublic Object execute(SqlSession sqlSession, Object[] args) {  Object result;  if (SqlCommandType.INSERT == command.getType()) {    Object param = method.convertArgsToSqlCommandParam(args);    result = rowCountResult(sqlSession.insert(command.getName(), param));  } else if (SqlCommandType.UPDATE == command.getType()) {    Object param = method.convertArgsToSqlCommandParam(args);    result = rowCountResult(sqlSession.update(command.getName(), param));  } else if (SqlCommandType.DELETE == command.getType()) {    Object param = method.convertArgsToSqlCommandParam(args);    result = rowCountResult(sqlSession.delete(command.getName(), param));  } else if (SqlCommandType.SELECT == command.getType()) {    if (method.returnsVoid() && method.hasResultHandler()) {      executeWithResultHandler(sqlSession, args);      result = null;    } else if (method.returnsMany()) {      result = executeForMany(sqlSession, args);    } else if (method.returnsMap()) {      result = executeForMap(sqlSession, args);    } else {      Object param = method.convertArgsToSqlCommandParam(args);      result = sqlSession.selectOne(command.getName(), param);    }  } else if (SqlCommandType.FLUSH == command.getType()) {      result = sqlSession.flushStatements();  } else {    throw new BindingException("Unknown execution method for: " + command.getName());  }  if (result == null && method.getReturnType().isPrimitive() && !method.returnsVoid()) {    throw new BindingException("Mapper method '" + command.getName()        + " attempted to return null from a method with a primitive return type (" + method.getReturnType() + ").");  }  return result;}

(7) Executor will first create StatementHandler, which can be understood as a statement execution.

(8) Then the Executor will obtain the connection. The specific method of obtaining the connection depends on the implementation of the Datasource. The connection can be obtained by means of connection pooling.

(9) Then call the StatementHandler.prepare method, which corresponds to the Connection in the JDBC execution process

.prepareStatement this step.

(10) Executor then calls the parameterize method of StatementHandler to set the parameters, corresponding to

The StatementHandler.setXXX() of the JDBC execution process sets the parameters, and the ParameterHandler method is created internally.

SimpleExecutor.java@Overridepublic <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {  Statement stmt = null;  try {    Configuration configuration = ms.getConfiguration();        StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);        stmt = prepareStatement(handler, ms.getStatementLog());        return handler.<E>query(stmt, resultHandler);  } finally {    closeStatement(stmt);  }}

(11) The returned result is processed by ResultSetHandler, the return value of JDBC is processed, and it is converted into a Java object.

3.3 When to create the MyBatis plugin

3.4 MyBatis plugin can intercept class selection

For the encryption of the input parameters, we need to change the parameter value to the encrypted parameter before the ParameterHandler calls the prepareStatement.setXXX() method to set the parameter, so it seems that intercepting Executor/StatementHandler/ParameterHandler can be done.

But actually? Since we are not encrypting the original field, but adding an encrypted field, what problems will this bring? Please see the dynamic SQL with encrypted fields in the mapper.xml file below:

<select id="selectUserList" resultMap="BaseResultMap" parameterType="com.xxx.internet.demo.entity.UserInfo">        SELECT        *        FROM        `t_user_info`        <where>            <if test="phone != null">                `phone` = #{phone}            </if>            <if test="secret != null">                AND `secret` = #{secret}            </if>            <if test="secretCiper != null">                AND `secret_ciper` = #{secretCiper}            </if>            <if test="name">                AND `name` = #{name}            </if>        </where>        ORDER BY `update_time` DESC    </select>

It can be seen that this statement has a dynamic tag, it must not be directly handed over to JDBC to create prepareStatement, it needs to be parsed into static state firstSQLand this step is done before the Executor calls StatementHandler.parameterize(), and the MappedStatementHandler.getBoundSql(Object parameterObject) function parses the dynamic tags and generates staticSQLstatement, the parameterObject here can be regarded as a Map for the time being, and the key value is the parameter name and parameter value respectively.

Then let’s take a look at the problems with using StatementHandler and ParameterHandler for parameter encryption. When MappedStatementHandler.getBoundSql is executed, the encrypted parameters are not written in parameterObject. When judging the label, it must be no, and the final generated staticSQLIt must not contain encrypted fields. No matter how we deal with parameterObject in StatementHandler and ParameterHandler, the encryption of input parameters cannot be realized.

Therefore, in the encryption of input parameters, we can only choose to intercept the update and query methods of Executor.

What about decryption of the return value? Referring to the flow chart, we can intercept ResultSetHandler and Executor, which is indeed the case. In terms of processing return values, the two are equivalent. The return value of ResultSetHandler.handleResultSet() is directly passed to Executor, and then by Executor is transparently transmitted to SqlSession, so you can choose one of the two.

Referring to the creation process of the parameterObject in the figure above, the processing of the parameterObject by the encryption plug-in is essentially a reverse process. If it is a list, we traverse each value in the list, and if it is a map, we traverse each value in the map.

After obtaining the Object to be processed, traverse each attribute in the Object to determine whether it is in the srcKeys parameter of the @TEncrypt annotation. If so, encrypt it and set it to the Object.

The logic of the decryption plug-in is basically the same as that of the encryption plug-in, and will not be repeated here.

5.1 The paging plugin automatically generates the count statement

The answer is that pagehelper will execute one more count statement. Let’s not talk about the principle of executing an additional count statement, let’s first see what problems can be caused by adding a count statement.

Referring to the previous selectUserList interface, suppose we want to select data whose secret is a certain value, then the final execution after processing by the encryption plugin is roughly the following statement “select * from t_user_info where secret_ciper = ? order by update_time limit ?, ?” .

However, since pagehelper will execute another statement, and since the statement does not have the @TEncrypt annotation, it will not be intercepted by the encryption plugin. The final executed count statement is similar to this: “select count

from t_user_info where secret = ? order by update_time”.

It can be clearly seen that the first statement uses secret_ciper as the query condition, and the count statement uses secret as the query condition, which will cause the final amount of data to be inconsistent with the actual amount of data.

Therefore, we have specially treated the count statement in the code of the encryption plug-in. Since the id of the mappedStatement corresponding to the count statement added by the pagehelper is fixed at the end of “_COUNT”, and this id is the full path of the method in the corresponding mapper, for example Let’s say the id of the original statement is “com.xxx.internet.demo.entity