January 21, 2021
Hot Topics:

OP-ED: Why LINQ to SQL is a Better Option than Straight SQL

  • By Paul Kimmel
  • Send Email »
  • More Articles »

Eliminating SQL Injection with LINQ to SQL

To simplify the focus, the Web application sample is shown as a console application in Listing 2. The console application simply shows malicious SQL being injected into a SQL statement. (For the discussions going forward, it doesn't matter how it got there. In the previous section, one way SQL might be injected was demonstrated, so it is enough to know that bad SQL can end up in undesirable places.)

Listing 2: SQL injection simplified for discussion purposes.

Imports System.Data.SqlClient

Module Module1

   Sub Main()
   End Sub

   Sub SqlInjectionToDiscoverOtherDatabase()
      Dim connectionString As String = _
         "Data Source=.\SQLExpress;Initial _
            Catalog=northwind;Integrated Security=True"

      Dim sql As String = _
         "SELECT CustomerID, CompanyName FROM Customers _
            WHERE CustomerID = '{0}'" _
      Dim customerID = _
         "ALFKI' UNION SELECT 'database name' as dummy, " + _
         “name FROM master.sys.databases --"

      Using connection As SqlConnection = _
         New SqlConnection(connectionString)
      Dim command As SqlCommand = _
         New SqlCommand(String.Format(sql, customerID), connection)
      Dim reader As SqlDataReader = command.ExecuteReader

      While (reader.Read())
         Dim i As Integer
         For i = 0 To reader.VisibleFieldCount - 1
      End While

      End Using


   End Sub

End Module

Listing 2 declares a customerID local variable representing the malicious SQL (input by a user). Now, let me talk about LINQ to SQL in general before demonstrating how LINQ to SQL eliminates SQL injection. If you are familiar with LINQ to SQL basics, you can skip the next section.

Understanding LINQ to SQL

LINQ, or Language Integrated Query, is a SQL-like language written with VB or C# code. LINQ to SQL is basically LINQ code that is converted to SQL queries by the LINQ to SQL technology.

To summarize, LINQ to SQL works like this: You define custom entity classes that match tables in your SQL database. The class is tagged with the TableAttribute and properties that represent columns are tagged with the ColumnAttribute. You can use SqlMetal to generate these classes for you. This literally means that only properties in your (or SqlMetal.exe's) custom classes can be returned from SQL and only those properties that are associated with columns in tables by the ColumnAttribute. Then, you define a simple class that inherits from DataContext. The DataContext represents your connection to the database. That's it. You don't use ADO.NET directly. You don't write SQL directly. LINQ to SQL takes care of the plumbing for you.

The net result is that only data matching your entities can get returned, your users are working with compiled code and not dynamic SQL strings, and nothing gets passed to SQL directly. The LINQ to SQL engine is writing your SQL for you.

Understanding How LINQ to SQL Stops SQL Injection

Dynamic SQL is potentially problematic because it's a string of text that gets passed to the SQL engine. LINQ to SQL is compiled code. The SQL is generated by the LINQ to SQL technology.

The next critical key is that LINQ only supports Select queries. There is no Update, Delete, or Insert. This means that a malicious user can only read data. This could be a problem, but I'll show you why it's not.

LINQ is converted to method calls at compile time. This means the executable code is already compiled. A user can't input new LINQ keywords at runtime to compromise your pre-defined queries. That is, a Select can't become a join or a union at runtime.

Mild Caution: A user could input dynamic LINQ at runtime if your application was designed to use the CodeDOM to compile LINQ queries at runtime, but of course, this is a pretty technically advanced thing to do. Many people won't know how to do it and there is almost no reason to include dynamic compilation in business applications.

To recap, LINQ to SQL eliminates injection because the user is dealing with compiled code, not dynamic queries. LINQ queries only perform reads; that is, Select exists but there is no Delete, Update, or Insert. Modifications to underlying data are made through method calls and not queries. Listing 3 shows how you can convert the code in Listing 2 (a console application) to perform the same task using LINQ to SQL.

Page 3 of 4

This article was originally published on August 15, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date