CA2100 : Rechercher des failles de sécurité dans des requêtes SQLCA2100: Review SQL queries for security vulnerabilities

TypeNameTypeName ReviewSqlQueriesForSecurityVulnerabilitiesReviewSqlQueriesForSecurityVulnerabilities
CheckIdCheckId CA2100CA2100
CategoryCategory Microsoft.SecurityMicrosoft.Security
Modification avec ruptureBreaking Change Sans ruptureNon-breaking

CauseCause

Une méthode définit le System.Data.IDbCommand.CommandText propriété à l’aide d’une chaîne qui est construite à partir d’un argument de chaîne à la méthode.A method sets the System.Data.IDbCommand.CommandText property by using a string that is built from a string argument to the method.

Description de la règleRule description

Cette règle suppose que l’argument de chaîne contient des entrées d’utilisateur.This rule assumes that the string argument contains user input. Une chaîne de commande SQL générée par une entrée d'utilisateur est vulnérable aux attaques par injection de code SQL.A SQL command string that is built from user input is vulnerable to SQL injection attacks. Dans une attaque par injection SQL, un utilisateur malveillant fournit des entrées qui modifie la conception d’une requête dans le but d’endommager ou d’accès non autorisé à la base de données sous-jacente.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. Les techniques classiques englobent l’injection d’un guillemet ou une apostrophe, qui est le délimiteur de chaîne littérale SQL ; deux tirets, ce qui signifie un commentaire SQL ; et un point-virgule, ce qui indique qu’une nouvelle commande suit.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. Si l’entrée d’utilisateur doit faire partie de la requête, utilisez un des éléments suivants, répertoriés par ordre d’efficacité, afin de réduire le risque d’attaque.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.

  • Utiliser une procédure stockée.Use a stored procedure.

  • Utilisez une chaîne de commande paramétrable.Use a parameterized command string.

  • Valider l’entrée d’utilisateur pour le type et le contenu avant de générer la chaîne de commande.Validate the user input for both type and content before you build the command string.

Ce qui suit .NET Framework.NET Framework types implémentent le CommandText propriété ou fournissent des constructeurs qui définissent la propriété à l’aide d’un argument de chaîne.The following .NET Framework.NET Framework types implement the CommandText property or provide constructors that set the property by using a string argument.

Notez que cette règle est violée lorsque la méthode ToString d’un type est utilisée explicitement ou implicitement pour construire la chaîne de requête.Notice that this rule is violated when the ToString method of a type is used explicitly or implicitly to construct the query string. Voici un exemple.The following is an example.

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

La règle est enfreinte, car un utilisateur malveillant peut substituer la méthode ToString().The rule is violated because a malicious user can override the ToString() method.

La règle est enfreinte lors de la méthode ToString est utilisée implicitement.The rule also is violated when ToString is used implicitly.

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

Comment corriger les violationsHow to fix violations

Pour corriger une violation de cette règle, utilisez une requête paramétrable.To fix a violation of this rule, use a parameterized query.

Quand supprimer les avertissementsWhen to suppress warnings

Il est possible de supprimer un avertissement de cette règle si le texte de commande ne contient pas d’entrée d’utilisateur.It is safe to suppress a warning from this rule if the command text does not contain any user input.

ExempleExample

L’exemple suivant montre une méthode, UnsafeQuery, qui enfreint la règle et une méthode, SaferQuery, qui satisfait la règle en utilisant une chaîne de commande paramétrable.The following example shows a method, UnsafeQuery, that violates the rule and a method, SaferQuery, that satisfies the rule by using a parameterized command string.

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

Voir aussiSee also

Vue d’ensemble de la sécuritéSecurity Overview