preface

For security reasons, the sensitive information of the database needs to be encrypted and stored in the database, but normal business interaction still needs to use plaintext data, so the query return can only be returned to the caller after corresponding decryption.

Ps: In daily development, we should have a certain sense of security, encryption, storage and protection of passwords, financial data and other sensitive information.

This requirement is not too difficult to say, we just need to encrypt the specified data in advance before executing the SQL. After the SQL is executed, the result returned is obtained and the corresponding decryption is performed. Modify the original code slightly, and quickly complete the requirements.

Existing encryption algorithms such as RSA2 and AES, the ciphertext length is several times that of the plaintext. The on-line encryption and decryption scheme must evaluate whether the existing field length of the database meets the post-encryption length.

If this were a new table, the above implementation would be fine. But this time we are transforming several tables with tens of millions of bytes of data already stored, none of which is encrypted.

If the preceding code is used, the historical data is queried using the encrypted ciphertext information. Of course, no result is obtained. In addition, decrypting the plaintext database may cause decryption errors when the query returns plaintext.

Therefore, in order to be compatible with historical data, the following modifications are needed:

  • Add new fields to store the corresponding encrypted data, and modify SQL equivalent condition query to IN query
  • Check whether the returned record is ciphertext. If yes, decrypt it

The code modification is as follows:

Although the above code solves the business requirements, the solution is not very elegant. The business code changes a lot, and the encryption and decryption codes are not universal. All the methods involving related fields need to be changed, and almost all of them are repetitive codes, which are very intrusive and not very friendly.

Experienced students might think of using Spring AOP to solve the above problems.

Intercepts query parameters uniformly in the front method of the slice, encrypts specified fields with custom annotations.

It then intercepts the return value in the afterReturn method of the slice, along with custom annotations, to decrypt the specified field.

Spring AOP code implementation is more complex, so I won’t post the specific code here.

However, the Spring AOP solution is not universal, and if other applications have the same requirements, the same code, and need to be implemented repeatedly, it can be time-consuming and laborious.

Finally, we refer to a Github open source project “TypeHandlers-ENCRYPT”, with the help of Mybatis TypeHandler, to achieve a general data encryption and decryption solution. Users only need to introduce related dependencies, do not need to change a line of service code, and only need a small amount of configuration to realize the encryption and decryption operation of specified fields, saving time and effort.

Typehandlers -encrypt Github address: github.com/drtrang/typ…

Realize the principle of

Mybatis uses typeHandler (built-in type converter) to convert Java type and JDBC type to each other. We can just use this feature to add encryption and decryption steps before conversion.

TypeHandler the underlying principle is not complicated, if we didn’t use Mybatis, but use the original JDBC directly execute the query, the code is as follows:

We need to manually determine the Java type and then call PreparedStatement to set the appropriate type parameters. After obtaining the returned result, you need to manually call the ResultSet ResultSet to obtain the corresponding type of data, which is a very tedious process.

With MyBatis, the above steps are no longer needed. Mybatis can automatically implement the conversion logic by identifying Java/JDBC types and calling the corresponding typeHandler.

The following figure shows the MyBatis built-in type converter, which basically covers all Java/JDBC data types.

Universal solution

Custom typeHandler

Below we implemented with the function of encryption type converter, implementation is simple, as long as inherit org. Apache. Ibatis. The BaseTypeHandler, rewrite relevant methods.

For the sake of simplicity, the above encryption and decryption only uses Base64, you can replace it with the corresponding encryption and decryption algorithm or introduce the corresponding encryption and decryption service.

The encryption conversion will be performed in setNonNullParameter and the decryption conversion will be performed in getNullableResult.

The CryptTypeHandler uses a MappedTypes annotation that contains a CryptType class that uses the Mybatis alias feature to greatly simplify SQLMap-related configuration.

Registered typeHandler

The user must register typeHandler and Alias with MyBatis otherwise they will not take effect.

The following three methods are available. You can choose one of them according to the project situation:

Use Mybatis alone

This scenario needs to be configured in mybatis-config.xml, which will be loaded when MyBatis starts.

<typeHandlers>
  <! Type converter package path -->
  <package name="com.xx.xx"/>
</typeHandlers>
  <! -- Alias definition -->
<typeAliases>
		<! -- Define a path for a single alias. Type: type alias: alias -->
		<typeAlias type="xx.xx.xx" alias="xx"/>
</typeAliases>
Copy the code

Configure Mybatis beans using Spring

When used with Spring, typeHandler is injected into SqlSessionFactoryBean as follows:

<! -- MyBatis Factory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />

    <! - alias injection -- -- >
    <property name="typeAliasesPackage" value="xx.xx.xx"/>
    <! -- typeHandlers injected -->
    <property name="typeHandlersPackage" value="xx.xx.xx"/>
</bean>
Copy the code

SpringBoot

The SpringBoot mode is the simplest, as long as the introduction of mybatis-starter, configuration file to add the following configuration can be:

Handlers -package=com.xx.xx.x mybatis. Type-aliases -package=com.xx.xxCopy the code

Modify the MAPper SQL configuration

Finally, we can achieve encryption and decryption by simply modifying the resultMap or SQL S configuration in Mapper.

Suppose we encrypt and decrypt an existing bank_card table. The table structure is as follows:

CREATE TABLE bank_card (
id int primary key auto_increment,
gmt_create timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
gmt_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
card_no varchar(256) NOT NULL DEFAULT ' ' COMMENT 'number',
phone varchar(256) NOT NULL DEFAULT ' ' COMMENT 'Mobile phone Number'.name varchar(256) NOT NULL DEFAULT ' ' COMMENT 'name',
id_no varchar(256) NOT NULL DEFAULT ' ' COMMENT 'Certificate No.'
);
Copy the code

Insert the encryption

Card_no, phone, name, id_no, insert

<insert id="insertBankCard" keyProperty="id" useGeneratedKeys="true" parameterType="org.demo.pojo.BankCardDO">
    INSERT INTO bank_card (card_no, phone,name,id_no)
    VALUES
    (#{card_no,javaType=crypt},
    #{phone,typeHandler=org.demo.type.CryptTypeHandler},
    #{name,javaType=crypt},
    #{id_no,javaType=crypt})
</insert>
Copy the code

We just need to specify typeHandler in #{}, and the incoming parameters will eventually be encrypted. Using typeHandler requires using the full path of the class, which is a bit cumbersome. We can use the javaType property and use the crypt alias we defined above.

The database finally executes SQL as follows:

INSERT INTO bank_card (card_no, phone,name,id_no) VALUES ('NjQzMjEyMzEyMzE='.'MTM1Njc4OTEyMzQ='.'5rWL6K+V5Y2h'.'MTIzMTIzMTIzMQ==');
Copy the code

Ps: Mybatis -log-plugin is recommended for IDEA, which can automatically restore mybatis SQL log into real SQL execution

Query encryption and decryption

The following is an example of common query decryption:

<resultMap id="bankCardXml" type="org.demo.pojo.BankCardDO">
        <result property="card_no" column="card_no" typeHandler="org.demo.type.CryptTypeHandler"/>
        <result property="name" column="name" typeHandler="org.demo.type.CryptTypeHandler"/>
        <result property="id_no" column="id_no" typeHandler="org.demo.type.CryptTypeHandler"/>
        <result property="phone" column="phone" typeHandler="org.demo.type.CryptTypeHandler"/>
</resultMap>
<select id="queryById" resultMap="bankCardXml">
        select * from bank_card where id=#{id}
</select>
Copy the code

Here we can only specify typeHandler using the resultMap attribute in the SELECT configuration.

The following is an example of query decryption when plaintext and ciphertext coexist in the database:

<! -- resultMap as above -->
<select id="queryByPhone" resultMap="bankCardXml">
      select * from bank_card where phone in(#{card_no,javaType=crypt},#{card_no})
</select>
Copy the code

Finally, we can package the customized typeHandler separately and publish it. Other business parties only need to reference and modify the relevant configuration files to complete data encryption and decryption.

The code sample above has been uploaded to Github at github.com/9526xu/myba…

conclusion

With the help of the custom typeHandler, we have implemented a general encryption and decryption scheme, which is less intrusive for the user code, out of the box, can quickly complete the transformation of encryption and decryption.

Ps: Do you have the same needs, you can write down your plan in the comments below, learn from each other, grow together!

Finally, I would like to thank **@ Brother ** for providing solutions.

Reference

  1. Github.com/9526xu/myba…
  2. Github.com/drtrang/typ…

Finally (for attention)

See here, everyone must be tired, put an interesting map to relax.

Finally, thank you again for reading. I am a black brother downstairs, a tool ape who is not bald yet. See you in the next article

Welcome to pay attention to my public account: procedures to get daily dry goods push. If you are interested in my topics, you can also follow my blog: studyidea.cn