Eliminate SQL Injection Attacks Painlessly with LINQ

http://www.devx.com/dotnet/Article/34653/0/page/3

LINQ Overview
At its simplest, LINQ adds standard patterns for querying and updating data in any type of data store—from SQL databases to XML documents to .NET objects. When building database-driven applications, the component of LINQ that enables developers to manage relational data as objects in C# or VB is known as "LINQ to SQL," which is considered part of the ADO.NET family of data technologies. When originally introduced in CTP form, LINQ to SQL was known as DLINQ.

LINQ to SQL enables you to treat data in your applications as native objects in the programming language you are using (C# in the downloadable sample ASP.NET applications), abstracting the complexity of relational data management and database connections. In fact, you can display and manipulate database data through LINQ without writing a single SQL statement. At runtime, LINQ to SQL translates queries embedded or "integrated" in your code into SQL, and executes them on the database. LINQ to SQL returns the query results to the application as objects, completely abstracting your interaction with the database and SQL. There is no faster way to eliminate the possibilities of SQL injection in web applications than to eliminate SQL from your application. With LINQ to SQL, you can do that.

There is no faster way to eliminate the possibilities of SQL injection in web applications than to eliminate SQL from your application.
Securing Data Access with LINQ
LINQ to SQL, when used exclusively for data access, eliminates the possibility of SQL injection in your application for one simple reason: every SQL query that LINQ executes on your behalf is parameterized. Any input provided to the query from any source is treated as a literal when LINQ builds the SQL query from your embedded query syntax. Furthermore, LINQ's integration with Visual Studio Orcas assists developers in building valid queries through IntelliSense and compile-time syntax checking. The compiler catches a lot of query misuse that might introduce functional defects or other types of vulnerabilities into your application. In contrast, SQL statements you write are parsed and interpreted on the database only at runtime before you know whether it is correct or not. The only attack vector against LINQ to SQL is for an attacker to try to trick LINQ into forming illegal or unintended SQL. Fortunately, the languages and compilers are designed to protect you from that.


Figure 2. Object Relational Designer: The O/R Designer automatically builds the classes you need for language integrated queries when you drag database tables from Server Explorer to the design surface.
With that in mind, here's how you can implement the customer search example using LINQ to SQL to protect against SQL injection attacks. The first step is to create the object model of the relational data in the database. Visual Studio Orcas includes a new Object Relational Designer (O/R Designer) that enables you to generate the full object model for your database by dragging tables onto the design surface and defining relationships. To build the object model for our Northwind Customers table, you create a LINQ to SQL database file in your application by selecting "Add New Item…" on your project and choosing the "LINQ to SQL File" template, which opens in the O/R Designer. To automatically build the complete object model for the Customers table, select that table in the Server Explorer and drag it on to the O/R Designer design surface, as shown in Figure 2. In this example, the O/R Designer adds a file named Customers.designer.cs that defines the classes you'll use in code rather than writing code to interact directly with the database.

After defining the object model classes for the data in the Customers table, you can query the data directly in code for the customer data search page. The Page_Load method for the LINQ-powered page (LINQtoSQL.aspx.cs), instantiates the CustomersDataContext class created by the O/R Designer, reusing the same connection string used previously in the SQLInjection.aspx page. The LINQ query below retrieves a collection of Customer objects that match my where clause:

  protected void Page_Load(object sender, EventArgs e)
  {
     string connectionString =
        ConfigurationManager.ConnectionStrings
        ["northwndConnectionString1"].ConnectionString;
 
     CustomersDataContext db = new
        CustomersDataContext(connectionString);
 
     GridView1.DataSource =
         from customer in db.Customers
         where customer.CompanyName ==
         txtCompanyName.Text
         orderby customer.CompanyName
         select customer;
 
     GridView1.DataBind();
  }
 

Using LINQ to SQL, if I provide "Ernst Handel" as the Search value, the SQL statement generated by LINQ at runtime and executed on the server looks like this:

  SELECT [t0].[CustomerID], [t0].[CompanyName],
    [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
    [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],
    [t0].[Phone], [t0].[Fax]
  FROM [dbo].[Customers] AS [t0]
  WHERE [t0].[CompanyName] = @p0
  ORDER BY [t0].[CompanyName]}

As you can see, the WHERE clause is parameterized automatically; therefore, it's impervious to attack with conventional SQL injection attacks. No matter what values a user provides as input to the search page, this query is type-safe and will not allow input to execute commands on the server. If you input the attack string used earlier for the SQL injection exploit, the query returns no rows. In fact, the most harm that a user could do with this query is to perform a brute force attack, using the search function to enumerate all the company records in the Customers table by guessing every possible value. But even that only provides the Customers values already exposed on that page, and gives attackers no opportunity to inject commands that provide access to additional tables or data in the database.

LINQ to Security
As the examples have shown, it's easy to introduce SQL injection vulnerabilities into web applications, and easy to fix them with proper diligence. But nothing inherently protects developers from making these simple, yet dangerous mistakes. However, Microsoft's LINQ to SQL technology removes the possibility of SQL injection attacks from database applications by letting developers interact directly with object models generated from relational data rather than directly with the database itself. The LINQ infrastructure built into C# and Visual Basic takes care of formulating legal and safe SQL statements, preventing SQL injection attacks and enabling developers to focus on the programming language most natural to them. Whether you choose to use LINQ to SQL as part of new .NET application development or have the opportunity to retrofit it into the data access of existing production applications, you will be making a choice to build more secure applications.