You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "wsc20190102 (via GitHub)" <gi...@apache.org> on 2023/02/28 10:47:03 UTC
[GitHub] [shardingsphere] wsc20190102 opened a new issue, #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
wsc20190102 opened a new issue, #24386:
URL: https://github.com/apache/shardingsphere/issues/24386
## Question
shardingsphere 5.1.0 whether support "=" operator in sql with cipher column? spring data jpa in use
for example,email is the cipher column
`
select * from sys_user where email = "";
`
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24386:
URL: https://github.com/apache/shardingsphere/issues/24386#issuecomment-1637906576
Long time no response.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wsc20190102 commented on issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "wsc20190102 (via GitHub)" <gi...@apache.org>.
wsc20190102 commented on issue #24386:
URL: https://github.com/apache/shardingsphere/issues/24386#issuecomment-1447982532
this is my config ,but it can not work @strongduanmu
`
rules:
encrypt: # 加密规则配置
query-with-cipher-column: true # 设置查询是否使用密文列
encryptors:
mysharding-encryptor:
type: MYAES # 声明加密处理器的类型
props:
aes-key-value: ${com.sns.file-encrypt-key}
tables:
sys_user:
columns:
email:
cipher-column: email
encryptor-name: mysharding-encryptor
mobile:
cipher-column: mobile
encryptor-name: mysharding-encryptor
query-with-cipher-column: true # 设置查询是否使用密文列
`
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu closed issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
URL: https://github.com/apache/shardingsphere/issues/24386
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] FlyingZC commented on issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC commented on issue #24386:
URL: https://github.com/apache/shardingsphere/issues/24386#issuecomment-1447993439
Can you test it with the native JDBC and format this SQL.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24386:
URL: https://github.com/apache/shardingsphere/issues/24386#issuecomment-1447970503
Of course, you can try to test it.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wsc20190102 commented on issue #24386: shardingsphere 5.1.0 whether support "=" operator in sql with cipher column?
Posted by "wsc20190102 (via GitHub)" <gi...@apache.org>.
wsc20190102 commented on issue #24386:
URL: https://github.com/apache/shardingsphere/issues/24386#issuecomment-1447990493
this is the sql with spring data jpa
`
@Query(nativeQuery = true,
value = "SELECT " +
"u.id AS id, " +
"u.create_date AS createDate, " +
"u.update_date AS updateDate, " +
"u.user_name AS userName, " +
"u.disable AS disable, " +
"u.email AS email, " +
"ent.org_name AS orgName, " +
"escrow.escrow_org_name AS escrowOrgName, " +
"GROUP_CONCAT(r.role_name) AS roleName "+
"FROM sys_user AS u " +
"LEFT JOIN nbms_ent AS ent " +
"ON u.id = ent.user_id AND ent.is_deleted = 0 " +
"LEFT JOIN nbms_escrow AS escrow " +
"ON u.escrow_id = escrow.id " +
"LEFT JOIN sys_user_role AS ur " +
"ON u.id = ur.user_id "+
"LEFT JOIN sys_role AS r " +
"ON ur.role_id = r.id "+
"WHERE if( :isEscrowSelected = 0, u.escrow_id IS NULL , u.escrow_id IS NOT NULL ) " +
"AND if( :roleId IS NOT NULL, ur.role_id = :roleId, 1=1 ) "+
"AND if( :userName != '', u.user_name LIKE CONCAT('%',:userName,'%'), 1=1 ) " +
"AND if( :orgName != '', ent.org_name LIKE CONCAT('%',:orgName,'%'), 1=1 ) " +
"AND if( :escrowOrgName != '', escrow.escrow_org_name LIKE CONCAT('%',:escrowOrgName,'%'), 1=1 ) " +
"AND if( :email != '', u.email = :email, 1=1 ) " +
"AND if( :beginDate IS NOT NULL, u.create_date >= :beginDate, 1=1 ) " +
"AND if( :endDate IS NOT NULL, u.create_date <= :endDate, 1=1 ) " +
"GROUP BY id,org_name,escrow_org_name " +
"ORDER BY createDate DESC ",
countQuery =
"SELECT count(*) " +
"FROM sys_user AS u " +
"LEFT JOIN nbms_ent AS ent " +
"ON u.id = ent.user_id AND ent.is_deleted = 0 " +
"LEFT JOIN nbms_escrow AS escrow " +
"ON u.escrow_id = escrow.id " +
"LEFT JOIN sys_user_role AS ur " +
"ON u.id = ur.user_id "+
"LEFT JOIN sys_role AS r " +
"ON ur.role_id = r.id "+
"WHERE if( :isEscrowSelected = 0, u.escrow_id IS NULL , u.escrow_id IS NOT NULL ) " +
"AND if( :roleId IS NOT NULL, ur.role_id = :roleId, 1=1 ) "+
"AND if( :userName != '', u.user_name LIKE CONCAT('%',:userName,'%'), 1=1 ) " +
"AND if( :orgName != '', ent.org_name LIKE CONCAT('%',:orgName,'%'), 1=1 ) " +
"AND if( :escrowOrgName != '', escrow.escrow_org_name LIKE CONCAT('%',:escrowOrgName,'%'), 1=1 ) " +
"AND if( :email != '', u.email = :email, 1=1 ) " +
"AND if( :beginDate IS NOT NULL, u.create_date >= :beginDate, 1=1 ) " +
"AND if( :endDate IS NOT NULL, u.create_date <= :endDate, 1=1 ) ")
Page<SysUserEscrowDTO> pageQueryAll(@Param("isEscrowSelected") Integer isEscrowSelected,
@Param("userName") String userName,
@Param("roleId") Integer roleId,
@Param("orgName") String orgName,
@Param("escrowOrgName") String escrowOrgName,
@Param("email") String email,
@Param("beginDate") Date beginDate,
@Param("endDate") Date endDate,
@Param("pageable") Pageable pageable);
`
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org