sql function cipherColumn not performed

[yanwencheng]

shardingsphere-jdbc-core: 5.5.0
mysql：8.0.24

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

the same issue is 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.

[terrymanu]

Can you provide the SQL for example?

[yanwencheng]

Can you provide the SQL for example?

1.the encrypt filed is phone ,user_name
concat_ws sql like

SELECT
	CONCAT_WS( '-', user_name, phone ) 
FROM
	t_sys_user

case sql like

SELECT
CASE
		
	WHEN
		user_name THEN
			user_name ELSE phone 
			END AS result 
FROM
	t_sys_user

The decrypted data cannot be returned

[terrymanu]

I am sorry to reply can not support encrypt column in function expression for now.

[thicv]

@yanwencheng The encryption feature in ShardingSphere currently only supports simple column encryption and decryption, and does not support functions, expressions, or other complex SQL encryption or decryption. The SphereEx commercial edition has a database UDF (User-Defined Function) feature that can adapt to more complex SQL encryption and decryption based on UDFs. Recommand your have a try : https://www.sphere-ex.cn/
开源版本加密功能目前只支持简单列加解密，不支持函数，表达式等复杂 SQL 加解密。SphereEx 的商业版研发了数据库 UDF 功能，能够基于 UDF 适配更多复杂的 SQL 加解密。

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.