encrypt AES bug

[15102552479]

AES encrypt bug:sharding-sphere5.5.0 1.table CREATE TABLE test ( id int NOT NULL AUTO_INCREMENT COMMENT 'id', patient_id varchar(255) DEFAULT NULL COMMENT '换着ID', visit_sn varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

version:5.5.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

Encrypt table "test", field "visit_sn", use sql "select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id;" is correct.but use sql "select * from (select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id) a;" is incorrect. I think the result is the same in both cases

Actual behavior

use the second sql: Exception:Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class iscom.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. java.sql.SQLException: Unsupported SQL operation: Can not support encrypt shorthand expand with subquery statement. at org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:76) at org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:188) at org.apache.shardingsphere.driver.api.ShardingJDBCDemo.querycourse(ShardingJDBCDemo.java:167) at org.apache.shardingsphere.driver.api.ShardingJDBCDemo.main(ShardingJDBCDemo.java:135)

Reason analyze (If you can)

I think is a bug

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1.select * from (select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id) a; 2.java code `package org.apache.shardingsphere.driver.api;

import com.zaxxer.hikari.HikariDataSource; import org.apache.shardingsphere.encrypt.api.config.EncryptRuleConfiguration; import org.apache.shardingsphere.encrypt.api.config.rule.EncryptColumnItemRuleConfiguration; import org.apache.shardingsphere.encrypt.api.config.rule.EncryptColumnRuleConfiguration; import org.apache.shardingsphere.encrypt.api.config.rule.EncryptTableRuleConfiguration; import org.apache.shardingsphere.infra.algorithm.core.config.AlgorithmConfiguration; import org.apache.shardingsphere.infra.config.rule.RuleConfiguration;

import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.*;

/**

• @author ：
• @date ：Created in 2020/12/16
• @description: 复制application02.properties中的分库分表逻辑 **/

public class ShardingJDBCDemo {

public static void main(String[] args) throws SQLException {

    //=======一、配置数据库
    Map<String, DataSource> dataSourceMap = new HashMap<>(2);//为两个数据库的datasource
    // 配置第一个数据源
    HikariDataSource dataSource0 = new HikariDataSource();
    dataSource0.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource0.setJdbcUrl("jdbc:mysql://localhost:3306/coursedb?serverTimezone=GMT%2B8&useSSL=false");
    dataSource0.setUsername("root");
    dataSource0.setPassword("root");
    dataSourceMap.put("m1", dataSource0);
    // 配置第二个数据源

    //三、配置属性值
    Properties properties = new Properties();
    //打开日志输出
    properties.setProperty("sql.show", "true");
    //K1 创建ShardingSphere的数据源 ShardingDataSource

    //1.table/column加密
    EncryptColumnItemRuleConfiguration cipher = new EncryptColumnItemRuleConfiguration("visit_sn", "aes_encryptor");
    EncryptColumnRuleConfiguration encryptColumnRuleConfiguration = new EncryptColumnRuleConfiguration("visit_sn",cipher);
    List<EncryptColumnRuleConfiguration> columnList = new ArrayList<>();
    columnList.add(encryptColumnRuleConfiguration);
    EncryptTableRuleConfiguration encryptTableRuleConfiguration = new EncryptTableRuleConfiguration("test",columnList);
    ArrayList<EncryptTableRuleConfiguration> encryptTableRuleConfigurations = new ArrayList<>();
    encryptTableRuleConfigurations.add(encryptTableRuleConfiguration);

    //2.加密配置
    Properties props = new Properties();
    props.put("aes-key-value","123456");
    AlgorithmConfiguration algorithmConfiguration = new AlgorithmConfiguration("AES", props);
    Map<String, AlgorithmConfiguration> encryptors = new HashMap<>();
    encryptors.put("aes_encryptor",algorithmConfiguration);

    EncryptRuleConfiguration encryptRuleConfiguration = new EncryptRuleConfiguration(encryptTableRuleConfigurations, encryptors);
    List<RuleConfiguration> encryptRuleConfigurations = new ArrayList<>();
    encryptRuleConfigurations.add(encryptRuleConfiguration);

    DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSource0, encryptRuleConfigurations, properties);

    //-------------测试部分-----------------//
    ShardingJDBCDemo test = new ShardingJDBCDemo();
    //建表

// test.droptable(dataSource); // test.createtable(dataSource);

    //插入数据

// test.addcourse(dataSource); //K1 调试的起点 查询数据 test.querycourse(dataSource); }

//雪花算法，需要有机器序号，手动配置序号
private static Properties getProps() {
    Properties props = new Properties();
    props.setProperty("worker.id", "123");
    return props;
}

//添加10条课程记录
public void addcourse(DataSource dataSource) throws SQLException {
    for (int i = 1; i < 10; i++) {
        long orderId = executeAndGetGeneratedKey(dataSource, "INSERT INTO course (cname, user_id, cstatus) VALUES ('java'," + i + ", '1')");
        System.out.println("添加课程成功，课程ID：" + orderId);
    }
}

public void querycourse(DataSource dataSource) throws SQLException {
    Connection conn = null;
    try {
        //ShardingConnectioin
        conn = dataSource.getConnection();
        //ShardingStatement
        Statement statement = conn.createStatement();

// String sql = "insert into test(patient_id, visit_sn) values ('A', 'A')"; //ShardingResultSet // boolean execute = statement.execute(sql); // System.out.println("execute:"+execute); // String sql = "select from (select ISNULL(GROUP_CONCAT(t1.id)) as ids ,t1.patient_id, t1.visit_sn from test t1 LEFT JOIN test_surgery t2 on t1.id = t2.tid GROUP BY patient_id) a;"; // String sql = "select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id;"; String sql = "select from (select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id) a;"; ResultSet result = statement.executeQuery(sql); while (result.next()) { System.out.println("id:" + result.getString("ids")); System.out.println("patient_id:" + result.getString("patient_id")); System.out.println("visit_sn:" + result.getString("visit_sn")); } } catch (SQLException e) { e.printStackTrace(); } finally { if (null != conn) { conn.close(); } } }

private void execute(final DataSource dataSource, final String sql) throws SQLException {
    try (
            Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement()) {
        statement.execute(sql);
    }
}

private long executeAndGetGeneratedKey(final DataSource dataSource, final String sql) throws SQLException {
    long result = -1;
    try (
            Connection conn = dataSource.getConnection();
            Statement statement = conn.createStatement()) {
        statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet resultSet = statement.getGeneratedKeys();
        if (resultSet.next()) {
            result = resultSet.getLong(1);
        }
    }
    return result;
}

/**
 * -----------------------------表初始化--------------------------------
 */
public void droptable(DataSource dataSource) throws SQLException {
    execute(dataSource, "DROP TABLE IF EXISTS course_1");
    execute(dataSource, "DROP TABLE IF EXISTS course_2");
}

public void createtable(DataSource dataSource) throws SQLException {
    execute(dataSource, "CREATE TABLE course_1 (cid BIGINT(20) PRIMARY KEY,cname VARCHAR(50) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);");
    execute(dataSource, "CREATE TABLE course_2 (cid BIGINT(20) PRIMARY KEY,cname VARCHAR(50) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);");
}

} 3. Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class iscom.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. java.sql.SQLException: Unsupported SQL operation: Can not support encrypt shorthand expand with subquery statement. at org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:76) at org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.executeQuery(ShardingSphereStatement.java:188) at org.apache.shardingsphere.driver.api.ShardingJDBCDemo.querycourse(ShardingJDBCDemo.java:167) at org.apache.shardingsphere.driver.api.ShardingJDBCDemo.main(ShardingJDBCDemo.java:135)`

Example codes for reproduce this issue (such as a github link).

[15102552479]

求各位大神指点

[terrymanu]

The markdown format in your issue is incorrect. To facilitate reading, could you please revise it?

[15102552479]

OK

[terrymanu]

I don't think the content is clear enough, for example: what is the sql mean before Bug Report? I don't understand about Expected behavior too.

[15102552479]

Basically, I'm using encryption features with AES encryption. I've tried the 5.2.1, 5.4.1, and 5.5.0 versions of sharding-jdbc, but none of them support aliases. For example, this SQL statement will give an error directly: "select * from (select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id) a;". However, removing the outer alias makes it work: "select ISNULL(GROUP_CONCAT(id)) as ids ,patient_id, visit_sn from test GROUP BY patient_id;". It's clear that the SQL parser does not support aliasing in this format.

[15102552479]

1. The sql before the bug report is the DDL statement of the test table I made.
2. The expected result is that the two sql execution results are consistent, but the current results are inconsistent

[terrymanu]

Firstly, could you revise your first comment to make it clear?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.