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