preface

   companies in the Internet industry must encrypt the sensitive fields of the database. There are many schemes. The most direct one is to write an encryption and decryption tool class and then process it manually in each business logic. This method is obviously unrealistic in slightly large-scale projects, which is not only heavy workload, but also difficult to maintain in the later stage.

   at present, mybatis plus has provided a very good encryption and decryption scheme, and Jushi has tried it with good results. However, many Internet companies do not necessarily introduce mybatis plus as a data layer tool, but prefer to use mybatis, and many even use SpringDataJPA, so it is not necessary to introduce mybatis plus specifically for encryption and decryption.

                 8195? The answer is yes. Shardingsphere provides a scheme. Why choose it? Because Internet companies will probably consider database and table splitting. At present, the best database and table splitting scheme is actually shardingsphere. In that case, directly using its database encryption and decryption scheme does not need to introduce additional third-party tools.

use

1. Case preparation

The technical system is as follows. The introduction of MybatisPlus is to facilitate the case to be formed faster. ShardingSphere does not recommend using 5 X version, because the version difference is large, it has always been the reason why people criticize it. Even the small version has many differences, 4 The X version is more informative.

technology	edition
SpringBoot	2.6.3
MybatisPlus	3.5.1
ShardingSphere	4.1.1

2. Introduce dependency

<dependencies>
    <!-- spring web -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- sharding-jdbc -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
    </dependency>
    <!-- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- mybatis-plus -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.1</version>
    </dependency>
    <!-- Code generator  mybatisPlus Built in generator -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-generator</artifactId>
        <version>3.5.1</version>
    </dependency>
    <!-- freemarker Template generator introduces code generator -->
    <dependency>
        <groupId>org.freemarker</groupId>
        <artifactId>freemarker</artifactId>
        <version>2.3.31</version>
    </dependency>
    <!-- swagger because mybatisPlus The code generator comes with it swagger Notes for -->
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger2</artifactId>
        <version>2.7.0</version>
    </dependency>
    <dependency>
        <groupId>io.springfox</groupId>
        <artifactId>springfox-swagger-ui</artifactId>
        <version>2.7.0</version>
    </dependency>
    <!-- Load configuration file after startup -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <optional>true</optional>
    </dependency>
    <!-- lombok Simplify entity class management tools-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <!-- fastjson analysis json If you want to use it, you can also change it to what you like -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.79</version>
    </dependency>
    <!-- test -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

3. yml configuration

server:
  port: 8888

# data source
spring:
  # shardingsphere configuration
  shardingsphere:
    props:
      sql:
        show: false
      query:
        with:
          cipher:
            column: true # Query whether to use ciphertext column
    datasource:
      name: master
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/encrypt_demo?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456
        initial-size: 20
        max-active: 200
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
    # Encryption configuration
    encrypt:
      encryptors:
        my_encryptor:
          type: mySharding # Declare the type of encryption processor and customize it.
          props:
            aes.key.value: fly13579@# # The encryption processor will use 10 numbers and English letters to create the key, which can be customized.
      # Which fields in which table need to be encrypted, and which encryption processor is used for each field, here is my_encryptor is the name of the processor configured above.
      tables:
        tb_order:
          columns:
            id_card:
              cipherColumn: id_card
              encryptor: my_encryptor
            name:
              cipherColumn: name
              encryptor: my_encryptor

4. Custom encryption and decryption processor

ShardingSphere has its own encryption and decryption processor, which can be used directly. In the production environment, it still prefers to customize the processor, because it is safer and not easy to be brutally cracked.

1) . add SPI pointing

Create a new META-INF/services directory under the resources directory, and write a file to point to the custom processor.

Document name: org.org apache. shardingsphere. encrypt. strategy. spi. Encryptor

PS: the names of versions 4.1.1 and 4.0.0 are different because many class names and package names have changed.

2) Write custom encryption and decryption processor

package com.example.encrypt.config;

import com.google.common.base.Preconditions;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.codec.binary.StringUtils;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.shardingsphere.encrypt.strategy.impl.AESEncryptor;
import org.apache.shardingsphere.encrypt.strategy.spi.Encryptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;

import javax.crypto.Cipher;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.spec.SecretKeySpec;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.util.Properties;

/**
 * <p>
 *  Shardingsphere Encryption processor
 * </p>
 *
 * @author Fu Long court, the official account: Java sharing inn.
 * @since 2022-02-20
 */
@Configuration
public class MyShardingEncryptor implements Encryptor {

    private final Logger log = LoggerFactory.getLogger(MyShardingEncryptor.class);

    // AES KEY
    private static final String AES_KEY = "aes.key.value";

    private Properties properties = new Properties();

    public MyShardingEncryptor(){

    }

    @Override
    public void init() {

    }

    @Override
    public String encrypt(Object plaintext) {
        try {
            byte[] result = this.getCipher(1).doFinal(StringUtils.getBytesUtf8(String.valueOf(plaintext)));
            log.debug("[MyShardingEncryptor]>>>> encryption: {}", Base64.encodeBase64String(result));
            return Base64.encodeBase64String(result);
        } catch (Exception ex) {
            log.error("[MyShardingEncryptor]>>>> Encryption exception:", ex);
        }
        return null;

    }

    @Override
    public Object decrypt(String ciphertext) {
        try {
            if (null == ciphertext) {
                return null;
            } else {
                byte[] result = this.getCipher(2).doFinal(Base64.decodeBase64(ciphertext));
                log.debug("[MyShardingEncryptor]>>>> decrypt: {}", new String(result));
                return new String(result);
            }
        } catch (Exception ex) {
            log.error("[MyShardingEncryptor]>>>> Decryption exception:", ex);
        }
        return null;
    }

    @Override
    public String getType() {
        return "mySharding"; // Consistent with yml configuration
    }

    @Override
    public Properties getProperties() {
        return this.properties;
    }

    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }

   /**
    * Encryption and decryption algorithm
    * @param decryptMode 1-Encryption, 2- decryption, and other types can click in to see the source code.
    */
    private Cipher getCipher(int decryptMode) throws NoSuchPaddingException, NoSuchAlgorithmException, InvalidKeyException {
        Preconditions.checkArgument(this.properties.containsKey("aes.key.value"), "No available secret key for `%s`.",
            AESEncryptor.class.getName());
        Cipher result = Cipher.getInstance("AES");
        result.init(decryptMode, new SecretKeySpec(this.createSecretKey(), "AES"));
        return result;
    }

   /**
    * Create keys and define rules according to your needs.
    * -- PS: The production environment specification requires that the key related log cannot be printed to avoid accidental disclosure.
    */
   private byte[] createSecretKey() {
      // Original key configured in yml
        String oldKey = this.properties.get("aes.key.value").toString();
        Preconditions.checkArgument(null != oldKey, String.format("%s can not be null.", "aes.key.value"));
        /*
         * Encrypt the original key and the customized salt again to generate a new key
         * Note that the final key of AES encryption and decryption must be 16 bits, otherwise AES will report an error,
         * And application AES configured in YML key. Value is a 10 bit character combination, so substring(0,5) is used here. Otherwise, if 16 bits are not returned, AES exception will be thrown. You can test it yourself.
         */
      String secretKey = DigestUtils.sha1Hex(oldKey + AES_KEY).toUpperCase().substring(0, 5) + "!" + oldKey;
      // The key print must be deleted before going online to avoid security accidents caused by disclosure.
      log.debug("[MyShardingEncryptor]>>>> secret key: {}", secretKey);
        return secretKey.getBytes();
    }

}

5. Write test interface

package com.example.encrypt.controller;


import com.alibaba.fastjson.JSONObject;
import com.example.encrypt.entity.Order;
import com.example.encrypt.enums.ResponseCodeEnum;
import com.example.encrypt.service.IOrderService;
import com.example.encrypt.util.ResultEntity;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Front end controller
 * </p>
 *
 * @author Fu Long court, the official account: Java sharing inn.
 * @since 2022-02-21
 */
@RestController
@RequestMapping("/api/order")
@Slf4j
public class OrderController {

   private final IOrderService orderService;

   public OrderController(IOrderService orderService) {
      this.orderService = orderService;
   }

   /**
    * Query order
    */
   @GetMapping("/list")
   public ResponseEntity<List<Order>> list() {
      return ResponseEntity.ok().body(orderService.list());
   }

   /**
    * Insert order
    */
   @PostMapping("/save")
   public ResponseEntity<List<Order>> save(@RequestBody Order order) {
      // This is just a simple demonstration. Remember not to directly transfer the formal code to the entity object, but to transfer the VO object for conversion, and do parameter verification.
      log.debug("[Insert order]>>>> order={}", JSONObject.toJSONString(order));
      order.setCreatedAt(new Date());
      order.setUpdatedAt(new Date());
      boolean ret = orderService.save(order);
      return ret ? ResponseEntity.ok().body(orderService.list())
            : ResponseEntity.badRequest().body(new ArrayList<>());
   }
}

6. Effect

Insert order

Our yml is configured to encrypt the name and ID number. Look at the database order record and find that it has been automatically encrypted.

Try to query the order again to see if the encrypted field of the database will be decrypted and the result will be returned. It is found that there is no problem with decryption.

7. Usage of key in database

Here is a special explanation of how the key can directly encrypt and decrypt the field value in the database through SQL, because many companies will use fortress machine or cloud desktop to access the production environment database. At this time, when troubleshooting online problems, we often need to know what the encrypted field is.

1) I. get the key

PS: remember that this key can be kept once before going online. The log of printing the key must be deleted to avoid accidents caused by the disclosure of the key in the production environment. Generally, regular companies will have requirements.
The key printing place is in the custom encryption and decryption processor, which can be debug ged and printed.

2) Used in database

The statement is as follows. Save it and copy it when you use it in the future.

# encryption 
SELECT to_base64(AES_ENCRYPT('Value to encrypt','secret key')) 
# decrypt 
SELECT AES_DECRYPT(FROM_BASE64('Value to decrypt'),'secret key') 
# Chinese decryption, anti aliasing. 
select CAST(AES_DECRYPT(FROM_BASE64('Chinese value to decrypt'),'secret key') as char)

summary

   ShardingSphere's encryption and decryption steps are simple, but people who are not familiar with or have not used this tool will step on many holes, including version differences, unresolved defects, etc., but its advantages far outweigh its disadvantages, so it will be accepted by many companies and a required course for learning sub database and sub table.

   the lay provides you with the source code that can be run directly and the small notes of personal stepping on the pit. If you need it, you can download it and run for experiments.

  the source code link will be shared in the comments~

My original article is purely hand-made. If you feel that there is a drop of help, please extend your slender jade hand and point out your praise and collection~~~