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;