CA2100：必須檢視 SQL 查詢中是否有安全性弱點
型別名稱 |
ReviewSqlQueriesForSecurityVulnerabilities |
CheckId |
CA2100 |
分類 |
Microsoft.Security |
中斷變更 |
中斷 |
原因
方法會使用從方法之字串引數所建置的字串，設定 IDbCommand.CommandText 屬性。
規則描述
這項規則假設字串引數包含使用者輸入。從使用者輸入所建置的 SQL 命令字串很容易遭到 SQL 插入 (SQL Injection) 攻擊。在 SQL 插入攻擊中，惡意使用者所提供的輸入會嘗試更改查詢設計，以損壞基礎資料庫或取得未經授權的基礎資料庫存取權限。典型的技術包括插入一個單引號或所有格符號 (這是 SQL 常值字串分隔符號)、雙虛線 (這表示 SQL 註解)、分號 (這表示緊接著新命令)。如果使用者輸入必須為查詢的一部分，請使用下列其中一項 (依照效能的順序列出) 來減少攻擊的風險。
使用預存程序 (Stored Procedure)
使用參數型命令 (Parameterized Command) 字串
在建置命令字串之前，驗證使用者輸入的型別和內容。
下列 .NET Framework 型別會實作 CommandText 屬性，或提供會使用字串引數設定屬性的建構函式。
請注意，當明確或隱含地使用型別的 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'
}