You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2020/06/17 02:45:59 UTC
[shardingsphere] branch master updated: fix(encrypt): Fixed
issues#5979 (#5985)
This is an automated email from the ASF dual-hosted git repository.
panjuan 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 57047ec fix(encrypt): Fixed issues#5979 (#5985)
57047ec is described below
commit 57047eca59612192a89742b05a13d025425b47e8
Author: DemonLms <30...@users.noreply.github.com>
AuthorDate: Wed Jun 17 10:45:46 2020 +0800
fix(encrypt): Fixed issues#5979 (#5985)
* fix(encrypt): Fixed issues#5979
Fixed data encryption can not properly processing SQL mixed with `?` and
literal. And add some test cases for mixed `?` and literal.
Closes #5979
* pref: Get the parameter index using an existing implementation.
* pref: Optimize instert SQL rewrite logic for mixing letters and parameters: literals insert literals, parameters insert parameters.
* pref: Extract duplicate code as a function.
Co-authored-by: Your Name <yo...@example.com>
Co-authored-by: Demon <de...@liuguopingdeiMac.local>
---
.../impl/EncryptInsertValueParameterRewriter.java | 34 ++++---
.../impl/EncryptInsertValuesTokenGenerator.java | 9 +-
.../src/test/resources/encrypt/insert.xml | 106 ++++++++++++++++++---
.../segment/insert/values/InsertValueContext.java | 11 +++
4 files changed, 131 insertions(+), 29 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/parameter/impl/EncryptInsertValueParameterRewriter.java b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/parameter/impl/EncryptInsertValueParameterRewriter.java
index fff861c..0f1632f 100644
--- a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/parameter/impl/EncryptInsertValueParameterRewriter.java
+++ b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/parameter/impl/EncryptInsertValueParameterRewriter.java
@@ -26,6 +26,8 @@ import org.apache.shardingsphere.infra.rewrite.parameter.builder.impl.GroupedPar
import org.apache.shardingsphere.infra.rewrite.parameter.builder.impl.StandardParameterBuilder;
import org.apache.shardingsphere.sql.parser.binder.statement.SQLStatementContext;
import org.apache.shardingsphere.sql.parser.binder.statement.dml.InsertStatementContext;
+import org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.ExpressionSegment;
+import org.apache.shardingsphere.sql.parser.sql.segment.dml.expr.simple.ParameterMarkerExpressionSegment;
import java.util.ArrayList;
import java.util.Collection;
@@ -38,12 +40,12 @@ import java.util.Optional;
* Insert value parameter rewriter for encrypt.
*/
public final class EncryptInsertValueParameterRewriter extends EncryptParameterRewriter<InsertStatementContext> {
-
+
@Override
protected boolean isNeedRewriteForEncrypt(final SQLStatementContext sqlStatementContext) {
return sqlStatementContext instanceof InsertStatementContext && !(((InsertStatementContext) sqlStatementContext).getSqlStatement()).getSetAssignment().isPresent();
}
-
+
@Override
public void rewrite(final ParameterBuilder parameterBuilder, final InsertStatementContext insertStatementContext, final List<Object> parameters) {
String tableName = insertStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
@@ -54,21 +56,26 @@ public final class EncryptInsertValueParameterRewriter extends EncryptParameterR
encryptAlgorithm -> encryptInsertValues((GroupedParameterBuilder) parameterBuilder, insertStatementContext, encryptAlgorithm, tableName, columnName));
}
}
-
+
private void encryptInsertValues(final GroupedParameterBuilder parameterBuilder,
final InsertStatementContext insertStatementContext, final EncryptAlgorithm encryptAlgorithm, final String tableName, final String encryptLogicColumnName) {
int columnIndex = getColumnIndex(parameterBuilder, insertStatementContext, encryptLogicColumnName);
int count = 0;
for (List<Object> each : insertStatementContext.getGroupedParameters()) {
+ int paramterIndex = insertStatementContext.getInsertValueContexts().get(count).getParameterIndex(columnIndex);
if (!each.isEmpty()) {
StandardParameterBuilder standardParameterBuilder = parameterBuilder.getParameterBuilders().get(count);
- encryptInsertValue(
- encryptAlgorithm, tableName, columnIndex, insertStatementContext.getInsertValueContexts().get(count).getValue(columnIndex), standardParameterBuilder, encryptLogicColumnName);
+ ExpressionSegment expressionSegment = insertStatementContext.getInsertValueContexts().get(count).getValueExpressions().get(columnIndex);
+ if (expressionSegment instanceof ParameterMarkerExpressionSegment) {
+ encryptInsertValue(
+ encryptAlgorithm, tableName, paramterIndex, insertStatementContext.getInsertValueContexts().get(count).getValue(columnIndex),
+ standardParameterBuilder, encryptLogicColumnName);
+ }
}
count++;
}
}
-
+
private int getColumnIndex(final GroupedParameterBuilder parameterBuilder, final InsertStatementContext insertStatementContext, final String encryptLogicColumnName) {
List<String> columnNames;
if (parameterBuilder.getDerivedColumnName().isPresent()) {
@@ -79,13 +86,10 @@ public final class EncryptInsertValueParameterRewriter extends EncryptParameterR
}
return columnNames.indexOf(encryptLogicColumnName);
}
-
- private void encryptInsertValue(final EncryptAlgorithm encryptAlgorithm, final String tableName, final int columnIndex,
+
+ private void encryptInsertValue(final EncryptAlgorithm encryptAlgorithm, final String tableName, final int paramterIndex,
final Object originalValue, final StandardParameterBuilder parameterBuilder, final String encryptLogicColumnName) {
- // FIXME: can process all part of insert value is ? or literal, can not process mix ? and literal
- // For example: values (?, ?), (1, 1) can process
- // For example: values (?, 1), (?, 2) can not process
- parameterBuilder.addReplacedParameters(columnIndex, encryptAlgorithm.encrypt(originalValue));
+ parameterBuilder.addReplacedParameters(paramterIndex, encryptAlgorithm.encrypt(originalValue));
Collection<Object> addedParameters = new LinkedList<>();
if (encryptAlgorithm instanceof QueryAssistedEncryptAlgorithm) {
Optional<String> assistedColumnName = getEncryptRule().findAssistedQueryColumn(tableName, encryptLogicColumnName);
@@ -96,10 +100,10 @@ public final class EncryptInsertValueParameterRewriter extends EncryptParameterR
addedParameters.add(originalValue);
}
if (!addedParameters.isEmpty()) {
- if (!parameterBuilder.getAddedIndexAndParameters().containsKey(columnIndex + 1)) {
- parameterBuilder.getAddedIndexAndParameters().put(columnIndex + 1, new LinkedList<>());
+ if (!parameterBuilder.getAddedIndexAndParameters().containsKey(paramterIndex + 1)) {
+ parameterBuilder.getAddedIndexAndParameters().put(paramterIndex + 1, new LinkedList<>());
}
- parameterBuilder.getAddedIndexAndParameters().get(columnIndex + 1).addAll(addedParameters);
+ parameterBuilder.getAddedIndexAndParameters().get(paramterIndex + 1).addAll(addedParameters);
}
}
}
diff --git a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptInsertValuesTokenGenerator.java b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptInsertValuesTokenGenerator.java
index 84588a5..0e0bfe4 100644
--- a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptInsertValuesTokenGenerator.java
+++ b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/impl/EncryptInsertValuesTokenGenerator.java
@@ -133,7 +133,7 @@ public final class EncryptInsertValuesTokenGenerator extends BaseEncryptSQLToken
private void addPlainColumn(final InsertValue insertValueToken, final int columnIndex,
final String tableName, final String columnName, final InsertValueContext insertValueContext, final Object originalValue) {
if (getEncryptRule().findPlainColumn(tableName, columnName).isPresent()) {
- DerivedSimpleExpressionSegment derivedExpressionSegment = insertValueContext.getParameters().isEmpty()
+ DerivedSimpleExpressionSegment derivedExpressionSegment = isAddLiteralExpressionSegment(insertValueContext, columnIndex)
? new DerivedLiteralExpressionSegment(originalValue) : new DerivedParameterMarkerExpressionSegment(getParameterIndexCount(insertValueToken));
insertValueToken.getValues().add(columnIndex + 1, derivedExpressionSegment);
}
@@ -142,12 +142,17 @@ public final class EncryptInsertValuesTokenGenerator extends BaseEncryptSQLToken
private void addAssistedQueryColumn(final InsertValue insertValueToken, final EncryptAlgorithm encryptAlgorithm, final int columnIndex,
final String tableName, final String columnName, final InsertValueContext insertValueContext, final Object originalValue) {
if (getEncryptRule().findAssistedQueryColumn(tableName, columnName).isPresent()) {
- DerivedSimpleExpressionSegment derivedExpressionSegment = insertValueContext.getParameters().isEmpty()
+ DerivedSimpleExpressionSegment derivedExpressionSegment = isAddLiteralExpressionSegment(insertValueContext, columnIndex)
? new DerivedLiteralExpressionSegment(((QueryAssistedEncryptAlgorithm) encryptAlgorithm).queryAssistedEncrypt(null == originalValue ? null : originalValue.toString()))
: new DerivedParameterMarkerExpressionSegment(getParameterIndexCount(insertValueToken));
insertValueToken.getValues().add(columnIndex + 1, derivedExpressionSegment);
}
}
+
+ private boolean isAddLiteralExpressionSegment(final InsertValueContext insertValueContext, final int columnIndex) {
+ return insertValueContext.getParameters().isEmpty()
+ || insertValueContext.getValueExpressions().get(columnIndex) instanceof LiteralExpressionSegment;
+ }
private int getParameterIndexCount(final InsertValue insertValueToken) {
int result = 0;
diff --git a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/test/resources/encrypt/insert.xml b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/test/resources/encrypt/insert.xml
index bc41a79..32c5e84 100644
--- a/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/test/resources/encrypt/insert.xml
+++ b/shardingsphere-features/shardingsphere-encrypt/shardingsphere-encrypt-rewrite/src/test/resources/encrypt/insert.xml
@@ -21,12 +21,12 @@
<input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'encrypt_4000', 'OK')" parameters="1, encrypt_111X, assisted_query_111X, e [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_with_columns_for_literals">
<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, cipher_password, assisted_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (3, 'encrypt_333X', 'assisted_query_333X', 'encrypt_ccc', 'assisted_query_ccc', 'encrypt_3000', ' [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_with_columns_and_configuration_for_different_sequence_for_parameters">
<input sql="INSERT INTO t_account(password, certificate_number, account_id, amount, status) VALUES (?, ?, ?, ?, ?), ('bbb', '222X', 2, 2000, 'OK'), (?, ?, ?, ?, ?), ('ddd', '444X', 4, 4000, 'OK')" parameters="aaa, 111X, 1, 1000, OK, ccc, 333X, 3, 3000, OK" />
<output sql="INSERT INTO t_account(cipher_password, assisted_query_password, cipher_certificate_number, assisted_query_certificate_number, account_id, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?), ('encrypt_bbb', 'assisted_query_bbb', 'encrypt_222X', 'assisted_query_222X', 2, 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?), ('encrypt_ddd', 'assisted_query_ddd', 'encrypt_444X', 'assisted_query_444X', 4, 'encrypt_4000', 'OK')" parameters="encrypt_aaa, assisted_query_aaa, encryp [...]
@@ -36,7 +36,7 @@
<input sql="INSERT INTO t_account(password, certificate_number, account_id, amount, status) VALUES ('aaa', '111X', 1, 1000, 'OK'), ('bbb', '222X', 2, 2000, 'OK'), ('ccc', '333X', 3, 3000, 'OK'), ('ddd', '444X', 4, 4000, 'OK')" />
<output sql="INSERT INTO t_account(cipher_password, assisted_query_password, cipher_certificate_number, assisted_query_certificate_number, account_id, cipher_amount, status) VALUES ('encrypt_aaa', 'assisted_query_aaa', 'encrypt_111X', 'assisted_query_111X', 1, 'encrypt_1000', 'OK'), ('encrypt_bbb', 'assisted_query_bbb', 'encrypt_222X', 'assisted_query_222X', 2, 'encrypt_2000', 'OK'), ('encrypt_ccc', 'assisted_query_ccc', 'encrypt_333X', 'assisted_query_333X', 3, 'encrypt_3000', ' [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_with_columns_with_plain_for_parameters">
<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')" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK" />
<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, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', '444X', 'enc [...]
@@ -56,49 +56,131 @@
<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, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'O [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_with_columns_with_plain_for_literals">
<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')" />
<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, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'O [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_without_columns_for_parameters">
<input sql="INSERT INTO t_account VALUES (?, ?, ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK" />
<output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'encrypt_4000', 'OK')" parameters="1, encrypt_111X, assisted_query_111X, e [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_without_columns_for_literals">
<input sql="INSERT INTO t_account 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, cipher_password, assisted_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', 'encrypt_aaa', 'assisted_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (3, 'encrypt_333X', 'assisted_query_333X', 'encrypt_ccc', 'assisted_query_ccc', 'encrypt_3000', ' [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_without_columns_with_plain_for_parameters">
<input sql="INSERT INTO t_account_bak VALUES (?, ?, ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, 111X, aaa, 1000, OK, 3, 333X, ccc, 3000, OK" />
<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, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', '444X', 'enc [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_values_without_columns_with_plain_for_literals">
<input sql="INSERT INTO t_account_bak 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_bak(account_id, cipher_certificate_number, assisted_query_certificate_number, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', '111X', 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'O [...]
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_set_for_parameters" db-type="MySQL">
<input sql="INSERT INTO t_account SET account_id = ?, certificate_number = ?, password = ?, amount = ?, status = ?" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account SET account_id = ?, cipher_certificate_number = ?, assisted_query_certificate_number = ?, cipher_password = ?, assisted_query_password = ?, cipher_amount = ?, status = ?" parameters="1, encrypt_111X, assisted_query_111X, encrypt_aaa, assisted_query_aaa, encrypt_1000, OK" />
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_set_for_literals" db-type="MySQL">
<input sql="INSERT INTO t_account SET account_id = 1, certificate_number = '111X', password = 'aaa', amount = 1000, status = 'OK'" />
<output sql="INSERT INTO t_account SET account_id = 1, cipher_certificate_number = 'encrypt_111X', assisted_query_certificate_number = 'assisted_query_111X', cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', cipher_amount = 'encrypt_1000', status = 'OK'" />
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_set_with_plain_for_parameters" db-type="MySQL">
<input sql="INSERT INTO t_account_bak SET account_id = ?, certificate_number = ?, password = ?, amount = ?, status = ?" parameters="1, 111X, aaa, 1000, OK" />
<output sql="INSERT INTO t_account_bak SET account_id = ?, cipher_certificate_number = ?, assisted_query_certificate_number = ?, plain_certificate_number = ?, cipher_password = ?, assisted_query_password = ?, plain_password = ?, cipher_amount = ?, plain_amount = ?, status = ?" parameters="1, encrypt_111X, assisted_query_111X, 111X, encrypt_aaa, assisted_query_aaa, aaa, encrypt_1000, 1000, OK" />
</rewrite-assertion>
-
+
<rewrite-assertion id="insert_set_with_plain_for_literals" db-type="MySQL">
<input sql="INSERT INTO t_account_bak SET account_id = 1, certificate_number = '111X', password = 'aaa', amount = 1000, status = 'OK'" />
<output sql="INSERT INTO t_account_bak SET account_id = 1, cipher_certificate_number = 'encrypt_111X', assisted_query_certificate_number = 'assisted_query_111X', plain_certificate_number = '111X', cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', plain_password = 'aaa', cipher_amount = 'encrypt_1000', plain_amount = 1000, status = 'OK'" />
</rewrite-assertion>
+
+ <!-- insert values mixed parameters and literals-->
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_for_parameters">
+ <input sql="INSERT INTO t_account(account_id, certificate_number, password, amount, status) VALUES (?, '111X', ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, '333X', ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, aaa, 1000, OK, 3, ccc, 3000, OK" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount, status) VALUES (?, 'encrypt_111X', 'assisted_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (?, 'encrypt_333X', 'assisted_query_333X', ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'encrypt [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_for_literals">
+ <input sql="INSERT INTO t_account(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')" parameters="111X, 333X"/>
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount, status) VALUES (1, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (3, ?, ?, 'encrypt_ccc', 'assisted_query_ccc', 'encrypt_3000', 'OK'), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd', 'a [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_and_configuration_for_different_sequence_for_parameters">
+ <input sql="INSERT INTO t_account(password, certificate_number, account_id, amount, status) VALUES (?, ?, ?, 1000, ?), ('bbb', '222X', 2, 2000, 'OK'), (?, ?, ?, 3000, ?), ('ddd', '444X', 4, 4000, 'OK')" parameters="aaa, 111X, 1, OK, ccc, 333X, 3, OK" />
+ <output sql="INSERT INTO t_account(cipher_password, assisted_query_password, cipher_certificate_number, assisted_query_certificate_number, account_id, cipher_amount, status) VALUES (?, ?, ?, ?, ?, 'encrypt_1000', ?), ('encrypt_bbb', 'assisted_query_bbb', 'encrypt_222X', 'assisted_query_222X', 2, 'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, 'encrypt_3000', ?), ('encrypt_ddd', 'assisted_query_ddd', 'encrypt_444X', 'assisted_query_444X', 4, 'encrypt_4000', 'OK')" parameters="encrypt_aaa, [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_and_configuration_for_different_sequence_for_literals">
+ <input sql="INSERT INTO t_account(password, certificate_number, account_id, amount, status) VALUES ('aaa', ?, 1, 1000, 'OK'), ('bbb', '222X', 2, 2000, 'OK'), ('ccc', ?, 3, 3000, 'OK'), ('ddd', '444X', 4, 4000, 'OK')" parameters="111X, 333X"/>
+ <output sql="INSERT INTO t_account(cipher_password, assisted_query_password, cipher_certificate_number, assisted_query_certificate_number, account_id, cipher_amount, status) VALUES ('encrypt_aaa', 'assisted_query_aaa', ?, ?, 1, 'encrypt_1000', 'OK'), ('encrypt_bbb', 'assisted_query_bbb', 'encrypt_222X', 'assisted_query_222X', 2, 'encrypt_2000', 'OK'), ('encrypt_ccc', 'assisted_query_ccc', ?, ?, 3, 'encrypt_3000', 'OK'), ('encrypt_ddd', 'assisted_query_ddd', 'encrypt_444X', 'assis [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_with_plain_for_parameters">
+ <input sql="INSERT INTO t_account_bak(account_id, certificate_number, password, amount, status) VALUES (?, ?, ?, ?, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, ?, ?, 'OK'), (4, '444X', 'ddd', 4000, 'OK')" 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, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?, 'OK'), (4, 'encrypt_444X', 'assisted_query_444X', '444X' [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_columns_with_plain_for_parameters" db-type="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, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'aaa', ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', '222X', ?, ?, ?, 'encrypt_2000', 2000, 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', '444X', 'enc [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_on_duplicate_key_update_with_insert_value_literals_and_on_duplicate_parameterized" db-type="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, plain_certificate_number, cipher_password, assisted_query_password, plain_password, cipher_amount, plain_amount, status) VALUES (1, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (3, ?, ?, ?, 'encrypt_ccc', 'assi [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_with_columns_with_plain_for_literals">
+ <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')" 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, status) VALUES (1, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (3, ?, ?, ?, 'encrypt_ccc', 'assi [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_without_columns_for_parameters">
+ <input sql="INSERT INTO t_account VALUES (?, '111X', ?, ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, '333X', ?, ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, aaa, 1000, OK, 3, ccc, 3000, OK" />
+ <output sql="INSERT INTO t_account(account_id, cipher_certificate_number, assisted_query_certificate_number, cipher_password, assisted_query_password, cipher_amount, status) VALUES (?, 'encrypt_111X', 'assisted_query_111X', ?, ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (?, 'encrypt_333X', 'assisted_query_333X', ?, ?, ?, ?), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd', 'assisted_query_ddd', 'encrypt [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_without_columns_for_literals">
+ <input sql="INSERT INTO t_account VALUES (1, '111X', ?, 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, '333X', ?, 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK')" 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, status) VALUES (1, 'encrypt_111X', 'assisted_query_111X', ?, ?, 'encrypt_1000', 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', 'encrypt_bbb', 'assisted_query_bbb', 'encrypt_2000', 'OK'), (3, 'encrypt_333X', 'assisted_query_333X', ?, ?, 'encrypt_3000', 'OK'), (4, 'encrypt_444X', 'assisted_query_444X', 'encrypt_ddd' [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_parameters">
+ <input sql="INSERT INTO t_account_bak VALUES (?, ?, 'aaa', ?, ?), (2, '222X', 'bbb', 2000, 'OK'), (?, ?, 'ccc', ?, ?), (4, '444X', 'ddd', 4000, 'OK')" parameters="1, 111X, 1000, OK, 3, 333X, 3000, OK" />
+ <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, status) VALUES (?, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'aaa', ?, ?, ?), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (?, ?, ?, ?, 'encrypt_ccc', 'assisted_query_ccc', 'c [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_values_without_columns_with_plain_for_literals">
+ <input sql="INSERT INTO t_account_bak VALUES (1, ?, 'aaa', 1000, 'OK'), (2, '222X', 'bbb', 2000, 'OK'), (3, ?, 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000, 'OK')" 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, status) VALUES (1, ?, ?, ?, 'encrypt_aaa', 'assisted_query_aaa', 'aaa', 'encrypt_1000', 1000, 'OK'), (2, 'encrypt_222X', 'assisted_query_222X', '222X', 'encrypt_bbb', 'assisted_query_bbb', 'bbb', 'encrypt_2000', 2000, 'OK'), (3, ?, ?, ?, 'encrypt_ccc', 'assi [...]
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_set_for_parameters" db-type="MySQL">
+ <input sql="INSERT INTO t_account SET account_id = 1, certificate_number = ?, password = ?, amount = ?, status = ?" parameters="111X, aaa, 1000, OK" />
+ <output sql="INSERT INTO t_account SET account_id = 1, cipher_certificate_number = ?, assisted_query_certificate_number = ?, cipher_password = ?, assisted_query_password = ?, cipher_amount = ?, status = ?" parameters="encrypt_111X, assisted_query_111X, encrypt_aaa, assisted_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_set_for_literals" db-type="MySQL">
+ <input sql="INSERT INTO t_account SET account_id = 1, certificate_number = ?, password = 'aaa', amount = 1000, status = 'OK'" parameters="111X"/>
+ <output sql="INSERT INTO t_account SET account_id = 1, cipher_certificate_number = ?, assisted_query_certificate_number = ?, cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', cipher_amount = 'encrypt_1000', status = 'OK'" parameters="encrypt_111X, assisted_query_111X"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_set_with_plain_for_parameters" db-type="MySQL">
+ <input sql="INSERT INTO t_account_bak SET account_id = ?, certificate_number = ?, password = 'aaa', amount = ?, status = ?" parameters="1, 111X, 1000, OK" />
+ <output sql="INSERT INTO t_account_bak SET account_id = ?, cipher_certificate_number = ?, assisted_query_certificate_number = ?, plain_certificate_number = ?, cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', plain_password = 'aaa', cipher_amount = ?, plain_amount = ?, status = ?" parameters="1, encrypt_111X, assisted_query_111X, 111X, encrypt_1000, 1000, OK" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="mixed_insert_set_with_plain_for_literals" db-type="MySQL">
+ <input sql="INSERT INTO t_account_bak SET account_id = 1, certificate_number = '111X', password = 'aaa', amount = ?, status = 'OK'" parameters="1000"/>
+ <output sql="INSERT INTO t_account_bak SET account_id = 1, cipher_certificate_number = 'encrypt_111X', assisted_query_certificate_number = 'assisted_query_111X', plain_certificate_number = '111X', cipher_password = 'encrypt_aaa', assisted_query_password = 'assisted_query_aaa', plain_password = 'aaa', cipher_amount = ?, plain_amount = ?, status = 'OK'" parameters="encrypt_1000, 1000"/>
+ </rewrite-assertion>
</rewrite-assertions>
diff --git a/shardingsphere-sql-parser/shardingsphere-sql-parser-binder/src/main/java/org/apache/shardingsphere/sql/parser/binder/segment/insert/values/InsertValueContext.java b/shardingsphere-sql-parser/shardingsphere-sql-parser-binder/src/main/java/org/apache/shardingsphere/sql/parser/binder/segment/insert/values/InsertValueContext.java
index dc21c3a..e76df71 100644
--- a/shardingsphere-sql-parser/shardingsphere-sql-parser-binder/src/main/java/org/apache/shardingsphere/sql/parser/binder/segment/insert/values/InsertValueContext.java
+++ b/shardingsphere-sql-parser/shardingsphere-sql-parser-binder/src/main/java/org/apache/shardingsphere/sql/parser/binder/segment/insert/values/InsertValueContext.java
@@ -82,6 +82,17 @@ public final class InsertValueContext {
ExpressionSegment valueExpression = valueExpressions.get(index);
return valueExpression instanceof ParameterMarkerExpressionSegment ? parameters.get(getParameterIndex(valueExpression)) : ((LiteralExpressionSegment) valueExpression).getLiterals();
}
+
+ /**
+ * Get parameter index via column index.
+ *
+ * @param index column index
+ * @return parameter index
+ */
+ public int getParameterIndex(final int index) {
+ ExpressionSegment valueExpression = valueExpressions.get(index);
+ return getParameterIndex(valueExpression);
+ }
private int getParameterIndex(final ExpressionSegment valueExpression) {
int result = 0;