wangtiga opened a new issue, #25032:
URL: https://github.com/apache/shardingsphere/issues/25032
## Bug Report
In the encryption scenario of PostgreSQL, the LikeQueryColumn in the SQL
statement is misjudged as an AssistedQueryColumn. Here is the complete SQL:
```sql
-- Logic SQL：
SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE
account_id = 1 AND password = 'aaa' AND password like 'aaa' AND amount = 1000
AND status = 'OK'
-- Expected SQL：
SELECT account_id, cipher_password AS password, cipher_amount AS a, status
AS s FROM t_account WHERE account_id = 1 AND assisted_query_password =
'assisted_query_aaa' AND like_query_password like 'like_query_aaa' AND
cipher_amount = 'encrypt_1000' AND status = 'OK'
-- Actual SQL：
SELECT account_id, cipher_password AS password, cipher_amount AS a, status
AS s FROM t_account WHERE account_id = 1 AND assisted_query_password =
'assisted_query_aaa' AND like_query_password like 'assisted_query_aaa' AND
cipher_amount = 'encrypt_1000' AND status = 'OK'
```
### Which version of ShardingSphere did you use?
ShardingSphere version 5.3.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Both ShardingSphere-JDBC and ShardingSphere-Proxy
### Expected behavior
Adding PostreSQL to the rewrite-assertion of encryption scenario, the unit
test of EncryptSQLRewriterIT runs without errors.
```sh
$ git diff
test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
<rewrite-assertions
yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
- <rewrite-assertion id="select_for_parameters" db-types="MySQL">
+ <rewrite-assertion id="select_for_parameters"
db-types="MySQL,PostgreSQL">
```
### Actual behavior
The error message when running the unit test EncryptSQLRewriterIT is as
follows:
```txt
java.lang.AssertionError:
Expected: is "like_query_aaa"
but: was "assisted_query_aaa"
```
### Reason analyze (If you can)
When using the LIKE operator in uppercase letters in SQL executed in
PostgreSQL, it can be correctly recognized and rewritten in the SQL, but when
mixed with lowercase letters, the rewriting of SQL will fail. The reason is
related to the sql-parser process. When `connection.prepareStatement` is
executed, PostgreSQLSelectStatement will parse
`sqlStatement.where.expr.operator` as a lowercase 'Like'. Therefore, when the
function `EncryptPredicateRightValueTokenGenerator@getEncryptedValues` is
executed, the LikeQueryColumn will be misjudged as an AssistedQueryColumn.
features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptPredicateRightValueTokenGenerator.java
```java
private List<Object> getEncryptedValues(final String schemaName, final
EncryptCondition encryptCondition, final List<Object> originalValues) {
if (encryptCondition instanceof EncryptBinaryCondition &&
"LIKE".equals(((EncryptBinaryCondition) encryptCondition).getOperator())) {
Optional<String> likeQueryColumn =
encryptRule.findLikeQueryColumn(encryptCondition.getTableName(),
encryptCondition.getColumnName());
if (!likeQueryColumn.isPresent()) {
throw new UnsupportedEncryptSQLException("LIKE");
} else {
return encryptRule.getEncryptLikeQueryValues(databaseName,
schemaName, encryptCondition.getTableName(), encryptCondition.getColumnName(),
originalValues);
}
}
Optional<String> assistedQueryColumn =
encryptRule.findAssistedQueryColumn(encryptCondition.getTableName(),
encryptCondition.getColumnName());
return assistedQueryColumn.isPresent()
? encryptRule.getEncryptAssistedQueryValues(databaseName,
schemaName, encryptCondition.getTableName(), encryptCondition.getColumnName(),
originalValues)
: encryptRule.getEncryptValues(databaseName, schemaName,
encryptCondition.getTableName(), encryptCondition.getColumnName(),
originalValues);
}
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
Step 1: Adding PostreSQL to the rewrite-assertion of encryption scenario.
```sh
$ git diff
test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
index 52b76eb0af8..008c008703b 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
@@ -17,7 +17,7 @@
-->
<rewrite-assertions
yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
- <rewrite-assertion id="select_for_parameters" db-types="MySQL">
+ <rewrite-assertion id="select_for_parameters"
db-types="MySQL,PostgreSQL">
<input sql="SELECT account_id, password, amount AS a, status AS s
FROM t_account WHERE account_id = ? AND password = ? AND password like ? AND
amount = ? AND status = ?" parameters="1, aaa, aaa, 1000, OK" />
<output sql="SELECT account_id, cipher_password AS password,
cipher_amount AS a, status AS s FROM t_account WHERE account_id = ? AND
assisted_query_password = ? AND like_query_password like ? AND cipher_amount =
? AND statu
s = ?" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK"
/>
</rewrite-assertion>
```
Step 2: The error message when running the unit test EncryptSQLRewriterIT is
as follows:
```txt
java.lang.AssertionError:
Expected: is "like_query_aaa"
but: was "assisted_query_aaa"
预期:like_query_aaa
实际:assisted_query_aaa
at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:8)
at
org.apache.shardingsphere.test.it.rewrite.engine.AbstractSQLRewriterIT.assertRewrite(AbstractSQLRewriterIT.java:101)
at jdk.internal.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
at
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at
org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runners.Suite.runChild(Suite.java:128)
at org.junit.runners.Suite.runChild(Suite.java:27)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at
com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
进程已结束,退出代码-1
```
### Example codes for reproduce this issue (such as a github link).
None
--
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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
