T-SQL Parse Error Jinja Templates

SonarQube
#1

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.*.

1 Like
#5

Hi Garret

Welcome to the community :slight_smile:

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

Tom

2 Likes
#6

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.

#7

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?