CA2100：必須檢視 SQL 查詢中是否有安全性弱點

發行項
02/28/2013

型別名稱	ReviewSqlQueriesForSecurityVulnerabilities
CheckId	CA2100
分類	Microsoft.Security
中斷變更	中斷

原因

方法會使用從方法之字串引數所建置的字串，設定 IDbCommand.CommandText 屬性。

規則描述

這項規則假設字串引數包含使用者輸入。從使用者輸入所建置的 SQL 命令字串很容易遭到 SQL 插入 (SQL Injection) 攻擊。在 SQL 插入攻擊中，惡意使用者所提供的輸入會嘗試更改查詢設計，以損壞基礎資料庫或取得未經授權的基礎資料庫存取權限。典型的技術包括插入一個單引號或所有格符號 (這是 SQL 常值字串分隔符號)、雙虛線 (這表示 SQL 註解)、分號 (這表示緊接著新命令)。如果使用者輸入必須為查詢的一部分，請使用下列其中一項 (依照效能的順序列出) 來減少攻擊的風險。

使用預存程序 (Stored Procedure)
使用參數型命令 (Parameterized Command) 字串
在建置命令字串之前，驗證使用者輸入的型別和內容。

下列 .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 查詢中是否有安全性弱點

原因

規則描述

如何修正違規

隱藏警告的時機

範例

請參閱

概念

其他資源