Data Encryption SQL Compatibility #
Based on the SQL parsing engine of SphereEx-DBPlusEngine, the data security encryption function can realize encryption and decryption of sensitive fields in SQL by rewriting the SQL syntax tree and using different encryption algorithms. Limited by the operation characteristics of different encryption algorithms, the data encryption function may be incompatible with some SQL operations, which requires users to avoid them during use. The following shows a list of common SQL that SphereEx-DBPlusEngine supports and does not support for users to use. Can be used for user reference.
Note: The following unsupported SQL list is only for scenarios where encrypt columns are configured. SphereEx-DBPlusEngine can support all SQL operations in non-encrypt scenarios. Complex queries such as subqueries are limited by the compatibility of simple query statements. If functions and expressions contain encrypt columns, operations such as greater than, less than comparison, BETWEEN AND, and sorting of encrypt columns are currently not supported.
Supported List Of Common SQL #
MySQL #
- DML Statement
| SQL Example | Description |
|---|---|
| SELECT u.* FROM t_user u WHERE u.user_id = ? | Support query asterisk expansion for simple queries |
| SELECT u., o., i.* FROM t_user u INNER JOIN t_order o ON u.user_id = o.user_id INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE u.user_id = ? | Support query asterisk expansion for associated queries |
| SELECT * FROM (SELECT COUNT(DISTINCT user_id) FROM t_user u WHERE user_id = ? GROUP BY user_id) temp | Support query asterisk expansion for subqueries |
| SELECT temp.* FROM (SELECT COUNT(user_id), MAX(user_id), MAX(user_id) + 1, (SELECT 1 + 1 FROM DUAL) FROM t_user WHERE user_id = ? GROUP BY user_id) temp | Supports simple query, associated query, complex query asterisk expansion in subquery combination scenarios |
| SELECT u.user_id, (SELECT user_name FROM t_user LIMIT 1) FROM t_user u WHERE u.user_id = ? | Supports Projection subquery |
| SELECT * FROM (SELECT * FROM t_user) AS temp WHERE user_id = ? | Supports Table subquery and Join subquery |
| SELECT u.* FROM t_user u WHERE u.user_name = (SELECT user_name FROM t_user WHERE user_id = ?) | Supports Predicate subquery |
| SELECT * FROM t_user u INNER JOIN t_merchant m ON u.telephone = m.telephone WHERE u.user_id = ? | Supports MySQL INNER JOIN, CROSS JOIN, STRAIGHT_JOIN, NATURAL JOIN, NATURAL LEFT JOIN, NATURAL RIGHT JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN and other association types |
| SELECT * FROM t_user u INNER JOIN t_merchant m USING (telephone) INNER JOIN t_order USING (user_id) WHERE u.user_id = ? | Support using ON/USING or NATURAL JOIN and other association condition |
| SELECT COUNT(DISTINCT user_id) FROM t_user u WHERE user_id = ? GROUP BY telephone | Supports encrypt columns included in MySQL GROUP BY statement |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) VALUES (?, ?, ?, ?, ?, ‘2017-08-08’) | Supports encryption and rewriting of simple INSERT VALUES statements |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) SELECT 40, user_name, password, email, telephone, creation_date FROM t_user WHERE user_id = ? | Supports INSERT SELECT encryption rewriting. The encrypt columns of the source table and target table need to use the same encrypt algorithm |
| DELETE FROM t_user t WHERE t.password IN (?) | Supports simple DELETE statement encryption rewriting |
| UPDATE t_user SET password = ? WHERE user_id = ? | Supports simple Update statement encryption rewriting |
Oracle #
- DML Statement
| SQL Example | 说明 |
|---|---|
| SELECT user_id, u.* FROM t_user u WHERE u.user_id = ? | Support query asterisk expansion for simple queries |
| SELECT * FROM t_user u FULL OUTER JOIN t_order o ON u.user_id = o.user_id FULL OUTER JOIN t_order_item i ON o.order_id = i.order_id WHERE u.user_id = ? | Support query asterisk expansion for associated queries |
| SELECT temp.* FROM (SELECT COUNT(user_id), MAX(user_id), MAX(user_id) + 1, (SELECT 1 + 1 FROM DUAL) FROM t_user WHERE user_id = ? GROUP BY user_id) temp | Support query asterisk expansion for subqueries |
| SELECT temp.* FROM (SELECT COUNT(user_id) AS user_id_count, MAX(user_id) AS user_id_max, MAX(user_id) + 1 AS user_id_expression_max, (SELECT 1 + 1 FROM DUAL) AS subquery_expression FROM t_user WHERE user_id = ? GROUP BY user_id) temp | Supports simple query, associated query, complex query asterisk expansion in subquery combination scenarios |
| SELECT u.user_id, (SELECT user_name FROM t_user WHERE ROWNUM <= 1) FROM t_user u WHERE u.user_id = ? | Supports Projection subquery |
| SELECT * FROM (SELECT * FROM t_user) WHERE user_id = ? | Supports Table subquery and Join subquery |
| SELECT u.* FROM t_user u WHERE u.user_name = (SELECT user_name FROM t_user WHERE user_id = ?) | Supports Predicate subquery |
| SELECT * FROM t_user u LEFT OUTER JOIN t_merchant m USING (telephone) LEFT OUTER JOIN t_order USING (user_id) WHERE user_id = ? | Supports Oracle INNER JOIN、CROSS JOIN、NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN、FULL OUTER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN and other associated types |
| SELECT * FROM t_user u NATURAL JOIN t_merchant m WHERE u.user_id = ? | Support using ON/USING or NATURAL JOIN and other association condition |
| SELECT COUNT(user_id), MAX(user_id), MAX(user_id) + 1, (SELECT 1 + 1 FROM DUAL) FROM t_user WHERE user_id = ? GROUP BY telephone | Supports encrypt columns included in Oracle GROUP BY statement |
| SELECT * FROM t_user u WHERE u.user_id = ? FOR UPDATE OF u.telephone, u.user_id | Support Oracle SELECT FOR UPDATE column encryption |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) VALUES (?, ?, ?, ?, ?, DATE ‘2017-08-08’) | Supports encryption and rewriting of simple INSERT VALUES statements |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) SELECT 40, user_name, password, email, telephone, creation_date FROM t_user WHERE user_id = ? | Supports INSERT SELECT encryption rewriting. The encrypt columns of the source table and target table need to use the same encrypt algorithm |
| INSERT ALL INTO t_user (user_id, user_name, password, email, telephone, creation_date) VALUES (?, ?, ?, ?, ?, DATE ‘2017-08-08’) INTO t_merchant (merchant_id, country_id, merchant_name, business_code, telephone, creation_date) VALUES (?, ?, ?, ?, ?, DATE ‘2017-08-08’) SELECT * FROM DUAL | Supports encryption and rewriting of INSERT ALL VALUES batch insert statements |
| INSERT ALL WHEN creation_date < DATE ‘2017-08-08’ THEN INTO t_user (user_id, user_name, password, email, telephone, creation_date) WHEN creation_date = DATE ‘2017-08-08’ THEN INTO t_user (user_id, user_name, password, email, telephone, creation_date) ELSE INTO t_user (user_id, user_name, password, email, telephone, creation_date) SELECT user_id + 2, user_name, password, email, telephone, creation_date FROM t_user WHERE user_id >= ? | Supports encryption and rewriting of INSERT ALL conditional insertion statements. Encrypted column operations are not supported in conditional expressions |
| DELETE FROM t_user t WHERE t.password IN (?) | Supports simple DELETE statement encryption rewriting |
| UPDATE t_user SET password = ? WHERE user_id = ? | Supports simple UPDATE statement encryption rewriting |
| MERGE INTO t_user t1 USING t_user_bak t2 ON (t1.id = t2.id) WHEN MATCHED THEN UPDATE SET t1.password = t2.password, t1.status = ? DELETE WHERE t1.status = ? WHEN NOT MATCHED THEN INSERT (t1.password, t1.status) VALUES (t2.password, t2.status) | Supports encryption and rewriting of simple MERGE statements. The encryption algorithms of the target table and the source table need to be consistent. If the source table is a subquery, only the encryption of the subquery is supported |
不支持的常用 SQL 列表 #
MySQL #
- DML Statement
| SQL Example | Description |
|---|---|
| - | Subquery encryption and rewriting other than Projection subquery, Table subquery, Join subquery, Predicate subquery and Insert Select subquery are not supported |
| SELECT * FROM t_user u INNER JOIN t_merchant m USING (telephone) INNER JOIN t_order USING (user_id) WHERE u.user_id = ? | The use of different encryption algorithms for associated columns in JOIN queries (including those used in On, Using, Natural Join, and Where) is not supported |
| SELECT * FROM t_user u INNER JOIN t_merchant m ON u.telephone > m.telephone WHERE u.user_id = ? | Using comparison operators such as >, >=, <, <= and so on to perform associated queries in the associated columns in JOIN queries are not supported |
| SELECT u.* FROM t_user u WHERE u.user_id = ? ORDER BY u.telephone | The use of encrypt columns in Order By statements is not supported |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) SELECT 40, ‘zhangsan’, ‘123456’, ‘11@qq.com’, ‘12345678901’, NOW() | Encryption of SELECT constants in INSERT SELECT is not supported |
| - | DELETE JOIN encryption rewriting is not supported |
| - | UPDATE JOIN encryption rewriting is not supported |
| - | MERGE The encryption configuration of the target table and the source table are inconsistent, or the scenario of the source table is a subquery and the target table needs to be encrypted is not supported |
Oracle #
- DML Statement
| SQL Example | Description |
|---|---|
| - | Subquery encryption and rewriting other than Projection subquery, Table subquery, Join subquery, Predicate subquery and Insert Select subquery are not supported |
| SELECT * FROM t_user u LEFT OUTER JOIN t_merchant m USING (telephone) LEFT OUTER JOIN t_order USING (user_id) WHERE user_id = ? | The use of different encryption algorithms for associated columns in JOIN queries (including those used in On, Using, Natural Join, and Where) is not supported |
| SELECT * FROM t_user u FULL OUTER JOIN t_order o ON u.user_id > o.user_id FULL OUTER JOIN t_order_item i ON o.order_id < i.order_id WHERE u.user_id = ? | Using comparison operators such as >, >=, <, <= and so on to perform associated queries in the associated columns in JOIN queries are not supported |
| SELECT u.* FROM t_user u WHERE u.user_id = ? ORDER BY u.telephone | The use of encrypt columns in Order By statements is not supported |
| INSERT INTO t_user (user_id, user_name, password, email, telephone, creation_date) SELECT 40, ‘zhangsan’, ‘123456’, ‘11@qq.com’, ‘12345678901’, NOW() FROM DUAL | Encryption of SELECT constants in INSERT SELECT is not supported |
| - | DELETE JOIN encryption rewriting is not supported |
| - | UPDATE JOIN encryption rewriting is not supported |