Skip to content

Security Considerations for LINQ to SQL

April 11, 2011

With version 3.5 of the .NET Framework, Microsoft introduced a technique for writing language-independent queries directly in C# (or any .NET language), which are then translated by the Framework into the appropriate syntax for whichever data structure the developer is using.  This allows .NET developers to keep all of the code for a project in one place, rather than dividing it between .NET code in the project and stored procedures or other functions stored on the database server.   It also provides a way for developers to spend more time in the .NET environment, which may be more familiar, and allows the same query to work regardless of the type of database being used.

As great as it is, though, this process, called Language Independent Query, or LINQ. is also creating a new platform for the web developer’s oldest foe, SQL Injection.  SQL Injection is a process by which malicious users attempt to execute code on a SQL Server by carefully formatting querystring or form values.  For example, a web page might present a user with a search field, and then perform a SQL query using the text in the form as a parameter.  With SQL Injection, a user would add some code to the text in the search box so that that code is executed as well when the page runs a search.  For the most part, LINQ queries are protected from SQL Injection because the translation process, from C# to SQL, uses parameterized values.  This provides the same type of protection that Stored Procedures would normally provide.  But there is one rather gaping hole – the LINQ command ExecuteQuery.  This method executes a command string directly on the SQL server, and is therefore vulnerable to SQL Injection.

Interestingly, even this one problem method, ExecuteQuery, does support parameters and can be used to prevent SQL Injection.  I’m going to divert from my usual standards and show a quick line of code:

IEnumerable<User>  oResults = db.ExecuteQuery<User> {
                “select top 10 * from users where state = {0}”,
                “CA”}

 In this example, SQL Injection is avoided because the search string, “CA”, is passed into the query using a parameter (that’s the {0} part).  That parameterized value is preserved in the translation from C# to SQL, and the query is protected.

 So, LINQ to SQL prevents SQL Injection, but only when the developer is careful to always use parameterized data when building a query.  At SNQ, this is exactly the type of detail we take to heart.  I’ve been working lately on an online shopping cart system, and LINQ has already made my development easier and faster.  But often with these new tools come new risks, and as a business owner its more important than ever too keep researching and learning, not just so that I’m aware of what is available, but also so that I can use the tools I have with my clients’ security and data integrity in mind.

Advertisements
2 Comments leave one →
  1. April 13, 2011 7:04 am

    good article, trendy page template, keep up the great work

  2. April 14, 2011 5:06 pm

    Thanks very much – I appreciate the feedback!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: