CA2100: Review SQL queries for security vulnerabilities

CheckId CA2100
Category Microsoft.Security
Breaking change Non-breaking

Cause

A method sets the System.Data.IDbCommand.CommandText property by using a string that is built from a string argument to the method.

By default, this rule analyzes the entire codebase, but this is configurable.

Rule description

This rule assumes that any string, whose value can't be determined at compile time, may contain user input. A SQL command string that is built from user input is vulnerable to SQL injection attacks. In a SQL injection attack, a malicious user supplies input that alters the design of a query in an attempt to damage or gain unauthorized access to the underlying database. Typical techniques include injection of a single quotation mark or apostrophe, which is the SQL literal string delimiter; two dashes, which signifies a SQL comment; and a semicolon, which indicates that a new command follows. If user input must be part of the query, use one of the following, listed in order of effectiveness, to reduce the risk of attack.

  • Use a stored procedure.

  • Use a parameterized command string.

  • Validate the user input for both type and content before you build the command string.

The following .NET types implement the CommandText property or provide constructors that set the property by using a string argument.

In some cases, this rule might not determine a string's value at compile time, even though you can. In those cases, this rule produces false positives when using those strings as SQL commands. The following is an example of such a string.

int x = 10;
string query = "SELECT TOP " + x.ToString() + " FROM Table";

The same applies when using ToString() implicitly.

int x = 10;
string query = String.Format("SELECT TOP {0} FROM Table", x);

How to fix violations

To fix a violation of this rule, use a parameterized query.

When to suppress warnings

It is safe to suppress a warning from this rule if the command text does not contain any user input.

Configurability

If you're running this rule from FxCop analyzers (and not with legacy analysis), you can configure the analysis for this rule.

Excluded symbol names

You can configure which parts of your codebase to exclude from analysis. For example, to specify that the rule should not run on any code within types named MyType, add the following key-value pair to an .editorconfig file in your project:

dotnet_code_quality.CA2100.excluded_symbol_names = MyType

Allowed symbol name formats in the option value (separated by '|'):

  • Symbol name only (includes all symbols with the name, regardless of the containing type or namespace)
  • Fully qualified names in the symbol's documentation ID format. Each symbol name requires a symbol kind prefix, such as "M:" prefix for methods, "T:" prefix for types, "N:" prefix for namespaces, etc.
  • .ctor for constructors and .cctor for static constructors

Examples:

Option Value Summary
dotnet_code_quality.CA2100.excluded_symbol_names = MyType Matches all symbols named 'MyType' in the compilation
dotnet_code_quality.CA2100.excluded_symbol_names = MyType1|MyType2 Matches all symbols named either 'MyType1' or 'MyType2' in the compilation
dotnet_code_quality.CA2100.excluded_symbol_names = M:NS.MyType.MyMethod(ParamType) Matches specific method 'MyMethod' with given fully qualified signature
dotnet_code_quality.CA2100.excluded_symbol_names = M:NS1.MyType1.MyMethod1(ParamType)|M:NS2.MyType2.MyMethod2(ParamType) Matches specific methods 'MyMethod1' and 'MyMethod2' with respective fully qualified signature

You can configure all of these options for just this rule, for all rules, or for all rules in this category (Security). For more information, see Configure FxCop analyzers.

Example

The following example shows a method, UnsafeQuery, that violates the rule and a method, SaferQuery, that satisfies the rule by using a parameterized command string.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace SecurityLibrary

   Public Class SqlQueries

      Function UnsafeQuery(connection As String, _ 
         name As String, password As String) As Object
      
         Dim someConnection As New SqlConnection(connection)
         Dim someCommand As New SqlCommand()
         someCommand.Connection = someConnection

         someCommand.CommandText = "SELECT AccountNumber FROM Users " & _ 
            "WHERE Username='" & name & "' AND Password='" & password & "'"

         someConnection.Open()
         Dim accountNumber As Object = someCommand.ExecuteScalar()
         someConnection.Close()
         Return accountNumber

      End Function

      Function SaferQuery(connection As String, _ 
         name As String, password As String) As Object
      
         Dim someConnection As New SqlConnection(connection)
         Dim someCommand As New SqlCommand()
         someCommand.Connection = someConnection

         someCommand.Parameters.Add( _ 
            "@username", SqlDbType.NChar).Value = name
         someCommand.Parameters.Add( _ 
            "@password", SqlDbType.NChar).Value = password
         someCommand.CommandText = "SELECT AccountNumber FROM Users " & _  
            "WHERE Username=@username AND Password=@password"

         someConnection.Open()
         Dim accountNumber As Object = someCommand.ExecuteScalar()
         someConnection.Close()
         Return accountNumber

      End Function

   End Class 

   Class MaliciousCode

      Shared Sub Main(args As String())
      
         Dim queries As New SqlQueries()
         queries.UnsafeQuery(args(0), "' OR 1=1 --", "anything")
         ' Resultant query (which is always true): 
         ' SELECT AccountNumber FROM Users WHERE Username='' OR 1=1

         queries.SaferQuery(args(0), "' OR 1 = 1 --", "anything")
         ' Resultant query (notice the additional single quote character):
         ' SELECT AccountNumber FROM Users WHERE Username=''' OR 1=1 --' 
         '                                   AND Password='anything'
      End Sub

   End Class

End Namespace
using System;
using System.Data;
using System.Data.SqlClient;

namespace SecurityLibrary
{
   public class SqlQueries
   {
      public object UnsafeQuery(
         string connection, string name, string password)
      {
         SqlConnection someConnection = new SqlConnection(connection);
         SqlCommand someCommand = new SqlCommand();
         someCommand.Connection = someConnection;

         someCommand.CommandText = "SELECT AccountNumber FROM Users " +
            "WHERE Username='" + name + 
            "' AND Password='" + password + "'";

         someConnection.Open();
         object accountNumber = someCommand.ExecuteScalar();
         someConnection.Close();
         return accountNumber;
      }

      public object SaferQuery(
         string connection, string name, string password)
      {
         SqlConnection someConnection = new SqlConnection(connection);
         SqlCommand someCommand = new SqlCommand();
         someCommand.Connection = someConnection;

         someCommand.Parameters.Add(
            "@username", SqlDbType.NChar).Value = name;
         someCommand.Parameters.Add(
            "@password", SqlDbType.NChar).Value = password;
         someCommand.CommandText = "SELECT AccountNumber FROM Users " + 
            "WHERE Username=@username AND Password=@password";

         someConnection.Open();
         object accountNumber = someCommand.ExecuteScalar();
         someConnection.Close();
         return accountNumber;
      }
   }

   class MaliciousCode
   {
      static void Main(string[] args)
      {
         SqlQueries queries = new SqlQueries();
         queries.UnsafeQuery(args[0], "' OR 1=1 --", "anything");
         // Resultant query (which is always true): 
         // SELECT AccountNumber FROM Users WHERE Username='' OR 1=1

         queries.SaferQuery(args[0], "' OR 1 = 1 --", "anything");
         // Resultant query (notice the additional single quote character):
         // SELECT AccountNumber FROM Users WHERE Username=''' OR 1=1 --'
         //                                   AND Password='anything'
      }
   }
}
#using <System.dll>
#using <System.Data.dll>
#using <System.EnterpriseServices.dll>
#using <System.Transactions.dll>
#using <System.Xml.dll>
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;

namespace SecurityLibrary
{
   public ref class SqlQueries
   {
   public:
      Object^ UnsafeQuery(
         String^ connection, String^ name, String^ password)
      {
         SqlConnection^ someConnection = gcnew SqlConnection(connection);
         SqlCommand^ someCommand = gcnew SqlCommand();
         someCommand->Connection = someConnection;

         someCommand->CommandText = String::Concat(
            "SELECT AccountNumber FROM Users WHERE Username='", 
            name, "' AND Password='", password, "'");

         someConnection->Open();
         Object^ accountNumber = someCommand->ExecuteScalar();
         someConnection->Close();
         return accountNumber;
      }

      Object^ SaferQuery(
         String^ connection, String^ name, String^ password)
      {
         SqlConnection^ someConnection = gcnew SqlConnection(connection);
         SqlCommand^ someCommand = gcnew SqlCommand();
         someCommand->Connection = someConnection;

         someCommand->Parameters->Add(
            "@username", SqlDbType::NChar)->Value = name;
         someCommand->Parameters->Add(
            "@password", SqlDbType::NChar)->Value = password;
         someCommand->CommandText = "SELECT AccountNumber FROM Users "  
            "WHERE Username=@username AND Password=@password";

         someConnection->Open();
         Object^ accountNumber = someCommand->ExecuteScalar();
         someConnection->Close();
         return accountNumber;
      }
   };
}

using namespace SecurityLibrary;

void main()
{
   SqlQueries^ queries = gcnew SqlQueries();
   queries->UnsafeQuery(Environment::GetCommandLineArgs()[1], 
      "' OR 1=1 --", "anything");
   // Resultant query (which is always true): 
   // SELECT AccountNumber FROM Users WHERE Username='' OR 1=1

   queries->SaferQuery(Environment::GetCommandLineArgs()[1], 
      "' OR 1 = 1 --", "anything");
   // Resultant query (notice the additional single quote character):
   // SELECT AccountNumber FROM Users WHERE Username=''' OR 1=1 --'
   //                                   AND Password='anything'
}

See also