GBritton827020 (Community Member) asked a question.
When performing static analysis of T-SQL code, Veracode seems to flag all dynamic SQL statements as critical vulnerabilities.
- a statement such as
EXEC sp-executesql @stmt=N'SELECT foo FROM bar WHERE fum=@parm', @params=N'@parm int', @parm =42
is flagged as "should be parameterized" when it clearly is
- dynamic sql statements where object names are substituted at runtime are flagged as "should be parameterized" when in fact it is impossible to parameterize object names in dynamic SQL.
- Context is ignored. e.g. I have a system using dynamic SQL but there is no user input. All parameters are obtained from the run time environment or read-only tables.
.png){kind=logo}
Hi @GBritton827020 (Community Member) ,
"EXEC sp-executesql @stmt=N'SELECT foo FROM bar WHERE fum=@parm', @params=N'@parm int', @parm =42"
Looks to have no dynamic parts. Please provide the details for this flaw to Veracode Technical Support (including a link to the Veracode Platform of the scan and the relevant flaw ids).
Our general remediation advice for CWE 89 SQL injection is to recommend wherever possible to parameterise queries rather than concatenate dynamic variables into the query, for TSQL this is described here: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/specify-parameters?view=sql-server-ver15
However, this is unfortunately not possible for identifiers, if you have dynamic identifiers our primary recommendation is to try to hardcode them or to see if it's possible to have a hardcoded list. If Veracode Static Analysis can see that the query is only composed of hardcoded parts it will be able to automatically close the flaw.
This may not always be possible, if the design of your application requires dynamic identifiers you should implement some validation, for example with a regex such as '^[a-zA-Z0-9]{1,50}$'.
Note that Vercode Static Analysis does not automatically verify validation and you will be required to document this in a mitigation proposal ( https://help.veracode.com/r/improve_mitigation ) and manually contact your security team for a mitigation proposal review.
Please note that Veracode Static Analysis is automated analysis that cannot see beyond the scope of what is provided in the input, if dynamic data originates from a table, we cannot see how that table is managed.
Thank you,
Boy Baukema