How to avoid CA2100 with a constructed query

Question

In many places in my code I'm building a SQL statement using StringBuilder, and in every case this triggers a CA2100: Review SQL queries for security vulnerabilities from Code Analysis, because the SQLCommand contents come from the StringBuilder rather than a literal.

Often these queries are assembled via some flow control (case or if), where pieces of the query might be conditional.

My question is, should I suppress every single one of these, or is there a different pattern for building (sometimes complex) queries inline, but avoiding the warning?

An example of code which triggers this:

System.Text.StringBuilder sb = new System.Text.StringBuilder();

sb.AppendLine("select ");
sb.AppendLine("  Q.QUOTE_TITLE as [@Description] ");
sb.AppendLine("from ");
sb.AppendLine("  QUOTE Q ");
sb.AppendLine("where ");
sb.AppendLine("  Q.QUOTE_ID = @QUOTE_ID ");
sb.AppendLine("  and Q.QUOTE_VERS = @QUOTE_VERS  ");
sb.AppendLine("for xml path('Contract') ");

SqlCommand sqlCmd = new SqlCommand(sb.ToString(), MainDBConnection);
sqlCmd.Parameters.Add("@QUOTE_ID", SqlDbType.Int).Value = QuoteID;
sqlCmd.Parameters.Add("@QUOTE_VERS", SqlDbType.SmallInt).Value = QuoteVersion;

Answer 1

Why do you use a StringBuilder at all? You can use a string literal which is also more readable:

string sql = @"select Q.QUOTE_TITLE as [@Description]
               from QUOTE Q
               where Q.QUOTE_ID = @QUOTE_ID
               and Q.QUOTE_VERS = @QUOTE_VERS
               for xml path('Contract')"; 
SqlCommand sqlCmd = new SqlCommand(sql, MainDBConnection);
sqlCmd.Parameters.Add("@QUOTE_ID", SqlDbType.Int).Value = QuoteID;
sqlCmd.Parameters.Add("@QUOTE_VERS", SqlDbType.SmallInt).Value = QuoteVersion;