CA2100: Review SQL queries for security vulnerabilities

Cause

A method sets the System.Data.IDbCommand.CommandText property by using a string that's 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 might contain user input. A SQL command string that's 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 System.Data.IDbCommand.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.

csharp

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

The same applies when using ToString() implicitly.

csharp

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's safe to suppress a warning from this rule if the command text does not contain any user input.

Suppress a warning

If you just want to suppress a single violation, add preprocessor directives to your source file to disable and then re-enable the rule.

csharp

#pragma warning disable CA2100
// The code that's violating the rule is on this line.
#pragma warning restore CA2100

To disable the rule for a file, folder, or project, set its severity to none in the configuration file.

ini

[*.{cs,vb}]
dotnet_diagnostic.CA2100.severity = none

For more information, see How to suppress code analysis warnings.

Configure code to analyze

Use the following options to configure which parts of your codebase to run this rule on.

Exclude specific symbols
Exclude specific types and their derived types

In addition, the following dataflow analysis–related options apply to this rule:

You can configure these options for just this rule, for all rules they apply to, or for all rules in this category (Security) that they apply to. For more information, see Code quality rule configuration options.

Exclude specific symbols

You can exclude specific symbols, such as types and methods, from analysis by setting the excluded_symbol_names option. 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:

ini

dotnet_code_quality.CAXXXX.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: for methods, T: for types, and N: for namespaces.
.ctor for constructors and .cctor for static constructors.

Examples:

Option Value	Summary
`dotnet_code_quality.CAXXXX.excluded_symbol_names = MyType`	Matches all symbols named `MyType`.
`dotnet_code_quality.CAXXXX.excluded_symbol_names = MyType1	MyType2`
`dotnet_code_quality.CAXXXX.excluded_symbol_names = M:NS.MyType.MyMethod(ParamType)`	Matches specific method `MyMethod` with the specified fully qualified signature.
`dotnet_code_quality.CAXXXX.excluded_symbol_names = M:NS1.MyType1.MyMethod1(ParamType)	M:NS2.MyType2.MyMethod2(ParamType)`

Exclude specific types and their derived types

You can exclude specific types and their derived types from analysis by setting the excluded_type_names_with_derived_types option. For example, to specify that the rule should not run on any methods within types named MyType and their derived types, add the following key-value pair to an .editorconfig file in your project:

ini

dotnet_code_quality.CAXXXX.excluded_type_names_with_derived_types = MyType

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

Type name only (includes all types with the name, regardless of the containing type or namespace).
Fully qualified names in the symbol's documentation ID format, with an optional T: prefix.

Examples:

Option value	Summary
`dotnet_code_quality.CAXXXX.excluded_type_names_with_derived_types = MyType`	Matches all types named `MyType` and all of their derived types.
`dotnet_code_quality.CAXXXX.excluded_type_names_with_derived_types = MyType1	MyType2`
`dotnet_code_quality.CAXXXX.excluded_type_names_with_derived_types = M:NS.MyType`	Matches specific type `MyType` with given fully qualified name and all of its derived types.
`dotnet_code_quality.CAXXXX.excluded_type_names_with_derived_types = M:NS1.MyType1	M:NS2.MyType2`

Example

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

sample · vbdocs/fundamentals/code-analysis/quality-rules/snippets/vb/all-rules/ca2100-review-sql-queries-for-security-vulnerabilities_1.vb

Imports System.Data
Imports System.Data.OleDb
Imports System.Runtime.Versioning

Namespace ca2100

    Public Class SqlQueries

        <SupportedOSPlatform("windows")>
        Function UnsafeQuery(connection As String,
         name As String, password As String) As Object

            Dim someConnection As New OleDbConnection(connection)
            Dim someCommand As New OleDbCommand With {
                .Connection = someConnection,
                .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

        <SupportedOSPlatform("windows")>
        Function SaferQuery(connection As String,
         name As String, password As String) As Object

            Dim someConnection As New OleDbConnection(connection)
            Dim someCommand As New OleDbCommand With {
                .Connection = someConnection
            }

            someCommand.Parameters.AddWithValue(
            "@username", OleDbType.Char).Value = name
            someCommand.Parameters.AddWithValue(
            "@password", OleDbType.Char).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

        <SupportedOSPlatform("windows")>
        Shared Sub Main2100(args As String())

            Dim queries As New SqlQueries()
            queries.UnsafeQuery(args(0), "' OR 1=1 --", "[PLACEHOLDER]")
            ' Resultant query (which is always true):
            ' SELECT AccountNumber FROM Users WHERE Username='' OR 1=1

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

    End Class

End Namespace

sample · csharpdocs/fundamentals/code-analysis/quality-rules/snippets/csharp/all-rules/ca2100.cssnippet1

csharp

using System.Data.OleDb;
using System.Runtime.Versioning;

namespace ca2100
{
    //<snippet1>
    [SupportedOSPlatform("Windows")]
    public class OleDbQueries
    {
        public object UnsafeQuery(
           string connection, string name, string password)
        {
            using OleDbConnection someConnection = new(connection);
            using OleDbCommand someCommand = new();
            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)
        {
            using OleDbConnection someConnection = new(connection);
            using OleDbCommand someCommand = new();
            someCommand.Connection = someConnection;

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

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

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

            queries.SaferQuery(args[0], "' OR 1=1 --", "[PLACEHOLDER]");
            // Resultant query (notice the additional single quote character):
            // SELECT AccountNumber FROM Users WHERE Username=''' OR 1=1 --'
            //                                   AND Password='[PLACEHOLDER]'
        }
    }
    //</snippet1>
}

#Cause

#Rule description

#How to fix violations

#When to suppress warnings

#Suppress a warning

#Configure code to analyze

#Exclude specific symbols

#Exclude specific types and their derived types

#Example

#See also

Cause

Rule description

How to fix violations

When to suppress warnings

Suppress a warning

Configure code to analyze

Exclude specific symbols

Exclude specific types and their derived types

Example

See also