To protect your application from SQL injection, perform the
following steps:
- Step 1. Constrain input.
- Step 2. Use parameters with stored procedures.
- Step 3. Use parameters with dynamic SQL.
Step 1. Constrain
Input
You should validate all input to your ASP.NET applications for type,
length, format, and range. By constraining the input used in your data
access queries, you can protect your application from SQL injection.
Note When constraining input, it is a good
practice to create a list of acceptable characters and use regular
expressions to reject any characters that are not on the list. The
potential risk associated with using a list of unacceptable characters
is that it is always possible to overlook an unacceptable character when
defining the list; also, an unacceptable character can be represented
in an alternate format to pass validation.
Constrain Input in ASP.NET Web Pages
Start by constraining input in the server-side code for your ASP.NET
Web pages. Do not rely on client-side validation because it can be
easily bypassed. Use client-side validation only to reduce round trips
and to improve the user experience.
If you use server controls, use the ASP.NET validator controls, such
as the RegularExpressionValidator and RangeValidator
controls to constrain input. If you use regular HTML input controls,
use the Regex class in your server-side code to
constrain input.
If in the previous code example, the SSN value is captured by an
ASP.NET TextBox control, you can constrain its input by
using a RegularExpressionValidator control as shown in
the following.
If the SSN input is from another source, such as an HTML control, a
query string parameter, or a cookie, you can constrain it by using the Regex
class from the System.Text.RegularExpressions
namespace. The following example assumes that the input is obtained from
a cookie.
using System.Text.RegularExpressions;
For more information about how to constrain input in your ASP.NET Web
pages, see how to:Protect from Injection Attacks in ASP .Net.
Constrain Input in Data Access Code
In some situations, you need to provide validation in your data
access code, perhaps in addition to your ASP.NET page-level validation.
Two common situations where you need to provide validation in your data
access code are:
- Untrusted clients. If the data can come from an
untrusted source or you cannot guarantee how well the data has been
validated and constrained, add validation logic that constrains input to
your data access routines.
- Library code. If your data access code is packaged
as a library designed for use by multiple applications, your data access
code should perform its own validation, because you can make no safe
assumptions about the client applications.
The following example shows how a data access routine can validate
its input parameters by using regular expressions prior to using the
parameters in a SQL statement.
Step 2. Use Parameters with Stored
Procedures
Using stored procedures does not necessarily prevent SQL injection.
The important thing to do is use parameters with stored procedures. If
you do not use parameters, your stored procedures can be susceptible to
SQL injection if they use unfiltered input as described in the
"Overview" section of this document.
The following code shows how to use SqlParameterCollection
when calling a stored procedure.
In this case, the @au_id parameter is treated as a
literal value and not as executable code. Also, the parameter is checked
for type and length. In the preceding code example, the input value
cannot be longer than 11 characters. If the data does not conform to the
type or length defined by the parameter, the SqlParameter
class throws an exception.
Review Your Application's Use of Parameterized Stored
Procedures
Because using stored procedures with parameters does not necessarily
prevent SQL injection, you should review your application's use of this
type of stored procedure. For example, the following parameterized
stored procedure has several security vulnerabilities.
An application that uses a stored procedure similar to the one in the
preceding code example has the following vulnerabilities:
Step 3. Use Parameters with Dynamic SQL
If you cannot use stored procedures, you should still use parameters
when constructing dynamic SQL statements. The following code shows how
to use SqlParametersCollection with dynamic SQL.
Using Parameter Batching
A common misconception is that if you concatenate several SQL
statements to send a batch of statements to the server in a single round
trip, you cannot use parameters. However, you can use this technique if
you make sure that parameter names are not repeated. You can easily do
this by making sure that you use unique parameter names during SQL text
concatenation, as shown here.
Additional Considerations
Other things to consider when you develop countermeasures to prevent
SQL injection include:
- Use escape routines to handle special input characters.
- Use a least-privileged database account.
- Avoid disclosing error information.
Use Escape Routines to Handle Special Input Characters
In situations where parameterized SQL cannot be used and you are
forced to use dynamic SQL instead, you need to safeguard against input
characters that have special meaning to SQL Server (such as the single
quote character). If not handled, special characters such as the single
quote character in the input can be utilized to cause SQL injection.
Note Special input characters pose a threat only
with dynamic SQL and not when using parameterized SQL.
Escape routines add an escape character to characters that have
special meaning to SQL Server, thereby making them harmless. This is
illustrated in the following code fragment:
}
Use a Least-Privileged Database Account
Your application should connect to the database by using a
least-privileged account. If you use Windows authentication to connect,
the Windows account should be least-privileged from an operating system
perspective and should have limited privileges and limited ability to
access Windows resources. Additionally, whether or not you use Windows
authentication or SQL authentication, the corresponding SQL Server login
should be restricted by permissions in the database.
Consider the example of an ASP.NET application running on Microsoft
Windows Server 2003 that accesses a database on a different server in
the same domain. By default, the ASP.NET application runs in an
application pool that runs under the Network Service account. This
account is a least privileged account.
To access SQL Server with the Network Service account
- Create a SQL Server login for the Web server's Network Service
account. The Network Service account has network credentials that are
presented at the database server as the identity DOMAIN\WEBSERVERNAME$.
For example, if your domain is called XYZ and the Web server is called
123, you create a database login for XYZ\123$.
- Grant the new login access to the required database by creating a
database user and adding the user to a database role.
- Establish permissions to let this database role call the required
stored procedures or access the required tables in the database. Only
grant access to stored procedures the application needs to use, and only
grant sufficient access to tables based on the application's minimum
requirements.
For example, if the ASP.NET application only performs database
lookups and does not update any data, you only need to grant read access
to the tables. This limits the damage that an attacker can cause if the
attacker succeeds in a SQL injection attack.
Avoid Disclosing Error Information
Use structured exception handling to catch errors and prevent them
from propagating back to the client. Log detailed error information
locally, but return limited error details to the client.
If errors occur while the user is connecting to the database, be sure
that you provide only limited information about the nature of the error
to the user. If you disclose information related to data access and
database errors, you could provide a malicious user with useful
information that he or she can use to compromise your database security.
Attackers use the information in detailed error messages to help
deconstruct a SQL query that they are trying to inject with malicious
code. A detailed error message may reveal valuable information such as
the connection string, SQL server name, or table and database naming
conventions.
private
string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'",
"''");
using
System.Data; using System.Data.SqlClient; . . . using (SqlConnection
connection = new SqlConnection(connectionString)) { SqlDataAdapter
dataAdapter = new SqlDataAdapter( "SELECT CustomerID INTO #Temp1 FROM
Customers " + "WHERE CustomerID > @custIDParm; SELECT CompanyName
FROM Customers " + "WHERE Country = @countryParm and CustomerID IN " +
"(SELECT CustomerID FROM #Temp1);", connection); SqlParameter custIDParm
= dataAdapter.SelectCommand.Parameters.Add( "@custIDParm",
SqlDbType.NChar, 5); custIDParm.Value = customerID.Text; SqlParameter
countryParm = dataAdapter.SelectCommand.Parameters.Add( "@countryParm",
SqlDbType.NVarChar, 15); countryParm.Value = country.Text;
connection.Open(); DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet); } . . .
using
System.Data; using System.Data.SqlClient; using (SqlConnection
connection = new SqlConnection(connectionString)) { DataSet userDataset =
new DataSet(); SqlDataAdapter myDataAdapter = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",
connection); myCommand.SelectCommand.Parameters.Add("@au_id",
SqlDbType.VarChar, 11);
myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
myDataAdapter.Fill(userDataset); }
CREATE
PROCEDURE dbo.RunQuery @var ntext AS exec sp_executesql @var GO
using
System.Data; using System.Data.SqlClient; using (SqlConnection
connection = new SqlConnection(connectionString)) { DataSet userDataset =
new DataSet(); SqlDataAdapter myCommand = new SqlDataAdapter(
"LoginStoredProcedure", connection); myCommand.SelectCommand.CommandType
= CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
myCommand.Fill(userDataset); }
using
System; using System.Text.RegularExpressions; public void
CreateNewUserAccount(string name, string password) { // Check name
contains only lower case or upper case letters, // the apostrophe, a
dot, or white space. Also check it is // between 1 and 40 characters
long if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$")) throw
new FormatException("Invalid name format"); // Check password contains
at least one digit, one lower case // letter, one uppercase letter, and
is between 8 and 10 // characters long if (
!Regex.IsMatch(passwordTxt.Text,
@"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" )) throw new
FormatException("Invalid password format"); // Perform data access logic
(using type safe parameters) ... }
if
(Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$")) { //
access the database } else { // handle the bad input }
<%@
language="C#" %> <form id="form1" runat="server">
<asp:TextBox ID="SSN" runat="server"/>
<asp:RegularExpressionValidator ID="regexpSSN" runat="server"
ErrorMessage="Incorrect SSN Number" ControlToValidate="SSN"
ValidationExpression="^\d{3}-\d{2}-\d{4}$" /> </form>