ADO.NET Secure Coding Guidelines
The .NET Framework provides many useful classes and services that enable developers to write secure applications (see Security in Native and .NET Framework Code). Secure Coding Guidelines provides an overview of the different ways code can be designed to work with the .NET Framework security system. In addition, you must follow secure data access coding practices in your ADO.NET code to prevent it being exploited by a potential attacker. Common attacks related to ADO.NET arise from attackers using SQL injection or determining private database information from exceptions returned by an application. These can be thwarted by using parameterized commands and effective exception handling.
Preventing SQL Injection Attacks
SQL injection occurs when an attacker inserts, or injects, additional SQL statements into commands that are processed at a data source or database server. These commands can retrieve private information as well as modify or destroy information at the database server. So long as the injected SQL statements are syntactically correct, it is impossible to detect tampering on the server side programmatically. Therefore, you must ensure that user input cannot be inserted into executed commands. Following these guidelines will help protect against SQL injection attacks:
Always run under a least-privilege account.
Always validate all user input from external sources.
Always pass column values as parameters instead of concatenating values.
SQL Injection Attack Example
The following code is vulnerable to SQL injection attack because it accepts any user input from a TextBox control, concatenates it with a Transact-SQL statement, and submits the concatenated string to SQL Server for processing. As long as the concatenated Transact-SQL statement is syntactically correct and the caller has the appropriate permissions, SQL Server will process the commands. By using string concatenation, you are opening the door to the possibility that your application can be exploited by an attacker who enters data that can execute unexpected commands on the server.
' Retrieve CustomerID to search for.
Dim ID As String = TextBox1.Text
' The following line of code allows for SQL injection attack.
Dim query As String = _
"SELECT * FROM dbo.Orders WHERE CustomerID = '" & ID & "';"
' Code connecting to a data source has been omitted for brevity.
Dim cmd As SqlCommand = New SqlCommand(query, connection)
Dim reader As SqlDataReader = cmd.ExecuteReader()
reader.Close()
// Retrieve CustomerID to search for.
string ID =TextBox1.Text;
// The following line of code allows for SQL injection attack.
string query =
"SELECT * FROM dbo.Orders WHERE CustomerID = '" + ID + "';"
// Code connecting to a data source has been omitted for brevity.
SqlCommand cmd = new SqlCommand(query, connection);
SqlDataReader reader = cmd.ExecuteReader();
reader.Close();
In this scenario, a potential attacker could enter a value of "ABCD';DELETE FROM Orders;--" for the CustomerID, where ABCD would be a valid value for the expected WHERE clause. The single quote after ABCD completes the WHERE clause for the intended query and the semi-colon delimits the end of the first command. The DELETE FROM statement starts a new command representing the SQL injection attack. The double-hyphen character sequence (--) tells SQL Server that anything that follows is a comment and should be ignored, so that the closing single quote and semi-colon concatenated in the original code (+ "';") does not generate a syntax error. The server would process the following string, which consists of two separate commands:
SELECT * FROM dbo.Orders WHERE CustomerID = 'ABCD';DELETE FROM Orders;--'
When SQL Server processes the first command, it will select matching records in the Orders table. When it processes the second command, it will delete all of the records in the Orders table.
SQL injection attacks can include syntax for dropping tables or for executing other commands on the server. The scope of the damage depends on the permissions granted to the calling process. The ability to use string concatenation requires that SELECT permissions on the tables be granted, so all of the data is exposed to the attacker.
Use Parameterized Commands
Parameters provide a convenient method for organizing values passed with a Transact-SQL statement or to a stored procedure. Additionally, parameters can guard against a SQL injection attack by ensuring that values received from an external source are passed as values only, and not part of the Transact-SQL statement. As a result, Transact-SQL commands inserted into a value are not executed at the data source. Rather, they are evaluated solely as a parameter value. For more information about validating user input in stored procedures, see Validating User Input.
The following code shows an example of using a parameter to pass a value. The parameter is defined as having a size of 5 characters, so a longer string value submitted in the TextBox control will throw an exception when the parameter is added to the command. However, even if the size were large enough to accommodate a malicious Transact-SQL fragment, the fragment would be treated only as part of the value, not as executable Transact-SQL code. In this example, exception handling has been omitted to keep the code brief. For more information, see Exception Handling and Logging, later in this topic.
' Retrieve CustomerID to search for.
Dim ID As String = TextBox1.Text
Dim query As String = _
"SELECT * FROM Orders WHERE CustomerID = @CustomerID"
Dim cmd As SqlCommand = New SqlCommand(query, conn)
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5).Value = ID
' Code connecting to a data source has been omitted for brevity.
Dim reader As SqlDataReader = cmd.ExecuteReader()
' Process results.
reader.Close()
// Retrieve CustomerID to search for.
string ID = TextBox1.Text;
string queryString = "SELECT * FROM Orders WHERE CustomerID = @CustomerID";
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.Parameters.Add("@CustomerID", SqlDbType.VarChar, 5).Value = ID;
// Code connecting to a data source has been omitted for brevity.
SqlDataReader reader = cmd.ExecuteReader();
' Process results.
reader.Close();
Exception Handling and Logging
Attackers often use information from an exception, such as the name of your server, database, or table to mount an attack on your system. Because exceptions can contain specific information about your application or data source, you can help keep your application and data source better protected by only exposing essential information to the client. For more information, see Exception Handling Fundamentals and Best Practices for Handling Exceptions. For more information about exception handling in ASP.NET applications, see How to: Display Safe Error Messages. For more information about logging errors, see How to: Write to the Application Event Log (Visual C#) and How to: Write to an Application Event Log.