You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2022/12/05 10:01:45 UTC
[shardingsphere] branch master updated: add integration test case for encrypt like feature and fix add like columnName by insert (#22667)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 569b9ae5d3e add integration test case for encrypt like feature and fix add like columnName by insert (#22667)
569b9ae5d3e is described below
commit 569b9ae5d3ea5714c44675b26a2d1119d0393848
Author: gxxiong <xi...@foxmail.com>
AuthorDate: Mon Dec 5 18:01:31 2022 +0800
add integration test case for encrypt like feature and fix add like columnName by insert (#22667)
Signed-off-by: gxxiong <xi...@foxmail.com>
Signed-off-by: gxxiong <xi...@foxmail.com>
---
...ptForUseDefaultInsertColumnsTokenGenerator.java | 18 ++++++++--
.../EncryptInsertValuesTokenGenerator.java | 22 +++++++-----
...rUseDefaultInsertColumnsTokenGeneratorTest.java | 4 +--
.../case/query-with-cipher/dml/delete/delete.xml | 10 ++++++
.../query-with-cipher/dml/insert/insert-column.xml | 39 +++++++++++---------
.../dml/insert/insert-on-duplicate.xml | 33 ++++++++++++++---
.../dml/select/select-group-by.xml | 14 ++++++--
.../query-with-cipher/dml/select/select-join.xml | 32 +++++++++++++----
.../dml/select/select-order-by.xml | 18 +++++++++-
.../dml/select/select-projection.xml | 42 ++++++++++++++++------
.../query-with-cipher/dml/select/select-where.xml | 5 ---
.../case/query-with-cipher/dml/update/update.xml | 8 ++---
.../case/query-with-plain/dml/delete/delete.xml | 10 ++++++
.../query-with-plain/dml/insert/insert-column.xml | 26 +++++++-------
.../dml/insert/insert-on-duplicate.xml | 14 ++++++--
.../dml/select/select-group-by.xml | 32 +++++++++++++----
.../query-with-plain/dml/select/select-join.xml | 8 ++---
.../dml/select/select-order-by.xml | 21 +++++++++--
.../query-with-plain/dml/select/select-where.xml | 6 ----
.../case/query-with-plain/dml/update/update.xml | 34 ++++++++++++++++--
20 files changed, 298 insertions(+), 98 deletions(-)
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGenerator.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGenerator.java
index 6231244476b..754bbd5bd72 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGenerator.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGenerator.java
@@ -93,9 +93,19 @@ public final class EncryptForUseDefaultInsertColumnsTokenGenerator implements Op
String columnName = descendingColumnNames.next();
if (encryptTable.findEncryptorName(columnName).isPresent()) {
int columnIndex = result.indexOf(columnName);
- addPlainColumn(result, encryptTable, columnName, columnIndex);
- addAssistedQueryColumn(result, encryptTable, columnName, columnIndex);
setCipherColumn(result, encryptTable, columnName, columnIndex);
+ if (encryptTable.findAssistedQueryColumn(columnName).isPresent()) {
+ addAssistedQueryColumn(result, encryptTable, columnName, columnIndex);
+ columnIndex++;
+ }
+ if (encryptTable.findLikeQueryEncryptorName(columnName).isPresent()) {
+ addLikeQueryColumn(result, encryptTable, columnName, columnIndex);
+ columnIndex++;
+ }
+ if (encryptTable.findPlainColumn(columnName).isPresent()) {
+ addPlainColumn(result, encryptTable, columnName, columnIndex);
+ columnIndex++;
+ }
}
}
return result;
@@ -109,6 +119,10 @@ public final class EncryptForUseDefaultInsertColumnsTokenGenerator implements Op
encryptTable.findAssistedQueryColumn(columnName).ifPresent(optional -> columnNames.add(columnIndex + 1, optional));
}
+ private void addLikeQueryColumn(final List<String> columnNames, final EncryptTable encryptTable, final String columnName, final int columnIndex) {
+ encryptTable.findLikeQueryColumn(columnName).ifPresent(optional -> columnNames.add(columnIndex + 1, optional));
+ }
+
private void setCipherColumn(final List<String> columnNames, final EncryptTable encryptTable, final String columnName, final int columnIndex) {
columnNames.set(columnIndex, encryptTable.getCipherColumn(columnName));
}
diff --git a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGenerator.java b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGenerator.java
index 7986d10af2f..db3f85901fe 100644
--- a/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGenerator.java
+++ b/features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptInsertValuesTokenGenerator.java
@@ -137,37 +137,43 @@ public final class EncryptInsertValuesTokenGenerator implements OptionalSQLToken
.orElseGet(() -> insertStatementContext.getColumnNames().indexOf(columnName));
Object originalValue = insertValueContext.getLiteralValue(columnIndex).orElse(null);
EncryptContext encryptContext = EncryptContextBuilder.build(databaseName, schemaName, tableName, columnName);
- addPlainColumn(insertValueToken, columnIndex, encryptContext, insertValueContext, originalValue);
+ setCipherColumn(insertValueToken, encryptor.get(), columnIndex, encryptContext, insertValueContext.getValueExpressions().get(columnIndex), originalValue);
int indexDelta = 1;
if (encryptRule.findAssistedQueryEncryptor(tableName, columnName).isPresent()) {
- addAssistedQueryColumn(insertValueToken, encryptRule.findAssistedQueryEncryptor(tableName, columnName).get(), columnIndex, encryptContext, insertValueContext, originalValue);
- indexDelta = indexDelta + 1;
+ addAssistedQueryColumn(insertValueToken, encryptRule.findAssistedQueryEncryptor(tableName, columnName).get(), columnIndex, encryptContext,
+ insertValueContext, originalValue, indexDelta);
+ indexDelta++;
}
if (encryptRule.findLikeQueryEncryptor(tableName, columnName).isPresent()) {
addLikeQueryColumn(insertValueToken, encryptRule.findLikeQueryEncryptor(tableName, columnName).get(), columnIndex, encryptContext, insertValueContext, originalValue, indexDelta);
+ indexDelta++;
+ }
+ if (encryptRule.findPlainColumn(tableName, columnName).isPresent()) {
+ addPlainColumn(insertValueToken, columnIndex, encryptContext, insertValueContext, originalValue, indexDelta);
}
- setCipherColumn(insertValueToken, encryptor.get(), columnIndex, encryptContext, insertValueContext.getValueExpressions().get(columnIndex), originalValue);
}
}
}
private void addPlainColumn(final InsertValue insertValueToken, final int columnIndex,
- final EncryptContext encryptContext, final InsertValueContext insertValueContext, final Object originalValue) {
+ final EncryptContext encryptContext, final InsertValueContext insertValueContext,
+ final Object originalValue, final int indexDelta) {
if (encryptRule.findPlainColumn(encryptContext.getTableName(), encryptContext.getColumnName()).isPresent()) {
DerivedSimpleExpressionSegment derivedExpressionSegment = isAddLiteralExpressionSegment(insertValueContext, columnIndex)
? new DerivedLiteralExpressionSegment(originalValue)
: new DerivedParameterMarkerExpressionSegment(getParameterIndexCount(insertValueToken));
- insertValueToken.getValues().add(columnIndex + 1, derivedExpressionSegment);
+ insertValueToken.getValues().add(columnIndex + indexDelta, derivedExpressionSegment);
}
}
private void addAssistedQueryColumn(final InsertValue insertValueToken, final StandardEncryptAlgorithm encryptAlgorithm, final int columnIndex,
- final EncryptContext encryptContext, final InsertValueContext insertValueContext, final Object originalValue) {
+ final EncryptContext encryptContext, final InsertValueContext insertValueContext,
+ final Object originalValue, final int indexDelta) {
if (encryptRule.findAssistedQueryColumn(encryptContext.getTableName(), encryptContext.getColumnName()).isPresent()) {
DerivedSimpleExpressionSegment derivedExpressionSegment = isAddLiteralExpressionSegment(insertValueContext, columnIndex)
? new DerivedLiteralExpressionSegment(encryptAlgorithm.encrypt(originalValue, encryptContext))
: new DerivedParameterMarkerExpressionSegment(getParameterIndexCount(insertValueToken));
- insertValueToken.getValues().add(columnIndex + 1, derivedExpressionSegment);
+ insertValueToken.getValues().add(columnIndex + indexDelta, derivedExpressionSegment);
}
}
diff --git a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGeneratorTest.java b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGeneratorTest.java
index 8c1311dd275..2d2101ae9ff 100644
--- a/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGeneratorTest.java
+++ b/features/encrypt/core/src/test/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/EncryptForUseDefaultInsertColumnsTokenGeneratorTest.java
@@ -39,7 +39,7 @@ public final class EncryptForUseDefaultInsertColumnsTokenGeneratorTest extends E
EncryptForUseDefaultInsertColumnsTokenGenerator tokenGenerator = new EncryptForUseDefaultInsertColumnsTokenGenerator();
tokenGenerator.setEncryptRule(createEncryptRule());
tokenGenerator.setPreviousSQLTokens(Collections.emptyList());
- assertThat(tokenGenerator.generateSQLToken(createInsertStatementContext(Collections.emptyList())).toString(), is("(id, name, status, pwd_cipher, pwd_assist, pwd_plain)"));
+ assertThat(tokenGenerator.generateSQLToken(createInsertStatementContext(Collections.emptyList())).toString(), is("(id, name, status, pwd_cipher, pwd_assist, pwd_like, pwd_plain)"));
}
@Test
@@ -47,6 +47,6 @@ public final class EncryptForUseDefaultInsertColumnsTokenGeneratorTest extends E
EncryptForUseDefaultInsertColumnsTokenGenerator tokenGenerator = new EncryptForUseDefaultInsertColumnsTokenGenerator();
tokenGenerator.setEncryptRule(createEncryptRule());
tokenGenerator.setPreviousSQLTokens(getPreviousSQLTokens());
- assertThat(tokenGenerator.generateSQLToken(createInsertStatementContext(Collections.emptyList())).toString(), is("(id, name, status, pwd_cipher, pwd_assist, pwd_plain)"));
+ assertThat(tokenGenerator.generateSQLToken(createInsertStatementContext(Collections.emptyList())).toString(), is("(id, name, status, pwd_cipher, pwd_assist, pwd_like, pwd_plain)"));
}
}
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
index ab87593aab8..99ef9e65388 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
@@ -36,4 +36,14 @@
<input sql="DELETE FROM t_account_bak WHERE account_id = 1 AND password = 'aaa' AND password like 'aaa' AND amount = 1000 AND status = 'OK'" />
<output sql="DELETE FROM t_account_bak 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'" />
</rewrite-assertion>
+
+ <rewrite-assertion id="delete_table_with_alias_for_parameters" db-types="MySQL">
+ <input sql="DELETE t FROM t_account t WHERE t.account_id = ? AND t.password = ? AND t.password like ? AND t.amount = ? AND t.status = ?" parameters="1, aaa, aaa, 1000, OK" />
+ <output sql="DELETE t FROM t_account t WHERE t.account_id = ? AND t.assisted_query_password = ? AND t.like_query_password like ? AND t.cipher_amount = ? AND t.status = ?" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="delete_table_with_alias_for_literals" db-types="MySQL">
+ <input sql="DELETE t FROM t_account t WHERE t.account_id = 1 AND t.password = 'aaa' AND t.password like 'aaa' AND t.amount = 1000 AND t.status = 'OK'" />
+ <output sql="DELETE t FROM t_account t WHERE t.account_id = 1 AND t.assisted_query_password = 'assisted_query_aaa' AND t.like_query_password like 'like_query_aaa' AND t.cipher_amount = 'encrypt_1000' AND t.status = 'OK'" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
index 776f657598f..6afb2cbafa3 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
@@ -22,14 +22,19 @@
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_quer [...]
</rewrite-assertion>
- <rewrite-assertion id="insert_values_with_columns_for_parameters_with_plain_column" db-types="PostgreSQL">
+ <rewrite-assertion id="insert_values_with_columns_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK')" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_ [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_with_columns_for_parameters_with_plain_column_for_parameters" db-types="PostgreSQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?)" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" parameters="1, encrypt_111X, assisted_query_111X, like_query_111X, 111X, encrypt_aaa, assisted_query_aaa, like_query_aaa, aaa, encrypt_1000, 1000, OK" />
</rewrite-assertion>
- <rewrite-assertion id="insert_values_with_columns_for_literals" db-types="MySQL">
- <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK')" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_ [...]
+ <rewrite-assertion id="insert_values_with_columns_for_parameters_with_plain_column_for_literals" db-types="PostgreSQL">
+ <input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', '1000', 'OK')" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', '1000', 'OK')" />
</rewrite-assertion>
<rewrite-assertion id="insert_values_with_columns_and_configuration_for_different_sequence_for_parameters" db-types="MySQL">
@@ -54,22 +59,22 @@
<rewrite-assertion id="insert_values_without_columns_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (?, ?, ?, ?), (2, '222X', 'bbb', 2000), (?, ?, ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, aaa, 1000, 3, 333X, ccc, 3000" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'like_query_ddd', 'encrypt [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_dd [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_for_literals" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (1, '111X', 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, '333X', 'ccc', 3000), (4, '444X', 'ddd', 4000)" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_333X', [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_with_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (?, ?, ?, ?), (2, '222X', 'bbb', 2000), (?, ?, ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, aaa, 1000, 3, 333X, ccc, 3000" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assis [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_with_plain_for_literals" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (1, '111X', 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, '333X', 'ccc', 3000), (4, '444X', 'ddd', 4000)" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_q [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', ' [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_with_columns_for_parameters" db-types="MySQL">
@@ -104,27 +109,22 @@
<rewrite-assertion id="mixed_insert_values_without_columns_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (?, '111X', ?, ?), (2, '222X', 'bbb', 2000), (?, '333X', ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, aaa, 1000, 3, ccc, 3000" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (?, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, 'encrypt_333X', 'assisted_query_333X', 'like_query_333X', ?, ?, ?, ?), (4, 'encrypt_444X', 'assist [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (?, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, 'encrypt_333X', 'assisted_query_333X', 'like_q [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_for_literals" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (1, '111X', ?, 1000), (2, '222X', 'bbb', 2000), (3, '333X', ?, 3000), (4, '444X', 'ddd', 4000)" parameters="aaa, ccc" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_333X', 'like_query_333X', ?, ?, ?, 'encrypt_3000'), ( [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_3 [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (?, ?, 'aaa', ?), (2, '222X', 'bbb', 2000), (?, ?, 'ccc', ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, 1000, 3, 333X, 3000" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, ? [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_q [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_literals" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (1, ?, 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, ?, 'ccc', 3000), (4, '444X', 'ddd', 4000)" parameters="111X, 333X" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 20 [...]
- </rewrite-assertion>
-
- <rewrite-assertion id="insert_values_with_schema_based_algorithm_for_plain" db-types="MySQL">
- <input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (1, 1, 'TEST');" />
- <output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (1, 1, 'encrypt_TEST_t_order', 'TEST');" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_que [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_with_schema_based_algorithm_for_parameters" db-types="MySQL">
@@ -132,6 +132,11 @@
<output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (?, ?, ?, ?);" parameters="1, 1, encrypt_TEST_t_order, TEST" />
</rewrite-assertion>
+ <rewrite-assertion id="insert_values_with_schema_based_algorithm_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (1, 1, 'TEST');" />
+ <output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (1, 1, 'encrypt_TEST_t_order', 'TEST');" />
+ </rewrite-assertion>
+
<rewrite-assertion id="insert_values_with_null_encrypt_column_for_parameters" db-types="MySQL,PostgreSQL,openGauss">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', NULL, 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', NULL, 'OK')" parameters="1, NULL, aaa, 1000, OK, 3, 333X, NULL, 3000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', NULL, NULL, NULL, 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_ddd', 'assisted_que [...]
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
index 0d97dc54827..67e531a154a 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
@@ -17,15 +17,25 @@
-->
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
- <rewrite-assertion id="insert_values_on_duplicated_update_values" db-types="MySQL">
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE certificate_number = VALUES(certificate_number)" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE cipher_certificate_number = VALUES(cipher_certificate_number), assisted_query_certificate_number = VALUES(assisted_query_certificate_number), like_query_certificate_number = VALUES(like_query_certifi [...]
</rewrite-assertion>
- <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match" db-types="MySQL">
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', '1000', 'OK') ON DUPLICATE KEY UPDATE certificate_number = VALUES(certificate_number)" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK') ON DUPLICATE KEY UPDATE cipher_certificate_number = VALUES(cipher_certificate_number), assisted_query_certificat [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = VALUES(status)" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = VALUES(status)" parameters="1, encrypt_111X, assisted_query_111X, like_query_111X, encrypt_aaa, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', '1000', 'OK') ON DUPLICATE KEY UPDATE status = VALUES(status)" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK') ON DUPLICATE KEY UPDATE status = VALUES(status)" />
+ </rewrite-assertion>
<rewrite-assertion id="insert_values_with_on_duplicate_key_update_with_columns_with_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = ?" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK, ccc_update" />
@@ -37,18 +47,33 @@
<output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_ [...]
</rewrite-assertion>
- <rewrite-assertion id="insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized" db-types="MySQL">
+ <rewrite-assertion id="insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = ?" parameters="ccc_update" />
<output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_ [...]
</rewrite-assertion>
+ <rewrite-assertion id="insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = 'ccc_update'" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_ [...]
+ </rewrite-assertion>
+
<rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_columns_with_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (?, ?, 'aaa', ?, ?), (2, '222X', ?, 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = ?" parameters="1, 111X, 1000, OK, bbb, 3, 333X, ccc, 3000, OK, ccc_update" />
<output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (?, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', ?, ?, ?, ?, 'encrypt_2000', 2000, [...]
</rewrite-assertion>
- <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized" db-types="MySQL">
+ <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_columns_with_plain_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = 'ccc_update'" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_ [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, ?, 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, ?, 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = ?" parameters="111X, 333X, ccc_update" />
<output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', [...]
</rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = 'ccc_update'" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_ [...]
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
index 02c578e886d..6b118cf9d97 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
@@ -17,13 +17,23 @@
-->
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
- <rewrite-assertion id="select_with_groupby1" db-types="MySQL">
+ <rewrite-assertion id="select_with_groupby_assisted_for_parameters" db-types="MySQL">
<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 = ? group by password desc" 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 status = ? group by assisted_query_password desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_groupby2" db-types="MySQL">
+ <rewrite-assertion id="select_with_groupby_assisted_for_literals" db-types="MySQL">
+ <input 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' group by password desc" />
+ <output 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' group by assisted_query_password desc" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_groupby_cipher_for_parameters" db-types="MySQL">
<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 = ? group by amount desc" 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 status = ? group by cipher_amount desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_with_groupby_cipher_for_literals" db-types="MySQL">
+ <input 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' group by amount desc" />
+ <output 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' group by cipher_amount desc" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
index 358ecdc0b88..880a669529b 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
@@ -22,18 +22,38 @@
<output sql="SELECT a.cipher_password AS password from t_account a, t_account_bak b where a.account_id = b.account_id" />
</rewrite-assertion>
+ <rewrite-assertion id="select_join_with_alias_for_assisted" db-types="MySQL">
+ <input sql="SELECT a.password from t_account a, t_account_bak b where a.certificate_number = b.certificate_number" />
+ <output sql="SELECT a.cipher_password AS password from t_account a, t_account_bak b where a.assisted_query_certificate_number = b.assisted_query_certificate_number" />
+ </rewrite-assertion>
+
<rewrite-assertion id="select_join_with_table_name_for_cipher" db-types="MySQL">
<input sql="SELECT t_account.password from t_account, t_account_bak where t_account.account_id = t_account_bak.account_id" />
<output sql="SELECT t_account.cipher_password AS password from t_account, t_account_bak where t_account.account_id = t_account_bak.account_id" />
</rewrite-assertion>
- <rewrite-assertion id="select_unqualified_shorthand_projection_with_join" db-types="MySQL">
- <input sql="SELECT * FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.amount = ? OR b.amount = ?" parameters="1, 2" />
- <output sql="SELECT `t`.`account_id`, `t`.`cipher_certificate_number` AS `certificate_number`, `t`.`cipher_password` AS `password`, `t`.`cipher_amount` AS `amount`, `b`.`account_id`, `b`.`cipher_certificate_number` AS `certificate_number`, `b`.`cipher_password` AS `password`, `b`.`cipher_amount` AS `amount` FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.cipher_amount = ? OR b.cipher_amount = ?" parameters="encrypt_1, encrypt_2" />
+ <rewrite-assertion id="select_join_with_table_name_for_assisted" db-types="MySQL">
+ <input sql="SELECT t_account.password from t_account, t_account_bak where t_account.certificate_number = t_account_bak.certificate_number" />
+ <output sql="SELECT t_account.cipher_password AS password from t_account, t_account_bak where t_account.assisted_query_certificate_number = t_account_bak.assisted_query_certificate_number" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_unqualified_shorthand_projection_with_join_for_parameters" db-types="MySQL">
+ <input sql="SELECT * FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.password = ? OR b.amount = ? AND t.certificate_number like ?" parameters="1, 2, 3" />
+ <output sql="SELECT `t`.`account_id`, `t`.`cipher_certificate_number` AS `certificate_number`, `t`.`cipher_password` AS `password`, `t`.`cipher_amount` AS `amount`, `b`.`account_id`, `b`.`cipher_certificate_number` AS `certificate_number`, `b`.`cipher_password` AS `password`, `b`.`cipher_amount` AS `amount` FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.assisted_query_password = ? OR b.cipher_amount = ? AND t.like_query_certificate_number like ?" parameters="a [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_unqualified_shorthand_projection_with_join_for_literals" db-types="MySQL">
+ <input sql="SELECT * FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.password = 1 OR b.amount = 2 AND t.certificate_number like 3" />
+ <output sql="SELECT `t`.`account_id`, `t`.`cipher_certificate_number` AS `certificate_number`, `t`.`cipher_password` AS `password`, `t`.`cipher_amount` AS `amount`, `b`.`account_id`, `b`.`cipher_certificate_number` AS `certificate_number`, `b`.`cipher_password` AS `password`, `b`.`cipher_amount` AS `amount` FROM t_account t INNER JOIN t_account_bak b ON t.id = b.id WHERE t.assisted_query_password = 'assisted_query_1' OR b.cipher_amount = 'encrypt_2' AND t.like_query_certificate_n [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_join_for_parameters" db-types="MySQL">
+ <input sql="SELECT t_account.amount, t_account_bak.amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.password = ? OR t_account_bak.amount = ? AND t_account.certificate_number like ?" parameters="1, 2, 3" />
+ <output sql="SELECT t_account.cipher_amount AS amount, t_account_bak.cipher_amount AS amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.assisted_query_password = ? OR t_account_bak.cipher_amount = ? AND t_account.like_query_certificate_number like ?" parameters="assisted_query_1, encrypt_2, like_query_3" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_join" db-types="MySQL">
- <input sql="SELECT t_account.amount, t_account_bak.amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.amount = ? OR t_account_bak.amount = ?" parameters="1, 2" />
- <output sql="SELECT t_account.cipher_amount AS amount, t_account_bak.cipher_amount AS amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.cipher_amount = ? OR t_account_bak.cipher_amount = ?" parameters="encrypt_1, encrypt_2" />
+ <rewrite-assertion id="select_with_join_for_literals" db-types="MySQL">
+ <input sql="SELECT t_account.amount, t_account_bak.amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.password = 1 OR t_account_bak.amount = 2 AND t_account.certificate_number like 3" />
+ <output sql="SELECT t_account.cipher_amount AS amount, t_account_bak.cipher_amount AS amount FROM t_account LEFT JOIN t_account_bak ON t_account.id = t_account_bak.id WHERE t_account.assisted_query_password = 'assisted_query_1' OR t_account_bak.cipher_amount = 'encrypt_2' AND t_account.like_query_certificate_number like 'like_query_3'" />
</rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-order-by.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-order-by.xml
index 2823e4eed2b..1a12ead3396 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-order-by.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-order-by.xml
@@ -17,8 +17,24 @@
-->
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-cipher.yaml">
- <rewrite-assertion id="select_with_orderby2" db-types="MySQL">
+ <rewrite-assertion id="select_for_parameters_with_order_by_assisted" db-types="MySQL">
+ <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 = ? order by password desc" 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 status = ? order by assisted_query_password desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_for_literals_with_order_by_assisted" db-types="MySQL">
+ <input 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' order by password desc" />
+ <output 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' order by assisted_query_password desc" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_for_parameters_with_order_by_cipher" db-types="MySQL">
<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 = ? order by amount desc" 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 status = ? order by cipher_amount desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_for_literals_with_order_by_cipher" db-types="MySQL">
+ <input 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' order by amount desc" />
+ <output 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' order by cipher_amount desc" />
+ </rewrite-assertion>
+
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
index 0897219ca1a..e7097590a26 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
@@ -32,14 +32,24 @@
<output sql="SELECT `a`.`account_id`, `a`.`cipher_certificate_number` AS `certificate_number`, `a`.`cipher_password` AS `password`, `a`.`cipher_amount` AS `amount`, account_id, 1+1 FROM t_account_bak a" />
</rewrite-assertion>
- <rewrite-assertion id="select_for_predicate_and_right_value_should_be_matched_for_cipher" db-types="MySQL">
+ <rewrite-assertion id="select_for_predicate_and_right_value_should_be_matched_for_cipher_for_parameters" db-types="MySQL">
+ <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 status = ?" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_for_predicate_and_right_value_should_be_matched_for_cipher_for_literals" db-types="MySQL">
<input 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'" />
<output 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'" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_unqualified_shorthand" db-types="MySQL">
- <input sql="SELECT * FROM t_account" />
- <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account" />
+ <rewrite-assertion id="select_with_unqualified_shorthand_for_parameters" db-types="MySQL">
+ <input sql="SELECT * FROM t_account WHERE certificate_number = ? AND certificate_number LIKE ?" parameters="1, 1" />
+ <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account WHERE assisted_query_certificate_number = ? AND like_query_certificate_number LIKE ?" parameters="assisted_query_1, like_query_1" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_unqualified_shorthand_for_literals" db-types="MySQL">
+ <input sql="SELECT * FROM t_account WHERE certificate_number = 1 AND certificate_number LIKE 1" />
+ <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account WHERE assisted_query_certificate_number = 'assisted_query_1' AND like_query_certificate_number LIKE 'like_query_1'" />
</rewrite-assertion>
<rewrite-assertion id="select_with_qualified_shorthand" db-types="MySQL">
@@ -52,23 +62,33 @@
<output sql="SELECT `a`.`account_id`, `a`.`cipher_certificate_number` AS `certificate_number`, `a`.`cipher_password` AS `password`, `a`.`cipher_amount` AS `amount`, account_id, 1+1 FROM t_account a" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_schema_name_in_shorthand_projection" db-types="MySQL">
- <input sql="SELECT logic_db.t_account.* FROM t_account WHERE account_id = ?" parameters="100" />
- <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account WHERE account_id = ?" parameters="100" />
+ <rewrite-assertion id="select_with_schema_name_in_shorthand_projection_for_parameters" db-types="MySQL">
+ <input sql="SELECT logic_db.t_account.* FROM t_account WHERE account_id = ? AND certificate_number = ? AND certificate_number LIKE ?" parameters="100, 200, 300" />
+ <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account WHERE account_id = ? AND assisted_query_certificate_number = ? AND like_query_certificate_number LIKE ?" parameters="100, assisted_query_200, like_query_300" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_schema_name_in_shorthand_projection_for_literals" db-types="MySQL">
+ <input sql="SELECT logic_db.t_account.* FROM t_account WHERE account_id = 100 AND certificate_number = 200 AND certificate_number LIKE 300" />
+ <output sql="SELECT `t_account`.`account_id`, `t_account`.`cipher_certificate_number` AS `certificate_number`, `t_account`.`cipher_password` AS `password`, `t_account`.`cipher_amount` AS `amount` FROM t_account WHERE account_id = 100 AND assisted_query_certificate_number = 'assisted_query_200' AND like_query_certificate_number LIKE 'like_query_300'" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_schema_name_in_column_projection" db-types="MySQL">
+ <rewrite-assertion id="select_with_schema_name_in_column_projection_for_parameters" db-types="MySQL">
<input sql="SELECT logic_db.t_account.account_id FROM t_account WHERE account_id = ?" parameters="100" />
<output sql="SELECT t_account.account_id FROM t_account WHERE account_id = ?" parameters="100" />
</rewrite-assertion>
- <rewrite-assertion id="select_shorthand_with_upper_column_and_schema_based_algorithm_for_plain" db-types="MySQL">
- <input sql="SELECT * FROM t_order WHERE content = 'TEST'" />
- <output sql="SELECT `t_order`.`ORDER_ID`, `t_order`.`USER_ID`, `t_order`.`CIPHER_CONTENT` AS `CONTENT` FROM t_order WHERE CIPHER_CONTENT = 'encrypt_TEST_t_order'" />
+ <rewrite-assertion id="select_with_schema_name_in_column_projection_for_literals" db-types="MySQL">
+ <input sql="SELECT logic_db.t_account.account_id FROM t_account WHERE account_id = 100" />
+ <output sql="SELECT t_account.account_id FROM t_account WHERE account_id = 100" />
</rewrite-assertion>
<rewrite-assertion id="select_shorthand_with_upper_column_and_schema_based_algorithm_for_parameters" db-types="MySQL">
<input sql="SELECT * FROM t_order WHERE content = ?" parameters="TEST" />
<output sql="SELECT `t_order`.`ORDER_ID`, `t_order`.`USER_ID`, `t_order`.`CIPHER_CONTENT` AS `CONTENT` FROM t_order WHERE CIPHER_CONTENT = ?" parameters="encrypt_TEST_t_order" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_shorthand_with_upper_column_and_schema_based_algorithm_for_literals" db-types="MySQL">
+ <input sql="SELECT * FROM t_order WHERE content = 'TEST'" />
+ <output sql="SELECT `t_order`.`ORDER_ID`, `t_order`.`USER_ID`, `t_order`.`CIPHER_CONTENT` AS `CONTENT` FROM t_order WHERE CIPHER_CONTENT = 'encrypt_TEST_t_order'" />
+ </rewrite-assertion>
</rewrite-assertions>
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 64b539a8fa7..52b76eb0af8 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
@@ -22,11 +22,6 @@
<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 status = ?" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
- <rewrite-assertion id="select_with_orderby1" db-types="MySQL">
- <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 = ? order by password desc" 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 status = ? order by assisted_query_password desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
- </rewrite-assertion>
-
<rewrite-assertion id="select_for_literals" db-types="MySQL">
<input 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'" />
<output 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'" />
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
index 22a6c9de7cf..1325497ece5 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
@@ -80,8 +80,8 @@
</rewrite-assertion>
<rewrite-assertion id="update_null_to_clear_plain_where_is_null_with_multi" db-types="MySQL">
- <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NULL AND status = 'OK' AND certificate_number = '111X'" />
- <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE assisted_query_certificate_number IS NULL AND status = 'OK' AND assisted_query_certificate_number = 'assisted_query_111X'" />
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NULL AND status = 'OK' AND certificate_number = '111X' AND certificate_number like '111X'" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE assisted_query_certificate_number IS NULL AND status = 'OK' AND assisted_query_certificate_number = 'assisted_query_111X' AND like_query_certificate_number like 'like_query_111X'" />
</rewrite-assertion>
<rewrite-assertion id="update_null_to_clear_plain_where_is_not_null" db-types="MySQL">
@@ -90,7 +90,7 @@
</rewrite-assertion>
<rewrite-assertion id="update_null_to_clear_plain_where_is_not_null_with_multi" db-types="MySQL">
- <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NOT NULL AND status = 'OK' AND certificate_number = '111X'" />
- <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE assisted_query_certificate_number IS NOT NULL AND status = 'OK' AND assisted_query_certificate_number = 'assisted_query_111X'" />
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NOT NULL AND status = 'OK' AND certificate_number = '111X' AND certificate_number like '111X'" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE assisted_query_certificate_number IS NOT NULL AND status = 'OK' AND assisted_query_certificate_number = 'assisted_query_111X' AND like_query_certificate_number like 'like_query_111X'" />
</rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/delete/delete.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/delete/delete.xml
index cfa5615844a..bcd8a477ff4 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/delete/delete.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/delete/delete.xml
@@ -37,4 +37,14 @@
<input sql="DELETE FROM t_account_bak WHERE account_id = 1 AND password = 'aaa' AND password like 'aaa' AND amount = 1000 AND status = 'OK'" />
<output sql="DELETE FROM t_account_bak WHERE account_id = 1 AND plain_password = 'aaa' AND plain_password like 'aaa' AND plain_amount = 1000 AND status = 'OK'" />
</rewrite-assertion>
+
+ <rewrite-assertion id="delete_t_account_bak_with_alias_for_parameters" db-types="MySQL">
+ <input sql="DELETE t FROM t_account_bak t WHERE t.account_id = ? AND t.password = ? AND t.password like ? AND t.amount = ? AND t.status = ?" parameters="1, aaa, aaa, 1000, OK" />
+ <output sql="DELETE t FROM t_account_bak t WHERE t.account_id = ? AND t.plain_password = ? AND t.plain_password like ? AND t.plain_amount = ? AND t.status = ?" parameters="1, aaa, aaa, 1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="delete_t_account_bak_with_alias_for_literals" db-types="MySQL">
+ <input sql="DELETE t FROM t_account_bak t WHERE t.account_id = 1 AND t.password = 'aaa' AND t.password like 'aaa' AND t.amount = 1000 AND t.status = 'OK'" />
+ <output sql="DELETE t FROM t_account_bak t WHERE t.account_id = 1 AND t.plain_password = 'aaa' AND t.plain_password like 'aaa' AND t.plain_amount = 1000 AND t.status = 'OK'" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
index eed411a767d..d996dce5893 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-column.xml
@@ -49,22 +49,22 @@
<rewrite-assertion id="insert_values_without_columns_for_parameters_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (?, ?, ?, ?), (2, '222X', 'bbb', 2000), (?, ?, ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, aaa, 1000, 3, 333X, ccc, 3000" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'like_query_ddd', 'encrypt [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_dd [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_for_literals_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (1, '111X', 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, '333X', 'ccc', 3000), (4, '444X', 'ddd', 4000)" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_333X', [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_with_plain_for_parameters_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (?, ?, ?, ?), (2, '222X', 'bbb', 2000), (?, ?, ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, aaa, 1000, 3, 333X, ccc, 3000" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assis [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_without_columns_with_plain_for_literals_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (1, '111X', 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, '333X', 'ccc', 3000), (4, '444X', 'ddd', 4000)" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_q [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', ' [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_with_columns_for_parameters_for_plain" db-types="MySQL">
@@ -99,27 +99,22 @@
<rewrite-assertion id="mixed_insert_values_without_columns_for_parameters_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (?, '111X', ?, ?), (2, '222X', 'bbb', 2000), (?, '333X', ?, ?), (4, '444X', 'ddd', 4000)" parameters="1, aaa, 1000, 3, ccc, 3000" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (?, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, 'encrypt_333X', 'assisted_query_333X', 'like_query_333X', ?, ?, ?, ?), (4, 'encrypt_444X', 'assist [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (?, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (?, 'encrypt_333X', 'assisted_query_333X', 'like_q [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_for_literals_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account VALUES (1, '111X', ?, 1000), (2, '222X', 'bbb', 2000), (3, '333X', ?, 3000), (4, '444X', 'ddd', 4000)" parameters="aaa, ccc" />
- <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_333X', 'like_query_333X', ?, ?, ?, 'encrypt_3000'), ( [...]
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', ?, ?, ?, 'encrypt_1000'), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'encrypt_2000'), (3, 'encrypt_333X', 'assisted_query_3 [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_parameters_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (?, ?, 'aaa', ?), (2, '222X', 'bbb', 2000), (?, ?, 'ccc', ?), (4, '444X', 'ddd', 4000)" parameters="1, 111X, 1000, 3, 333X, 3000" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 2000), (?, ?, ?, ? [...]
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (?, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_q [...]
</rewrite-assertion>
<rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_literals_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account_bak VALUES (1, ?, 'aaa', 1000), (2, '222X', 'bbb', 2000), (3, ?, 'ccc', 3000), (4, '444X', 'ddd', 4000)" parameters="111X, 333X" />
- <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'like_query_bbb', 'bbb', 'encrypt_2000', 20 [...]
- </rewrite-assertion>
-
- <rewrite-assertion id="insert_values_with_schema_based_algorithm_for_plain_for_plain" db-types="MySQL">
- <input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (1, 1, 'TEST');" />
- <output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (1, 1, 'encrypt_TEST_t_order', 'TEST');" />
+ <output sql="INSERT INTO t_account_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, like_query_password, plain_password, cipher_amount, plain_amount) VALUES (1, ?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'aaa', 'encrypt_1000', 1000), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', '222X', 'encrypt_bbb', 'assisted_que [...]
</rewrite-assertion>
<rewrite-assertion id="insert_values_with_schema_based_algorithm_for_parameters_for_plain" db-types="MySQL">
@@ -127,6 +122,11 @@
<output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (?, ?, ?, ?);" parameters="1, 1, encrypt_TEST_t_order, TEST" />
</rewrite-assertion>
+ <rewrite-assertion id="insert_values_with_schema_based_algorithm_for_literals_for_plain" db-types="MySQL">
+ <input sql="INSERT INTO t_order(ORDER_ID, USER_ID, CONTENT) VALUES (1, 1, 'TEST');" />
+ <output sql="INSERT INTO t_order(ORDER_ID, USER_ID, CIPHER_CONTENT, PLAIN_CONTENT) VALUES (1, 1, 'encrypt_TEST_t_order', 'TEST');" />
+ </rewrite-assertion>
+
<rewrite-assertion id="insert_values_with_null_encrypt_column_for_plain" db-types="MySQL,PostgreSQL,openGauss">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', NULL, 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', NULL, 'OK')" parameters="1, NULL, aaa, 1000, OK, 3, 333X, NULL, 3000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', NULL, NULL, NULL, 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'like_query_444X', 'encrypt_ddd', 'assisted_que [...]
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-on-duplicate.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-on-duplicate.xml
index bcfa7ddb635..8bc32f31f29 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-on-duplicate.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/insert/insert-on-duplicate.xml
@@ -17,15 +17,25 @@
-->
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-plain.yaml">
- <rewrite-assertion id="insert_values_on_duplicated_update_values_for_plain" db-types="MySQL">
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_for_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE certificate_number = VALUES(certificate_number)" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE cipher_certificate_number = VALUES(cipher_certificate_number), assisted_query_certificate_number = VALUES(assisted_query_certificate_number), like_query_certificate_number = VALUES(like_query_certifi [...]
</rewrite-assertion>
- <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match_for_plain" db-types="MySQL">
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_for_plain_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK') ON DUPLICATE KEY UPDATE certificate_number = VALUES(certificate_number)" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK') ON DUPLICATE KEY UPDATE cipher_certificate_number = VALUES(cipher_certificate_number), assisted_query_certificat [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match_for_plain_for_parameters" db-types="MySQL">
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = VALUES(status)" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = VALUES(status)" parameters="1, encrypt_111X, assisted_query_111X, like_query_111X, encrypt_aaa, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_on_duplicated_update_values_wrong_match_for_plain_for_literals" db-types="MySQL">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK') ON DUPLICATE KEY UPDATE status = VALUES(status)" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, like_query_certificate_number, cipher_password, assisted_query_password, like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'like_query_aaa', 'encrypt_1000', 'OK') ON DUPLICATE KEY UPDATE status = VALUES(status)" />
+ </rewrite-assertion>
<rewrite-assertion id="insert_values_with_on_duplicate_key_update_with_columns_with_plain_for_parameters_for_plain" db-types="MySQL">
<input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK') ON DUPLICATE KEY UPDATE password = ?" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK, ccc_update" />
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-group-by.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-group-by.xml
index 56e95240edc..39ad9ad99ae 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-group-by.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-group-by.xml
@@ -18,14 +18,34 @@
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-plain.yaml">
<!-- TODO FIX ME!-->
-<!-- <rewrite-assertion id="select_with_groupby1_for_plain" db-types="MySQL">-->
-<!-- <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE account_id = ? AND password = ? AND amount = ? AND status = ? group by password desc" parameters="1, 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 cipher_amount = ? AND status = ? group by assisted_query_password desc" parameters="1, assisted_query_aaa, encrypt_1000, OK" />-->
+<!-- <rewrite-assertion id="select_with_groupby_assisted_for_parameters_for_plain" db-types="MySQL">-->
+<!-- <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 = ? group by password desc" 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 status = ? group by assisted_query_password desc" parameters="1, assisted_query_aaa, like_query_aaa encrypt_1000, OK" />-->
<!-- </rewrite-assertion>-->
+ <rewrite-assertion id="select_with_groupby_assisted_for_literals_for_plain" db-types="MySQL">
+ <input 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' group by password desc" />
+ <output 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' group by assisted_query_password desc" />
+ </rewrite-assertion>
+
<!-- TODO FIX ME!-->
-<!-- <rewrite-assertion id="select_with_groupby2_for_plain" db-types="MySQL">-->
-<!-- <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE account_id = ? AND password = ? AND amount = ? AND status = ? group by amount desc" parameters="1, 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 cipher_amount = ? AND status = ? group by cipher_amount desc" parameters="1, assisted_query_aaa, encrypt_1000, OK" />-->
+<!-- <rewrite-assertion id="select_with_groupby_cipher_for_parameters_for_plain" db-types="MySQL">-->
+<!-- <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 = ? group by amount desc" 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 status = ? group by cipher_amount desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />-->
<!-- </rewrite-assertion>-->
+
+ <rewrite-assertion id="select_with_groupby_cipher_for_literals_for_plain" db-types="MySQL">
+ <input 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' group by amount desc" />
+ <output 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' group by cipher_amount desc" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_groupby_plain_for_parameters_for_plain" db-types="MySQL">
+ <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account_bak WHERE account_id = ? AND password = ? AND password like ? AND amount = ? AND status = ? group by amount desc" parameters="1, aaa, aaa, 1000, OK" />
+ <output sql="SELECT account_id, plain_password AS password, plain_amount AS a, status AS s FROM t_account_bak WHERE account_id = ? AND plain_password = ? AND plain_password like ? AND plain_amount = ? AND status = ? group by plain_amount desc" parameters="1, aaa, aaa, 1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_with_groupby_plain_for_literals_for_plain" db-types="MySQL">
+ <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account_bak WHERE account_id = 1 AND password = 'aaa' AND password like 'aaa' AND amount = 1000 AND status = 'OK' group by amount desc" />
+ <output sql="SELECT account_id, plain_password AS password, plain_amount AS a, status AS s FROM t_account_bak WHERE account_id = 1 AND plain_password = 'aaa' AND plain_password like 'aaa' AND plain_amount = 1000 AND status = 'OK' group by plain_amount desc" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-join.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-join.xml
index 07861296792..2fb06a02712 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-join.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-join.xml
@@ -18,13 +18,13 @@
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-plain.yaml">
<rewrite-assertion id="select_join_with_alias_for_plain" db-types="MySQL">
- <input sql="SELECT a.password from t_account a, t_account_bak b where a.account_id = b.account_id" />
- <output sql="SELECT a.cipher_password AS password from t_account a, t_account_bak b where a.account_id = b.account_id" />
+ <input sql="SELECT a.password from t_account a, t_account_bak b where a.certificate_number = b.certificate_number" />
+ <output sql="SELECT a.cipher_password AS password from t_account a, t_account_bak b where a.assisted_query_certificate_number = b.plain_certificate_number" />
</rewrite-assertion>
<rewrite-assertion id="select_join_with_table_name_for_plain" db-types="MySQL">
- <input sql="SELECT t_account.password from t_account, t_account_bak where t_account.account_id = t_account_bak.account_id" />
- <output sql="SELECT t_account.cipher_password AS password from t_account, t_account_bak where t_account.account_id = t_account_bak.account_id" />
+ <input sql="SELECT t_account.password from t_account, t_account_bak where t_account.certificate_number = t_account_bak.certificate_number" />
+ <output sql="SELECT t_account.cipher_password AS password from t_account, t_account_bak where t_account.assisted_query_certificate_number = t_account_bak.plain_certificate_number" />
</rewrite-assertion>
<!-- TODO FIX ME!-->
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-order-by.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-order-by.xml
index 199c6a28423..f55da541ded 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-order-by.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-order-by.xml
@@ -18,8 +18,23 @@
<rewrite-assertions yaml-rule="scenario/encrypt/config/query-with-plain.yaml">
<!-- TODO FIX ME!-->
-<!-- <rewrite-assertion id="select_with_orderby2_for_plain" db-types="MySQL">-->
-<!-- <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE account_id = ? AND password = ? AND amount = ? AND status = ? order by amount desc" parameters="1, 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 cipher_amount = ? AND status = ? order by cipher_amount desc" parameters="1, assisted_query_aaa, encrypt_1000, OK" />-->
+<!-- <rewrite-assertion id="select_plain_for_parameters_with_order_by_cipher" db-types="MySQL">-->
+<!-- <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 = ? order by amount desc" 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 status = ? order by cipher_amount desc" parameters="1, assisted_query_aaa, like_query_aaa, encrypt_1000, OK" />-->
<!-- </rewrite-assertion>-->
+
+ <rewrite-assertion id="select_plain_for_literals_with_order_by_cipher" db-types="MySQL">
+ <input 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' order by amount desc" />
+ <output 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' order by cipher_amount desc" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_plain_for_parameters_with_order_by_plain" db-types="MySQL">
+ <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account_bak WHERE account_id = ? AND password = ? AND password like ? AND amount = ? AND status = ? order by amount desc" parameters="1, aaa, aaa, 1000, OK" />
+ <output sql="SELECT account_id, plain_password AS password, plain_amount AS a, status AS s FROM t_account_bak WHERE account_id = ? AND plain_password = ? AND plain_password like ? AND plain_amount = ? AND status = ? order by plain_amount desc" parameters="1, aaa, aaa, 1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_plain_for_literals_with_order_by_plain" db-types="MySQL">
+ <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account_bak WHERE account_id = 1 AND password = 'aaa' AND password like 'aaa' AND amount = '1000' AND status = 'OK' order by amount desc" />
+ <output sql="SELECT account_id, plain_password AS password, plain_amount AS a, status AS s FROM t_account_bak WHERE account_id = 1 AND plain_password = 'aaa' AND plain_password like 'aaa' AND plain_amount = '1000' AND status = 'OK' order by plain_amount desc" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-where.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-where.xml
index 3926a231da9..6a743747ac6 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-where.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/select/select-where.xml
@@ -21,12 +21,6 @@
<!-- <rewrite-assertion id="select_for_parameters" db-types="MySQL">-->
<!-- <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE account_id = ? AND password = ? AND amount = ? AND status = ?" parameters="1, 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 cipher_amount = ? AND status = ?" parameters="1, assisted_query_aaa, encrypt_1000, OK" />-->
-<!-- </rewrite-assertion>-->
-
-<!-- TODO FIX ME!-->
-<!-- <rewrite-assertion id="select_with_orderby1" db-types="MySQL">-->
-<!-- <input sql="SELECT account_id, password, amount AS a, status AS s FROM t_account WHERE account_id = ? AND password = ? AND amount = ? AND status = ? order by password desc" parameters="1, 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 cipher_amount = ? AND status = ? order by assisted_query_password desc" parameters="1, assisted_query_aaa, encrypt_1000, OK" />-->
<!-- </rewrite-assertion>-->
<rewrite-assertion id="select_for_literals" db-types="MySQL">
diff --git a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/update/update.xml b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/update/update.xml
index 72d31a6b9cf..c2ac558712f 100644
--- a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/update/update.xml
+++ b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-plain/dml/update/update.xml
@@ -29,8 +29,8 @@
<!-- TODO FIX ME-->
<!-- <rewrite-assertion id="update_for_parameters" db-types="MySQL">-->
-<!-- <input sql="UPDATE t_account SET account_id = ?, certificate_number = ?, password = ?, amount = ?, status = ? WHERE account_id = ? AND certificate_number = ? AND password = ? AND amount = ? AND status = ?" parameters="1, 111X, aaa, 1000, OK, 1, 111X, aaa, 1000, OK" />-->
-<!-- <output sql="UPDATE t_account SET account_id = ?, cipher_certificate_number = ?, assisted_query_certificate_number = ?, cipher_password = ?, assisted_query_password = ?, cipher_amount = ?, status = ? WHERE account_id = ? AND assisted_query_certificate_number = ? AND assisted_query_password = ? AND cipher_amount = ? AND status = ?" parameters="1, encrypt_111X, assisted_query_111X, encrypt_aaa, assisted_query_aaa, encrypt_1000, OK, 1, assisted_query_111X, assisted_query_aaa, en [...]
+<!-- <input sql="UPDATE t_account SET account_id = ?, certificate_number = ?, password = ?, amount = ?, status = ? WHERE account_id = ? AND certificate_number = ? AND password = ? AND password like ? AND amount = ? AND status = ?" parameters="1, 111X, aaa, 1000, OK, 1, 111X, aaa, aaa, 1000, OK" />-->
+<!-- <output sql="UPDATE t_account SET account_id = ?, cipher_certificate_number = ?, assisted_query_certificate_number = ?, like_query_certificate_number = ?, cipher_password = ?, assisted_query_password = ?, like_query_password = ?, cipher_amount = ?, status = ? WHERE account_id = ? AND assisted_query_certificate_number = ? AND assisted_query_password = ? AND like_query_password like ? AND cipher_amount = ? AND status = ?" parameters="1, encrypt_111X, assisted_query_111X, like_q [...]
<!-- </rewrite-assertion>-->
<rewrite-assertion id="update_for_literals" db-types="MySQL">
@@ -48,4 +48,34 @@
<input sql="UPDATE t_account_bak SET account_id = 1, certificate_number = '111X', password = 'aaa', amount = 1000, status = 'OK' WHERE account_id = 1 AND certificate_number = '111X' AND password = 'aaa' AND password like 'aaa' AND amount = 1000 AND status = 'OK'" />
<output sql="UPDATE t_account_bak SET account_id = 1, cipher_certificate_number = 'encrypt_111X', assisted_query_certificate_number = 'assisted_query_111X', like_query_certificate_number = 'like_query_111X', plain_certificate_number = '111X', cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', like_query_password = 'like_query_aaa', plain_password = 'aaa', cipher_amount = 'encrypt_1000', plain_amount = 1000, status = 'OK' WHERE account_id = 1 AND plai [...]
</rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain_with_multi" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL, certificate_number = ''" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL, cipher_certificate_number = 'encrypt_', assisted_query_certificate_number = 'assisted_query_', like_query_certificate_number = 'like_query_', plain_certificate_number = ''" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain_where_is_null" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NULL" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE plain_certificate_number IS NULL" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain_where_is_null_with_multi" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NULL AND status = 'OK' AND certificate_number = '111X' AND certificate_number like '111X'" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE plain_certificate_number IS NULL AND status = 'OK' AND plain_certificate_number = '111X' AND plain_certificate_number like '111X'" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain_where_is_not_null" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NOT NULL" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE plain_certificate_number IS NOT NULL" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="update_plain_null_to_clear_plain_where_is_not_null_with_multi" db-types="MySQL">
+ <input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE certificate_number IS NOT NULL AND status = 'OK' AND certificate_number = '111X' AND certificate_number like '111X'" />
+ <output sql="UPDATE t_account_bak SET cipher_certificate_number = NULL, assisted_query_certificate_number = NULL, like_query_certificate_number = NULL, plain_certificate_number = NULL WHERE plain_certificate_number IS NOT NULL AND status = 'OK' AND plain_certificate_number = '111X' AND plain_certificate_number like '111X'" />
+ </rewrite-assertion>
</rewrite-assertions>