We are having some issues with code smells having many false positives while analyzing sql scripts with dynamic sql statements that span multiple lines.
Example:
SET $SQL = 'SELECT ' + $ColumnName + ' FROM TABLE'
– This works fine
SET $SQL = '
SELECT ' + $ColumnName + '
FROM TABLE'
– 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?
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.
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.
WHILE @@ROWCOUNT > 0
BEGIN
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)
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.
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:
WARN: Unable to fully parse: file.sql
WARN: Parse error starting from line 5
WARN:
WARN:
...
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.
