Data encryption and decryption in Mybatis

There are various ways to realize data encryption and decryption. In myBatis environment, data encryption and decryption becomes very simple and easy to use. This paper aims to provide reference.

1, requirements,

** Original requirement: ** Data is encrypted when saved and decrypted when taken out to avoid disclosure of sensitive information when dragged into the library.

** Initial analysis: ** Data comes from the front end, arrives at the back end, and is stored in the database after business logic, which goes through three major links:

1. Whether to encrypt the transmission between the front end and the back end. If encryption is needed, the front end transmission needs to be encrypted, and HTTPS can be used to replace it temporarily. 2, to the back end, at this time the data usually need to pass some logical judgment, so encryption is meaningless, but will bring unnecessary trouble; 3, this is the last step, the data through insert SQL or UPDATE statement into the database, before the need to encrypt;Copy the code

** Core requirements: ** Data encryption is completed in the last step before warehousing. The purpose is to ensure data security to a certain extent if the database is exposed, and also to prevent data leakage by people with data operation permissions.

** encryption algorithm: ** symmetric and asymmetric algorithms are available, considering the efficiency of encryption and decryption and the scene, consider the symmetric algorithm AES encryption.

**ORM environment: ** Mybatis

** Encryption fields: ** encryption fields are uncertain, sensitive fields should be determined at the time of database table design, i.e. encryption fields can be customized.

Details to note:

1. After a field is encrypted, its access performance deteriorates. The more encrypted fields there are, the more performance deteriorates. 2. After the field is encrypted, the index of the field does not have much significance. For example, if the mobile number field is encrypted, it may be originally designed as a unique index to prevent number duplication. 3, some SQL comparison can not be directly realized, such as mobile phone number matching query, in the development and operation and maintenance, it is necessary to consider the operability of sensitive fields in the follow-up work; 4. The length of the original field needs to be expanded. The ciphertext must be longer than the original text. 5. Don't encrypt primary keys (really, some people do). 6. Sometimes, in order to reduce the associated query, we will make redundant fields on the table, for example, put the name field into the business table. If the name field is encrypted, it needs to encrypt the redundant table synchronously.Copy the code

Finally: Data encryption is used to improve security at the expense of overall program performance and ease of use.

2. Solutions

There are at least two automatic encryption methods in mybatis dependency environment:

1. Use interceptors to intercept INSERT and UPDATE statements, obtain the fields to be encrypted, and store them in the database after encryption. Query is intercepted when read and result object is saved after decryption. 2. Use TypeHandler, a type converter.Copy the code

3. Use interceptors

3.1 Defining an Encryption Interface

Because myBatis interceptor will intercept all the requests that meet the signature, in order to improve efficiency, it is very important to define a tag interface, since there is an interface, it is better to add the field information that needs to be encrypted in the interface, of course, it can not be added, according to the actual scene design.

/ * * *@author: xu.dm
 * @since: 2022/3/8 16:30 * This interface is used to flag that the entity class needs to be encrypted, and the specific encrypted content field is returned through getEncryptFields@EncryptAnnotations can be used together or mutually exclusive, depending on your requirements. * * /
public interface Encrypted {
    /** * Return an array of field names to be encrypted, which must be exactly the same as the fields in the class, case sensitive *@returnReturns the field */ that needs to be encrypted
    default String[] getEncryptFields() {
        return new String[0]; }}Copy the code

3.2 Defining encryption annotations

Mainly for some scenarios, directly mark the field of the entity class, which intuitively shows that the field is an encrypted field. Some business logic can also rely on this mark to do further operations. In a word, according to the scene to adapt and design.

/ * * *@author : xu.dm
 * @since: 2022/3/8 * Notes to identify encryption,value is not currently available, we can consider the encryption method and algorithm as required * Note: getEncryptFields and Encrypted interface@EncryptAnnotations can be used together or mutually exclusive, depending on your requirements. * /
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Encrypt {
    String value(a) default "";
}
Copy the code

3.3 Interceptor encrypts data

Initial interceptor definition is relatively single, using the reflection traversal encrypted field, the field character of encryption, namely to be encrypted field had better be to string type, and no reflex traverse to the parent class, if you have inherited, and the parent class also have need encryption field, need to adjust the code according to the scene, the parent class recursion, until the parent class. In the current design, only one Encrypted interface or @encrypt takes effect, and the interface takes precedence.


/ * * *@author: xu.dm
 * @since: 2022/3/8 * Intercept all insert and update operations of the entity class implementing the Encrypted interface * If the getEncryptFields of the interface returns an array length greater than 0, use this parameter for encryption, * otherwise check the band in the entity class@Encrypt* note: The field to be encrypted should be a string. Encryption calls the result of ToString(), which identifies the object. ** */
@Component
@Slf4j
@Intercepts({ @Signature(method = "update", type = Executor.class, args = {MappedStatement.class, Object.class}) })
public class EncryptionInterceptor implements Interceptor {

    public EncryptionInterceptor(a) {}@Override
    public Object intercept(Invocation invocation) throws Throwable {

        Object[] args = invocation.getArgs();
        SqlCommandType sqlCommandType = null;

        for (Object object : args) {
            // Get the operation type from the MappedStatement parameter
            if (object instanceof MappedStatement) {
                MappedStatement ms = (MappedStatement) object;
                sqlCommandType = ms.getSqlCommandType();
                log.debug("Encryption Interceptor Operation type: {}", sqlCommandType);
                continue;
            }
            log.debug("Encryption Interceptor operation parameter: {}",object);

            // Determine the parameters
            if (object instanceof Encrypted) {
                if (SqlCommandType.INSERT == sqlCommandType) {
                    encryptField((Encrypted)object);
                    continue;
                }
                if (SqlCommandType.UPDATE == sqlCommandType) {
                    encryptField((Encrypted)object);
                    log.debug("Encryption interceptor update operation,encrypt field: {}",object.toString()); }}}return invocation.proceed();
    }


    / * * *@paramObject Indicates the object * to be checked@throwsIllegalAccessException * Annotated by query@EncryptOr the field returned by Encrypted, dynamically Encrypted * the two methods are mutually exclusive */
    private void encryptField(Encrypted object) throws IllegalAccessException, NoSuchFieldException {
        String[] encryptFields = object.getEncryptFields();
        String factor = "xu.dm118dAADF! @ $"; Class<? > clazz = object.getClass();if(encryptFields.length==0){
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                Encrypt encrypt = field.getAnnotation(Encrypt.class);
                if(encrypt! =null) {
                    String encryptString = AesUtils.encrypt(field.get(object).toString(), factor);
                    field.set(object,encryptString);
                    log.debug(Encrypt Interceptor, encrypt field: {}",field.getName()); }}}else {
            for (String fieldName : encryptFields) {
                Field field = clazz.getDeclaredField(fieldName);
                field.setAccessible(true);
                String encryptString = AesUtils.encrypt(field.get(object).toString(), factor);
                field.set(object,encryptString);
                log.debug(Encrypt Interceptor, encrypt field: {}",field.getName()); }}}@Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {}}Copy the code

3.4 Interceptor decrypts data

During decryption, the query method is intercepted and only the result set is judged. If the result belongs to the Encrypted interface or the first data of the result set belongs to the Encrypted interface, the decryption process is entered.

If decryption fails or the decryption method returns an empty string, the original field data will not be modified.


/ * * *@author: xu.dm
 * @since* Decrypt data to return a list of elements of the same type. * Returns null on decryption failure, or returns an empty string without operating on the original data. * * /
@Component
@Slf4j
@Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}), })
public class DecryptionInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object result = invocation.proceed();
        if(result instanceof ArrayList) {
            @SuppressWarnings("rawtypes")
            ArrayList list = (ArrayList) result;
            if(list.size() == 0) {
                return result;
            }
            if(list.get(0) instanceof Encrypted) {
                for(Object item : list) { decryptField((Encrypted) item); }}return result;
        }
        if(result instanceof Encrypted) {
            decryptField((Encrypted) result);
        }
        return result;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {}/ * * *@paramObject Indicates the object * to be checked@throwsIllegalAccessException * Annotated by query@EncryptOr the field returned by Encrypted, decrypted * the two methods are mutually exclusive */
    private void decryptField(Encrypted object) throws IllegalAccessException, NoSuchFieldException {
        String[] encryptFields = object.getEncryptFields();
        String factor = "xu.dm118dAADF! @ $"; Class<? > clazz = object.getClass();if(encryptFields.length==0){
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                Encrypt encrypt = field.getAnnotation(Encrypt.class);
                if(encrypt! =null) {
                    String encryptString = AesUtils.decrypt(field.get(object).toString(), factor);
                    if(encryptString! =null){
                        field.set(object,encryptString);
                        log.debug(Encrypt Interceptor, encrypt field: {}",field.getName()); }}}}else {
            for (String fieldName : encryptFields) {
                Field field = clazz.getDeclaredField(fieldName);
                field.setAccessible(true);
                String encryptString = AesUtils.decrypt(field.get(object).toString(), factor);
                if(encryptString! =null && encryptString.length() > 0){
                    field.set(object,encryptString);
                    log.debug(Encrypt Interceptor, encrypt field: {}",field.getName());
                }
            }
        }
    }
}
Copy the code

3.5 Decrypting tool classes

The decryption utility class can be further optimized according to the scenario. For example, the decryption class can be resident in memory after instantiation to reduce CPU load.


/ * * *@author: xu.dm
 * @since: 2018/11/24 22:26
 *
 */
public class AesUtils {
    private static final String ALGORITHM = "AES/ECB/PKCS5Padding";

    public static String encrypt(String content, String key) {
        try {
            // Get a byte array of passwords
            byte[] raw = key.getBytes();
            // Generate AES key based on password
            SecretKeySpec keySpec = new SecretKeySpec(raw, "AES");
            // According to the specified ALGORITHM ALGORITHM is its own password
            Cipher cipher = Cipher.getInstance(ALGORITHM);
            // Initializes the cipher with the first parameter ENCRYPT_MODE or DECRYPT_MODE operation and the second parameter generated AES key
            cipher.init(Cipher.ENCRYPT_MODE, keySpec);
            // Get the byte array of the encrypted content (set to UTF-8) otherwise if the content contains Chinese and English mixed Chinese will be decrypted to garbled characters
            byte [] contentBytes = content.getBytes(StandardCharsets.UTF_8);
            // Encrypts data
            byte [] encodeContent = cipher.doFinal(contentBytes);
            // Returns the encrypted data as a string
            return Base64.encodeBase64String(encodeContent);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("AesUtils encryption failed"); }}public static String decrypt(String encryptStr, String decryptKey) {
        try {
            // Get a byte array of passwords
            byte[] raw = decryptKey.getBytes();
            // Generate AES key based on password
            SecretKeySpec keySpec = new SecretKeySpec(raw, "AES");
            // According to the specified ALGORITHM ALGORITHM is its own password
            Cipher cipher = Cipher.getInstance(ALGORITHM);
            // Initializes the cipher with the first parameter ENCRYPT_MODE or DECRYPT_MODE operation and the second parameter generated AES key
            cipher.init(Cipher.DECRYPT_MODE, keySpec);
            // Convert the ciphertext string back to the ciphertext byte array
            byte [] encodeContent = Base64.decodeBase64(encryptStr);
            // The cipher decrypts the data
            byte [] byteContent = cipher.doFinal(encodeContent);
            // Returns the decrypted data as a string
            return new String(byteContent, StandardCharsets.UTF_8);
        } catch (Exception e) {
            // e.printStackTrace();
            // Decryption failure temporarily returns null, which can throw a Runtime exception
            return null; }}}Copy the code

3.6 Entity Class Example


/** * (SysUser) entity class **@author xu.dm
 * @sinceThe 2020-05-02 09:34:53 * /
@EqualsAndHashCode(callSuper = true)
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(exclude = {"password","username"},callSuper = true)
public class SysUser extends BaseDO implements Serializable.Encrypted {
    private static final long serialVersionUID = 100317866935565576L;
    /** ** ID is converted to a string to the front end, otherwise js will have precision problems * For the front end Long 64 bits, the current end will lose precision after 53 bits, the excess will be displayed as 00. * Can be used@JsonSerialize(using = ToStringSerializer.class)
    */
    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;
    /** * Mobile phone number */
    @Encrypt
    private String mobile;
    /** * User login name */
    private String username;

    private String name;
    /** * Password */
    @JsonIgnore
    private String password;
    /** * email */
    private String email;

    @Override
    public String[] getEncryptFields() {
        return new String[]{"mobile"."name"}; }}Copy the code

4. Use type converters

The use of type converter in Mybatis is essentially to define a type (essentially a class), through the TypeHandler interface extension provided by Mybatis, the data type conversion, in the process of adding encryption and decryption business logic to achieve data storage and query encryption and decryption function.

4.1 Defining the Encryption Type

This type is directly understood as similar to java.lang.string. N EncryptType types can be expanded if you have multiple requirements for encryption methods.


/ * * *@author: xu.dm
 * @since: 2022/3/9 16:54 * Custom type used to indicate the encryption type in Mybatis * Fields that need to be encrypted are declared with the EncryptType **/
public class EncryptType {
    private String value;

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

    public String getValue(a) {
        return value;
    }

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

    @Override
    public String toString(a) {
        returnvalue; }}Copy the code

4.2 Defining the type conversion processor

The AesUtils utility class is described above.

The converter inherits from Mybatis BaseTypeHandler, overrides the method of value setting and value obtaining, and adds encryption and decryption logic in its process.


/ * * *@author: xu.dm
 * @sinceEncryptType converter, used to encrypt and decrypt data **/
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(EncryptType.class)
public class EncryptTypeHandler extends BaseTypeHandler<EncryptType> {
    private String factor = "xu.dm118dAADF! @ $";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, EncryptType parameter, JdbcType jdbcType) throws SQLException {
        if (parameter == null || parameter.getValue() == null) {
            ps.setString(i, null);
            return;
        }
        String encrypt = AesUtils.encrypt(parameter.getValue(),factor);
        ps.setString(i, encrypt);
    }

    @Override
    public EncryptType getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String decrypt = AesUtils.decrypt(rs.getString(columnName), factor);
        if(decrypt==null || decrypt.length()==0){
            decrypt = rs.getString(columnName);
        }
        return new EncryptType(decrypt);
    }

    @Override
    public EncryptType getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String decrypt = AesUtils.decrypt(rs.getString(columnIndex), factor);
        if(decrypt==null || decrypt.length()==0){
            decrypt = rs.getString(columnIndex);
        }
        return new EncryptType(decrypt);
    }

    @Override
    public EncryptType getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String decrypt = AesUtils.decrypt(cs.getString(columnIndex), factor);
        if(decrypt==null || decrypt.length()==0){
            decrypt = cs.getString(columnIndex);
        }
        return newEncryptType(decrypt); }}Copy the code

4.3 Configuring the Package Path of the Type Converter

This configuration is optional because it can be specified in mapper’s mapping XML file.

mybatis:
  Configuration is required for the mapped version of XML, not for the annotated version
  mapper-locations: classpath*:mapper/*.xml # Multimodule specifies the location of the SQL mapping file, which requires an asterisk after the classpath
  type-handlers-package: com.wood.encryption.handler
Copy the code

4.4 Entity classes for testing

An excerpt of the code focuses on fields name and Mobile that use type EncryptType.


/** * (TestUser) entity class **@author xu.dm
 * @sinceThe 2022-03-10 11:31:54 * /
@Data
public class TestUser extends BaseDO implements Serializable {
    private static final long serialVersionUID = -53491943096074862L;
    /** * ID */
    private Long id;
    /** * Mobile phone number */
    private EncryptType mobile;
    /** * User login name */
    private String username;
    /** * User name or nickname */
    private EncryptType name;
    /** * Password */
    private String password;
    /** * email */
    privateString email; . . }Copy the code

4.5 Mapper Interface Files

There are no essential changes to this class, which intercepts some of the code. Note the use of the EncryptType type.


/** * (TestUser) table database access layer **@author xu.dm
 * @sinceThe 2022-03-10 11:31:54 * /
public interface TestUserDao {

    /** * to query the mobile phone number, press the primary key **@param* id primary key@returnMobile phone number */
    EncryptType queryMobileById(Long id);

    /** * Query a single piece of data by phone number **@paramMobile Mobile number *@returnInstance object */
    List<TestUser> queryByMobile(EncryptType mobile);

    /** * Query single data by ID **@param* id primary key@returnInstance object */
    TestUser queryById(Long id);

    /** * query all data, according to the input parameter, decide whether to fuzzy query **@paramTestUser Query condition * *@returnObject list */
    List<TestUser> queryByBlurry(TestUser testUser);

    /** * Total number of rows **@paramTestUser Query condition *@returnThe total number of rows * /
    long count(TestUser testUser);

    /** * Add data **@paramTestUser Instance object *@returnAffects the number of rows */
    int insert(TestUser testUser);

   
    /** * Modify data **@paramTestUser Instance object *@returnAffects the number of rows */
    int update(TestUser testUser);


}

Copy the code

4.6 Mapper Mapping File

No essential changes, part of the code is captured, notice the use of the EncryptType type.


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wood.system.dao.TestUserDao">

    <resultMap type="com.wood.system.entity.TestUser" id="TestUserMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="mobile" column="mobile" jdbcType="VARCHAR"/>
        <result property="username" column="username" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="email" column="email" jdbcType="VARCHAR"/>
        <result property="state" column="state" jdbcType="VARCHAR"/>
        <result property="level" column="level" jdbcType="VARCHAR"/>
        <result property="companyId" column="company_id" jdbcType="INTEGER"/>
        <result property="deptId" column="dept_id" jdbcType="INTEGER"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
    </resultMap>

    <! -- Query single -->
    <select id="queryById" resultMap="TestUserMap">
        select
          id, mobile, username, name, password, email, state, level, company_id, dept_id, create_time, update_time
        from test_user
        where id = #{id}
    </select>

    <! Select * from row where id = 0;
    <select id="queryByBlurry" resultMap="TestUserMap">
        select
          id, mobile, username, name, password, email, state, level, company_id, dept_id, create_time, update_time
        from test_user
        <where>
            <if test="id ! = null">
                and id = #{id}
            </if>
            <if test="mobile ! = null and mobile ! = "">
                and mobile = #{mobile}
            </if>
            <if test="username ! = null and username ! = "">
                and username = #{username}
            </if>
            <if test="name ! = null and name ! = "">
                and name = #{name}
            </if>. .</where>        
    </select>
  
    <select id="queryMobileById" resultType="com.wood.encryption.type.EncryptType">
        select mobile from test_user where id = #{id}
    </select>

    <select id="queryByMobile" resultType="com.wood.system.entity.TestUser">
        select * from test_user where mobile = #{mobile}
    </select>

    <! -- Add all columns -->
    <insert id="insert" keyProperty="id" useGeneratedKeys="false">
        insert into test_user(id, mobile, username, name, password, email, state, level, company_id, dept_id, create_time, update_time)
        values (#{id}, #{mobile}, #{username}, #{name}, #{password}, #{email}, #{state}, #{level}, #{companyId}, #{deptId}, #{createTime}, #{updateTime})
    </insert>

  
    <! Alter data with primary key -->
    <update id="update">
        update test_user
        <set>
            <if test="mobile ! = null and mobile ! = "">
                mobile = #{mobile},
            </if>
            <if test="username ! = null and username ! = "">
                username = #{username},
            </if>
            <if test="name ! = null and name ! = "">
                name = #{name},
            </if>
         
            <if test="email ! = null and email ! = "">
                email = #{email},
            </if>
            <if test="state ! = null and state ! = "">
                state = #{state},
            </if>
            <if test="level ! = null and level ! = "">
                level = #{level},
            </if>
            <if test="companyId ! = null">
                company_id = #{companyId},
            </if>
            <if test="deptId ! = null">
                dept_id = #{deptId},
            </if>
          
            <if test="updateTime ! = null">
                update_time = #{updateTime},
            </if>
        </set>
        where id = #{id}
    </update>

</mapper>


Copy the code