We have several files that get parsed via the T-SQL profile that are jinja templates. Meaning they are generic sql code that have jinja style {{ variable }} templating within. When we run the analyze from an Azure DevOps pipeline, we are getting parse errors for those files. It would be really useful to add in logic to be able to scan these T-SQL template files.
This is the error we are receiving:
WARN: Unable to parse file '/opt/azure-agent/_work/1/s/dags/dagroup_db/sql/load_tables/report/report_sales.sql' : Parse error at line 3 column 30:
1: SET NOCOUNT ON;
2: SET ANSI_WARNINGS OFF;
3: DECLARE @env AS VARCHAR(4)= {{ "'prod'" if var.value.get("environment")=="prod" else "'uat'" }};
^
4: --DECLARE @env AS VARCHAR(4) = CASE WHEN @@servername = 'DATABASE' THEN 'uat' ELSE 'prod' END
5: DECLARE @offset AS INT = CASE WHEN @env='uat' THEN 1 ELSE 0 END
We are currently running SonarQube * Developer Edition Version 9.3 (build 51899) and our Azure DevOps “Prepare analysis on SonarQube”, “Run Code Analysis”, and “Publish Quality Gate Result” tasks are on version 5.*.
Thanks for raising this, I think that supporting templated SQL files makes sense and we’ll consider adding this in the future. In the meantime is it possible for you to exclude the templates and scan the sql that they generate?
Thanks for the reply Tom! It’s nice to hear that this will be a considered feature. I can ignore the template files to clear up the error, but unfortunately the templates are propagated dynamically through our python application and wouldn’t be easy to render them prior to the scan.
Tom, maybe as a stop gap sonarqube could add a patch to ignore any .sql lines with “{{” or “}}” so they still process the other sql code? seems like this would be a simple item to code in prior to full jinja support?
We use Apache Airflow to orchestrate our ETL. At its core we have python files that connect to our different MS SQL servers and they systematically execute .sql files and pass in variables for time, conditions, etc. and then the .sql file is reusable for many processes where end users have the same .sql code but with different parameters they can pass to it.
For a simple example, if we have a .sql file with the following content:
Select field1, field2, field3 from Table1 where field1 = {{ field_value }}
We then read and populate the template variable “field_value” with python and execute it on our databases. But because the .sql file has {{ field_value }} in it, SonarQube throws a parse error and won’t scan the file.
