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 <[email protected]>
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 <[email protected]>
Signed-off-by: gxxiong <[email protected]>
---
...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>
