ShardingSphere4.1.1: Sharding-JDBC data encryption and SPI encryption strategy implementation

0, desensitization rules

1. Environment construction

Create a Spring Boot project and introduce the following dependencies:

      
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <parent>
        <groupId>cn.zwqh</groupId>
        <artifactId>Sharding - sphere - 4.4.1</artifactId>
        <version>1.0 the SNAPSHOT</version>
    </parent>

    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-demo-8</artifactId>
    <version>${parent.version}</version>

    <packaging>jar</packaging>
    <name>sharding-sphere-demo-8</name>
    <description>Demo project for Spring Boot</description>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>


        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>


        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.zwqh.shardingspheredemo8.ShardingSphereDemo8Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>
Copy the code

1.2 Test database DS1

CREATE TABLE `t_user` (
  `user_id` int NOT NULL COMMENT 'user id',
  `user_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT User name,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Password plaintext',
  `password_encrypt` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Cipher text',
  `password_assisted` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Auxiliary query column'.PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Copy the code

1.3. Entity classes

@Data
public class UserEntity {

    private Integer userId;

    private String userName;

    private String password;

    private String passwordEncrypt;

    private String passwordAssisted;
}

Copy the code

1.4, Mapper

@Mapper
public interface UserMapper {

    @Insert("insert into t_user(user_id,user_name,password) values(#{userId},#{userName},#{password})")
    void insertUser(UserEntity userEntity);

    @Select("select * from t_user where user_name=#{userName} and password=#{password}")
    @Results({ @Result(column = "user_id", property = "userId"), @Result(column = "user_name", property = "userName"), @Result(column = "password", property = "password"), @Result(column = "password_assisted", property = "passwordAssisted") })
    List<UserEntity> getUserInfo(@Param("userName") String userName, @Param("password") String password);
}
Copy the code

1.5. Test class

@Slf4j
@SpringBootTest
class ShardingSphereDemo8ApplicationTests {

    @Resource
    private UserMapper userMapper;

    @Test
    void insertUser(a) {
        UserEntity userEntity = new UserEntity();
        userEntity.setUserId(19);
        userEntity.setUserName("user19");
        userEntity.setPassword("123456");
        userMapper.insertUser(userEntity);
    }

    @Test
    void getUserInfo(a) {
        List<UserEntity> userEntityList = userMapper.getUserInfo("user19"."123456"); log.info(userEntityList.toString()); }}Copy the code

2. Default encryption algorithm

2.1, AES

The configuration file

# Application name
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=JDBC: mysql: / / 127.0.0.1:3306 / ds1? serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
Use AES symmetric encryption policy
spring.shardingsphere.encrypt.encryptors.encryptor_aes.type=aes
spring.shardingsphere.encrypt.encryptors.encryptor_aes.props.aes.key.value=123456
# password is a logical column, password is a data display column, and password_encrypt is a data table ciphertext column
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_aes
spring.shardingsphere.props.sql.show=true
# Query whether ciphertext columns are used
spring.shardingsphere.props.query.with.cipher.column=true
Copy the code

The test results

1. The logical column is changed to the plaintext column and the ciphertext column when the logical column is added

2, only set the plaintext column, run direct error, so must set encryption column

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'encryptDataSource' defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'encryptDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Invalid encrypt column configurations in EncryptTableRuleConfigurations.
Copy the code

3. Set only the ciphertext column. If the operation succeeds, the plaintext column is encrypted and the database is actually inserted into the ciphertext column

4, set the plaintext and ciphertext columns, spring. Shardingsphere. Props. Query. With. The cipher. The column to false, the query by included, return data is included

5, set the plaintext and ciphertext columns, spring. Shardingsphere. Props. Query. With. The cipher, the column is true, the query by cipher text query, return the data is clear

6, set the cipher text columns, only spring. Shardingsphere. Props. Query. With. The cipher. The column to false, the default through the cipher text query, query but not into the parameter automatically encrypted, so can’t check the data

7, set the cipher text columns, only spring. Shardingsphere. Props. Query. With. The cipher, the column is true, the query by cipher text query, return the data is clear

2.2, MD5

The configuration file

# Application name
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=JDBC: mysql: / / 127.0.0.1:3306 / ds1? serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# Use MD5 encryption policy
spring.shardingsphere.encrypt.encryptors.encryptor_md5.type=MD5
# password is a logical column, password is a data display column, and password_encrypt is a data table ciphertext column
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_md5
spring.shardingsphere.props.sql.show=true
# Query whether ciphertext columns are used
spring.shardingsphere.props.query.with.cipher.column=true
Copy the code

The test results

1. When adding data, you can see that the encrypted data is different from the AES data

2, query, spring. Shardingsphere. Props. Query. With. Cipher. The column is true, through the cipher text query, because the MD5 encryption is symmetrical, so return the cipher text data

3, query, spring. Shardingsphere. Props. Query. With. Cipher. The column to false, by included a query, return to clear data

3. SPI custom encryption algorithm

3.1 SPI configuration

Add a new configuration in the resources/ meta-INF /services directory

The configuration file name is: the org. Apache. Shardingsphere. Encrypt. Strategy. Spi. The Encryptor

The contents of the configuration file, the full path of the class where the custom encryption policy is placed, and the full path of the class where the official built-in encryption policy is to be used

org.apache.shardingsphere.encrypt.strategy.impl.AESEncryptor
org.apache.shardingsphere.encrypt.strategy.impl.MD5Encryptor
cn.zwqh.shardingspheredemo8.encryptor.Sha256Encryptor
cn.zwqh.shardingspheredemo8.encryptor.Sha256RandomEncryptor
Copy the code

Implement the Encryptor interface

Create Sha256Encryptor class

@Getter
@Setter
public class Sha256Encryptor implements Encryptor {

    private Properties properties = new Properties();

    @Override
    public void init(a) {}@Override
    public String encrypt(Object plaintext) {
        if (null == plaintext) {
            return null;
        }
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public Object decrypt(String ciphertext) {
        return ciphertext;
    }

    @Override
    public String getType(a) {
        return "SHA256"; }}Copy the code

Modifying a Configuration File

# Application name
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=JDBC: mysql: / / 127.0.0.1:3306 / ds1? serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# Use custom policy SHA256
spring.shardingsphere.encrypt.encryptors.encryptor_sha256.type=SHA256
# password is a logical column, password is a data display column, and password_encrypt is a data table ciphertext column
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_sha256
spring.shardingsphere.props.sql.show=true
# Query whether ciphertext columns are used
spring.shardingsphere.props.query.with.cipher.column=true
Copy the code

The test results

1. If the encrypted data is different from the AES data, the user-defined encryption policy is successfully used

2, query, spring. Shardingsphere. Props. Query. With. Cipher. The column is true, through the cipher text query, due to similar SHA256 MD5 encryption, so return the cipher text data

3, query, spring. Shardingsphere. Props. Query. With. Cipher. The column to false, by included a query, return to clear data

Implement QueryAssistedEncryptor interface

Create Sha256RandomEncryptor class

@Getter
@Setter
public class Sha256RandomEncryptor implements QueryAssistedEncryptor {

    private Properties properties = new Properties();

    @Override
    public String queryAssistedEncrypt(String plaintext) {
        if (null == plaintext) {
            return null;
        }
        // Raw string
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public void init(a) {}@Override
    public String encrypt(Object plaintext) {
        if (null == plaintext) {
            return null;
        }
        // Raw string + change factor (such as timestamp)
        plaintext = plaintext.toString() + LocalDateTime.now().toString();
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public Object decrypt(String ciphertext) {
        return ciphertext;
    }

    @Override
    public String getType(a) {
        return "SHA256_RANDOM"; }}Copy the code

Modifying a Configuration File

# Application name
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=JDBC: mysql: / / 127.0.0.1:3306 / ds1? serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# Use custom policy SHA256_RANDOM
spring.shardingsphere.encrypt.encryptors.encryptor_sha256_random.type=SHA256_RANDOM
# password is the logical column, password is the data display column, password_ENCRYPT is the data table ciphertext column, and password_assisted query column
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.assistedQueryColumn=password_assisted
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_sha256_random
spring.shardingsphere.props.sql.show=true
# Query whether ciphertext columns are used
spring.shardingsphere.props.query.with.cipher.column=true
Copy the code

The test results

1. Add two user data with the same password. You can see that the data in the ciphertext column is inconsistent, and the data in the secondary column is consistent, which can better protect the data security

2, query, spring. Shardingsphere. Props. Query. With. Cipher. The column is true, through assisted query query, and returns the data for the cipher text columns

3, query, spring. Shardingsphere. Props. Query. With. Cipher. The column to false, by included a query, and the data returned is clear

The source code

github

Yards cloud

summary

1. The default data encryption algorithm supports AES and MD5. AES is symmetric encryption and MD5 is asymmetric encryption.

2. You can customize the encryption policy through SPI;

3. Select and configure an appropriate encryption algorithm based on service security requirements.

Link: www.zwqh.top/article/inf…