ASP.NET - Preventing SQL Injection Attacks

Consider a simple web application that requires user input in some fields, lets say some search box. Suppose a user types the following string in that textbox:

'; DROP DATABASE pubs -- 

On submit our application executes the following dynamic SQL statement

SqlDataAdapter myCommand = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = '" + OrderNumberTextBox.Text + "'", myConnection);

Or stored procedure:

SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList '" + OrderNumberTextBox.Text + "'", myConnection);

The intention being that the user input would be run as:

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123'

However, the code inserts the user's malicious input and generates the following query:

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = ''; DROP DATABASE pubs --'

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = ''

The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

; DROP DATABASE pubs

Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.

--'

Using stored procedures doesn’t solve the problem either because the generated query would be:

uspGetOrderList ''; DROP DATABASE pubs--'

Or perhaps this was your login page and your query being:

SELECT UserId FROM Users WHERE LoginId = <inputlogin> AND Password = <inputpwd> AND IsActive = 1

Someone could easily login by typing in the following in your login textbox:

' OR 1 = 1; --

Which makes our query:

SELECT UserId FROM Users WHERE LoginId = '' OR 1 = 1; --' AND Password = '' AND IsActive = 1

Viola, the attacker has now successfully logged in to your site using SQL injection attack.

SQL injection can occur, as demonstrated above, when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database. Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:

• Weak input validation.
• Dynamic construction of SQL statements without the use of type-safe parameters.
• Use of over-privileged database logins.

So what can we do to help protect our application from such attacks? To counter SQL injection attacks, we need to:

Constrain and sanitize input data

Check for known good data by validating for type, length, format, and range and using a list of acceptable characters to constrain input. Create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. Using the list of unacceptable characters is impractical because it is very difficult to anticipate all possible variations of bad input.

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. Check my other blog on Validation Application Block for server-side validation.

If in the previous code example, the Order Number value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

<%@ language="C#" %>
<form id="form1" runat="server">
    <asp:TextBox ID="OrderNumberTextBox" runat="server"/>
    <asp:RegularExpressionValidator ID="regexpPO" runat="server" ErrorMessage="Incorrect Order Number" ControlToValidate="OrderNumberTextBox" ValidationExpression="^PO\d{3}-\d{2}$" />
</form>

If the Order Number 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; 

if (Regex.IsMatch(Request.Cookies["OrderNumber"], "^PO\d{3}-\d{2}$"))
{
    // access the database
}
else
{
    // handle the bad input
}

Performing input validation is essential because almost all application-level attacks contain malicious input. You should validate all input, including form fields, query string parameters, and cookies to protect your application against malicious command injection. Assume all input to your Web application is malicious, and make sure that you use server validation for all sources of input. Use client-side validation to reduce round trips to the server and to improve the user experience, but do not rely on it because it is easily bypassed.

Apply ASP.NET request validation during development to identify injection attacks

ASP.NET request validation detects any HTML elements and reserved characters in data posted to the server. This helps prevent users from inserting script into your application. Request validation checks all input data against a hard-coded list of potentially dangerous values. If a match occurs, it throws an exception of type HttpRequestValidationException.

Request validation is enabled by ASP.NET by default. You can see the following default setting in the Machine.config.comments file. 

<pages validateRequest="true" ... />

Confirm that you have not disabled request validation by overriding the default settings in your server's Machine.config file or your application's Web.config file.

You can disable request validation in your Web.config application configuration file by adding a <pages> element with validateRequest="false" or on an individual page by setting ValidateRequest="false" on the @ Pages element.

NOTE: You should disable Request Validation only on the page with a free-format text field that accepts HTML-formatted input.

You can test the effects of request validation. To do this, create an ASP.NET page that disables request validation by setting ValidateRequest="false", as follows:

<%@ Language="C#" ValidateRequest="false" %>
<html>
    <script runat="server">
        void btnSubmit_Click(Object sender, EventArgs e)
        {
            // If ValidateRequest is false, then 'hello' is displayed
            // If ValidateRequest is true, then ASP.NET returns an exception
            Response.Write(txtString.Text);
        }
    </script>
    <body>
        <form id="form1" runat="server">
            <asp:TextBox id="txtString" runat="server" Text="<script>alert('hello');</script>" />
            <asp:Button id="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" />
        </form>
    </body>
</html>

When you run the page, "Hello" is displayed in a message box because the script in txtString is passed through and rendered as client-side script in your browser.

If you set ValidateRequest="true" or remove the ValidateRequest page attribute, ASP.NET request validation rejects the script input and produces an error similar to the following.

A potentially dangerous Request. Form value was detected from the client (txtString="<script>alert('hello..."). NOTE:  Do not rely on ASP.NET request validation. Treat it as an extra precautionary measure in addition to your own input validation.

Constrain input by using validator controls

To constrain input, use server-side input validation. Do not rely on client-side validation because it is easily bypassed. Use client-side validation in addition to server-side validation to reduce round trips to the server and to improve the user experience. Validate length, range, format and type. Make sure that any input meets your guidelines for known good input.

Use the ASP.NET validator controls to constrain form field input received through server controls. For other sources of input data, such as query strings, cookies, and HTTP headers, constrain input by using the Regex class from the System.Text.RegularExpressions namespace. Or you can use the Enterprise Library Validation Application Block to check for input validation. The Validation Application Block can not be used for input validation but it can also validate your business objects. See my other blog on Validation Application Block.

Encode unsafe output

If your application needs to accept a range of HTML elements—for example through a rich text input field such as a comments field—turn off ASP.NET request validation and create a filter that allows only the HTML elements that you want your application to accept. A common practice is to restrict formatting to safe HTML elements such as <b> (bold) and <i> (italic). Before writing the data, HTML-encode it. This makes any malicious script safe by causing it to be handled as text, not as executable code.

The HtmlEncode method replaces characters that have special meaning in HTML to HTML variables that represent those characters. For example, < is replaced with &lt; and " is replaced with &quot;. Encoded data does not cause the browser to execute code. Instead, the data is rendered as harmless text, and the tags are not interpreted as HTML.

The following page disables ASP.NET request validation by setting ValidateRequest="false". It HTML-encodes the input and selectively allows the <b> and <i> HTML elements to support simple text formatting.

<%@ Page Language="C#" ValidateRequest="false"%>  <script runat="server">  void submitBtn_Click(object sender, EventArgs e) { // Encode the string input  StringBuilder sb = new StringBuilder( HttpUtility.HtmlEncode(htmlInputTxt.Text)); // Selectively allow and <i>  sb.Replace("<b>", "<b>"); sb.Replace("</b>", ""); sb.Replace("<i>", "<i>"); sb.Replace("</i>", ""); Response.Write(sb.ToString()); } </script> <html>  <body>  <form id="form1" runat="server">  <asp:TextBox ID="htmlInputTxt" Runat="server" TextMode="MultiLine" Width="318px" Height="168px" />  <asp:Button ID="submitBtn" Runat="server" Text="Submit" OnClick="submitBtn_Click" />  </form>  </body> </html>

Use type-safe SQL parameters for data access

Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. You can use these parameters with stored procedures or dynamically constructed SQL command strings.

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.

The following code shows how to use SqlParameterCollection when calling a stored procedure:

using System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myCommand = new SqlDataAdapter("uspGetOrderList", connection); myCommand.SelectCommand.CommandType = CommandType.StoredProcedure; myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myCommand.Fill(userDataset); }

The @OrderNumber 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.

You should review your application's use of stored procedures because simply using stored procedures with parameters does not necessarily prevent SQL injection. For example, the following parameterized stored procedure has several security vulnerabilities.

CREATE PROCEDURE dbo.uspRunQuery
    @var ntext
AS
    exec sp_executesql @var
GO

The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to:

DROP TABLE ORDERS;

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 System.Data; using System.Data.SqlClient; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet userDataset = new DataSet(); SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = @OrderNumber", connection); myCommand.SelectCommand.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@OrderNumber"].Value = OrderNumberTextBox.Text; myDataAdapter.Fill(userDataset); } 

If you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you can still use parameters if you make sure that parameter names are not repeated i.e. use unique parameter names during SQL text concatenation.

SELECT OrderId, OrderNumber FROM Orders WHERE OrderNumber = 'PO123' using System.Data; using System.Data.SqlClient; using (SqlConnection oConn = new SqlConnection(connectionString)) { SqlDataAdapter oAdapter = 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);", oConn); SqlParameter custIDParm = oAdapter.SelectCommand.Parameters.Add("@custIDParm", SqlDbType.NChar, 5); custIDParm.Value = customerID.Text; SqlParameter countryParm = oAdapter.SelectCommand.Parameters.Add("@countryParm", SqlDbType.NVarChar, 15); countryParm.Value = country.Text; oConn.Open(); DataSet dataSet = new DataSet(); oAdapter.Fill(dataSet); }

Use a least privileged account that has restricted permissions in the database

Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application's login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation.

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.

1. 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$.
2. Grant the new login access to the required database by creating a database user and adding the user to a database role.
3. 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. 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.

Use Character Escaping Techniques

In situations where parameterized SQL cannot be used, consider using character escaping techniques. If you are forced to use dynamic SQL and parameterized SQL cannot be used, 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.

Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless.

private static string GetStringForSQL(string inputSQL)
{
    return inputSQL.Replace("'", "''");
}

Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL. Your first line of defense should always be to use parameterized SQL.

Avoid disclosing database error information

In the event of database errors, make sure you do not disclose detailed error messages to the user. 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. See my other post on Exception Handling - Do's and Dont's.

You can use the <customErrors> element to configure custom, generic error messages that should be returned to the client in the event of an application exception condition.

Make sure that the mode attribute is set to "remoteOnly" in the web.config file as shown in the following example.

<customErrors mode="remoteOnly" />

After installing an ASP.NET application, you can configure the setting to point to your custom error page as shown in the following example.

<customErrors mode="On" defaultRedirect="YourErrorPage.htm" />

Conclusion

The above list is just some points found on MSDN on how you can make your site more secure by effectively preventing SQL injection attacks. You should always be reviewing your code to find these or other security vulnerabilities; remember all type of attacks start with some input, and your first line of defense should be input validation using both client-side and server-side validation.

Original Author

Original article written by Misbah Arefin