You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "ClaireLytt (via GitHub)" <gi...@apache.org> on 2026/09/29 05:26:08 UTC
[PR] Throw unsupported exception for OPENQUERY in encrypt feature [shardingsphere]
ClaireLytt opened a new pull request, #39998:
URL: https://github.com/apache/shardingsphere/pull/39998
Closes #39313.
Closes #39977.
Related: #30227
Per maintainer feedback, the community has decided not to pursue full OPENQUERY encrypt rewrite support at this time. Instead, OPENQUERY statements are now explicitly rejected with a clear error message when the encrypt feature is active.
Previously, OPENQUERY (`SELECT/UPDATE/DELETE ... FROM OPENQUERY(...)`) silently bypassed encryption because the encrypt module only inspected `SimpleTableSegment` and ignored `FunctionTableSegment`. This meant encrypted columns could be sent to remote servers in plaintext without any warning.
Changes proposed in this pull request:
- Add `EncryptOpenQuerySupportedChecker` that detects OPENQUERY in SELECT (FROM clause), UPDATE and DELETE (target table) statements, and throws `UnsupportedEncryptSQLException("OPENQUERY")` with message: `The SQL clause 'OPENQUERY' is unsupported in encrypt feature.`
- Register the checker in `EncryptSupportedSQLCheckersBuilder`
- Add 6 focused unit tests covering: SELECT/UPDATE with OPENQUERY, case-insensitive detection, non-OPENQUERY functions, simple tables, and exception assertion
---
Before committing this PR, I'm sure that I have checked the following options:
- [x] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
- [x] I have self-reviewed the commit code.
- [x] I have (or in comment I request) added corresponding labels for the pull request.
- [ ] I have passed maven check locally : `./mvnw clean install -B -T1C -Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
- [ ] I have made corresponding changes to the documentation.
- [x] I have added corresponding unit tests for my changes.
- [ ] I have updated the Release Notes of the current development version. For more details, see [Update Release Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/)
- [x] I have disclosed the tool and affected files or scope for any material AI assistance, as required by the [AI-assisted contribution policy].
**AI Disclosure:** Claude Code assisted with locating existing checker patterns, reviewing coding standards compliance, and drafting the PR description. All production and test code was written and reviewed by the contributor.
[AI-assisted contribution policy]: https://github.com/apache/shardingsphere/blob/master/AI_POLICY.md
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Throw unsupported exception for OPENQUERY in encrypt feature [shardingsphere]
Posted by "ClaireLytt (via GitHub)" <gi...@apache.org>.
ClaireLytt commented on PR #39998:
URL: https://github.com/apache/shardingsphere/pull/39998#issuecomment-5907239764
This PR adds `EncryptOpenQuerySupportedChecker`, a fail-closed checker that detects OPENQUERY in SQL Server DML statements and rejects them with `UnsupportedEncryptSQLException` when Encrypt is active. The checker traverses the full AST produced by the SQL Server parser visitor, matching the expression-type coverage of the existing `SubqueryExtractor`.
Below is the complete list of SQL forms that trigger rejection.
## SELECT
```sql
-- FROM (direct, aliased, all JOIN types, comma-join, APPLY)
SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')
SELECT * FROM t JOIN OPENQUERY(LinkedSvr, 'SELECT col FROM t') q ON t.id = q.id
SELECT * FROM t CROSS APPLY OPENQUERY(LinkedSvr, 'SELECT col FROM t') q
-- Derived table
SELECT * FROM (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')) sub
-- SELECT INTO
SELECT * INTO #tmp FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')
-- Scalar subquery in projection
SELECT (SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')) AS c FROM t
-- WHERE: EXISTS / NOT EXISTS / IN / scalar comparison / ANY|ALL|SOME
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
SELECT * FROM t WHERE NOT EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
SELECT * FROM t WHERE col IN (SELECT col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
SELECT * FROM t WHERE col = (SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
SELECT * FROM t WHERE col > ALL (SELECT col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
-- WHERE: BETWEEN / function parameter / CASE WHEN
SELECT * FROM t WHERE id BETWEEN (SELECT MIN(id) FROM OPENQUERY(LinkedSvr, 'SELECT id FROM t')) AND 100
SELECT * FROM t WHERE ISNULL((SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')), 0) > 0
SELECT CASE WHEN EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t')) THEN 1 ELSE 0 END FROM t
-- JOIN ON condition with subquery
SELECT * FROM t1 JOIN t2 ON EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
-- HAVING / ORDER BY
SELECT col, COUNT(*) FROM t GROUP BY col
HAVING COUNT(*) > (SELECT COUNT(*) FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
SELECT * FROM t ORDER BY (SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
-- CTE (single and multiple)
WITH cte AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')) SELECT * FROM cte
WITH a AS (SELECT 1 n), b AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')) SELECT * FROM a, b
```
## UPDATE
```sql
-- Direct target
UPDATE OPENQUERY(LinkedSvr, 'SELECT col FROM t') SET col = 1
-- UPDATE FROM (SQL Server specific)
UPDATE t SET t.col = q.col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t') q
UPDATE t SET t.col = q.col FROM t JOIN OPENQUERY(LinkedSvr, 'SELECT id, col FROM t') q ON t.id = q.id
-- Alias target
UPDATE q SET col = 1 FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t') q
-- SET subquery / WHERE subquery / CTE prefix
UPDATE t SET col = (SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
UPDATE t SET col = 1 WHERE EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
WITH cte AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
UPDATE t SET col = 1 WHERE EXISTS (SELECT 1 FROM cte)
```
## DELETE
```sql
-- Direct target
DELETE FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t') WHERE col = 1
DELETE OPENQUERY(LinkedSvr, 'SELECT col FROM t') WHERE col = 1
-- Multi-table DELETE (FROM JOIN)
DELETE t FROM t JOIN OPENQUERY(LinkedSvr, 'SELECT id FROM t') q ON t.id = q.id
DELETE q FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t') q
-- WHERE subquery / CTE prefix
DELETE FROM t WHERE EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
WITH cte AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
DELETE FROM t WHERE EXISTS (SELECT 1 FROM cte)
```
## INSERT
```sql
-- Direct target (rowSetFunction)
INSERT INTO OPENQUERY(LinkedSvr, 'SELECT col FROM t') VALUES (1)
INSERT INTO OPENQUERY(LinkedSvr, 'SELECT col FROM t') (col) VALUES (1)
-- INSERT SELECT
INSERT INTO t SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')
-- VALUES subquery
INSERT INTO t (col) VALUES ((SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t')))
-- CTE prefix
WITH cte AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
INSERT INTO t SELECT * FROM cte
```
## MERGE
```sql
-- USING OPENQUERY / TARGET OPENQUERY
MERGE t USING OPENQUERY(LinkedSvr, 'SELECT id, col FROM t') q ON t.id = q.id
WHEN MATCHED THEN UPDATE SET t.col = q.col
MERGE OPENQUERY(LinkedSvr, 'SELECT col FROM t') AS target
USING src ON target.id = src.id
WHEN MATCHED THEN UPDATE SET col = src.col
-- USING derived table
MERGE t USING (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT id, col FROM t')) q ON t.id = q.id
WHEN MATCHED THEN UPDATE SET t.col = q.col
-- ON condition / WHEN AND condition with subquery
MERGE t USING src ON EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t'))
WHEN MATCHED THEN DELETE
MERGE t USING src ON t.id = src.id
WHEN MATCHED AND EXISTS (SELECT 1 FROM OPENQUERY(LinkedSvr, 'SELECT 1 FROM t')) THEN DELETE
-- WHEN UPDATE SET subquery / CTE prefix
MERGE t USING src ON t.id = src.id
WHEN MATCHED THEN UPDATE SET col = (SELECT TOP 1 col FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
WITH cte AS (SELECT * FROM OPENQUERY(LinkedSvr, 'SELECT col FROM t'))
MERGE t USING cte ON t.id = cte.id WHEN MATCHED THEN DELETE
```
## AST Coverage Summary
| Layer | Types Traversed |
| ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Statement fields | SELECT (from, where, with, combine, projections, having, orderBy), UPDATE (table, from, where, with, assignment), DELETE (table, where, with), INSERT (rowSetFunction, insertSelect, with, values), MERGE (target, source, with, expression, whenAndThens) |
| Table types | FunctionTableSegment, JoinTableSegment (left + right + condition), SubqueryTableSegment, DeleteMultiTableSegment, CollectionTableSegment |
| Expression types | ExistsSubqueryExpression, SubqueryExpressionSegment, QuantifySubqueryExpression, BinaryOperationExpression, InExpression, NotExpression, BetweenExpression, ListExpression, FunctionSegment, CaseWhenExpression, TypeCastExpression, CollateExpression |
> Expression-type coverage matches `SubqueryExtractor.extractSubquerySegmentsFromExpression()`. Types handled by SubqueryExtractor but absent from the checker (`DatetimeExpression`, `MatchAgainstExpression`) are not produced by the SQL Server parser visitor.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Throw unsupported exception for OPENQUERY in encrypt feature [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu merged PR #39998:
URL: https://github.com/apache/shardingsphere/pull/39998
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org