Review sql queries for security vulnerabilities
TypeName |
ReviewSqlQueriesForSecurityVulnerabilities |
CheckId |
CA2100 |
Category |
Microsoft.Security |
Breaking Change |
NonBreaking |
Cause
A method sets the System.Data.IDbCommand.CommandText property by using a string that is built from a string argument to the method.
Rule Description
This rule assumes that the string argument contains user input. A SQL command string built from user input is vulnerable to SQL injection attacks. 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. 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. 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.
Use a stored procedure.
Use a parameterized command string.
Validate the user input for both type and content before building the command string.
The following .NET Framework types implement the CommandText property or provide constructors that set the property using a string argument.
System.Data.Odbc.OdbcCommand and System.Data.Odbc.OdbcDataAdapter
System.Data.OleDb.OleDbCommand and System.Data.OleDb.OleDbDataAdapter
System.Data.OracleClient.OracleCommand and System.Data.OracleClient.OracleDataAdapter
System.Data.SqlServerCe.SqlCeCommand and System.Data.SqlServerCe.SqlCeDataAdapter
System.Data.SqlClient.SqlCommand and System.Data.SqlClient.SqlDataAdapter
How to Fix Violations
To fix a violation of this rule, use a parameterized query.
When to Exclude Warnings
It is safe to exclude a warning from this rule if the command text does not contain any user input.
Example
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'
}