Chỉnh sửa

Chia sẻ qua

CA3001: Review code for SQL injection vulnerabilities

  • Bài viết
Property Value
Rule ID CA3001
Title Review code for SQL injection vulnerabilities
Category Security
Fix is breaking or non-breaking Non-breaking
Enabled by default in .NET 8 No

Cause

Potentially untrusted HTTP request input reaches an SQL command's text.

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

Rule description

When working with untrusted input and SQL commands, be mindful of SQL injection attacks. An SQL injection attack can execute malicious SQL commands, compromising the security and integrity of your application. Typical techniques include using a single quotation mark or apostrophe for delimiting literal strings, two dashes for a comment, and a semicolon for the end of a statement. For more information, see SQL Injection.

This rule attempts to find input from HTTP requests reaching an SQL command's text.

Note

This rule can't track data across assemblies. For example, if one assembly reads the HTTP request input and then passes it to another assembly that executes the SQL command, this rule won't produce a warning.

Note

There is a configurable limit to how deep this rule will analyze data flow across method calls. See Analyzer Configuration for how to configure the limit in an EditorConfig file.

How to fix violations

Use parameterized SQL commands, or stored procedures, with parameters containing the untrusted input.

When to suppress warnings

It's safe to suppress a warning from this rule if you know that the input is always validated against a known safe set of characters.

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.

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

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

[*.{cs,vb}]
dotnet_diagnostic.CA3001.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.

You can configure these options for just this rule, for all rules it applies to, or for all rules in this category (Security) that it applies 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. 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.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 Matches all symbols named either MyType1 or 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) Matches specific methods MyMethod1 and MyMethod2 with the respective fully qualified signatures.

Exclude specific types and their derived types

You can exclude specific types and their derived types from analysis. 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:

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 Matches all types named either MyType1 or MyType2 and all of their derived types.
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 Matches specific types MyType1 and MyType2 with the respective fully qualified names, and all of their derived types.

Pseudo-code examples

Violation

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestNamespace
{
    public partial class WebForm : System.Web.UI.Page
    {
        public static string ConnectionString { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            string name = Request.Form["product_name"];
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand sqlCommand = new SqlCommand()
                {
                    CommandText = "SELECT ProductId FROM Products WHERE ProductName = '" + name + "'",
                    CommandType = CommandType.Text,
                };

                SqlDataReader reader = sqlCommand.ExecuteReader();
            }
        }
    }
}

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

Namespace VulnerableWebApp
    Partial Public Class WebForm
        Inherits System.Web.UI.Page

        Public Property ConnectionString As String

        Protected Sub Page_Load(sender As Object, e As EventArgs)
            Dim name As String = Me.Request.Form("product_name")
            Using connection As SqlConnection = New SqlConnection(ConnectionString)
                Dim sqlCommand As SqlCommand = New SqlCommand With {.CommandText = "SELECT ProductId FROM Products WHERE ProductName = '" + name + "'",
                                                                    .CommandType = CommandType.Text}
                Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
            End Using
        End Sub
    End Class
End Namespace

Parameterized solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestNamespace
{
    public partial class WebForm : System.Web.UI.Page
    {
        public static string ConnectionString { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            string name = Request.Form["product_name"];
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand sqlCommand = new SqlCommand()
                {
                    CommandText = "SELECT ProductId FROM Products WHERE ProductName = @productName",
                    CommandType = CommandType.Text,
                };

                sqlCommand.Parameters.Add("@productName", SqlDbType.NVarChar, 128).Value = name;

                SqlDataReader reader = sqlCommand.ExecuteReader();
            }
        }
    }
}

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

Namespace VulnerableWebApp
    Partial Public Class WebForm
        Inherits System.Web.UI.Page

        Public Property ConnectionString As String

        Protected Sub Page_Load(sender As Object, e As EventArgs)
            Dim name As String = Me.Request.Form("product_name")
            Using connection As SqlConnection = New SqlConnection(ConnectionString)
                Dim sqlCommand As SqlCommand = New SqlCommand With {.CommandText = "SELECT ProductId FROM Products WHERE ProductName = @productName",
                                                                    .CommandType = CommandType.Text}
                sqlCommand.Parameters.Add("@productName", SqlDbType.NVarChar, 128).Value = name
                Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
            End Using
        End Sub
    End Class
End Namespace

Stored procedure solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestNamespace
{
    public partial class WebForm : System.Web.UI.Page
    {
        public static string ConnectionString { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
            string name = Request.Form["product_name"];
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand sqlCommand = new SqlCommand()
                {
                    CommandText = "sp_GetProductIdFromName",
                    CommandType = CommandType.StoredProcedure,
                };

                sqlCommand.Parameters.Add("@productName", SqlDbType.NVarChar, 128).Value = name;

                SqlDataReader reader = sqlCommand.ExecuteReader();
            }
        }
    }
}

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

Namespace VulnerableWebApp
    Partial Public Class WebForm
        Inherits System.Web.UI.Page

        Public Property ConnectionString As String

        Protected Sub Page_Load(sender As Object, e As EventArgs)
            Dim name As String = Me.Request.Form("product_name")
            Using connection As SqlConnection = New SqlConnection(ConnectionString)
                Dim sqlCommand As SqlCommand = New SqlCommand With {.CommandText = "sp_GetProductIdFromName",
                                                                    .CommandType = CommandType.StoredProcedure}
                sqlCommand.Parameters.Add("@productName", SqlDbType.NVarChar, 128).Value = name
                Dim reader As SqlDataReader = sqlCommand.ExecuteReader()
            End Using
        End Sub
    End Class
End Namespace