You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "wangtiga (via GitHub)" <gi...@apache.org> on 2023/04/06 09:56:30 UTC
[GitHub] [shardingsphere] wangtiga opened a new issue, #25032: In the encryption scenario of PostgreSQL, the LikeQueryColumn in the SQL statement is misjudged as an AssistedQueryColumn
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #25032: In the encryption scenario of PostgreSQL, the LikeQueryColumn in the SQL statement is misjudged as an AssistedQueryColumn
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #25032: In the encryption scenario of PostgreSQL, the LikeQueryColumn in the SQL statement is misjudged as an AssistedQueryColumn
URL: https://github.com/apache/shardingsphere/issues/25032
--
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] wangtiga commented on issue #25032: In the encryption scenario of PostgreSQL, the LikeQueryColumn in the SQL statement is misjudged as an AssistedQueryColumn
Posted by "wangtiga (via GitHub)" <gi...@apache.org>.
wangtiga commented on issue #25032:
URL: https://github.com/apache/shardingsphere/issues/25032#issuecomment-1498848520
I want to try to fix this problem and submit pull request already.
--
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