(superset) 01/01: fix: prevent anonymous code in Postgres

[be...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

beto pushed a commit to branch fix-is-mutating-procedure
in repository https://gitbox.apache.org/repos/asf/superset.git

commit 7d94853ddf1c7ed0d0764fe8cd8fd4bd7fd295d8
Author: Beto Dealmeida <ro...@dealmeida.net>
AuthorDate: Wed Jul 30 16:34:59 2025 -0400

    fix: prevent anonymous code in Postgres
---
 .../pythonpath_dev/superset_config_docker_light.py |  1 +
 superset/sql/parse.py                              | 10 +++++++
 tests/conftest.py                                  |  3 +-
 tests/unit_tests/sql/parse_tests.py                | 35 ++++++++++++++++++++++
 4 files changed, 48 insertions(+), 1 deletion(-)

diff --git a/docker/pythonpath_dev/superset_config_docker_light.py b/docker/pythonpath_dev/superset_config_docker_light.py
index 9a5ae0ae67..1f053c2ce3 100644
--- a/docker/pythonpath_dev/superset_config_docker_light.py
+++ b/docker/pythonpath_dev/superset_config_docker_light.py
@@ -19,6 +19,7 @@
 
 # Import all settings from the main config first
 from flask_caching.backends.filesystemcache import FileSystemCache
+
 from superset_config import *  # noqa: F403
 
 # Override caching to use simple in-memory cache instead of Redis
diff --git a/superset/sql/parse.py b/superset/sql/parse.py
index c9ed22ff0f..bcc6b32382 100644
--- a/superset/sql/parse.py
+++ b/superset/sql/parse.py
@@ -654,6 +654,16 @@ class SQLStatement(BaseSQLStatement[exp.Expression]):
         if isinstance(self._parsed, exp.Command) and self._parsed.name == "ALTER":
             return True  # pragma: no cover
 
+        if (
+            self._dialect == Dialects.POSTGRES
+            and isinstance(self._parsed, exp.Command)
+            and self._parsed.name == "DO"
+        ):
+            # anonymous blocks can be written in many different languages (the default
+            # is PL/pgSQL), so parsing them it out of scope of this class; we just
+            # assume the anonymous block is mutating
+            return True
+
         # Postgres runs DMLs prefixed by `EXPLAIN ANALYZE`, see
         # https://www.postgresql.org/docs/current/sql-explain.html
         if (
diff --git a/tests/conftest.py b/tests/conftest.py
index 09a16f1871..d9b88ac8f4 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -38,7 +38,6 @@ from tests.example_data.data_loading.pandas.pands_data_loading_conf import (
 from tests.example_data.data_loading.pandas.table_df_convertor import (
     TableToDfConvertorImpl,
 )
-from tests.integration_tests.test_app import app
 
 SUPPORT_DATETIME_TYPE = "support_datetime_type"
 
@@ -69,6 +68,8 @@ def example_db_provider() -> Callable[[], Database]:
 
 @fixture(scope="session")
 def example_db_engine(example_db_provider: Callable[[], Database]) -> Engine:
+    from tests.integration_tests.test_app import app
+
     with app.app_context():
         with example_db_provider().get_sqla_engine() as engine:
             return engine
diff --git a/tests/unit_tests/sql/parse_tests.py b/tests/unit_tests/sql/parse_tests.py
index 458caf5fa1..262d1b4e66 100644
--- a/tests/unit_tests/sql/parse_tests.py
+++ b/tests/unit_tests/sql/parse_tests.py
@@ -1189,6 +1189,41 @@ def test_is_mutating(sql: str, engine: str, expected: bool) -> None:
     assert SQLStatement(sql, engine).is_mutating() == expected
 
 
+@pytest.mark.parametrize(
+    "sql, expected",
+    [
+        (
+            """
+DO $$
+BEGIN
+  INSERT INTO public.users (name, real_name)
+    VALUES ('SQLLab bypass DML', 'SQLLab bypass DML');
+END;
+$$;
+            """,
+            True,
+        ),
+        (
+            """
+DO $$
+BEGIN
+    IF (SELECT COUNT(*) FROM orders WHERE status = 'pending') > 100 THEN
+        RAISE NOTICE 'High pending order volume detected';
+    END IF;
+END;
+$$;
+            """,
+            True,
+        ),
+    ],
+)
+def test_is_mutating_procedure(sql: str, expected: bool) -> None:
+    """
+    Test for `is_mutating` with a Postgres procedure.
+    """
+    assert SQLStatement(sql, "postgresql").is_mutating() == expected
+
+
 def test_optimize() -> None:
     """
     Test that the `optimize` method works as expected.