Belo-betty opened a new issue, #33408:
URL: https://github.com/apache/shardingsphere/issues/33408
#### sharding version：
org.apache.shardingsphere
shardingsphere-jdbc
5.5.1
### snakeYaml version：
org.yaml
snakeyaml
2.2
### sharding.yaml
```yaml
mode:
type: Standalone
repository:
type: JDBC
dataSources:
unique_ds:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://localhost:3306/sharding_sphere?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC&useSSL=false
username: root
password: 123456
rules:
- !ENCRYPT
tables:
user:
columns:
phone:
cipher:
name: phone_cipher
encryptorName: aes_encryptor
id_card_no:
cipher:
name: id_card_no_cipher
encryptorName: aes_encryptor
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
digest-algorithm-name: SHA-1
```
### mysql
```mysql
create table user
(
id bigint auto_increment
primary key,
name varchar(32) not null comment '姓名',
phone varchar(16) null comment '手机号',
id_card_no varchar(32) null comment '身份证',
phone_cipher varchar(50) null comment '密文手机号',
id_card_no_cipher varchar(50) null comment '密文身份证'
)
comment '用户' charset = utf8;
```
### db data
### search
```java
@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long> {
@Query(value = "select distinct phone from user",
nativeQuery = true)
List<String> listDistinctPhone();
@Query(value = "select *\n" +
"from user\n" +
"where 1 = 1\n" +
" and if(:phone is not null, phone = :phone, 1=1)",
nativeQuery = true)
List<UserEntity> listByPhoneIfJudge(@Param("phone") String phone);
@Query(value = "select count(case when phone is not null then 1 end)
total\n" +
"from user",
nativeQuery = true)
Long countPhoneCaseWhen();
}
```
### expected result
1. listByPhoneIfJudge method
The expected result is simulated by me after removing the if judgment
```java
@Query(value = "select *\n" +
"from user\n" +
"where 1 = 1\n" +
// " and if(:phone is not null, phone = :phone, 1=1)",
" and phone = :phone",
nativeQuery = true)
List<UserEntity> listByPhoneIfJudge(@Param("phone") String phone);
```
2. countPhoneCaseWhen method
3
### actual result
1. listByPhoneIfJudge method
2. countPhoneCaseWhen method
1
### possible conclusions
It seems that in special condition, the encrypted column has not been parsed
into a ciphertext column.
--
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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
