False positives in multi-line dynamic sql

We are having some issues with code smells having many false positives while analyzing sql scripts with dynamic sql statements that span multiple lines.


SET $SQL = 'SELECT ' + $ColumnName + ' FROM TABLE' 

– This works fine

SET $SQL = '
     SELECT ' + $ColumnName + '

– The analysis seems to ignore the ’ on the first line, then get confused in the following lines between ‘text’ and commands. As a result, it thinks ’ + $ColumnName + ’ is text and, if this variable is used multiple times in the dynamic sql statement, will raise a duplicate string literal issue. Is this a known issue?

Hello @nrcjli

Thank you for reporting the problem. This is a new issue, and I created a ticket in our internal tracking system. If you know of a workaround, please post it here for other users until we fix the parsing error. Thank you.

Link to the internal issue

Best Martin

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

Hello @nrcjli

I’m coming back at this problem as I was recently looking at it and was not able to reproduce the issue. The sample code you provided is working fine on my side.

Would it be possible to have the full code from the screenshot (the right part is cut from it). It would help us to understand exactly what is causing the issue.

Many thanks,

Here you go.

        SET @sql = 'INSERT INTO VendorFile_NULLCounts (VendorFile_ID,Field_id,strField_nm,Occurrences)        
                    SELECT ' + cast(@VendorFile_ID AS VARCHAR(10)) + ' as VendorFile_ID, ' + cast(@Field_id AS VARCHAR(10)) + ' as Field_ID, ' + '''' + @strField_nm + ''' as strField_nm, Count(*) 
                    FROM  ' + @DataTableName + '       
                    WHERE VendorFile_ID = ' + cast(@VendorFile_ID AS VARCHAR(10)) + ' AND ' + @strField_nm + ' IS NULL
                    Group by ' + @strField_nm

        IF @inDebug = 1
              PRINT @SQL

        EXEC (@sql)

Thanks for coming back with more information.

However, I’m still not able to reproduce the issue with the sample code.

Can you confirm you still face the issue initially reported?
Would it be possible to provide a complete code sample (possibly simplified) reproducing the issue, not only a fraction of the code?
You can reach out to me privately if needed.

Yes, the dynamic sql issue is still there.
I can send you the entire file. What is the best way to reach out privately?

Thanks for your patience and collaboration.

After further investigation, it seems to me that the code you are talking about is (erroneously) analyzed as PL/SQL code and not TSQL. The hint was in the analysis logs:

INFO: Sensor PL/SQL Sensor [plsql]
WARN: The Data Dictionary is not configured for the PLSQL analyzer, which prevents rule(s) S3641, S3921, S3618, S3651 from raising issues. See https://docs.sonarqube.org/latest/analysis/languages/plsql/
INFO: 1 source files to be analyzed
WARN: Unable to fully parse: file.sql
WARN: Parse error starting from line 5

From the documentation, it is a known behavior:

With the default configuration, only files with the .tsql are analyzed as T-SQL, and files with the .sql file extension are analyzed as PL/SQL. This behavior is defined in Administration > General Settings > T-SQL > File Suffixes and Administration > General Settings > PL/SQL > File Suffixes . You can override these properties either at the server level or at the project level.

Can you confirm that the file containing the code mentioned uses the .sql extension? If yes, changing the properties mentioned previously should solve the issue.

Yes, the file uses the .sql extension. We will try changing the properties you mentioned and report back if there are any further issues. Thank you!