encrypt error when sql has CONCAT_WS

[yanwencheng]

shardingsphere-jdbc-core: 5.3.2
mysql：8.0.24

When I set cipherColumn as name,the select statement contains CONCAT_WS。The decryption method is not performed

[strongduanmu]

Hi @yanwencheng, can you try the latest 5.5.0?

[yanwencheng]

Hi @yanwencheng, can you try the latest 5.5.0?

Doesn't look like it's in maven repository

[strongduanmu]

You can search shardingsphere-jdnc.

[yanwencheng]

You can search shardingsphere-jdnc.

i have try the latest 5.5.0 ，the same issue. and find a new issue if encrypt column is phone ,init select sql

SELECT COUNT(*) FROM (SELECT * FROM t_sys_user WHERE is_deleted = 0 AND id NOT IN (SELECT user_id FROM t_sys_user_role t1 LEFT JOIN t_sys_role t2 ON t1.role_id = t2.id WHERE t2.enname = 'ROLE_ADMIN')) t1 WHERE t1.company_id IN (?, ?, ?, ?, ?)

sharding-sql log error

Cause: java.sql.SQLSyntaxErrorException: Duplicate column name 'phone'

SELECT COUNT(*) FROM (SELECT t_sys_user.`post_ids`, t_sys_user.`dept_id`, t_sys_user.`id`, t_sys_user.`user_name`, t_sys_user.`password`, t_sys_user.`phone` AS `phone`, t_sys_user.`phone`, t_sys_user.`state`, t_sys_user.`real_name` AS `real_name`, t_sys_user.`real_name`, t_sys_user.`type`, t_sys_user.`company_id`, t_sys_user.`company_code`, t_sys_user.`over_time`, t_sys_user.`position_id`, t_sys_user.`creator`, t_sys_user.`create_time`, t_sys_user.`updater`, t_sys_user.`update_time`, t_sys_user.`is_deleted`, t_sys_user.`init_password`, t_sys_user.`password_date` FROM t_sys_user WHERE is_deleted = 0 AND id NOT IN (SELECT user_id FROM t_sys_user_role t1 LEFT JOIN t_sys_role t2 ON t1.role_id = t2.id WHERE t2.enname = 'ROLE_ADMIN')) t1 WHERE t1.company_id IN (?, ?, ?, ?, ?) ::: [1824654233383866370, 1824684768466440194, 1833488354902151170, 1833691625633746946, 1824647647248650242]

[yanwencheng]

You can search `shardingsphere-jdnc

When using a CASE statement within an SQL SELECT query to conditionally return different encrypted fields such as user_name or phone and aliasing the result, it's possible that the encryption/decryption does not work as expected.

        SELECT
       *,
        CASE
        WHEN user_name IS NOT NULL THEN user_name 
        WHEN phone IS NOT NULL THEN phone 
        END AS companyName
        FROM
        xxxx

[aillamsun]

You can search shardingsphere-jdnc.

unable to find 5.5.0. do you have an address?

[strongduanmu]

creator

Can you show your configuration? It looks like your plaintext column and ciphertext column are incorrectly configured with the same name.

[strongduanmu]

You can search shardingsphere-jdnc.

unable to find 5.5.0. do you have an address?

You can get latest version here——https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc/5.5.0

[strongduanmu]

shardingsphere-jdbc-core: 5.3.2 mysql：8.0.24

When I set cipherColumn as name,the select statement contains CONCAT_WS。The decryption method is not performed

Currently ShardingSphere does not support the use of encrypted columns in functions, inluding CONCAT_WS. Let's see if there are community volunteers to improve this feature.

[strongduanmu]

Duplicate with #32835