Repository: apache/superset
File: superset/sqllab/query_render.py (Python)

Code before:
            sql_template_processor = self._sql_template_processor_factory(
                database=query_model.database, query=query_model
            )

            rendered_query = sql_template_processor.process_template(
                query_model.sql.strip().strip(";"),
                **execution_context.template_params,
            )
            self._validate(execution_context, rendered_query, sql_template_processor)
            return rendered_query
        except TemplateError as ex:
            self._raise_template_exception(ex, execution_context)
            return "NOT_REACHABLE_CODE"
        except Exception as ex:
            from superset.jinja_context import UndefinedTemplateFunctionException

            if isinstance(ex, UndefinedTemplateFunctionException):
                return query_model.sql.strip().strip(";")
            raise

    def _strip_sql_comments(self, sql: str) -> str:
        import re

        sql = re.sub(r"/\*.*?\*/", "", sql, flags=re.DOTALL)
        sql = re.sub(r"--[^\n\r]*", "", sql)
        return sql

    def _validate(
        self,
        execution_context: SqlJsonExecutionContext,
        rendered_query: str,
        sql_template_processor: BaseTemplateProcessor,
    ) -> None:
        if is_feature_enabled("ENABLE_TEMPLATE_PROCESSING"):
            sql_for_validation = self._strip_sql_comments(rendered_query)
            syntax_tree = sql_template_processor.env.parse(sql_for_validation)
            undefined_parameters = find_undeclared_variables(syntax_tree)
            if undefined_parameters:
                self._raise_undefined_parameter_exception(
                    execution_context, undefined_parameters
                )

    def _raise_undefined_parameter_exception(
        self, execution_context: SqlJsonExecutionContext, undefined_parameters: Any
    ) -> None:
        raise SqlQueryRenderException(
            sql_json_execution_context=execution_context,
            error_type=SupersetErrorType.MISSING_TEMPLATE_PARAMS_ERROR,
            reason_message=ngettext(
                "The parameter %(parameters)s in your query is undefined.",
                "The following parameters in your query are undefined: %(parameters)s.",

Review comment (bug):
The regex approach is flaky, it can't handle nested comments (supported by some dialects) and might produce invalid SQL:

```python
>>> sql = "SELECT '--debug' AS flag"
>>> re.sub(r"--[^\n\r]*", "", sql)
"SELECT '"
```

Since you have a reference to the database it's better to use the SQL parser:

```python
dialect = execution_context.query.database.db_engine.spec.engine
sql_for_validation = SQLScript(sql, dialect).format(comments=False)
```


Code after:
            sql_template_processor = self._sql_template_processor_factory(
                database=query_model.database, query=query_model
            )

            rendered_query = sql_template_processor.process_template(
                query_model.sql.strip().strip(";"),
                **execution_context.template_params,
            )
            self._validate(execution_context, rendered_query, sql_template_processor)
            return rendered_query
        except TemplateError as ex:
            self._raise_template_exception(ex, execution_context)
            return "NOT_REACHABLE_CODE"
        except Exception as ex:
            from superset.jinja_context import UndefinedTemplateFunctionException

            if isinstance(ex, UndefinedTemplateFunctionException):
                return query_model.sql.strip().strip(";")
            raise

    def _strip_sql_comments(
        self,
        execution_context: SqlJsonExecutionContext,
        sql: str,
    ) -> str:
        from superset.sql.parse import SQLScript

        engine = execution_context.query.database.db_engine_spec.engine
        script = SQLScript(sql, engine)
        return script.format(comments=False)

    def _validate(
        self,
        execution_context: SqlJsonExecutionContext,
        rendered_query: str,
        sql_template_processor: BaseTemplateProcessor,
    ) -> None:
        if is_feature_enabled("ENABLE_TEMPLATE_PROCESSING"):
            sql_for_validation = self._strip_sql_comments(
                execution_context,
                rendered_query,
            )
            syntax_tree = sql_template_processor.env.parse(sql_for_validation)
            undefined_parameters = find_undeclared_variables(syntax_tree)
            if undefined_parameters:
                self._raise_undefined_parameter_exception(
                    execution_context, undefined_parameters
                )

    def _raise_undefined_parameter_exception(
        self, execution_context: SqlJsonExecutionContext, undefined_parameters: Any