You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "sha174n (via GitHub)" <gi...@apache.org> on 2026/07/15 18:02:24 UTC
[PR] fix(dataset): validate stored column and metric expressions at query time [superset]
sha174n opened a new pull request, #42087:
URL: https://github.com/apache/superset/pull/42087
### SUMMARY
Stored calculated-column and metric expressions are validated at save time (`validate_stored_expression`) and in the expression-validator helper, but not at the point where they are rendered into the query. The save-time check can be deferred past — Jinja templating (a `{{ ... }}` block is blanked before the save-time parse), the create path (which has no expression validation), or pre-existing data — after which the expression reaches `literal_column(...)` unvalidated.
This routes stored expressions through the same `validate_adhoc_subquery` gate that adhoc expressions already use, applied at each query sink: `TableColumn.get_sqla_col` and `SqlMetric.get_sqla_col`, `convert_tbl_column_to_sqla_col`, and the timestamp-expression path. That makes the sub-query policy and RLS injection apply where the SQL is actually built, consistent with the adhoc path. Save-time validation stays as defense in depth. Completes the hardening started in #40392.
### TESTING INSTRUCTIONS
`pytest tests/unit_tests/connectors/sqla/models_test.py`
Adds `test_get_sqla_col_validates_stored_expression_at_query_time`: `get_sqla_col` enforces the sub-query gate at query time even when the save-time check is bypassed.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "gabotorresruiz (via GitHub)" <gi...@apache.org>.
gabotorresruiz commented on code in PR #42087:
URL: https://github.com/apache/superset/pull/42087#discussion_r3730861964
##########
superset/connectors/sqla/models.py:
##########
@@ -1103,6 +1113,7 @@ def get_sqla_col(
msg=msg,
)
) from ex
+ expression = self._validate_stored_expression(expression)
Review Comment:
Not a blocker, more a consistency question: should this raise the way the adhoc siblings do?
`validate_adhoc_subquery` raises `SupersetSecurityException`, and that carries `status = 403` (`superset/exceptions.py:168`). The adhoc paths deliberately do not let it escape: `adhoc_metric_to_sqla` at `models.py:1760` and `adhoc_column_to_sqla` at `models.py:1855` both convert it with `raise QueryObjectValidationError(ex.message) from ex`, which `query_context_processor.py:142` catches, records as the query's error message, and surfaces as a 400.
Here it propagates raw, and I traced that nothing in between converts it (`ChartDataCommand.run` only maps `CacheLoadError`). So a dashboard with a pre-existing calculated column containing a sub-query would get a 403 rather than the chart-level "Custom SQL fields cannot contain sub-queries" message, and the failure never gets recorded on the query result. It also makes those failures look like access-control errors in logs and alerting, which sends whoever debugs it to the wrong place.
Wrapping it inside the shared helper would cover all five sinks at once. Or is there a reason to keep the security typing on this path?
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "sha174n (via GitHub)" <gi...@apache.org>.
sha174n merged PR #42087:
URL: https://github.com/apache/superset/pull/42087
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "sha174n (via GitHub)" <gi...@apache.org>.
sha174n commented on code in PR #42087:
URL: https://github.com/apache/superset/pull/42087#discussion_r3731717517
##########
tests/unit_tests/connectors/sqla/models_test.py:
##########
@@ -1177,3 +1179,86 @@ def test_validate_stored_expression_rejects_subquery_around_jinja(
None,
"(SELECT password FROM ab_user LIMIT 1) {# x #}",
)
+
+
+def test_get_sqla_col_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored calculated-column expression must be validated at the query sink,
+ not only at save time. ``get_sqla_col`` routes the expression through
+ ``validate_adhoc_subquery`` so a disallowed sub-query is rejected even when
+ it reaches the query with the save-time check bypassed (templating, the
+ create path, or older data). Locks in the query-time gate.
+ """
+ tc = TableColumn(
+ column_name="leak",
+ expression="(SELECT password FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_sqla_col()
+ spy.assert_called_once()
+
+
+def test_get_timestamp_expression_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ The timestamp-expression sink must enforce the same query-time gate as
+ ``get_sqla_col``: a stored datetime column expression is routed through
+ ``validate_adhoc_subquery`` before it reaches ``literal_column``, so a
+ disallowed sub-query is rejected on the time-grained query path too.
+ """
+ tc = TableColumn(
+ column_name="ds",
+ expression="(SELECT ts FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_timestamp_expression(time_grain=None)
+ spy.assert_called_once()
+
+
+def test_get_sqla_col_falls_back_when_stored_expression_unparseable(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored expression using dialect-specific syntax that sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL) pre-dates the query-time gate and went
+ to the query unparsed. A parse failure must fall back to the raw expression
+ rather than break the query; a genuine sub-query still parses and is caught.
+ """
+ tc = TableColumn(column_name="ds", expression="DATE_ADD(ds, 1)")
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "mysql"
+ mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"),
+ )
+ literal = mocker.patch("superset.connectors.sqla.models.literal_column")
+ tc.get_sqla_col()
+ # The raw expression reaches ``literal_column`` unchanged; no exception.
+ assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
Review Comment:
Reworked this into a real-parse test: it now drives the actual validator through get_sqla_col with a MySQL backend, so it fails if the query-time gate is removed rather than relying on a mocked validator. 271c5f5612
##########
superset/models/helpers.py:
##########
@@ -259,6 +260,31 @@ def validate_adhoc_subquery(
return parsed_statement.format() if rls_applied else sql
+def validate_stored_expression_at_query_time(
+ expression: str,
+ database: Database,
+ catalog: str | None,
+ schema: str,
+ engine: str,
+) -> str:
+ """
+ Validate a stored column/metric expression at the point of use, applying the
+ same sub-query policy and RLS injection as adhoc expressions. The save-time
+ check can be deferred past (Jinja templating, the create path, older data),
+ so the query sink is the reliable place to enforce it.
+
+ Stored expressions can contain dialect-specific syntax sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL); such expressions pre-date this gate and
+ went straight to the query unparsed, so a parse failure falls back to the raw
+ expression rather than breaking the query. A genuine sub-query still parses
+ and is caught.
+ """
+ try:
+ return validate_adhoc_subquery(expression, database, catalog, schema, engine)
+ except SupersetParseError:
+ return expression
Review Comment:
Added a permissive-dialect re-check as a detector before the raw fallback (and a warning log when validation is skipped), so a sub-query next to unparseable dialect syntax is still caught; updated the docstring accordingly and added a DATE_ADD(ds, 1) + (SELECT 1) test on MySQL. 271c5f5612
##########
tests/unit_tests/connectors/sqla/models_test.py:
##########
@@ -1177,3 +1179,86 @@ def test_validate_stored_expression_rejects_subquery_around_jinja(
None,
"(SELECT password FROM ab_user LIMIT 1) {# x #}",
)
+
+
+def test_get_sqla_col_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored calculated-column expression must be validated at the query sink,
+ not only at save time. ``get_sqla_col`` routes the expression through
+ ``validate_adhoc_subquery`` so a disallowed sub-query is rejected even when
+ it reaches the query with the save-time check bypassed (templating, the
+ create path, or older data). Locks in the query-time gate.
+ """
+ tc = TableColumn(
+ column_name="leak",
+ expression="(SELECT password FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_sqla_col()
+ spy.assert_called_once()
+
+
+def test_get_timestamp_expression_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ The timestamp-expression sink must enforce the same query-time gate as
+ ``get_sqla_col``: a stored datetime column expression is routed through
+ ``validate_adhoc_subquery`` before it reaches ``literal_column``, so a
+ disallowed sub-query is rejected on the time-grained query path too.
+ """
+ tc = TableColumn(
+ column_name="ds",
+ expression="(SELECT ts FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_timestamp_expression(time_grain=None)
+ spy.assert_called_once()
+
+
+def test_get_sqla_col_falls_back_when_stored_expression_unparseable(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored expression using dialect-specific syntax that sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL) pre-dates the query-time gate and went
+ to the query unparsed. A parse failure must fall back to the raw expression
+ rather than break the query; a genuine sub-query still parses and is caught.
+ """
+ tc = TableColumn(column_name="ds", expression="DATE_ADD(ds, 1)")
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "mysql"
+ mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"),
+ )
+ literal = mocker.patch("superset.connectors.sqla.models.literal_column")
+ tc.get_sqla_col()
+ # The raw expression reaches ``literal_column`` unchanged; no exception.
+ assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
Review Comment:
Reworked all sink tests to run the real validator via a _stored_col helper (real MySQL parse), and added coverage for the metric sink and convert_tbl_column_to_sqla_col. 271c5f5612
##########
superset/connectors/sqla/models.py:
##########
@@ -1103,6 +1113,7 @@ def get_sqla_col(
msg=msg,
)
) from ex
+ expression = self._validate_stored_expression(expression)
Review Comment:
Good call, the shared helper now converts SupersetSecurityException to QueryObjectValidationError, so all five sinks surface it as a chart-level 400 like adhoc_metric_to_sqla/adhoc_column_to_sqla instead of a raw 403. 271c5f5612
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "gabotorresruiz (via GitHub)" <gi...@apache.org>.
gabotorresruiz commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5207887503
Two small things on the description and docs, neither blocking.
The summary says the save-time check can be deferred past via "the create path (which has no expression validation)". That one does not quite hold: `DatasetPostSchema` (`superset/datasets/schemas.py:160`) does not accept `columns` or `metrics` at all, so create cannot set an expression in the first place. The write paths that really skip validation are import v1 (`ImportV1ColumnSchema.expression` at `schemas.py:275`, `ImportV1MetricSchema.expression` at `schemas.py:315`, and nothing under `superset/commands/dataset/importers/v1/` calls `validate_stored_expression`) and duplicate (`superset/commands/dataset/duplicate.py:91,104`, which copies expressions verbatim). Naming those two makes the case for this PR stronger, and both are live today.
Second, this is a user-visible behavior change worth an `UPDATING.md` entry under `## Next`. `ALLOW_ADHOC_SUBQUERY` defaults to False, so any existing dataset with a sub-query in a stored column or metric works today and starts failing after this lands, with no migration step and nothing in the error naming the offending dataset column. There is precedent for exactly this at `UPDATING.md:983` (PR 19242, when adhoc sub-queries were first disabled by default). Something short like:
```md
- [42087](https://github.com/apache/superset/pull/42087): Stored calculated-column and metric expressions are now validated at query time with the same sub-query policy as adhoc expressions. Datasets whose stored expressions contain sub-queries will fail at query time unless `ALLOW_ADHOC_SUBQUERY` is enabled.
```
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5209975103
AI Code Review is in progress (usually takes 3 to 15 minutes unless it's a very large PR).
<details>
<summary>
Bito Usage Guide
</summary>
**Commands**
Type the following command in the pull request comment and save the comment.
- `/review` - Manually triggers a full AI review.
- `/pause` - Pauses automatic reviews on this pull request.
- `/resume` - Resumes automatic reviews.
- `/resolve` - Marks all Bito-posted review comments as resolved.
- `/abort` - Cancels all in-progress reviews.
Refer to the <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/available-commands" target="_blank" rel="noopener noreferrer">documentation</a> for additional commands.
**Configuration**
This repository uses `Superset` You can customize the agent settings <a href="https://alpha.bito.ai/home/ai-agents/code-review-agent" target="_blank" rel="noopener noreferrer">here</a> or contact your Bito workspace admin at evan@preset.io.
**Documentation & Help**
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/getting-started/install-run-using-bito-cloud/create-or-customize-an-agent-instance" target="_blank" rel="noopener noreferrer">Customize agent settings</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/implementing-custom-code-review-rules" target="_blank" rel="noopener noreferrer">Review rules</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent" target="_blank" rel="noopener noreferrer">General documentation</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/faqs" target="_blank" rel="noopener noreferrer">FAQ</a>
</details>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5012955099
<!-- Bito Reply -->
The flagged issue is correct. In `test_get_sqla_col_falls_back_when_stored_expression_unparseable`, the test patches `validate_adhoc_subquery` but does not verify that it was actually called. This means the test could pass even if the call to `_validate_stored_expression` (which invokes the validator) were accidentally removed from `get_sqla_col`, as `literal_column` would still receive the raw expression. To resolve this, you should capture the patch as a spy and assert that it was called.
Would you like me to implement this fix for you? If so, I can also check the other comments on this PR and apply fixes for them as well.
**tests/unit_tests/connectors/sqla/models_test.py**
```
spy = mocker.patch(
"superset.models.helpers.validate_adhoc_subquery",
side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"),
)
literal = mocker.patch("superset.connectors.sqla.models.literal_column")
tc.get_sqla_col()
# The raw expression reaches ``literal_column`` unchanged; no exception.
assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
spy.assert_called_once()
```
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "netlify[bot] (via GitHub)" <gi...@apache.org>.
netlify[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5208932307
### <span aria-hidden="true">✅</span> Deploy Preview for *superset-docs-preview* ready!
| Name | Link |
|:-:|------------------------|
|<span aria-hidden="true">🔨</span> Latest commit | 271c5f56123c13eb6661350bf5e4efc3db238fbc |
|<span aria-hidden="true">🔍</span> Latest deploy log | https://app.netlify.com/projects/superset-docs-preview/deploys/6a74ee9ed6c0080008f93a2d |
|<span aria-hidden="true">😎</span> Deploy Preview | [https://deploy-preview-42087--superset-docs-preview.netlify.app](https://deploy-preview-42087--superset-docs-preview.netlify.app) |
|<span aria-hidden="true">📱</span> Preview on mobile | <details><summary> Toggle QR Code... </summary><br /><br /><br /><br />_Use your smartphone camera to open QR code link._</details> |
|<span aria-hidden="true">🤖</span> Make changes | [Run an agent on this branch](https://app.netlify.com/projects/superset-docs-preview/agent-runs#DctNCoAgEEDhu8w6SSr6u0HHmJyRBEubLIro7rl48DbfC1HCGhOMAAXMgptZ8lt3l0cKwqT4jqL2k-VRya2sLvSOMLmwZUAcfXgmyqTFrmEemFqjda9zdqixIvh-) |
---
<!-- [superset-docs-preview Preview](https://deploy-preview-42087--superset-docs-preview.netlify.app) -->
_To edit notification comments on pull requests, go to your [Netlify project configuration](https://app.netlify.com/projects/superset-docs-preview/configuration/notifications#deploy-notifications)._
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-4983780009
AI Code Review is in progress (usually takes 3 to 15 minutes unless it's a very large PR).
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "gabotorresruiz (via GitHub)" <gi...@apache.org>.
gabotorresruiz commented on code in PR #42087:
URL: https://github.com/apache/superset/pull/42087#discussion_r3730858946
##########
superset/models/helpers.py:
##########
@@ -259,6 +260,31 @@ def validate_adhoc_subquery(
return parsed_statement.format() if rls_applied else sql
+def validate_stored_expression_at_query_time(
+ expression: str,
+ database: Database,
+ catalog: str | None,
+ schema: str,
+ engine: str,
+) -> str:
+ """
+ Validate a stored column/metric expression at the point of use, applying the
+ same sub-query policy and RLS injection as adhoc expressions. The save-time
+ check can be deferred past (Jinja templating, the create path, older data),
+ so the query sink is the reliable place to enforce it.
+
+ Stored expressions can contain dialect-specific syntax sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL); such expressions pre-date this gate and
+ went straight to the query unparsed, so a parse failure falls back to the raw
+ expression rather than breaking the query. A genuine sub-query still parses
+ and is caught.
+ """
+ try:
+ return validate_adhoc_subquery(expression, database, catalog, schema, engine)
+ except SupersetParseError:
+ return expression
Review Comment:
Hi @sha174n, thanks for this. Enforcing at the query sink instead of trusting save time is the right call.
This block worries me a bit, mostly because of what the docstring right above it promises. `SupersetParseError` covers two different situations: benign dialect syntax sqlglot cannot handle, and SQL that fails to parse precisely because something was appended to it. Swallowing both means "A genuine sub-query still parses and is caught" does not hold whenever the sub-query sits next to unparseable syntax.
I checked this on the branch with the real code, no mocks, going through `TableColumn.get_sqla_col` with a MySQL backend:
- `(SELECT 1)` raises `SupersetSecurityException`. The gate works.
- `DATE_ADD(ds, 1)` falls back to the raw expression. Intended, and it is your own example from the docstring.
- `DATE_ADD(ds, 1) + (SELECT 1)` also falls back, so the sub-query reaches `literal_column` unvalidated.
- `1; DROP TABLE t` on sqlite falls back too, so the multi-statement rule that `test_validate_stored_expression_rejects_multi_statement` locks in at save time has no query-time counterpart.
To be clear about scope: master has no query-time gate at all, so this is not a regression, it just means the hardening is narrower than the docstring claims. It is reachable through the same door this PR is closing, since save time skips validation when Jinja is present and the skeleton does not parse (`models.py:898`), and the template processor strips the Jinja before we get here.
One cheap way to narrow the gap is to re-check with the permissive dialect purely as a detector before giving up, and log when we do give up so a skipped gate is visible:
```python
try:
return validate_adhoc_subquery(expression, database, catalog, schema, engine)
except SupersetParseError:
# A parse failure is ambiguous: benign dialect syntax sqlglot cannot
# handle, or SQL that is unparseable because something was appended to
# it. Re-check with the permissive dialect as a detector before falling
# back to the raw expression.
try:
validate_adhoc_subquery(expression, database, catalog, schema, "base")
except SupersetParseError:
logger.warning(
"Skipping query-time validation of unparseable stored expression"
)
return expression
```
I confirmed this actually buys something: `SQLStatement("DATE_ADD(ds, 1) + (SELECT 1)", "base")` parses and reports `has_subquery()` as True, while `SQLStatement("DATE_ADD(ds, 1)", "base")` parses clean and reports False, so the benign case you documented still falls through. It will not catch deliberately malformed SQL, and that is fine. At minimum I think the docstring should stop promising that a genuine sub-query is always caught. A test with `DATE_ADD(ds, 1) + (SELECT 1)` on MySQL would lock in whichever behavior you settle on.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "sha174n (via GitHub)" <gi...@apache.org>.
sha174n commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5255519943
@rusackas addressed in e5a1bb3. The fallback test now patches the validator with `wraps=`, so the real parser still runs, and asserts both passes fired (`("DATE_ADD(ds, 1)", "mysql")` then `("SELECT DATE_ADD(ds, 1)", "base")`). Confirmed it is load-bearing by deleting the call locally: the test fails without it.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5013004184
AI Code Review is in progress (usually takes 3 to 15 minutes unless it's a very large PR).
<details>
<summary>
Bito Usage Guide
</summary>
**Commands**
Type the following command in the pull request comment and save the comment.
- `/review` - Manually triggers a full AI review.
- `/pause` - Pauses automatic reviews on this pull request.
- `/resume` - Resumes automatic reviews.
- `/resolve` - Marks all Bito-posted review comments as resolved.
- `/abort` - Cancels all in-progress reviews.
Refer to the <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/available-commands" target="_blank" rel="noopener noreferrer">documentation</a> for additional commands.
**Configuration**
This repository uses `Superset` You can customize the agent settings <a href="https://alpha.bito.ai/home/ai-agents/code-review-agent" target="_blank" rel="noopener noreferrer">here</a> or contact your Bito workspace admin at evan@preset.io.
**Documentation & Help**
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/getting-started/install-run-using-bito-cloud/create-or-customize-an-agent-instance" target="_blank" rel="noopener noreferrer">Customize agent settings</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/implementing-custom-code-review-rules" target="_blank" rel="noopener noreferrer">Review rules</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent" target="_blank" rel="noopener noreferrer">General documentation</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/faqs" target="_blank" rel="noopener noreferrer">FAQ</a>
</details>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "codecov[bot] (via GitHub)" <gi...@apache.org>.
codecov[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-4983954612
## [Codecov](https://app.codecov.io/gh/apache/superset/pull/42087?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:x: Patch coverage is `40.00000%` with `3 lines` in your changes missing coverage. Please review.
:white_check_mark: Project coverage is 64.28%. Comparing base ([`90f9238`](https://app.codecov.io/gh/apache/superset/commit/90f9238f8a5a9cc8b604e74559e2125b0c49a08d?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`b643cfd`](https://app.codecov.io/gh/apache/superset/commit/b643cfd30cb19a8f86d00cb4e26ba58399187a1b?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
| [Files with missing lines](https://app.codecov.io/gh/apache/superset/pull/42087?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
|---|---|---|
| [superset/connectors/sqla/models.py](https://app.codecov.io/gh/apache/superset/pull/42087?src=pr&el=tree&filepath=superset%2Fconnectors%2Fsqla%2Fmodels.py&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-c3VwZXJzZXQvY29ubmVjdG9ycy9zcWxhL21vZGVscy5weQ==) | 33.33% | [1 Missing and 1 partial :warning: ](https://app.codecov.io/gh/apache/superset/pull/42087?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [superset/models/helpers.py](https://app.codecov.io/gh/apache/superset/pull/42087?src=pr&el=tree&filepath=superset%2Fmodels%2Fhelpers.py&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-c3VwZXJzZXQvbW9kZWxzL2hlbHBlcnMucHk=) | 50.00% | [1 Missing :warning: ](https://app.codecov.io/gh/apache/superset/pull/42087?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## master #42087 +/- ##
==========================================
- Coverage 64.45% 64.28% -0.17%
==========================================
Files 2747 2747
Lines 153926 153931 +5
Branches 35304 35305 +1
==========================================
- Hits 99207 98960 -247
- Misses 52806 53068 +262
+ Partials 1913 1903 -10
```
| [Flag](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Coverage Δ | |
|---|---|---|
| [hive](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `39.05% <0.00%> (-0.01%)` | :arrow_down: |
| [mysql](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
| [presto](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `41.05% <20.00%> (+0.01%)` | :arrow_up: |
| [python](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `57.69% <40.00%> (-0.33%)` | :arrow_down: |
| [sqlite](https://app.codecov.io/gh/apache/superset/pull/42087/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `57.54% <40.00%> (-0.01%)` | :arrow_down: |
Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#carryforward-flags-in-the-pull-request-comment) to find out more.
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/superset/pull/42087?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "rusackas (via GitHub)" <gi...@apache.org>.
rusackas commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5018546025
Codeant's thread on `models_test.py` is still open, and it seems right. `test_get_sqla_col_falls_back_when_stored_expression_unparseable` never asserts `validate_adhoc_subquery` got called, so it'd still pass even if the query-time gate got ripped out entirely. Mind adding the same `spy.assert_called_once()` the two tests above it use? Otherwise this looks like a solid continuation of #40392.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "sha174n (via GitHub)" <gi...@apache.org>.
sha174n commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5256635569
Rebased onto current master in `cf2e51fb` — the conflict is cleared and the PR is mergeable again.
That also fixes the two CodeQL failures, which were not code issues: the branch had caught master mid-dependabot-bump, with `codeql-action/init` at v4.37.6 and `analyze` still at v4.37.5, so init wrote a config the older analyzer refused to load. Master aligned both pins in #43040, which came in with the merge.
Also folded in a small no-op cleanup of `validate_stored_expression_at_query_time`: the detector loop's two try/except blocks are now one, and the optional schema argument is normalised once in the callee instead of at each of the three call sites. Behaviour is unchanged and the sink tests still pass.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5258570338
AI Code Review is in progress (usually takes 3 to 15 minutes unless it's a very large PR).
<details>
<summary>
Bito Usage Guide
</summary>
**Commands**
Type the following command in the pull request comment and save the comment.
- `/review` - Manually triggers a full AI review.
- `/pause` - Pauses automatic reviews on this pull request.
- `/resume` - Resumes automatic reviews.
- `/resolve` - Marks all Bito-posted review comments as resolved.
- `/abort` - Cancels all in-progress reviews.
Refer to the <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/available-commands" target="_blank" rel="noopener noreferrer">documentation</a> for additional commands.
**Configuration**
This repository uses `Superset` You can customize the agent settings <a href="https://alpha.bito.ai/home/ai-agents/code-review-agent" target="_blank" rel="noopener noreferrer">here</a> or contact your Bito workspace admin at evan@preset.io.
**Documentation & Help**
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/getting-started/install-run-using-bito-cloud/create-or-customize-an-agent-instance" target="_blank" rel="noopener noreferrer">Customize agent settings</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/implementing-custom-code-review-rules" target="_blank" rel="noopener noreferrer">Review rules</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent" target="_blank" rel="noopener noreferrer">General documentation</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/faqs" target="_blank" rel="noopener noreferrer">FAQ</a>
</details>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "gabotorresruiz (via GitHub)" <gi...@apache.org>.
gabotorresruiz commented on code in PR #42087:
URL: https://github.com/apache/superset/pull/42087#discussion_r3730860607
##########
tests/unit_tests/connectors/sqla/models_test.py:
##########
@@ -1177,3 +1179,86 @@ def test_validate_stored_expression_rejects_subquery_around_jinja(
None,
"(SELECT password FROM ab_user LIMIT 1) {# x #}",
)
+
+
+def test_get_sqla_col_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored calculated-column expression must be validated at the query sink,
+ not only at save time. ``get_sqla_col`` routes the expression through
+ ``validate_adhoc_subquery`` so a disallowed sub-query is rejected even when
+ it reaches the query with the save-time check bypassed (templating, the
+ create path, or older data). Locks in the query-time gate.
+ """
+ tc = TableColumn(
+ column_name="leak",
+ expression="(SELECT password FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_sqla_col()
+ spy.assert_called_once()
+
+
+def test_get_timestamp_expression_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ The timestamp-expression sink must enforce the same query-time gate as
+ ``get_sqla_col``: a stored datetime column expression is routed through
+ ``validate_adhoc_subquery`` before it reaches ``literal_column``, so a
+ disallowed sub-query is rejected on the time-grained query path too.
+ """
+ tc = TableColumn(
+ column_name="ds",
+ expression="(SELECT ts FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_timestamp_expression(time_grain=None)
+ spy.assert_called_once()
+
+
+def test_get_sqla_col_falls_back_when_stored_expression_unparseable(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored expression using dialect-specific syntax that sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL) pre-dates the query-time gate and went
+ to the query unparsed. A parse failure must fall back to the raw expression
+ rather than break the query; a genuine sub-query still parses and is caught.
+ """
+ tc = TableColumn(column_name="ds", expression="DATE_ADD(ds, 1)")
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "mysql"
+ mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"),
+ )
+ literal = mocker.patch("superset.connectors.sqla.models.literal_column")
+ tc.get_sqla_col()
+ # The raw expression reaches ``literal_column`` unchanged; no exception.
+ assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
Review Comment:
Agreeing with @rusackas and the bot thread here, and I would go one step further rather than only adding `spy.assert_called_once()`.
All three new tests patch `superset.models.helpers.validate_adhoc_subquery`, which is the thing being wired in, so between them they prove the call happens and nothing else. I ran them against master with only this test file overlaid: the two `..._at_query_time` tests fail there, so those are real regression tests, but this one passes, so it currently pins nothing.
The save-time tests right above in this same file already show the pattern that does not need the mock: build a `MagicMock(spec=Database)` with a backend and let the real validator run. The query-time sinks work the same way. I ran these two against the branch and both pass, and the first one is the sharp one since it fails against master:
```python
def _stored_col(expression: str, backend: str, mocker: MockerFixture) -> TableColumn:
tc = TableColumn(column_name="ds", expression=expression)
tc.table = mocker.MagicMock()
tc.table.database.backend = backend
tc.table.catalog = None
tc.table.schema = "public"
tc.db_engine_spec.engine = backend
return tc
def test_get_sqla_col_rejects_stored_subquery(mocker: MockerFixture) -> None:
tc = _stored_col("(SELECT 1)", "mysql", mocker)
with pytest.raises(SupersetSecurityException):
tc.get_sqla_col()
def test_get_sqla_col_falls_back_when_unparseable(mocker: MockerFixture) -> None:
literal = mocker.patch("superset.connectors.sqla.models.literal_column")
_stored_col("DATE_ADD(ds, 1)", "mysql", mocker).get_sqla_col()
assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
```
That way the fallback case is anchored to a real MySQL parse failure instead of a mocked one, and the rejection case proves the policy rather than the wiring. The metric sink and `convert_tbl_column_to_sqla_col` have no test at all right now, which is also what codecov is pointing at with the uncovered lines. One test each on the same helper would close it.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "sha174n (via GitHub)" <gi...@apache.org>.
sha174n commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5255522316
@gabotorresruiz both correct, thanks, addressed in e5a1bb3.
You are right that create cannot set an expression: `DatasetPostSchema` has no `columns`/`metrics`, `DatasetPutSchema` does and `update.py:312` validates it. The docstring now names the two paths that really persist expressions as-is, v1 import and `duplicate.py`, instead of create. `UPDATING.md` has an entry under `## Next` describing the query-time check, the `ALLOW_ADHOC_SUBQUERY` default, and the fact that the error does not name the offending column.
Your point about parse failures being ambiguous turned up two more cases the fallback let through, both verified against the real sinks:
- `DISTINCT (SELECT ...)` and `*, (SELECT ...)` only parse inside a select list, so parsing the bare expression failed in every dialect.
- `1; DROP TABLE ab_user` is unparseable as a single statement, so it also took the fallback, while save time rejects it.
The detector now retries the synthetic `SELECT <expr>` form that `validate_stored_expression` already uses, and rejects a script that parses to more than one statement. A semicolon inside a string literal still parses as one statement, so `CASE WHEN x = 'a;b' THEN 1 END` is unaffected; there is a test for that.
One thing I have not changed: the PR description still has the create-path wording. Leaving that edit to a maintainer pass rather than rewriting the body under review.
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "codeant-ai-for-open-source[bot] (via GitHub)" <gi...@apache.org>.
codeant-ai-for-open-source[bot] commented on code in PR #42087:
URL: https://github.com/apache/superset/pull/42087#discussion_r3609208436
##########
tests/unit_tests/connectors/sqla/models_test.py:
##########
@@ -1177,3 +1179,86 @@ def test_validate_stored_expression_rejects_subquery_around_jinja(
None,
"(SELECT password FROM ab_user LIMIT 1) {# x #}",
)
+
+
+def test_get_sqla_col_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored calculated-column expression must be validated at the query sink,
+ not only at save time. ``get_sqla_col`` routes the expression through
+ ``validate_adhoc_subquery`` so a disallowed sub-query is rejected even when
+ it reaches the query with the save-time check bypassed (templating, the
+ create path, or older data). Locks in the query-time gate.
+ """
+ tc = TableColumn(
+ column_name="leak",
+ expression="(SELECT password FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_sqla_col()
+ spy.assert_called_once()
+
+
+def test_get_timestamp_expression_validates_stored_expression_at_query_time(
+ mocker: MockerFixture,
+) -> None:
+ """
+ The timestamp-expression sink must enforce the same query-time gate as
+ ``get_sqla_col``: a stored datetime column expression is routed through
+ ``validate_adhoc_subquery`` before it reaches ``literal_column``, so a
+ disallowed sub-query is rejected on the time-grained query path too.
+ """
+ tc = TableColumn(
+ column_name="ds",
+ expression="(SELECT ts FROM ab_user LIMIT 1)",
+ )
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "sqlite"
+ spy = mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetSecurityException(
+ SupersetError(
+ message="Sub-queries are not allowed in stored expressions.",
+ error_type=SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR,
+ level=ErrorLevel.ERROR,
+ )
+ ),
+ )
+ with pytest.raises(SupersetSecurityException):
+ tc.get_timestamp_expression(time_grain=None)
+ spy.assert_called_once()
+
+
+def test_get_sqla_col_falls_back_when_stored_expression_unparseable(
+ mocker: MockerFixture,
+) -> None:
+ """
+ A stored expression using dialect-specific syntax that sqlglot cannot parse
+ (e.g. ``DATE_ADD(ds, 1)`` on MySQL) pre-dates the query-time gate and went
+ to the query unparsed. A parse failure must fall back to the raw expression
+ rather than break the query; a genuine sub-query still parses and is caught.
+ """
+ tc = TableColumn(column_name="ds", expression="DATE_ADD(ds, 1)")
+ tc.table = mocker.MagicMock()
+ tc.table.database.backend = "mysql"
+ mocker.patch(
+ "superset.models.helpers.validate_adhoc_subquery",
+ side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"),
+ )
+ literal = mocker.patch("superset.connectors.sqla.models.literal_column")
+ tc.get_sqla_col()
+ # The raw expression reaches ``literal_column`` unchanged; no exception.
+ assert literal.call_args.args[0] == "DATE_ADD(ds, 1)"
Review Comment:
**Suggestion:** In this fallback test, `validate_adhoc_subquery` is patched to raise `SupersetParseError`, but the test never verifies that the patched validator was actually called. As written, the test can still pass if query-time validation is accidentally removed entirely (because `literal_column` would still receive the raw expression), so it does not reliably lock in the intended behavior. Capture the patch as a spy and assert it was called (ideally with expected arguments) before asserting the fallback result. [incomplete implementation]
<details>
<summary><b>Severity Level:</b> Major ⚠️</summary>
```mdx
⚠️ Query-time validation gate can be removed without detection.
⚠️ Security regression tests for stored expressions less trustworthy.
```
</details>
<details>
<summary><b>Steps of Reproduction ✅ </b></summary>
```mdx
1. Open `tests/unit_tests/connectors/sqla/models_test.py` and locate
`test_get_sqla_col_falls_back_when_stored_expression_unparseable` at lines 1245–1264 (per
PR diff and BulkRead output, function body shown at snippet lines 46–65).
2. Observe that within this test,
`mocker.patch("superset.models.helpers.validate_adhoc_subquery",
side_effect=SupersetParseError("DATE_ADD(ds, 1)", "mysql"))` is called at lines 1257–1260,
but the result of `mocker.patch(...)` is not captured into a variable and there is no
`assert_called_*` on the patched validator.
3. Compare with the neighboring tests
`test_get_sqla_col_validates_stored_expression_at_query_time` and
`test_get_timestamp_expression_validates_stored_expression_at_query_time` in the same file
(snippet lines 1–43, actual lines 1184–1243), where the patch to
`superset.models.helpers.validate_adhoc_subquery` is stored in `spy` and
`spy.assert_called_once()` is asserted after calling `TableColumn.get_sqla_col()` or
`get_timestamp_expression(...)`, explicitly locking in that the query-time gate runs.
4. Inspect `superset/connectors/sqla/models.py` around lines 1095–1121 (Read offset 1060,
snippet lines 36–62), where `TableColumn.get_sqla_col` calls
`self._validate_stored_expression(expression)` and then `literal_column(expression,
type_=type_)`. If a future refactor erroneously removes
`self._validate_stored_expression(...)` (and thus the underlying
`validate_stored_expression_at_query_time`/`validate_adhoc_subquery` gate),
`tc.get_sqla_col()` would still call `literal_column` with the raw `"DATE_ADD(ds, 1)"`
expression, causing this fallback test to continue passing: the patched
`validate_adhoc_subquery` is never invoked, yet `literal.call_args.args[0]` still equals
`"DATE_ADD(ds, 1)"`. Because the test does not assert that the validator spy was called,
it cannot detect that the query-time validation gate has been removed, making the test
ineffective at locking in the intended behavior.
```
</details>
[](https://app.codeant.ai/fix-in-ide?tool=cursor&prompt_id=7c11665147024655a16e983c97e3789a&service=github&base_url=https%3A%2F%2Fgithub.com&org=apache&repo=apache%2Fsuperset) [](https://app.codeant.ai/fix-in-ide?tool=vscode-claude&prompt_id=7c11665147024655a16e983c97e3789a&service=github&base_url=https%3A%2F%2Fgithub.com&org=apache&repo=apache%2Fsuperset)
*(Use Cmd/Ctrl + Click for best experience)*
<details>
<summary><b>Prompt for AI Agent 🤖 </b></summary>
```mdx
This is a comment left during a code review.
**Path:** tests/unit_tests/connectors/sqla/models_test.py
**Line:** 1257:1264
**Comment:**
*Incomplete Implementation: In this fallback test, `validate_adhoc_subquery` is patched to raise `SupersetParseError`, but the test never verifies that the patched validator was actually called. As written, the test can still pass if query-time validation is accidentally removed entirely (because `literal_column` would still receive the raw expression), so it does not reliably lock in the intended behavior. Capture the patch as a spy and assert it was called (ideally with expected arguments) before asserting the fallback result.
Validate the correctness of the flagged issue. If correct, How can I resolve this? If you propose a fix, implement it and please make it concise.
Once fix is implemented, also check other comments on the same PR, and ask user if the user wants to fix the rest of the comments as well. if said yes, then fetch all the comments validate the correctness and implement a minimal fix
```
</details>
<a href='https://app.codeant.ai/feedback?pr_url=https%3A%2F%2Fgithub.com%2Fapache%2Fsuperset%2Fpull%2F42087&comment_hash=9063014e849fcb962eb559e1b67acb517532e315077d7489b27bf8c2c43f9686&reaction=like'>👍</a> | <a href='https://app.codeant.ai/feedback?pr_url=https%3A%2F%2Fgithub.com%2Fapache%2Fsuperset%2Fpull%2F42087&comment_hash=9063014e849fcb962eb559e1b67acb517532e315077d7489b27bf8c2c43f9686&reaction=dislike'>👎</a>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [PR] fix(dataset): validate stored column and metric expressions at query time [superset]
Posted by "bito-code-review[bot] (via GitHub)" <gi...@apache.org>.
bito-code-review[bot] commented on PR #42087:
URL: https://github.com/apache/superset/pull/42087#issuecomment-5256585385
AI Code Review is in progress (usually takes 3 to 15 minutes unless it's a very large PR).
<details>
<summary>
Bito Usage Guide
</summary>
**Commands**
Type the following command in the pull request comment and save the comment.
- `/review` - Manually triggers a full AI review.
- `/pause` - Pauses automatic reviews on this pull request.
- `/resume` - Resumes automatic reviews.
- `/resolve` - Marks all Bito-posted review comments as resolved.
- `/abort` - Cancels all in-progress reviews.
Refer to the <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/available-commands" target="_blank" rel="noopener noreferrer">documentation</a> for additional commands.
**Configuration**
This repository uses `Superset` You can customize the agent settings <a href="https://alpha.bito.ai/home/ai-agents/code-review-agent" target="_blank" rel="noopener noreferrer">here</a> or contact your Bito workspace admin at evan@preset.io.
**Documentation & Help**
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/getting-started/install-run-using-bito-cloud/create-or-customize-an-agent-instance" target="_blank" rel="noopener noreferrer">Customize agent settings</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/implementing-custom-code-review-rules" target="_blank" rel="noopener noreferrer">Review rules</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent" target="_blank" rel="noopener noreferrer">General documentation</a>
- <a href="https://docs.bito.ai/bito-dev-agents/ai-code-review-agent/faqs" target="_blank" rel="noopener noreferrer">FAQ</a>
</details>
--
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@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org