apache / apache/shardingsphere
Improve SQL support for SQLServer database when use encrypt feature
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 35m
- Merged PRs (30d)
- 326
Description
## Feature Request
### Is your feature request related to a problem?
https://github.com/apache/shardingsphere/issues?q=is%3Aopen+is%3Aissue+label%3A%22in%3A+SQL+parse%22+label%3A%22db%3A+SQLServer%22
### Describe the feature you would like.
Through the efforts of many developers in the community, ShardingSphere's support for SQLServer DML syntax is constantly improving. I think it's time to go a step further, and on top of SQL parsing, we can use encrypt feature and test SQLServer DML statements.
When we test SQLServer DML encryption, there will be some problems, it may be SQL parsing problems, it may be encryption rewriting problems, and it may even be SQL Binder problems. What we have to do is find these problems and fix them.
The following shows the steps required to complete the task:
1. Install SQLServer 2017 on your machine, please refer to https://hub.docker.com/_/microsoft-mssql-server;
2. Then config encrypt rule like following:
```yaml
databaseName: encrypt_db
dataSources:
db0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.microsoft.sqlserver
jdbcUrl: jdbc:sqlserver://db1.database.windows.net;databaseName=db1;loginTimeout=300;encrypt=false;
username: adm
rules:
- !SINGLE
tables:
- "*.*"
- !ENCRYPT
encryptors:
aes_encryptor:
type: AES
props:
aes-key-value: 123456abc
like_encryptor:
type: IT.ENCRYPT.LIKE.FIXTURE
props:
mask: 4093
tables:
t_user:
columns:
user_name:
cipher:
name: user_name_cipher
encryptorName: aes_encryptor
likeQuery:
name: user_name_like
encryptorName: like_encryptor
password:
cipher:
name: password_cipher
encryptorName: aes_encryptor
email:
cipher:
name: email_cipher
encryptorName: aes_encryptor
telephone:
cipher:
name: user_telephone_cipher
encryptorName: aes_encryptor
likeQuery:
name: user_telephone_like
encryptorName: like_encryptor
t_merchant:
columns:
business_code:
cipher:
name: business_code_cipher
encryptorName: aes_encryptor
likeQuery:
name: business_code_like
encryptorName: like_encryptor
telephone:
cipher:
name: merchant_telephone_cipher
encryptorName: aes_encryptor
likeQuery:
name: merchant_telephone_like
encryptorName: like_encryptor
props:
sql-show: true
```
3. Init encrypt table - https://github.com/apache/shardingsphere/blob/d39f46b5bf420d14c39523c34303ac0c95205d4d/test/e2e/sql/src/test/resources/env/scenario/encrypt/data/actual/init-sql/mysql/01-actual-init.sql
4. Write unit test to test SQLServer DML statement, you can refer - ShardingSphereDriverTest
5. Compare the same DML statement, using the encryption function and the result of native SQLServer, compare the result whether there is a difference, if there is a difference or an exception, these are the problems we need to fix.
6. After fixing the problem, since SQLServer is a commercial database, we can't add these cases to E2E, but that's okay, test/it/rewriter is also a good place to add SQL rewriting cases.
7. Some SQL is not supported by the current encrypt features, and if there are new discoveries, you can update it in the usage limitations document - https://shardingsphere.apache.org/document/current/en/features/encrypt/limitations/
### Target sql
- [ ] SELECT STATEMENT @TherChenYang - https://learn.microsoft.com/zh-cn/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16
- [ ] DELETE STATEMENT @yydeng626 - https://learn.microsoft.com/zh-cn/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16
- [ ] UPDATE STATEMENT - https://learn.microsoft.com/zh-cn/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16
- [ ] INSERT STATEMENT - https://learn.microsoft.com/zh-cn/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16 @zzyReal666
- [ ] MERGE STATEMENT - https://learn.microsoft.com/zh-cn/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16
Contributor guide
Assessment
This issue has not been assessed yet.