Secure Your ASP.NET Application from a SQL Injection Attack

What is a SQL Injection Attack?

A SQL Injection Attack is when an attacker is able to execute potentially malicious SQL commands by putting SQL queries into web form input or the query string of a page request. Input forms where user or query string input directly affects the building of dynamic SQL queries or stored procedure input parameters are vulnerable to such an attack. A common scenario is as follows:

• A web application has a login page through which access to the application is controlled. The login page requires a login and password to be provided.
• The input from the login page is used to build a dynamic SQL statement or as direct input to a stored procedure call. The following code is an example of what could be used to build the query:

  System.Text.StringBuilder query =
             new System.Text.StringBuilder(
   "SELECT * from Users WHERE login = '")
   .Append(txtLogin.Text).Append("' AND password='")
   .Append(txtPassword.Text).Append("'");
  

• The attacker enters input such as "' or '1'='1" for the login and the password.
• The resulting SQL dynamic statement becomes something similar to: "SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'".
• The query or stored procedure is then executed to compare the inputted credentials with those persisted in the database.
• The query is executed against the database and the attacker is incorrectly granted access to the web application because the SQL command was altered through the injected SQL statement.

Knowing that there is a relatively good chance the application is going to take the input and execute a search to validate it, an attacker is able to enter a partial SQL string that will cause the query to return all users and grant them access to the application.

What could someone do to my application?

The amount of damage an attacker could do is different for each environment. It mainly depends upon the security privileges under which your application is accessing the database. If the user account has administrator or some elevated privileges, then the attacker could do pretty much whatever they wanted to the application database tables, including adding, deleting, or updating data or even potentially dropping tables altogether.

How do I prevent it?

The good news is that preventing your ASP.NET application from being susceptible to a SQL Injection Attack is a relatively simple thing to do. You must filter all user input prior to using it in a query statement. The filtering can take on many forms.

• If you are using dynamically built queries, then employ the following techniques:
  
  
  • Delimit single quotes by replacing any instance of a single quote with two single quotes which prevents the attacker from changing the SQL command. Using the example from above, "SELECT * from Users WHERE login = ''' or ''1''=''1' AND password = ''' or ''1''=''1'" has a different result than "SELECT * from Users WHERE login = '' or '1'='1' AND password = '' or '1'='1'".
    
  • Remove hyphens from user input to prevent the attacker from constructing a query similar to: SELECT * from Users WHERE login = 'mas' -- AND password ='' that would result in the second half of the query being commented out and ignored. This would allow an attacker that knows a valid user login to gain access without knowing the user's password.
    
  • Limit the database permissions granted to the user account under which the query will be executing. Use different user accounts for selecting, inserting, updating, and deleting data. By separating the actions that can be performed by different accounts you eliminate the possibility that an insert, update, or delete statement could be executed in place of a select statement or vice versa.
  
  
• Setup and execute all queries as stored procedures. The way SQL parameters are passed prevents the use of apostrophes and hyphens in a way that would allow an injection attack to occur. In addition, it allows database permissions to be restricted to only allow specific procedures to be executed. All user input must then fit into the context of the procedure being called and it is less likely an injection attack could occur.
  
  
• Limit the length of the form or query string input. If your login is 10 characters long, then make sure you don't allow more characters than that to be input for the value. This will make it more difficult to inject potentially harmful SQL statements into the input.
  
  
• Perform validation on the user input to verify the input is limited to desired values. Data validation should be performed at both the client and the server. The server side validation is required to avoid a security weakness exposed by the client side validation. It is possible for an attacker to access and save your source code, modify your validation scripts (or simply remove them), and submit the form to your server with inappropriate data. The only way to be absolutely sure that validation has been performed is to perform validation on the server as well. There are a number of pre-built validation objects such as RegularExpressionValidator that can auto generate the client side script to perform validation, and allow you to hook in a server side method as well. If you don't find one that meets your needs within the palette of available validators, you can create your own using the CustomValidator.
  
  
• Store data such as user logins and passwords in an encrypted format. Encrypt user input for comparison against the data stored in the database. The data is now being compared in a sanitized fashion that has no meaning to the database and prevents the attacker from injecting SQL commands. The System.Web.Security.FormsAuthentication class has a HashPasswordForStoringInConfigFile that is particularly useful in sanitizing user input.
  
  
• Validate the number of rows returned from a query that is retrieving data. If you are expecting to retrieve a single row of data, then throw an error if multiple rows are retrieved.