[GitHub] [incubator-shardingsphere] HongJinFeng opened a new issue #3618: Encryp-JDBC rewrite wrong sql when sql contain character '('

[GitBox <gi...@apache.org>]

HongJinFeng opened a new issue #3618: Encryp-JDBC rewrite wrong sql when sql contain character '('
URL: https://github.com/apache/incubator-shardingsphere/issues/3618
 
 
   Encrypt-JDBC rewrite wrong sql when sql contain character '('. 
   
   
   Version of ShardingSphere
   4.0.0-RC3
   
   JDBC I use
   Encrypt-JDBC
   
   Expected behavior
   Rewrite the sql correct which is contain character '('
   
   Actual behavior
   The rewrite sql error when sql contain character '('
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   1.MySQL table and encrypt configuration
   table：
   create table t_demo{
   username varchar(20),
   phone varchar(20),
   username_encrypt(64),
   phone_encrypt(64)
   }
   encrypt configuration
   spring.shardingsphere.datasource.name = ds
   spring.shardingsphere.datasource.ds.type = com.alibaba.druid.pool.DruidDataSource
   spring.shardingsphere.datasource.ds.driver-class-name = com.mysql.jdbc.Driver
   spring.shardingsphere.datasource.ds.url = xxx
   spring.shardingsphere.datasource.ds.username = xxx
   spring.shardingsphere.datasource.ds.password = xxx
   spring.shardingsphere.encrypt.encryptors.encryptor_aes.type = AES
   spring.shardingsphere.encrypt.encryptors.encryptor_aes.props.aes.key.value =
   spring.shardingsphere.encrypt.tables.t_demo.columns.username.plainColumn = username
   spring.shardingsphere.encrypt.tables.t_demo.columns.username.cipherColumn = username_encrypt
   spring.shardingsphere.encrypt.tables.t_demo.columns.phone.plainColumn = phone
   spring.shardingsphere.encrypt.tables.t_demo.columns.phone.cipherColumn = phone_encrypt
   spring.shardingsphere.encrypt.tables.t_demo.columns.phone.encryptor = encryptor_aes
   spring.shardingsphere.encrypt.tables.t_demo.columns.username.encryptor = encryptor_aes
   
   2. My dao method definition
   List<User> query(@Param("phoneList") List<String> phoneList);
   
   3. My mybatis sql
   <select id="query" resultMap="xxx">
           SELECT * FROM t_demo t
           WHERE
           (t.username IN
           <foreach collection="phoneList" open="(" separator="," close=")" item="phone">
               #{phone}
           </foreach>
           OR t.phone IN
           <foreach collection="phoneList" open="(" separator="," close=")" item="phone">
               #{phone}
           </foreach>)
           AND delete = 0
   </select>
   
   4. Encrypt-JDBC rewrite sql(The rewrite sql has one moer left bracket)
   select * from t_demo t where (t.username_encrypt in (('xxx','xxxx') or t.phone_encrypt in (('xxx','xxxx')) and delete = 0;
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services