Preventing SQL Injection Attacks with Visual Studio Team System 2010

There are numerous articles on the web that describe the perils of SQL Injection attacks and warn developers about the fact that "All Input is Evil!". If you are interested in reading more about what a SQL Injection attack is, Wikipedia has a pretty good introduction to the subject. There are a number of recommendations on how to prevent SQL Injection attacks including sanitizing all user input, securing the database, etc. However, the onus is still on the developer to find out if his application is susceptible to SQL Injection attacks.

With Visual Studio Team System 2010, we've introduced a new Code Analysis feature that detects and warns developers about SQL Injection attacks, thus relieving the developer from some of the burden. But more importantly, this feature helps developers write more secure code to begin with.

A SQL Injection example

For this example, I’m going to use the good old Northwind sample database and some ASP.NET code to walk through the scenario. In the method below, the SQL string strSQL is dynamically constructed to retrieve products from the Northwind database. Furthermore, the user can filter out products by typing in some text that is used as a filter (txtFilter). The reason why this method is susceptible to a SQL Injection attack is because strSQL is created by using values directly entered by the user which are not sanitized in any way.

 private DataView CreateDataView()
{
    string connString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
    string strSQL = "SELECT ProductId, ProductName, QuantityPerUnit, UnitPrice FROM Products";
    
    if (txtFilter.Text.Length > 0) 
    {
        strSQL += " WHERE ProductName LIKE '" + txtFilter.Text + "'";
    }

    SqlConnection connection  = new SqlConnection(connString);
    SqlDataAdapter adapter = new SqlDataAdapter(strSQL, connection);
    DataTable dtProducts = new DataTable();

    adapter.Fill(dtProducts);

    return dtProducts.DefaultView;
}

Because SQL Server allows you to concatenate several SQL statements using a semicolon or space, I can append my own SQL statement to the query above by simply entering my statement in the txtFilter textbox, as shown below:

 '; UPDATE Products SET UnitPrice = 0.01 WHERE ProductId = 1--

Using the attack above, I can set the UnitPrice for the product with ProductID = 1 to $0.01, purchase a few units of the product without anyone finding out (not for a while anyway). There are many other vectors of attack here but this example is a simple demonstration of how devastating a SQL Injection attack can be.

Luckily, with Team System 2010, you can prevent such attacks by using the Code Analysis security rules. In order to take advantage of this fetaure, the first step is to configure Code Analysis to check for SQL Injection vulnerabilities in your application. To do that, select the "Configure Code Analysis for <project>" command from the Analyze menu, as shown here.

Once you've selected the Configure command, the Project Properties window opens with the "Code Analysis" tab selected. Using the Rule Set drop down, select the Microsoft Security Rules. This will tell Visual Studio to run Code Analysis on your application, checking for security vulnerabilities. To see which vulnerabilities Code Analysis is checking for, click the [Open] button.

After clicking the [Open] button, the Rule Set editor is opened. Other than security vulnerabilities, you can configure Code Analysis to detect many other issues in your application such as performance, correctness, reliability, etc. In the Rule Set editor window, expand the Microsoft.Security section and look for the rule where the ID is CA2100. This is the rule that Code Analysis uses to detect potential SQL Injection attacks in your code. What's also nice is that when you select the rule, the lower pane of the window shows you additional help on the rule, including sample code and some guidance.

The next step, after configuring Code Analysis, is to run it. From the Analyze menu, select the "Run Code Analysis on <project>" command. This command will run all the rules that have been enabled for your application and report any issues in the Error List.

In the Error List, you can see that Code Analysis has detected the SQL Injection vulnerability in our code. Furthermore, you can double click the error to navigate to the section of your code which contains this vulnerability.

If you have any feedback on this feature, I would love to hear about it. In a future post, I will delve into how Code Analysis detects these types of vulnerabilities.

Habib Heydarian.