CA2100：检查 SQL 查询中是否有安全漏洞

项目
02/21/2013

类型名	ReviewSqlQueriesForSecurityVulnerabilities
CheckId	CA2100
类别	Microsoft.Security
是否重大更改	非重大更改

原因

方法通过使用基于它的字符串参数生成的字符串来设置 IDbCommand.CommandText 属性。

规则说明

此规则假定字符串参数中包含用户输入。基于用户输入生成的 SQL 命令字符串易于受到 SQL 注入式攻击。在 SQL 注入式攻击中，恶意用户提供将更改查询设计的输入，以尝试破坏基础数据库或获取对基础数据库的未经授权的访问权限。典型的方法包括注入单引号或省略号（它们都是 SQL 文本字符串分隔符）、两个短划线（表示 SQL 注释）和一个分号（指示后面有新命令）。如果用户输入必须是查询的一部分，请使用下面按有效性顺序列出的方法之一来降低攻击风险。

使用存储过程。
使用参数化命令字符串。
在生成命令字符串之前针对类型和内容验证用户输入。

下面的 .NET Framework 类型实现 CommandText 属性，或提供可通过使用字符串参数来设置该属性的构造函数。

System.Data.Odbc.OdbcCommand 和 System.Data.Odbc.OdbcDataAdapter
System.Data.OleDb.OleDbCommand 和 System.Data.OleDb.OleDbDataAdapter
System.Data.OracleClient.OracleCommand 和 System.Data.OracleClient.OracleDataAdapter
[System.Data.SqlServerCe.SqlCeCommand] 和 [System.Data.SqlServerCe.SqlCeDataAdapter]
System.Data.SqlClient.SqlCommand 和 System.Data.SqlClient.SqlDataAdapter

请注意，一个类型的 ToString 方法被用来显式或隐式构造查询字符串时，会违反此规则。下面是一个示例。

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

因为恶意用户可以重写 ToString () 方法，会违反此规则。

隐式使用 ToString 时也会违反此规则。

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

如何解决冲突

若要修复与该规则的冲突，请使用参数化查询。

何时禁止显示警告

如果命令文本中不包含任何用户输入，则可以安全地禁止显示此规则发出的警告。

示例

下面的示例演示与该规则冲突的方法 UnsafeQuery 以及通过使用参数化命令字符串来满足该规则的方法 SaferQuery。

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 MalaciousCode

      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 MalaciousCode
   {
      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'
}

请参见

概念

Secure ADO.NET Coding Guidelines

通过

CA2100：检查 SQL 查询中是否有安全漏洞

原因

规则说明

如何解决冲突

何时禁止显示警告

示例

请参见

概念

其他资源