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 faae23db7fb Add rewrite with quote cases (#34269)
faae23db7fb is described below
commit faae23db7fbf717b390a2d97cbc9f45216024c0b
Author: ZhangCheng <[email protected]>
AuthorDate: Mon Jan 6 16:56:23 2025 +0800
Add rewrite with quote cases (#34269)
---
.../encrypt/case/query-with-cipher/dml/delete/delete.xml | 5 +++++
.../case/query-with-cipher/dml/insert/insert-column.xml | 10 ++++++++++
.../case/query-with-cipher/dml/insert/insert-on-duplicate.xml | 5 +++++
.../case/query-with-cipher/dml/select/select-group-by.xml | 5 +++++
.../encrypt/case/query-with-cipher/dml/select/select-join.xml | 5 +++++
.../case/query-with-cipher/dml/select/select-projection.xml | 5 +++++
.../encrypt/case/query-with-cipher/dml/update/update.xml | 5 +++++
7 files changed, 40 insertions(+)
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
index a8c7d4505c5..43b9de60c9a 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/delete/delete.xml
@@ -66,4 +66,9 @@
<input sql="DELETE t FROM encrypt.dbo.t_user as t WHERE t.user_id = 4
and t.user_name = 'test_user' and t.user_name like 'test_%' and t.password =
'admin123' and t.email = '[email protected]'" />
<output sql="DELETE t FROM dbo.t_user as t WHERE t.user_id = 4 and
t.user_name_cipher = 'o07WOpLlazifLKU747nd8w==' and t.user_name_like like
'udtu_%' and t.password_cipher = 'beO6rTHBW9jmVeSPPb62QA==' and t.email_cipher
= 'zclI1Wk2uaVYHyNckTRYzA=='" />
</rewrite-assertion>
+
+ <rewrite-assertion id="delete_with_quote" db-types="MySQL">
+ <input sql="DELETE FROM `t_account` WHERE `account_id` = ? AND
`password` = ? AND `password` like ? AND `amount` = ? AND `status` = ?"
parameters="1, aaa, aaa, 1000, OK" />
+ <output sql="DELETE FROM `t_account` WHERE `account_id` = ? AND
`assisted_query_password` = ? AND `like_query_password` like ? AND
`cipher_amount` = ? AND `status` = ?" parameters="1, assisted_query_aaa,
like_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
index 1c13db8ec66..9f81db551a1 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-column.xml
@@ -121,4 +121,14 @@
<input sql="INSERT INTO t_account(account_id, certificate_number,
password, amount, status) VALUES (?, ?, ?, ?, ?), (2, '222X', NULL, 2000,
'OK'), (?, ?, ?, ?, ?), (4, '444X', 'ddd', NULL, 'OK')" parameters="1, NULL,
aaa, 1000, OK, 3, 333X, NULL, 3000, OK" />
<output sql="INSERT INTO t_account(account_id,
cipher_certificate_number, assisted_query_certificate_number,
like_query_certificate_number, cipher_password, assisted_query_password,
like_query_password, cipher_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?),
(2, 'encrypt_222X', 'assisted_query_222X', 'like_query_222X', NULL, NULL, NULL,
'encrypt_2000', 'OK'), (?, ?, ?, ?, ?, ?, ?, ?, ?), (4, 'encrypt_444X',
'assisted_query_444X', 'like_query_444X', 'encrypt_ddd', 'assisted_que [...]
</rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_with_columns_and_quote"
db-types="MySQL">
+ <input sql="INSERT INTO `t_account`(`account_id`,
`certificate_number`, `password`, `amount`, `status`) VALUES (?, ?, ?, ?, ?)"
parameters="1, cert1, pwd1, 1000, OK1" />
+ <output sql="INSERT INTO `t_account`(`account_id`,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, `status`) VALUES (?, ?, ?, ?, ?, ?, ?,
?, ?)" parameters="1, encrypt_cert1, assisted_query_cert1, like_query_cert1,
encrypt_pwd1, assisted_query_pwd1, like_query_pwd1, encrypt_1000, OK1" />
+ </rewrite-assertion>
+
+ <rewrite-assertion id="insert_values_without_columns_and_quote"
db-types="MySQL">
+ <input sql="INSERT INTO `t_account_bak` VALUES (?, ?, ?, ?)"
parameters="1, cert1, pwd1, 1000" />
+ <output sql="INSERT INTO `t_account_bak`(`account_id`,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
parameters="1, encrypt_cert1, assisted_query_cert1, like_query_cert1,
encrypt_pwd1, assisted_query_pwd1, like_query_pwd1, encrypt_1000" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
index 70b74639ab3..9dc42834056 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/insert/insert-on-duplicate.xml
@@ -76,4 +76,9 @@
<input sql="INSERT INTO t_account_bak(account_id, certificate_number,
password, amount, status) VALUES (1, '111X', 'aaa', 1000, 'OK'), (2, '222X',
'bbb', 2000, 'OK'), (3, '333X', 'ccc', 3000, 'OK'), (4, '444X', 'ddd', 4000,
'OK') ON DUPLICATE KEY UPDATE password = 'ccc_update'" />
<output sql="INSERT INTO t_account_bak(account_id,
cipher_certificate_number, assisted_query_certificate_number,
like_query_certificate_number, cipher_password, assisted_query_password,
like_query_password, cipher_amount, status) VALUES (1, 'encrypt_111X',
'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa',
'like_query_aaa', 'encrypt_1000', 'OK'), (2, 'encrypt_222X',
'assisted_query_222X', 'like_query_222X', 'encrypt_bbb', 'assisted_query_bbb',
'like_qu [...]
</rewrite-assertion>
+
+ <rewrite-assertion
id="insert_values_with_on_duplicate_key_update_with_quote" db-types="MySQL">
+ <input sql="INSERT INTO `t_account_bak`(`account_id`,
`certificate_number`, `password`, `amount`, `status`) VALUES (1, '111X', 'aaa',
1000, 'OK') ON DUPLICATE KEY UPDATE `password` = 'ccc_update'" />
+ <output sql="INSERT INTO `t_account_bak`(`account_id`,
`cipher_certificate_number`, `assisted_query_certificate_number`,
`like_query_certificate_number`, `cipher_password`, `assisted_query_password`,
`like_query_password`, `cipher_amount`, `status`) VALUES (1, 'encrypt_111X',
'assisted_query_111X', 'like_query_111X', 'encrypt_aaa', 'assisted_query_aaa',
'like_query_aaa', 'encrypt_1000', 'OK') ON DUPLICATE KEY UPDATE
`cipher_password` = 'encrypt_ccc_update', `assisted_query_passwo [...]
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
index 6b118cf9d97..cef0dfe32e9 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-group-by.xml
@@ -36,4 +36,9 @@
<input sql="SELECT account_id, password, amount AS a, status AS s FROM
t_account WHERE account_id = 1 AND password = 'aaa' AND password like 'aaa' AND
amount = 1000 AND status = 'OK' group by amount desc" />
<output sql="SELECT account_id, cipher_password AS password,
cipher_amount AS a, status AS s FROM t_account WHERE account_id = 1 AND
assisted_query_password = 'assisted_query_aaa' AND like_query_password like
'like_query_aaa' AND cipher_amount = 'encrypt_1000' AND status = 'OK' group by
cipher_amount desc" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_with_groupby_quote" db-types="MySQL">
+ <input sql="SELECT `account_id`, `password`, `amount` AS `a`, `status`
AS `s` FROM `t_account` WHERE `account_id` = 1 AND `password` = 'aaa' AND
`password` like 'aaa' AND `amount` = 1000 AND `status` = 'OK' group by `amount`
desc" />
+ <output sql="SELECT `account_id`, `cipher_password` AS `password`,
`cipher_amount` AS `a`, `status` AS `s` FROM `t_account` WHERE `account_id` = 1
AND `assisted_query_password` = 'assisted_query_aaa' AND `like_query_password`
like 'like_query_aaa' AND `cipher_amount` = 'encrypt_1000' AND `status` = 'OK'
group by `cipher_amount` desc" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
index c7ea37f4434..fce5f6d8bab 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/select/select-join.xml
@@ -56,4 +56,9 @@
<input sql="SELECT t_account.amount, t_account_bak.amount FROM
t_account LEFT JOIN t_account_bak ON t_account.account_id =
t_account_bak.account_id WHERE t_account.password = 1 OR t_account_bak.amount =
2 AND t_account.certificate_number like 3" />
<output sql="SELECT t_account.cipher_amount AS amount,
t_account_bak.cipher_amount AS amount FROM t_account LEFT JOIN t_account_bak ON
t_account.account_id = t_account_bak.account_id WHERE
t_account.assisted_query_password = 'assisted_query_1' OR
t_account_bak.cipher_amount = 'encrypt_2' AND
t_account.like_query_certificate_number like 'like_query_3'" />
</rewrite-assertion>
+
+ <rewrite-assertion id="select_with_join_and_quote" db-types="MySQL">
+ <input sql="SELECT `t_account`.`amount`, `t_account_bak`.`amount` FROM
`t_account` LEFT JOIN `t_account_bak` ON `t_account`.`account_id` =
`t_account_bak`.`account_id` WHERE `t_account`.`password` = 1 OR
`t_account_bak`.`amount` = 2 AND `t_account`.`certificate_number` like 3" />
+ <output sql="SELECT `t_account`.`cipher_amount` AS `amount`,
`t_account_bak`.`cipher_amount` AS `amount` FROM `t_account` LEFT JOIN
`t_account_bak` ON `t_account`.`account_id` = `t_account_bak`.`account_id`
WHERE `t_account`.`assisted_query_password` = 'assisted_query_1' OR
`t_account_bak`.`cipher_amount` = 'encrypt_2' AND
`t_account`.`like_query_certificate_number` like 'like_query_3'" />
+ </rewrite-assertion>
</rewrite-assertions>
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 5898c838db8..046680da16d 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
@@ -91,4 +91,9 @@
<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-assertion id="select_with_quote" db-types="MySQL">
+ <input sql="SELECT `account_id`, `password`, `amount` AS `a`, `status`
AS `s` FROM `t_account` WHERE `account_id` = ? AND `password` = ? AND
`password` like ? AND amount = ? AND status = ?" parameters="1, aaa, aaa, 1000,
OK" />
+ <output sql="SELECT `account_id`, `cipher_password` AS `password`,
`cipher_amount` AS `a`, `status` AS `s` FROM `t_account` WHERE `account_id` = ?
AND `assisted_query_password` = ? AND `like_query_password` like ? AND
cipher_amount = ? AND status = ?" parameters="1, assisted_query_aaa,
like_query_aaa, encrypt_1000, OK" />
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
index 37888107690..066dda2b154 100644
---
a/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
+++
b/test/it/rewriter/src/test/resources/scenario/encrypt/case/query-with-cipher/dml/update/update.xml
@@ -93,4 +93,9 @@
<input sql="UPDATE t_account_bak SET certificate_number = NULL WHERE
certificate_number IS NOT NULL AND status = 'OK' AND certificate_number =
'111X' AND certificate_number like '111X'" />
<output sql="UPDATE t_account_bak SET cipher_certificate_number =
NULL, assisted_query_certificate_number = NULL, like_query_certificate_number =
NULL WHERE assisted_query_certificate_number IS NOT NULL AND status = 'OK' AND
assisted_query_certificate_number = 'assisted_query_111X' AND
like_query_certificate_number like 'like_query_111X'" />
</rewrite-assertion>
+
+ <rewrite-assertion id="update_account_bak_for_parameters_and_quote"
db-types="MySQL">
+ <input sql="UPDATE `t_account_bak` SET `account_id` = ?,
`certificate_number` = ?, `password` = ?, `amount` = ?, `status` = ? WHERE
`account_id` = ? AND `certificate_number` = ? AND `password` = ? AND `amount` =
? AND `status` = ?" parameters="1, cert, pwd, amt, OK, 2, cert1, pwd1, amt1,
OK1" />
+ <output sql="UPDATE `t_account_bak` SET `account_id` = ?,
`cipher_certificate_number` = ?, `assisted_query_certificate_number` = ?,
`like_query_certificate_number` = ?, `cipher_password` = ?,
`assisted_query_password` = ?, `like_query_password` = ?, `cipher_amount` = ?,
`status` = ? WHERE `account_id` = ? AND `assisted_query_certificate_number` = ?
AND `assisted_query_password` = ? AND `cipher_amount` = ? AND `status` = ?"
parameters="1, encrypt_cert, assisted_query_cert, like_qu [...]
+ </rewrite-assertion>
</rewrite-assertions>
