apache / apache/shardingsphere

Improve SQL support for SQLServer database when use encrypt feature

Open
#30,227 11 comments 0 reactions 4 assignees Claimed by @ClaireLytt View on GitHub
db: SQLServer feature: encrypt good pro issue status: volunteer wanted type: enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.