Secure ADO.NET Coding Guidelines
Securing your application includes writing code that is secure. Your code must only expose information and functionality that is required by your client code. Common attacks related to ADO.NET are a SQL Insertion attack and determining private database information from exceptions returned by an application.
Avoid SQL Insertion Attacks
SQL Insertion is where an attacker inserts additional SQL statements into your commands that process at your data source. These commands can retrieve private information as well as modify or destroy information at your data source. Code that is vulnerable to SQL Insertion concatenates command strings with external input. For example, the following code is vulnerable to SQL Insertion.
' Retrieve CustomerID to search for from external source.
Dim custID As String = GetCustomerID()
' The following line of code allows for SQL Insertion attack.
Dim selectString As String = "SELECT * FROM Customers WHERE CustomerID = " & custID
Dim cmd As SqlCommand = New SqlCommand(selectString, conn)
conn.Open()
Dim myReader As SqlDataReader = cmd.ExecuteReader()
' Process results.
myReader.Close()
conn.Close()
[C#]
// Retrieve CustomerID to search for from external source.
string custID = GetCustomerID();
// The following line of code allows for SQL Insertion attack.
string selectString = "SELECT * FROM Customers WHERE CustomerID = " + custID;
SqlCommand cmd = new SqlCommand(selectString, conn);
conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
' Process results.
myReader.Close();
conn.Close();
An attacker could enter a value of "1;DROP TABLE Customers" for the CustomerID to be queried. This would result in the following command being executed for the query.
SELECT * FROM Customers WHERE CustomerID = 1;DROP TABLE Customers
To help protect against a SQL Insertion attack, validate input from external sources and pass column values as parameters instead of concatenating values to create a SQL statement.
Validating Input
Regular expressions can be used to validate that input matches a particular format. The .NET Framework provides the Regex object to validate a value against a regular expression. For example, the following ensures that a value is a 5-character alphanumeric string.
Public Static Function Validate(inString As String) As Boolean
Dim r As Regex = New Regex("^[A-Za-z0-9]{5}$")
Return r.IsMatch(inString)
End Function
[C#]
public static bool Validate(string inString)
{
Regex r = new Regex("^[A-Za-z0-9]{5}$");
return r.IsMatch(inString)
}
Using Parameters
Parameters provide a convenient method for organizing values passed with a SQL statement or to a stored procedure. Additionally, parameters can guard against a SQL Insertion attack by ensuring that values received from an external source are passed as values only, and not part of the SQL statement. As a result, SQL commands inserted into a value are not executed at the data source. Rather, the values passed are treated as a parameter value only. The following code shows an example of using a parameter to pass a value.
' Retrieve CustomerID to search for from external source.
Dim custID As String = GetCustomerID()
Dim selectString As String = "SELECT * FROM Customers WHERE CustomerID = @CustomerID"
Dim cmd As SqlCommand = New SqlCommand(selectString, conn)
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5).Value = custID
conn.Open()
Dim myReader As SqlDataReader = cmd.ExecuteReader()
' Process results.
myReader.Close()
conn.Close()
[C#]
// Retrieve CustomerID to search for from external source.
string custID = GetCustomerID();
string selectString = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
SqlCommand cmd = new SqlCommand(selectString, conn);
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5).Value = custID;
conn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
' Process results.
myReader.Close();
conn.Close();
Keep Exception Information Private
Attackers often use information from an exception, such as the name of your server, database, or table to mount a specific attack on your system. Because exceptions can contain specific information about your application or data source, you can help your application and data source better protected by only exposing information to the client that is required.
To avoid exposing private information through exceptions, do not return the contents of a system exception to the user. Instead, handle the exception internally. If a message must be sent to the user, return your own custom message that contains minimal information (such as "Connection failed. Please contact your system administrator."), and log the specific information so that an administrator can utilize it.
For example, the following code traps for exceptions when opening a connection and writes the exception to the event log.
Dim conn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;")
Try
conn.Open()
Catch e As SqlException
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog()
log.Source = "My Application"
log.WriteEntry(e.ToString())
If conn.State <> ConnectionState.Open Then _
Console.WriteLine("Connection was not opened.")
Finally
conn.Close()
End Try
[C#]
SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;");
try
{
conn.Open();
}
catch (SqlException e)
{
System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
log.Source = "My Application";
log.WriteEntry(e.ToString());
if (conn.State != ConnectionState.Open)
Console.WriteLine("Connection was not opened.");
}
finally
{
conn.Close();
}