This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 0ed5fb214cc Add encryption rewrite of the SQLServer select statement
(#30334)
0ed5fb214cc is described below
commit 0ed5fb214ccb91ce5f56df6c1d6c9afa0537243c
Author: LotusMoon <[email protected]>
AuthorDate: Wed Feb 28 15:15:01 2024 +0800
Add encryption rewrite of the SQLServer select statement (#30334)
---
.../engine/scenario/EncryptSQLRewriterIT.java | 8 +
.../RewriteEncryptLikeAlgorithmFixture.java | 168 +++++++++++++++++++++
...che.shardingsphere.encrypt.spi.EncryptAlgorithm | 1 +
.../algorithm/like/common_chinese_character.dict | 18 +++
.../dml/select/select-projection.xml | 10 ++
.../query-with-cipher/dml/select/select-where.xml | 20 +++
.../scenario/encrypt/config/query-with-cipher.yaml | 46 ++++--
7 files changed, 257 insertions(+), 14 deletions(-)
diff --git
a/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/engine/scenario/EncryptSQLRewriterIT.java
b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/engine/scenario/EncryptSQLRewriterIT.java
index abe41f0294c..84d18274b2f 100644
---
a/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/engine/scenario/EncryptSQLRewriterIT.java
+++
b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/engine/scenario/EncryptSQLRewriterIT.java
@@ -87,6 +87,13 @@ class EncryptSQLRewriterIT extends SQLRewriterIT {
new ShardingSphereColumn("ORDER_ID", Types.INTEGER, false,
false, false, true, false, false),
new ShardingSphereColumn("USER_ID", Types.INTEGER, false,
false, false, true, false, false),
new ShardingSphereColumn("CONTENT", Types.VARCHAR, false,
false, false, true, false, false)), Collections.emptyList(),
Collections.emptyList()));
+ tables.put("t_user", new ShardingSphereTable("t_user", Arrays.asList(
+ new ShardingSphereColumn("user_id", Types.INTEGER, false,
false, false, true, false, false),
+ new ShardingSphereColumn("user_name", Types.VARCHAR, false,
false, false, true, false, false),
+ new ShardingSphereColumn("password", Types.VARCHAR, false,
false, false, true, false, false),
+ new ShardingSphereColumn("email", Types.VARCHAR, false, false,
false, true, false, false),
+ new ShardingSphereColumn("telephone", Types.VARCHAR, false,
false, false, true, false, false),
+ new ShardingSphereColumn("creation_date", Types.DATE, false,
false, false, true, false, false)), Collections.emptyList(),
Collections.emptyList()));
ShardingSphereSchema result = new ShardingSphereSchema(tables,
Collections.emptyMap());
return Collections.singletonMap(schemaName, result);
}
@@ -99,6 +106,7 @@ class EncryptSQLRewriterIT extends SQLRewriterIT {
singleRule.get().put("encrypt_ds", schemaName, "t_account_bak");
singleRule.get().put("encrypt_ds", schemaName, "t_account_detail");
singleRule.get().put("encrypt_ds", schemaName, "t_order");
+ singleRule.get().put("encrypt_ds", schemaName, "t_user");
}
}
diff --git
a/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/fixture/encrypt/RewriteEncryptLikeAlgorithmFixture.java
b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/fixture/encrypt/RewriteEncryptLikeAlgorithmFixture.java
new file mode 100644
index 00000000000..a0685d17655
--- /dev/null
+++
b/test/it/rewriter/src/test/java/org/apache/shardingsphere/test/it/rewrite/fixture/encrypt/RewriteEncryptLikeAlgorithmFixture.java
@@ -0,0 +1,168 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.test.it.rewrite.fixture.encrypt;
+
+import com.google.common.base.Strings;
+import lombok.Getter;
+import lombok.SneakyThrows;
+import
org.apache.shardingsphere.encrypt.exception.algorithm.EncryptAlgorithmInitializationException;
+import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm;
+import org.apache.shardingsphere.encrypt.spi.EncryptAlgorithmMetaData;
+import
org.apache.shardingsphere.infra.algorithm.core.context.AlgorithmSQLContext;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.Map;
+import java.util.Objects;
+import java.util.Properties;
+import java.util.Scanner;
+import java.util.stream.Collectors;
+import java.util.stream.IntStream;
+
+public final class RewriteEncryptLikeAlgorithmFixture implements
EncryptAlgorithm {
+
+ private static final String DELTA_KEY = "delta";
+
+ private static final String MASK_KEY = "mask";
+
+ private static final String START_KEY = "start";
+
+ private static final String DICT_KEY = "dict";
+
+ private static final int DEFAULT_DELTA = 1;
+
+ private static final int DEFAULT_MASK = 0b1111_0111_1101;
+
+ private static final int DEFAULT_START = 0x4e00;
+
+ private static final int MAX_NUMERIC_LETTER_CHAR = 255;
+
+ @Getter
+ private final EncryptAlgorithmMetaData metaData = new
EncryptAlgorithmMetaData(false, true, true);
+
+ private int delta;
+
+ private int mask;
+
+ private int start;
+
+ private Map<Character, Integer> charIndexes;
+
+ @Override
+ public void init(final Properties props) {
+ delta = createDelta(props);
+ mask = createMask(props);
+ start = createStart(props);
+ charIndexes = createCharIndexes(props);
+ }
+
+ private int createDelta(final Properties props) {
+ if (props.containsKey(DELTA_KEY)) {
+ try {
+ return Integer.parseInt(props.getProperty(DELTA_KEY));
+ } catch (final NumberFormatException ignored) {
+ throw new EncryptAlgorithmInitializationException(getType(),
"delta can only be a decimal number");
+ }
+ }
+ return DEFAULT_DELTA;
+ }
+
+ private int createMask(final Properties props) {
+ if (props.containsKey(MASK_KEY)) {
+ try {
+ return Integer.parseInt(props.getProperty(MASK_KEY));
+ } catch (final NumberFormatException ignored) {
+ throw new EncryptAlgorithmInitializationException(getType(),
"mask can only be a decimal number");
+ }
+ }
+ return DEFAULT_MASK;
+ }
+
+ private int createStart(final Properties props) {
+ if (props.containsKey(START_KEY)) {
+ try {
+ return Integer.parseInt(props.getProperty(START_KEY));
+ } catch (final NumberFormatException ignored) {
+ throw new EncryptAlgorithmInitializationException(getType(),
"start can only be a decimal number");
+ }
+ }
+ return DEFAULT_START;
+ }
+
+ private Map<Character, Integer> createCharIndexes(final Properties props) {
+ String dictContent = props.containsKey(DICT_KEY) &&
!Strings.isNullOrEmpty(props.getProperty(DICT_KEY)) ?
props.getProperty(DICT_KEY) : initDefaultDict();
+ return IntStream.range(0,
dictContent.length()).boxed().collect(Collectors.toMap(dictContent::charAt,
index -> index, (oldValue, currentValue) -> oldValue));
+ }
+
+ @SneakyThrows(IOException.class)
+ private String initDefaultDict() {
+ StringBuilder result = new StringBuilder();
+ try (
+ InputStream inputStream =
Objects.requireNonNull(Thread.currentThread().getContextClassLoader().getResourceAsStream("algorithm/like/common_chinese_character.dict"));
+ Scanner scanner = new Scanner(inputStream)) {
+ while (scanner.hasNextLine()) {
+ String line = scanner.nextLine();
+ if (!line.isEmpty() && !line.startsWith("#")) {
+ result.append(line);
+ }
+ }
+ }
+ return result.toString();
+ }
+
+ @Override
+ public String encrypt(final Object plainValue, final AlgorithmSQLContext
algorithmSQLContext) {
+ return null == plainValue ? null : digest(String.valueOf(plainValue));
+ }
+
+ @Override
+ public Object decrypt(final Object cipherValue, final AlgorithmSQLContext
algorithmSQLContext) {
+ throw new UnsupportedOperationException(String.format("Algorithm `%s`
is unsupported to decrypt", getType()));
+ }
+
+ private String digest(final String plainValue) {
+ StringBuilder result = new StringBuilder(plainValue.length());
+ for (char each : plainValue.toCharArray()) {
+ char maskedChar = getMaskedChar(each);
+ if ('%' == maskedChar || '_' == maskedChar) {
+ result.append(each);
+ } else {
+ result.append(maskedChar);
+ }
+ }
+ return result.toString();
+ }
+
+ private char getMaskedChar(final char originalChar) {
+ if ('%' == originalChar || '_' == originalChar) {
+ return originalChar;
+ }
+ if (originalChar <= MAX_NUMERIC_LETTER_CHAR) {
+ return (char) ((originalChar + delta) & mask);
+ }
+ if (charIndexes.containsKey(originalChar)) {
+ return (char) (((charIndexes.get(originalChar) + delta) & mask) +
start);
+ }
+ return (char) (((originalChar + delta) & mask) + start);
+ }
+
+ @Override
+ public String getType() {
+ return "IT.ENCRYPT.LIKE.FIXTURE";
+ }
+}
diff --git
a/test/it/rewriter/src/test/resources/META-INF/services/org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm
b/test/it/rewriter/src/test/resources/META-INF/services/org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm
index 26255a18998..f2d99d39850 100644
---
a/test/it/rewriter/src/test/resources/META-INF/services/org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm
+++
b/test/it/rewriter/src/test/resources/META-INF/services/org.apache.shardingsphere.encrypt.spi.EncryptAlgorithm
@@ -18,3 +18,4 @@
org.apache.shardingsphere.test.it.rewrite.fixture.encrypt.RewriteNormalEncryptAlgorithmFixture
org.apache.shardingsphere.test.it.rewrite.fixture.encrypt.RewriteQueryAssistedEncryptAlgorithmFixture
org.apache.shardingsphere.test.it.rewrite.fixture.encrypt.RewriteQueryLikeEncryptAlgorithmFixture
+org.apache.shardingsphere.test.it.rewrite.fixture.encrypt.RewriteEncryptLikeAlgorithmFixture
diff --git
a/test/it/rewriter/src/test/resources/algorithm/like/common_chinese_character.dict
b/test/it/rewriter/src/test/resources/algorithm/like/common_chinese_character.dict
new file mode 100644
index 00000000000..83c5ea53981
--- /dev/null
+++
b/test/it/rewriter/src/test/resources/algorithm/like/common_chinese_character.dict
@@ -0,0 +1,18 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+谤杉巫夏辅俯鸵直菱梨滨头矾讯芯巡泥簇何逊谜颐男拴冰响贰哈雄赌密愚思戊叔苔肩亏硕递意忻雷扫旋谭坎散拷廖余饮囤咖小娄藻唇妙枣豺料淡器谈赘托察湿莽算诽嘛越魁涤缮漱镣豁孔萝脾稻杠遮漏浪常滦呸层涪粪乐萄褐榜程伙椭句蚤入概鹰惨闻舶封瘴创详筹边络乞洪咯丈眯弛娱狸锐俄痔向片壬二剧壳羌劳澳到箔慧醛俏硬读宁谨谋捶牧豆脱饰肛渠坞富揉赤帽能巩雁题姬崔旁袄主蝇屏撼粗贡刃办肉穷态柳嚼鸥哨兔瓦籍谊唯指混丰肤值邹脸爪摄兑券浆薄漾盟磊牺筑锤匿碑萍拌醉焊扞韭群擦飞尺从咆蜘辗帅蘑懦彭翠痛联热惮溯啦乏沏颈渔奋曲衣焦迄喳狞登冯制腐青围业瑶拨碉赢幽开狠耐熄培朝编堂嫌媚王跳剖骸四爬氛既遣鱼氢利龟爱斯毅屠氦止邀厉员拭佛针牲菌耳罕诺虐倦瓣渐丛哼麻饱且楷诀港贤酣李管撅茧犊关聪凉试弦煤谐掣戈膊藩翘扬契货鸿逮众�
��汾芍凋撬罩数反罢背睛琵觉芒箩嫂钡豹峭箱纤拄哑留损售布旅搁蒲藕阶郝披恃邢落殉证盗鞠竟亢勺刀足畦熙帛曹娇宗亭蔑靖须医疮凯灾鸡蔫室耪沃阂伎墨卷随釜巾浴领臣啊绊纫较后圃桨厚教磋颖姥你荤丧沪芳栋休未茁舵林啥国糖规括搪扯补摹近绸履瞄兄般轧坛够雀烂锨嘱聚陵篷调躬印祈挟胶饯雪甸渤当裕汝鲸秽咬岸解剂丁勉鞋缺雨觅伶顿镁掠鸦埔郡场欧商推硒
[...]
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
index 2947fe90bef..c83971595a3 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-projection.xml
@@ -81,4 +81,14 @@
<input sql="SELECT logic_db.t_account.account_id FROM t_account WHERE
account_id = 100" />
<output sql="SELECT t_account.account_id FROM t_account WHERE
account_id = 100" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_shorthand"
db-types="SQLServer">
+ <input sql="SELECT * FROM t_user ORDER BY creation_date;" />
+ <output sql="SELECT t_user.[user_id], t_user.[user_name_cipher] AS
[user_name], t_user.[password_cipher] AS [password], t_user.[email_cipher] AS
[email], t_user.[user_telephone_cipher] AS [telephone], t_user.[creation_date]
FROM t_user ORDER BY creation_date;" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_shorthand_and_table_alias"
db-types="SQLServer">
+ <input sql="SELECT e.* FROM t_user AS e ORDER BY creation_date;"/>
+ <output sql="SELECT e.[user_id], e.[user_name_cipher] AS [user_name],
e.[password_cipher] AS [password], e.[email_cipher] AS [email],
e.[user_telephone_cipher] AS [telephone], e.[creation_date] FROM t_user AS e
ORDER BY creation_date;"/>
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
index 1fc4b224689..b7f3a9add40 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-where.xml
@@ -78,4 +78,24 @@
<input sql="SELECT a.account_id, a.password, a.amount AS a, a.status
AS s FROM t_account_bak AS a WHERE a.account_id = 1 AND a.certificate_number
like concat('%','abc','%')" />
<output sql="SELECT a.account_id, a.cipher_password AS password,
a.cipher_amount AS a, a.status AS s FROM t_account_bak AS a WHERE a.account_id
= 1 AND a.like_query_certificate_number like concat ('like_query_%',
'like_query_abc', 'like_query_%')" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_column_alias"
db-types="SQLServer">
+ <input sql="SELECT user_name, password, email AS user_email FROM
t_user ORDER BY creation_date;"/>
+ <output sql="SELECT user_name_cipher AS user_name, password_cipher AS
password, email_cipher AS user_email FROM t_user ORDER BY creation_date;"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion
id="select_from_user_with_column_alias_and_where_clause" db-types="SQLServer">
+ <input sql="SELECT user_name, password, email AS user_email FROM
t_user WHERE email IS NOT NULL AND user_name = 'yang' ORDER BY creation_date;"/>
+ <output sql="SELECT user_name_cipher AS user_name, password_cipher AS
password, email_cipher AS user_email FROM t_user WHERE email_cipher IS NOT NULL
AND user_name_cipher = 'zeqc7uDyv9tT/3cvl2rJzg==' ORDER BY creation_date;"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_distinct_username"
db-types="SQLServer">
+ <input sql="SELECT DISTINCT user_name FROM t_user;"/>
+ <output sql="SELECT DISTINCT user_name_cipher AS user_name FROM
t_user;"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion id="select_from_user_with_fuzzy_name"
db-types="SQLServer">
+ <input sql="SELECT * FROM t_user where user_name like '%an%'"/>
+ <output sql="SELECT t_user.[user_id], t_user.[user_name_cipher] AS
[user_name], t_user.[password_cipher] AS [password], t_user.[email_cipher] AS
[email], t_user.[user_telephone_cipher] AS [telephone], t_user.[creation_date]
FROM t_user where user_name_like like '%`m%'"/>
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
b/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
index de436cb33a7..40a27d8c5fd 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/config/query-with-cipher.yaml
@@ -48,10 +48,6 @@ rules:
cipher:
name: cipher_amount
encryptorName: rewrite_normal_fixture
- amount_new:
- cipher:
- name: amount_new_cipher
- encryptorName: rewrite_normal_fixture
t_account_bak:
columns:
certificate_number:
@@ -74,16 +70,6 @@ rules:
likeQuery:
name: like_query_password
encryptorName: rewrite_like_query_fixture
- password_new:
- cipher:
- name: password_new_cipher
- encryptorName: rewrite_normal_fixture
- assistedQuery:
- name: password_new_assisted
- encryptorName: rewrite_assisted_query_fixture
- likeQuery:
- name: password_new_like
- encryptorName: rewrite_like_query_fixture
amount:
cipher:
name: cipher_amount
@@ -114,6 +100,30 @@ rules:
cipher:
name: cipher_amount
encryptorName: rewrite_normal_fixture
+ t_user:
+ columns:
+ user_name:
+ cipher:
+ name: user_name_cipher
+ encryptorName: rewrite_aes_encryptor_fixture
+ likeQuery:
+ name: user_name_like
+ encryptorName: rewrite_it_like_encryptor_fixture
+ password:
+ cipher:
+ name: password_cipher
+ encryptorName: rewrite_aes_encryptor_fixture
+ email:
+ cipher:
+ name: email_cipher
+ encryptorName: rewrite_aes_encryptor_fixture
+ telephone:
+ cipher:
+ name: user_telephone_cipher
+ encryptorName: rewrite_aes_encryptor_fixture
+ likeQuery:
+ name: user_telephone_like
+ encryptorName: rewrite_it_like_encryptor_fixture
encryptors:
rewrite_normal_fixture:
type: REWRITE.NORMAL.FIXTURE
@@ -121,3 +131,11 @@ rules:
type: REWRITE.ASSISTED_QUERY.FIXTURE
rewrite_like_query_fixture:
type: REWRITE.LIKE_QUERY.FIXTURE
+ rewrite_aes_encryptor_fixture:
+ type: AES
+ props:
+ aes-key-value: 123456abc
+ rewrite_it_like_encryptor_fixture:
+ type: IT.ENCRYPT.LIKE.FIXTURE
+ props:
+ mask: 4093
