bug in encryption DISTINCT

FakeKotaro commented 1 year ago

Bug Report

Which version of ShardingSphere did you use?

5.3.1

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

ShardingSphere-JDBC

My SQL:

SELECT DISTINCT name FROM user

My Encryption Config:

rules:
- !ENCRYPT
    encryptors:
      encryptor-aes:
        props:
          aes-key-value: 123456a
        type: AES
    tables:
      user:
        columns:
          name:
            plainColumn: name
            cipherColumn: name_encode
            encryptorName: encryptor-aes

Then it throws an exception:

Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -4
    at java.lang.String.substring(String.java:1967)
    at org.apache.shardingsphere.infra.rewrite.sql.impl.AbstractSQLBuilder.getConjunctionText(AbstractSQLBuilder.java:74)
    at org.apache.shardingsphere.infra.rewrite.sql.impl.AbstractSQLBuilder.toSQL(AbstractSQLBuilder.java:57)
    at org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.addSQLRewriteUnits(RouteSQLRewriteEngine.java:96)
    at org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.rewrite(RouteSQLRewriteEngine.java:72)
    at org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.rewrite(SQLRewriteEntry.java:79)
    at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.rewrite(KernelProcessor.java:65)
    at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:52)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:551)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:407)
    ... 94 more

The expected actual SQL show be:

SELECT DISTINCT name_encode AS name FROM user

It's worth noting that this issue has been mentioned before in version 5.1.2, but it has never been fixed #20096

strongduanmu commented 1 year ago

@FakeKotaro Thank you for your feedback, I will investigate this issue.

liming0 commented 1 year ago

@FakeKotaro Thank you for your feedback, I will investigate this issue. @strongduanmu Have you solved this problem？ I saw a bugfix (25033) in 20096. I overwrote the file in fix to the local and this error still occurred. I used postgrea https://github.com/apache/shardingsphere/pull/25033 databaseType：postgrea version: 5.3.2 config:

rules:
# 加密策略
- !ENCRYPT
encryptors:
enc-aes-c:
type: AES-C
props:
aes-c-key-value: ${shardingsphere.encrypt.commons.ept-zqsb.aes-c-key-value}
enc-sha256:
type: ${shardingsphere.encrypt.commons.enc-sha256}
tables:
t_person0:
columns:
person_name:
encryptorName: enc-aes-c
cipherColumn: person_name_plain
assistedQueryColumn: person_name_query
assistedQueryEncryptorName: enc-sha256
plainColumn: person_name

sql：SELECT COUNT(1) FROM (SELECT DISTINCT person_name personName, gender FROM t_person0 WHERE personName IS NOT NULL) TOTAL error：

### Error querying database.  Cause: java.sql.SQLException: Unknown exception: String index out of range: -22
### The error may exist in com/thunisoft/ept/zqsb/mapper/Person0Mapper.java (best guess)
### The error may involve com.thunisoft.ept.zqsb.mapper.Person0Mapper.findPageAsDistinct-Inline
### The error occurred while setting parameters
### SQL: SELECT COUNT(1) FROM (SELECT DISTINCT person_name personName, gender FROM t_person0 WHERE personName IS NOT NULL) TOTAL
### Cause: java.sql.SQLException: Unknown exception: String index out of range: -22
; uncategorized SQLException; SQL state [HY000]; error code [30000]; Unknown exception: String index out of range: -22; nested exception is java.sql.SQLException: Unknown exception: String index out of range: -22]

Override：

liming0 commented 1 year ago

After testing, it is not related to count (1)

KoSChicken commented 1 month ago

If you are using ShardingSphere with Mybatis PageHelper, you can try to write a count SQL for your original SQL. Consider the original SQL function called query, write a query_COUNT for it, Mybatis PageHelper will use query_COUNT to count total rows. This approach will solve the String index out of range Exception.

apache / shardingsphere