You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/03/01 04:07:33 UTC
[GitHub] [shardingsphere] tuichenchuxin opened a new issue #15682: Some sql for encrypt is not supported
tuichenchuxin opened a new issue #15682:
URL: https://github.com/apache/shardingsphere/issues/15682
## Bug Report
### Which version of ShardingSphere did you use?
master - 2e43bd09bdae7625aad4eb61f4dfaf0086db9473
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
proxy
### Expected behavior
support
### Actual behavior
not support
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
- proxy add encrypt althgrim
```
public final class QueryAssistedEncryptAlgorithmFixture implements QueryAssistedEncryptAlgorithm<Object, String> {
@Override
public void init() {
}
@Override
public String encrypt(final Object plainValue, final EncryptContext encryptContext) {
if (null == plainValue) {
return null;
}
return DigestUtils.md5Hex(String.valueOf(plainValue));
}
@Override
public Object decrypt(final String cipherValue, final EncryptContext encryptContext) {
return cipherValue;
}
@Override
public String queryAssistedEncrypt(final Object plainValue, final EncryptContext encryptContext) {
if (null == plainValue) {
return null;
}
return DigestUtils.md5Hex(String.valueOf(plainValue));
}
@Override
public String getType() {
return "ASSISTED_QUERY_ENCRYPT";
}
}
```
- config
```
schemaName: encrypt_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
# ds_1:
# url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
# username: root
# password:
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# minPoolSize: 1
rules:
- !ENCRYPT
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
md5_encryptor:
type: MD5
assisted_query:
type: ASSISTED_QUERY_ENCRYPT
tables:
t_encrypt:
columns:
user_id:
plainColumn: user_plain
cipherColumn: user_cipher
encryptorName: aes_encryptor
order_id:
cipherColumn: order_cipher
encryptorName: md5_encryptor
t_account_bak:
columns:
certificate_number:
cipherColumn: cipher_certificate_number
assistedQueryColumn: assisted_query_certificate_number
plainColumn: plain_certificate_number
encryptorName: assisted_query
password:
cipherColumn: cipher_password
assistedQueryColumn: assisted_query_password
plainColumn: plain_password
encryptorName: assisted_query
amount:
cipherColumn: cipher_amount
plainColumn: plain_amount
encryptorName: aes_encryptor
```
- use proxy execute sql
```
CREATE TABLE t_account_bak (user_id INT NOT NULL, certificate_number VARCHAR(200), password VARCHAR(200), amount VARCHAR(200), status VARCHAR(45) NULL, PRIMARY KEY (user_id));
insert into t_account_bak values (1,'10','ass','100','ok');
create table t_user
(
user_id int not null
primary key,
user_cipher varchar(20) not null,
user_plain varchar(20) not null,
pwd_encrypt varchar(200) default '' not null,
pwd_assist varchar(200) not null,
pwd_plain int null,
status varchar(45) null
);
insert into t_user values(1,'faa','a','1121','32','332','NO');
CREATE TABLE t_account_plain (user_id INT NOT NULL, certificate_number VARCHAR(200), password VARCHAR(200), amount VARCHAR(200), status VARCHAR(45) NULL, PRIMARY KEY (user_id));
insert into t_account_plain values (100,'100','ass','1000','ok');
create table t_account_bak_copy
(
user_id int not null
primary key,
cipher_certificate_number varchar(200) null,
assisted_query_certificate_number varchar(200) null,
plain_certificate_number varchar(200) null,
cipher_password varchar(200) null,
assisted_query_password varchar(200) null,
plain_password varchar(200) null,
cipher_amount varchar(200) null,
plain_amount varchar(200) null,
status varchar(45) null
);
insert into t_account_bak_copy values (100,'f899139df5e1059396431415e770c6dd','f899139df5e1059396431415e770c6dd','100','964d72e72d053d501f2949969849b96c','964d72e72d053d501f2949969849b96c','ass','lu87Uc9Z6mOocjIkKH3/QQ==','1000','ok');
insert into t_account_bak values (2,'11','bss','101','ok');
```
- These sqls are not supported
```
Select amount from t_account_bak where (select amount from t_account_bak) in ('100');
insert into t_user (pwd_encrypt) Select amount from t_user;
Select count(*) from (Select amount from t_account_bak group by amount) temp;
Select amount from t_account_bak where (Select amount from t_account_bak group by amount) in ('100');
Select user_id from t_account_bak where certificate_number between 1 and 2
Select user_id from t_account_bak where certificate_number like '10'
Insert into t_account_bak select * from t_account_plain
Update t_account_bak set amount = certificate_number where user_id = 1
Update t_account_bak set status = 'OK' where certificate_number in (select certificate_number from t_account_bak)
Update t_account_bak set status = 'OK' where exists (select certificate_number from t_account_bak)
Update t_account_bak set status = 'okk' where user_id = 1 order by certificate_number
delete from t_account_bak where certificate_number in (select certificate_number from t_account_bak)
delete from t_account_bak where exists (select certificate_number from t_account_bak)
delete from t_account_bak where user_id = 1 order by certificate_number
ALTER TABLE t_account_bak rename Column certificate_number_new to certificate_number
```
--
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