An Issue when working with the encryption feature

[yuki-mirai]

Bug Report

In data insertion scene, when I enabling both cipher and assistedQuery, the order of sql columns and parameters will mismatch, finally will cause type mismatching.

Which version of ShardingSphere did you use?

shardingsphere-jdbc:5.5.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

The order of sql column names should corresponding to the parameters ordering.

Actual behavior

2025-10-10T17:14:09.688+08:00 INFO 86600 --- [shardingdb] [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: insert into t_order (address_id,amount,order_time,order_type,status,user_id,username) values (?,?,?,?,?,?,?)
2025-10-10T17:14:09.688+08:00 INFO 86600 --- [shardingdb] [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: ds_0 ::: insert into t_order_2 (address_id,amount,order_time,order_type,status,user_id,username, order_id, assisted_username) values (?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1, 23.4, 2025-10-05 00:00:00.0, 1, PENDING, null, 9qZSEAI39lWoUL9H5t/1QA==, 481701fa4093499eacd05b57bebc7ffc, 1183456636337913856]
2025-10-10T17:14:10.010+08:00 WARN 86600 --- [shardingdb] [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1265, SQLState: 01000
2025-10-10T17:14:10.010+08:00 ERROR 86600 --- [shardingdb] [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : Data truncated for column 'order_id' at row 1
2025-10-10T17:14:10.017+08:00 ERROR 86600 --- [shardingdb] [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.orm.jpa.JpaSystemException: could not execute statement [Data truncated for column 'order_id' at row 1] [insert into t_order (address_id,amount,order_time,order_type,status,user_id,username) values (?,?,?,?,?,?,?)]] with root cause

java.sql.SQLException: Data truncated for column 'order_id' at row 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:612) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:320) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1166) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1101) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1448) ~[mysql-connector-j-9.4.0.jar:9.4.0]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1084) ~[mysql-connector-j-9.4.0.jar:9.4.0]

PS: the literal '1183456636337913856' is generates by snowflake algorithm which is numerical, and should be order_id's value, and the value '481701fa4093499eacd05b57bebc7ffc' is assisted_username's value which is generates with md5.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Runtime Environment/Framework Information:

Oracle JDK: 21
SpringBoot: 3.5.6
SpringBoot Starter Data Jpa: 3.5.6
MySQL: 8.0.32 MySQL Community Server

DDL for bug reproducing

-- four sharding tables in two datasources(t_order_0,t_order_2 in ds_0, the remainning in ds_1) respectively, here is the common structure of the sharding tables.
create table t_order_0
(
    order_id          bigint                              not null comment '订单ID（主键）'
        primary key,
    order_type        smallint                            not null comment '订单类型',
    user_id           bigint                              null comment '用户ID',
    address_id        bigint                              not null comment '配送地址ID',
    amount            decimal(10, 2)                      not null comment '订单金额（保留两位小数）',
    status            varchar(50)                         not null comment '订单状态',
    order_time        timestamp default CURRENT_TIMESTAMP not null comment '下单时间',
    username          varchar(100)                        null,
    assisted_username varchar(100)                        null
)
    comment '订单表';

Sharding Configuration:

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_0
    username: root
    password: 12345678
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 600000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 20
    minPoolSize: 5

  ds_1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/ds_1
    username: root
    password: 12345678
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 600000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 20
    minPoolSize: 5

rules:
  - !SHARDING
    autoTables:
      t_order:
        actualDataSources: ds_${0..1}
        shardingStrategy:
          standard:
            shardingColumn: order_id
            shardingAlgorithmName: order_mod_sharding_algo

        keyGenerateStrategy:
          column: order_id
          keyGeneratorName: snowflake

    shardingAlgorithms:
      order_mod_sharding_algo:
        type: HASH_MOD
        props:
          sharding-count: 4

    keyGenerators:
      snowflake:
        type: SNOWFLAKE
  - !ENCRYPT
    tables:
      t_order:
        columns:
          username:
            cipher:
              name: username
              encryptorName: aes_encryptor
            assistedQuery:
              name: assisted_username
              encryptorName: assisted_encryptor

    encryptors:
      aes_encryptor:
        type: AES
        props:
          aes-key-value: 123456abc
          digest-algorithm-name: SHA-1
      assisted_encryptor:
        type: MD5

props:
  sql-show: true