You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "FakeKotaro (via GitHub)" <gi...@apache.org> on 2023/04/04 08:05:22 UTC
[GitHub] [shardingsphere] FakeKotaro opened a new issue, #25005: bug in encryption DISTINCT
FakeKotaro opened a new issue, #25005:
URL: https://github.com/apache/shardingsphere/issues/25005
## 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:
```sql
SELECT DISTINCT name FROM user
```
My Encryption Config:
```yaml
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:
```sql
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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] liming0 commented on issue #25005: bug in encryption DISTINCT
Posted by "liming0 (via GitHub)" <gi...@apache.org>.
liming0 commented on issue #25005:
URL: https://github.com/apache/shardingsphere/issues/25005#issuecomment-1501575754
After testing, it is not related to count (1)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #25005: bug in encryption DISTINCT
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25005:
URL: https://github.com/apache/shardingsphere/issues/25005#issuecomment-1498533464
@FakeKotaro Thank you for your feedback, I will investigate this issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lid1987 commented on issue #25005: bug in encryption DISTINCT
Posted by "lid1987 (via GitHub)" <gi...@apache.org>.
lid1987 commented on issue #25005:
URL: https://github.com/apache/shardingsphere/issues/25005#issuecomment-1504748723
I encountered the same problem, and my solution is to add the judgment of whether SQLToken is repeated in the generateSQLTokens method in SQLTokenGenerators, as follows:
<img width="786" alt="image" src="https://user-images.githubusercontent.com/23111804/231373424-fac231cb-6397-4810-96d3-a64f59e5fde2.png">
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] liming0 commented on issue #25005: bug in encryption DISTINCT
Posted by "liming0 (via GitHub)" <gi...@apache.org>.
liming0 commented on issue #25005:
URL: https://github.com/apache/shardingsphere/issues/25005#issuecomment-1501572616
> @FakeKotaro Thank you for your feedback, I will investigate this issue.
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](url)
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：
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org