You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/08/21 16:11:11 UTC
(superset) branch fix/sqllab-render-once-and-cache-scoping created (now c9a8188896b)
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a change to branch fix/sqllab-render-once-and-cache-scoping
in repository https://gitbox.apache.org/repos/asf/superset.git
at c9a8188896b fix(sql_lab): remove legacy kv: permalink resolution path
This branch includes the following new commits:
new 9674e7bfc86 fix(jinja): render escaped list/dict values without repr-added quote delimiters
new 846452c9780 fix(sql_lab): scope executor result cache by effective user identity
new 4825236d15f fix(sql_lab): re-authorize the literal rendered SQL before execution
new c9a8188896b fix(sql_lab): remove legacy kv: permalink resolution path
The 4 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
(superset) 04/04: fix(sql_lab): remove legacy kv: permalink resolution path
Posted by ru...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch fix/sqllab-render-once-and-cache-scoping
in repository https://gitbox.apache.org/repos/asf/superset.git
commit c9a8188896b822dbfedd701fdd6ddc86094725e7
Author: Superset Dev <de...@superset.apache.org>
AuthorDate: Fri Aug 21 00:52:06 2026 -0700
fix(sql_lab): remove legacy kv: permalink resolution path
GetSqlLabPermalinkCommand special-cased keys starting with "kv:" and
resolved them by a raw sequential primary-key lookup against the legacy
keyvalue table. That table predates the salted-hashid permalink store,
has no owner column, and reflected raw exception text on failure, so
any authenticated caller with permalink read access could enumerate
other users' saved SQL Lab editor state (SQL text plus connection and
schema context) by walking sequential ids.
Remove the kv: branch entirely: there is no owner column to check
against, so an ownership check isn't possible, and re-keying legacy
rows into the salted store is a migration concern, not a code fix.
kv:-prefixed keys now fall through to decode_permalink_id like any
other key and are rejected there, leaving the salted-hashid store as
the only resolution path.
Co-Authored-By: Claude Sonnet 5 <no...@anthropic.com>
---
superset/commands/sql_lab/permalink/get.py | 23 ++----
.../commands/sql_lab/permalink_get_test.py | 87 ++++++++++++++++++++++
2 files changed, 95 insertions(+), 15 deletions(-)
diff --git a/superset/commands/sql_lab/permalink/get.py b/superset/commands/sql_lab/permalink/get.py
index 0ae510bda0d..e001c709866 100644
--- a/superset/commands/sql_lab/permalink/get.py
+++ b/superset/commands/sql_lab/permalink/get.py
@@ -17,7 +17,6 @@
import logging
from typing import Optional
-from superset import db
from superset.commands.dataset.exceptions import DatasetNotFoundError
from superset.commands.sql_lab.permalink.base import BaseSqlLabPermalinkCommand
from superset.daos.key_value import KeyValueDAO
@@ -27,10 +26,8 @@ from superset.key_value.exceptions import (
KeyValueParseKeyError,
)
from superset.key_value.utils import decode_permalink_id
-from superset.models import core as models
from superset.sqllab.permalink.exceptions import SqlLabPermalinkGetFailedError
from superset.sqllab.permalink.types import SqlLabPermalinkValue
-from superset.utils import core as utils, json
logger = logging.getLogger(__name__)
@@ -41,18 +38,14 @@ class GetSqlLabPermalinkCommand(BaseSqlLabPermalinkCommand):
def run(self) -> Optional[SqlLabPermalinkValue]:
self.validate()
- if self.key.startswith("kv:"):
- id = int(self.key[3:])
- try:
- kv = db.session.query(models.KeyValue).filter_by(id=id).scalar()
- if not kv:
- return None
- return json.loads(kv.value)
- except Exception as ex:
- raise SqlLabPermalinkGetFailedError(
- message=utils.error_msg_from_exception(ex)
- ) from ex
-
+ # Legacy `kv:<int>` keys (from the pre-permalink `keyvalue` table) are
+ # no longer resolved here: that table has sequential integer primary
+ # keys and no owner column, so any authenticated caller could
+ # enumerate other users' saved editor state (SQL text and
+ # connection/schema context) by incrementing the id. Such keys now
+ # fall through to `decode_permalink_id` below, which rejects them
+ # (they don't decode against the salted hashid scheme), leaving the
+ # modern salted-hashid store as the only way to resolve a permalink.
try:
key = decode_permalink_id(self.key, salt=self.salt)
value = KeyValueDAO.get_value(self.resource, key, self.codec)
diff --git a/tests/unit_tests/commands/sql_lab/permalink_get_test.py b/tests/unit_tests/commands/sql_lab/permalink_get_test.py
new file mode 100644
index 00000000000..ec3c1c4ceac
--- /dev/null
+++ b/tests/unit_tests/commands/sql_lab/permalink_get_test.py
@@ -0,0 +1,87 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""Unit tests for GetSqlLabPermalinkCommand's handling of legacy keys."""
+
+from unittest.mock import PropertyMock
+
+import pytest
+from pytest_mock import MockerFixture
+
+from superset.commands.sql_lab.permalink.get import GetSqlLabPermalinkCommand
+from superset.key_value.exceptions import KeyValueParseKeyError
+from superset.sqllab.permalink.exceptions import SqlLabPermalinkGetFailedError
+
+
+def test_legacy_kv_keys_are_not_special_cased(mocker: MockerFixture) -> None:
+ """
+ Regression: ``kv:<int>`` keys used to be resolved by a raw sequential
+ primary-key lookup against the legacy ``keyvalue`` table, which has no
+ owner column -- any authenticated caller could enumerate other users'
+ saved SQL Lab editor state (SQL text, connection/schema context) by
+ incrementing the id. Such keys must now be treated like any other
+ permalink key: decoded via the salted hashid scheme (and rejected,
+ since ``kv:<int>`` never was a valid hashid), never looked up by raw
+ integer id.
+ """
+ decode = mocker.patch(
+ "superset.commands.sql_lab.permalink.get.decode_permalink_id",
+ side_effect=KeyValueParseKeyError(),
+ )
+ mocker.patch.object(
+ GetSqlLabPermalinkCommand,
+ "salt",
+ new_callable=PropertyMock,
+ return_value="salt",
+ )
+
+ command = GetSqlLabPermalinkCommand("kv:1")
+ with pytest.raises(SqlLabPermalinkGetFailedError):
+ command.run()
+
+ decode.assert_called_once_with("kv:1", salt="salt")
+
+
+def test_legacy_kv_keys_do_not_touch_the_keyvalue_table(mocker: MockerFixture) -> None:
+ """
+ A ``kv:<int>``-shaped key must never trigger a query against the
+ legacy ``KeyValue`` model, regardless of whether that id exists.
+ """
+ mock_session = mocker.patch("superset.db.session")
+ mocker.patch(
+ "superset.commands.sql_lab.permalink.get.decode_permalink_id",
+ side_effect=KeyValueParseKeyError(),
+ )
+ mocker.patch.object(
+ GetSqlLabPermalinkCommand,
+ "salt",
+ new_callable=PropertyMock,
+ return_value="salt",
+ )
+
+ command = GetSqlLabPermalinkCommand("kv:1")
+ with pytest.raises(SqlLabPermalinkGetFailedError):
+ command.run()
+
+ mock_session.query.assert_not_called()
+
+
+def test_get_command_has_no_legacy_kv_branch() -> None:
+ """The command module no longer imports the legacy KeyValue model."""
+ import superset.commands.sql_lab.permalink.get as get_module
+
+ assert not hasattr(get_module, "db")
+ assert not hasattr(get_module, "models")
(superset) 02/04: fix(sql_lab): scope executor result cache by effective user identity
Posted by ru...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch fix/sqllab-render-once-and-cache-scoping
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 846452c97808cfab35675bc5c3f61dcc71dba944
Author: Superset Dev <de...@superset.apache.org>
AuthorDate: Fri Aug 21 00:45:21 2026 -0700
fix(sql_lab): scope executor result cache by effective user identity
SQLExecutor._generate_cache_key keyed cached query results on
[database.id, sql, catalog, schema, limit] only, with no component for
the calling user. For databases where the raw connection is bound to
the caller's identity (impersonate_user or per-user OAuth2), two
different users running identical SQL text can get materially
different data from the database itself, so sharing a cache entry
between them leaks one user's rows to another.
Add _connection_carries_user_identity() and, for such databases,
include the effective user id in the cache key; also merge in the
engine-specific impersonation key via
utils.cache_keys.add_impersonation_cache_key_if_needed, mirroring the
chart-data cache-key path so CACHE_IMPERSONATION / CACHE_QUERY_BY_USER
/ per_user_caching semantics carry over. When the effective user id
cannot be determined for an identity-carrying database,
_generate_cache_key returns None and both _get_from_cache and
_store_in_cache skip caching entirely.
Co-Authored-By: Claude Sonnet 5 <no...@anthropic.com>
---
superset/sql/execution/executor.py | 42 +++++++-
tests/unit_tests/sql/execution/test_executor.py | 132 ++++++++++++++++++++++++
2 files changed, 172 insertions(+), 2 deletions(-)
diff --git a/superset/sql/execution/executor.py b/superset/sql/execution/executor.py
index 4c29dfdaa0d..532699a74aa 100644
--- a/superset/sql/execution/executor.py
+++ b/superset/sql/execution/executor.py
@@ -943,6 +943,8 @@ class SQLExecutor:
)
cache_key = self._generate_cache_key(sql, opts)
+ if cache_key is None:
+ return None
if (cached := cache_manager.data_cache.get(cache_key)) is not None:
# Reconstruct statement results from cached data
@@ -982,6 +984,9 @@ class SQLExecutor:
return
cache_key = self._generate_cache_key(sql, opts)
+ if cache_key is None:
+ return
+
timeout = (
(opts.cache.timeout if opts.cache else None)
or self.database.cache_timeout
@@ -1018,14 +1023,29 @@ class SQLExecutor:
timeout=timeout,
)
- def _generate_cache_key(self, sql: str, opts: QueryOptions) -> str:
+ def _connection_carries_user_identity(self) -> bool:
+ """
+ Whether the raw connection this executor's database hands out is
+ bound to the calling user's identity (user impersonation or
+ per-user OAuth2 tokens), such that two different users running the
+ identical SQL text can see materially different data because the
+ database itself, not just Superset, distinguishes them.
+ """
+ return bool(self.database.impersonate_user) or self.database.is_oauth2_enabled()
+
+ def _generate_cache_key(self, sql: str, opts: QueryOptions) -> str | None:
"""
Generate cache key for query result.
:param sql: SQL query
:param opts: Query options
- :returns: Cache key string
+ :returns: Cache key string, or ``None`` if the query must not be
+ cached because the connection carries per-user identity but
+ the effective user could not be determined -- caching in that
+ case would risk serving one user's results to another.
"""
+ from superset.utils.cache_keys import add_impersonation_cache_key_if_needed
+
# Include relevant options in the cache key
key_parts = [
str(self.database.id),
@@ -1034,6 +1054,24 @@ class SQLExecutor:
opts.schema or "",
str(opts.limit) if opts.limit is not None else "",
]
+
+ if self._connection_carries_user_identity():
+ user_id = utils.get_user_id()
+ if user_id is None:
+ # Effective identity is unknown (e.g. no request context) --
+ # fail safe by refusing to cache rather than risk sharing
+ # results across users.
+ return None
+ key_parts.append(f"user:{user_id}")
+
+ # Mirror the chart-data cache-key path so CACHE_IMPERSONATION /
+ # CACHE_QUERY_BY_USER / per_user_caching semantics also scope the
+ # SQL executor's result cache.
+ impersonation_cache_dict: dict[str, Any] = {}
+ add_impersonation_cache_key_if_needed(self.database, impersonation_cache_dict)
+ if impersonation_key := impersonation_cache_dict.get("impersonation_key"):
+ key_parts.append(f"impersonation:{impersonation_key}")
+
key_string = "|".join(key_parts)
return hashlib.sha256(key_string.encode()).hexdigest()
diff --git a/tests/unit_tests/sql/execution/test_executor.py b/tests/unit_tests/sql/execution/test_executor.py
index 445156fd372..65d9d3cf42b 100644
--- a/tests/unit_tests/sql/execution/test_executor.py
+++ b/tests/unit_tests/sql/execution/test_executor.py
@@ -2449,3 +2449,135 @@ def test_build_statement_blocks_skips_validation_for_unparseable_mutated_sql(
assert len(blocks) == 1
assert blocks[0].count("ENGINE SPECIFIC") == 2
+
+
+# =============================================================================
+# Cache Key Identity Tests
+# =============================================================================
+
+
+def test_generate_cache_key_ignores_user_without_impersonation(
+ mocker: MockerFixture, database: Database, app_context: None
+) -> None:
+ """
+ When the connection does not carry per-user identity, the cache key
+ must not depend on the calling user -- unrelated users legitimately
+ share cache entries for identical queries.
+ """
+ from superset.sql.execution.executor import SQLExecutor
+
+ executor = SQLExecutor(database)
+ options = QueryOptions()
+
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=1)
+ key_user_1 = executor._generate_cache_key("SELECT * FROM salaries", options)
+
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=2)
+ key_user_2 = executor._generate_cache_key("SELECT * FROM salaries", options)
+
+ assert key_user_1 is not None
+ assert key_user_1 == key_user_2
+
+
+def test_generate_cache_key_scopes_by_user_when_impersonation_enabled(
+ mocker: MockerFixture, database: Database, app_context: None
+) -> None:
+ """
+ Regression: when the database impersonates the connecting user (or
+ uses per-user OAuth2), the effective identity the database sees
+ differs per user even for byte-identical SQL text. The cache key must
+ incorporate that identity so one user's cached rows can never be
+ served to another user the database itself would have denied.
+ """
+ from superset.sql.execution.executor import SQLExecutor
+
+ database.impersonate_user = True
+ executor = SQLExecutor(database)
+ options = QueryOptions()
+
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=1)
+ key_analyst_a = executor._generate_cache_key("SELECT * FROM salaries", options)
+
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=2)
+ key_analyst_b = executor._generate_cache_key("SELECT * FROM salaries", options)
+
+ assert key_analyst_a is not None
+ assert key_analyst_b is not None
+ assert key_analyst_a != key_analyst_b
+
+
+def test_generate_cache_key_none_when_identity_unknown(
+ mocker: MockerFixture, database: Database, app_context: None
+) -> None:
+ """
+ If the database carries per-user identity but the effective user
+ cannot be determined (e.g. no request context), the query must be
+ treated as uncacheable rather than risk a shared cache entry.
+ """
+ from superset.sql.execution.executor import SQLExecutor
+
+ database.impersonate_user = True
+ executor = SQLExecutor(database)
+ options = QueryOptions()
+
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=None)
+
+ assert executor._generate_cache_key("SELECT * FROM salaries", options) is None
+
+
+def test_get_from_cache_skips_when_identity_unknown(
+ mocker: MockerFixture, database: Database, app_context: None
+) -> None:
+ """
+ ``_get_from_cache`` must not read from the cache backend at all when
+ ``_generate_cache_key`` reports the query as uncacheable.
+ """
+ from superset.extensions import cache_manager
+ from superset.sql.execution.executor import SQLExecutor
+
+ database.impersonate_user = True
+ executor = SQLExecutor(database)
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=None)
+ mock_cache_get = mocker.patch.object(cache_manager.data_cache, "get")
+
+ result = executor._get_from_cache("SELECT * FROM salaries", QueryOptions())
+
+ assert result is None
+ mock_cache_get.assert_not_called()
+
+
+def test_store_in_cache_skips_when_identity_unknown(
+ mocker: MockerFixture, database: Database, app_context: None
+) -> None:
+ """
+ ``_store_in_cache`` must not write to the cache backend at all when
+ ``_generate_cache_key`` reports the query as uncacheable.
+ """
+ from superset_core.queries.types import (
+ QueryResult as QueryResultType,
+ StatementResult,
+ )
+
+ from superset.extensions import cache_manager
+ from superset.sql.execution.executor import SQLExecutor
+
+ database.impersonate_user = True
+ executor = SQLExecutor(database)
+ mocker.patch("superset.sql.execution.executor.utils.get_user_id", return_value=None)
+ mock_cache_set = mocker.patch.object(cache_manager.data_cache, "set")
+
+ result = QueryResultType(
+ status=QueryStatus.SUCCESS,
+ statements=[
+ StatementResult(
+ original_sql="SELECT * FROM salaries",
+ executed_sql="SELECT * FROM salaries",
+ data=pd.DataFrame({"salary": [1]}),
+ row_count=1,
+ )
+ ],
+ )
+
+ executor._store_in_cache(result, "SELECT * FROM salaries", QueryOptions())
+
+ mock_cache_set.assert_not_called()
(superset) 03/04: fix(sql_lab): re-authorize the literal rendered SQL before execution
Posted by ru...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch fix/sqllab-render-once-and-cache-scoping
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 4825236d15fa6f4f12d73f46fefd22d248f7bd2f
Author: Superset Dev <de...@superset.apache.org>
AuthorDate: Fri Aug 21 00:49:58 2026 -0700
fix(sql_lab): re-authorize the literal rendered SQL before execution
ExecuteSqlCommand._run_sql_json_exec_from_scratch authorized query.sql +
template_params (one Jinja render, inside the access validator) and then
rendered the query a second, independent time via SqlQueryRender.render()
before handing it to the executor. For a nondeterministic template (for
example one using Jinja's `random` filter to pick a table), the two
renders can diverge, so the SQL that actually executes was never the SQL
that got authorized.
Add ExecuteSqlCommand._validate_rendered_access, called right after
rendering: it pins query.executed_sql to the literal rendered text so
security_manager.raise_for_access's "prefer executed_sql" path
authorizes that exact SQL with no further re-render, then resets
executed_sql so the execution path still assigns its own final
(limited / per-block mutated) SQL.
Co-Authored-By: Claude Sonnet 5 <no...@anthropic.com>
---
superset/commands/sql_lab/execute.py | 26 ++++
tests/unit_tests/commands/sql_lab/test_execute.py | 161 ++++++++++++++++++++++
2 files changed, 187 insertions(+)
diff --git a/superset/commands/sql_lab/execute.py b/superset/commands/sql_lab/execute.py
index a3d29fe330f..99ed73fdef7 100644
--- a/superset/commands/sql_lab/execute.py
+++ b/superset/commands/sql_lab/execute.py
@@ -151,6 +151,15 @@ class ExecuteSqlCommand(BaseCommand):
self._validate_access(query, self._execution_context.template_params)
self._execution_context.set_query(query)
rendered_query = self._sql_query_render.render(self._execution_context)
+ # The check above authorizes a render of query.sql + template_params
+ # performed before rendering, so that macros with side effects are
+ # gated before they run. self._sql_query_render.render() above is an
+ # independent second render of the same source; for a
+ # nondeterministic template (e.g. one using Jinja's `random` filter
+ # to pick a table) the two renders can diverge, letting a query
+ # read a table the first check never saw. Re-validate the literal
+ # rendered text that is about to execute.
+ self._validate_rendered_access(query, rendered_query)
self._set_query_limit_if_required(rendered_query)
self._query_dao.update(
query, {"limit": self._execution_context.query.limit}
@@ -212,6 +221,23 @@ class ExecuteSqlCommand(BaseCommand):
except Exception as ex:
raise QueryIsForbiddenToAccessException(self._execution_context, ex) from ex
+ def _validate_rendered_access(self, query: Query, rendered_query: str) -> None:
+ """
+ Re-authorize the exact SQL that is about to execute.
+
+ Pins ``query.executed_sql`` to the literal, already-rendered text so
+ ``security_manager.raise_for_access``'s "prefer executed_sql" path
+ authorizes that exact SQL directly, with no further Jinja
+ re-render (see its docstring). ``executed_sql`` is reset
+ afterwards so the execution path can assign its own final
+ (limited / per-block mutated) SQL.
+ """
+ query.executed_sql = rendered_query
+ try:
+ self._validate_access(query, self._execution_context.template_params)
+ finally:
+ query.executed_sql = None
+
def _set_query_limit_if_required(
self,
rendered_query: str,
diff --git a/tests/unit_tests/commands/sql_lab/test_execute.py b/tests/unit_tests/commands/sql_lab/test_execute.py
new file mode 100644
index 00000000000..dabda8f6d48
--- /dev/null
+++ b/tests/unit_tests/commands/sql_lab/test_execute.py
@@ -0,0 +1,161 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""Unit tests for ExecuteSqlCommand's authorization of the SQL it executes."""
+
+from unittest.mock import MagicMock, patch
+
+import pytest
+
+from superset.commands.sql_lab.execute import ExecuteSqlCommand
+from superset.sqllab.command_status import SqlJsonExecutionStatus
+from superset.sqllab.exceptions import QueryIsForbiddenToAccessException
+
+
+def _make_command(**overrides: MagicMock) -> ExecuteSqlCommand:
+ kwargs: dict[str, MagicMock] = {
+ "execution_context": MagicMock(),
+ "query_dao": MagicMock(),
+ "database_dao": MagicMock(),
+ "access_validator": MagicMock(),
+ "sql_query_render": MagicMock(),
+ "sql_json_executor": MagicMock(),
+ "execution_context_convertor": MagicMock(),
+ }
+ kwargs.update(overrides)
+ return ExecuteSqlCommand(
+ sqllab_ctas_no_limit_flag=False,
+ **kwargs,
+ )
+
+
+# ---------------------------------------------------------------------------
+# _validate_rendered_access: pin / delegate / reset
+# ---------------------------------------------------------------------------
+
+
+def test_validate_rendered_access_authorizes_the_literal_rendered_sql() -> None:
+ """
+ ``_validate_rendered_access`` must pin ``query.executed_sql`` to the
+ already-rendered text *before* delegating to the access validator, so
+ ``raise_for_access``'s "prefer executed_sql" path authorizes exactly
+ the SQL that is about to execute rather than re-rendering the Jinja
+ source (which could pick a different table for a nondeterministic
+ template). Afterwards ``executed_sql`` must be reset so the execution
+ path can assign its own final (limited / per-block mutated) SQL.
+ """
+ command = _make_command()
+ query = MagicMock()
+ query.executed_sql = None
+ seen_executed_sql = []
+
+ def _capture_validate(q: MagicMock, template_params: object) -> None:
+ seen_executed_sql.append(q.executed_sql)
+
+ command._access_validator.validate.side_effect = _capture_validate # type: ignore[attr-defined] # noqa: E501
+
+ command._validate_rendered_access(query, "SELECT * FROM sales")
+
+ assert seen_executed_sql == ["SELECT * FROM sales"]
+ assert query.executed_sql is None
+
+
+def test_validate_rendered_access_resets_executed_sql_on_denial() -> None:
+ """
+ A denial for the rendered SQL must still surface as
+ ``QueryIsForbiddenToAccessException`` and must not leave
+ ``query.executed_sql`` pinned to the rejected text.
+ """
+ command = _make_command()
+ query = MagicMock()
+ query.executed_sql = None
+ command._access_validator.validate.side_effect = Exception("access denied") # type: ignore[attr-defined] # noqa: E501
+
+ with pytest.raises(QueryIsForbiddenToAccessException):
+ command._validate_rendered_access(query, "SELECT * FROM secret_payroll")
+
+ assert query.executed_sql is None
+
+
+# ---------------------------------------------------------------------------
+# Regression: nondeterministic templates must not let execution diverge
+# from the authorized render
+# ---------------------------------------------------------------------------
+
+
+@patch("superset.commands.sql_lab.execute.db")
+def test_run_sql_json_exec_from_scratch_revalidates_rendered_sql(
+ mock_db: MagicMock,
+) -> None:
+ """
+ Regression: previously only ``query.sql`` + ``template_params`` was
+ authorized (render #1, inside the access validator) while a second,
+ independent render (``sql_query_render.render()``) produced the SQL
+ that was actually handed to the executor. A nondeterministic Jinja
+ construct (e.g. the ``random`` filter picking a table) could make the
+ two renders diverge, letting a query read a table the authorization
+ check never saw. The literal rendered SQL must now be re-validated
+ before it reaches the executor.
+ """
+ execution_context = MagicMock()
+ execution_context.template_params = {}
+ # Skip the query-limit machinery entirely; it is unrelated to this fix.
+ execution_context.select_as_cta = True
+
+ query = MagicMock()
+ query.id = 1
+ query.executed_sql = None
+ execution_context.create_query.return_value = query
+
+ database_dao = MagicMock()
+ database_dao.find_by_id.return_value = MagicMock()
+
+ access_validator = MagicMock()
+ validate_calls: list[object] = []
+
+ def _capture_validate(q: MagicMock, template_params: object) -> None:
+ validate_calls.append(q.executed_sql)
+
+ access_validator.validate.side_effect = _capture_validate
+
+ sql_query_render = MagicMock()
+ sql_query_render.render.return_value = "SELECT * FROM sales"
+
+ sql_json_executor = MagicMock()
+ sql_json_executor.execute.return_value = SqlJsonExecutionStatus.HAS_RESULTS
+
+ command = _make_command(
+ execution_context=execution_context,
+ database_dao=database_dao,
+ access_validator=access_validator,
+ sql_query_render=sql_query_render,
+ sql_json_executor=sql_json_executor,
+ )
+ command._sqllab_ctas_no_limit = True
+
+ command._run_sql_json_exec_from_scratch()
+
+ # Authorized twice: once before rendering (macros with side effects run
+ # at render time) and again against the literal, already-rendered SQL
+ # that the executor is about to run.
+ assert access_validator.validate.call_count == 2
+ assert validate_calls == [None, "SELECT * FROM sales"]
+ # executed_sql is not left pinned; the execution path assigns its own
+ # final value.
+ assert query.executed_sql is None
+ sql_json_executor.execute.assert_called_once_with(
+ execution_context, "SELECT * FROM sales", None
+ )
(superset) 01/04: fix(jinja): render escaped list/dict values without repr-added quote delimiters
Posted by ru...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch fix/sqllab-render-once-and-cache-scoping
in repository https://gitbox.apache.org/repos/asf/superset.git
commit 9674e7bfc86032089cbd82f6a5a94ba62a68eb0e
Author: Superset Dev <de...@superset.apache.org>
AuthorDate: Fri Aug 21 00:41:09 2026 -0700
fix(jinja): render escaped list/dict values without repr-added quote delimiters
ExtraCache._escape_value already escapes string leaves nested inside
lists and dicts, but interpolating the whole container in a template
goes through Python's default str()/repr(), which wraps each string
element in its own quote delimiters. Those delimiters were never
escaped, so a container value with no leading quote could still break
out of a surrounding SQL literal (e.g. LIKE '{{ escaped_val }}').
Return escaped lists/dicts as SQLSafeList/SQLSafeDict, subclasses that
render their whole-container string form as the already-escaped
elements joined together with no extra delimiters, while still
comparing equal to and behaving like plain list/dict everywhere else.
Co-Authored-By: Claude Sonnet 5 <no...@anthropic.com>
---
superset/jinja_context.py | 56 +++++++++++++++++++++++++++-
tests/unit_tests/jinja_context_test.py | 67 ++++++++++++++++++++++++++++++++++
2 files changed, 121 insertions(+), 2 deletions(-)
diff --git a/superset/jinja_context.py b/superset/jinja_context.py
index 96b3756a68f..75724a68def 100644
--- a/superset/jinja_context.py
+++ b/superset/jinja_context.py
@@ -118,6 +118,48 @@ class TimeFilter:
time_range: str | None
+class SQLSafeList(list[Any]): # noqa: FURB189
+ """
+ A list of dialect-escaped values whose *whole-container* string
+ rendering cannot re-introduce raw quote characters.
+
+ Rendering a plain Python list in a Jinja template goes through
+ ``str()``/``repr()``, which wraps every string element in fresh quote
+ delimiters (and switches to double-quote delimiters when the element
+ contains a single quote, emitting that single quote raw). Either way
+ the rendered text can contain quote characters that were never
+ escaped for SQL, so a template interpolating the list inside its own
+ quotes -- e.g. ``LIKE '{{ filter.get('escaped_val') }}'`` -- could be
+ broken out of even though every string leaf was individually escaped.
+ This subclass renders as its (already-escaped) elements joined with
+ ``", "``, with no additional delimiters, so every quote in the output
+ is one the dialect's literal processor already escaped.
+ """
+
+ def __str__(self) -> str:
+ return ", ".join(str(element) for element in self)
+
+ __repr__ = __str__
+
+
+class SQLSafeDict(dict[Any, Any]): # noqa: FURB189
+ """
+ A dict of dialect-escaped values whose *whole-container* string
+ rendering cannot re-introduce raw quote characters. Mirrors
+ :class:`SQLSafeList` for the mapping case.
+
+ Keys are rendered as-is: they are used for member lookups (for
+ example ``{{ get_guest_user_attribute('tenant').id }}``), not
+ interpolated into SQL, and are never passed through
+ ``ExtraCache._escape_value``.
+ """
+
+ def __str__(self) -> str:
+ return ", ".join(f"{key}: {value}" for key, value in self.items())
+
+ __repr__ = __str__
+
+
def _normalize_postgresql_backslash_escapes(dialect: Dialect) -> None:
"""Correct a PostgreSQL dialect instance's ``_backslash_escapes`` default
in place so backslashes round-trip unchanged when the dialect is used to
@@ -467,6 +509,16 @@ class ExtraCache:
strings nested inside JSON structures are also escaped; dict keys
are left untouched since they are used for member lookups, not
interpolation. Non-string leaf values are left as-is.
+
+ Lists and dicts are returned as :class:`SQLSafeList` /
+ :class:`SQLSafeDict` rather than plain ``list``/``dict``: Jinja
+ renders a whole container through ``str()``/``repr()``, which
+ wraps string elements in fresh quote delimiters that were never
+ escaped, so even a fully-escaped container could re-introduce raw
+ quotes when interpolated as a whole. The safe subclasses render
+ without adding such delimiters, while still comparing equal to
+ (and behaving like) their plain built-in counterparts everywhere
+ else.
"""
if not self.dialect:
return val
@@ -475,9 +527,9 @@ class ExtraCache:
_normalize_postgresql_backslash_escapes(compiler.dialect)
return compiler.render_literal_value(val, String())[1:-1]
if isinstance(val, list):
- return [self._escape_value(v) for v in val]
+ return SQLSafeList(self._escape_value(v) for v in val)
if isinstance(val, dict):
- return {k: self._escape_value(v) for k, v in val.items()}
+ return SQLSafeDict((k, self._escape_value(v)) for k, v in val.items())
return val
def get_filters(self, column: str, remove_filter: bool = False) -> list[Filter]:
diff --git a/tests/unit_tests/jinja_context_test.py b/tests/unit_tests/jinja_context_test.py
index 2b50b9d6a84..cad42e0c426 100644
--- a/tests/unit_tests/jinja_context_test.py
+++ b/tests/unit_tests/jinja_context_test.py
@@ -474,6 +474,73 @@ def test_get_filters_no_escaped_val_without_dialect() -> None:
assert "escaped_val" not in result[0]
+def test_escape_value_nested_list_is_escaped() -> None:
+ """
+ Regression: string leaves nested more than one level deep used to pass
+ through ``_escape_value`` untouched, and Jinja's whole-container
+ rendering of the nested list re-emitted the single quote raw --
+ terminating the surrounding SQL string literal.
+ """
+ cache = ExtraCache(
+ dialect=dialect(),
+ query_context_filters=[
+ {
+ "col": "name",
+ "op": "LIKE",
+ "val": [["x' UNION SELECT username FROM users --"]],
+ },
+ ],
+ )
+ [entry] = cache.get_filters("name")
+ rendered = str(entry["escaped_val"])
+ # every single quote in the rendered output must be escaped (doubled)
+ assert "'" not in rendered.replace("''", "")
+ assert "x'' UNION SELECT username FROM users --" in rendered
+
+
+def test_escape_value_flat_list_renders_without_raw_quotes() -> None:
+ """
+ Regression: rendering a whole escaped list used to go through Python's
+ default ``str()``/``repr()``, which wraps elements in fresh quote
+ delimiters -- letting even a payload containing no single quotes break
+ out of the template's own quotes. The escaped list must render as
+ escaped elements only, with no delimiters added.
+ """
+ cache = ExtraCache(
+ dialect=dialect(),
+ query_context_filters=[
+ {"col": "name", "op": "LIKE", "val": ['x") OR 1=1 --', "O'Brien"]},
+ ],
+ )
+ [entry] = cache.get_filters("name")
+ # element-wise access still compares equal to the plain escaped values
+ assert entry["escaped_val"] == ['x") OR 1=1 --', "O''Brien"]
+ # whole-list rendering must not add quote delimiters around elements
+ rendered = str(entry["escaped_val"])
+ assert rendered == "x\") OR 1=1 --, O''Brien"
+ assert "'" not in rendered.replace("''", "")
+
+
+def test_escape_value_dict_renders_without_raw_quotes() -> None:
+ """
+ Regression: a dict value returned by ``get_guest_user_attribute`` is
+ recursively escaped at the leaf level, but rendering the whole dict in
+ a template used to go through Python's default ``repr()``, which
+ re-wraps string values in fresh quote delimiters -- reopening the
+ same whole-container escape hatch as for lists.
+ """
+ cache = ExtraCache(dialect=dialect())
+ escaped = cache._escape_value( # pylint: disable=protected-access
+ {"id": "foo' OR 1=1 --", "names": ["O'Brien", 42]}
+ )
+ # equality and member access behave exactly like a plain dict
+ assert escaped == {"id": "foo'' OR 1=1 --", "names": ["O''Brien", 42]}
+ assert isinstance(escaped, dict)
+ # whole-container rendering must not add quote delimiters
+ rendered = str(escaped)
+ assert "'" not in rendered.replace("''", "")
+
+
def test_url_param_query() -> None:
"""
Test the ``url_param`` macro.