Does LINQ To SQL C# eliminate any possibility of a SQL injection attack?

Question

I have a fom for my employeer using LINQ to SQL for CRUD. I can just rely on LINQ to SQL?

The following websites say: 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.

Sources: http://msdn.microsoft.com/en-us/library/bb386929.aspx

So basically microsoft has figured out how to prevent sql injection?

Answer 1

It's not Microsoft that has figured it out. Pretty much every language supports parametrized queries.

One of the biggest risks of SQL injection attacks come from simple naive string concats:

string query = "SELECT * FROM Users WHERE UserName = " + userName + " AND....

An article by Jeff Atwood: http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

Other language examples:

PHP Data Objects, mysqli::prepare, Ruby on Rails Parameterized Queries and ActiveRecord

The use of LINQ-to-SQL, EntityFramework, NHibernate will all help guard against these types of attacks.

There are other classes of attacks that you need to be aware of though, like XSS/CSRF attacks. SQL Injection is only half the battle. Microsoft has some other built in framework features for these, such as AntiForgeryToken in ASP.NET MVC.

http://blog.stevensanderson.com/2008/09/01/prevent-cross-site-request-forgery-csrf-using-aspnet-mvcs-antiforgerytoken-helper/

Answer 2

The solution to sql injection has been available since classic asp (though it was a lot more cumbersome to use back then): just don't substitute values directly into your sql query string.

Instead, you can put a placeholder in the sql code for each value, and the network protocol used to communicate with the database supports sending the values for each parameter as separate items from the sql command itself. The database then receives the sql and knows how to build an execution plan for the query using only the placeholders... before adding the data.

In this way, data is data, code is code, and never the twain shall meet. As long as there is no place in your app where query parameter values are substituted directly into sql code before sending it to the server, you are 100% safe from sql injection attacks. This approach is available for most databases and in most languages.

Linq-to-sql just happens to be one way (not the only way) to make use of this system. It builds queries with placeholders where needed behind the scenes.

If you are responsible for any code anywhere that uses string concatenation to put your sql code together, stop what you're doing right now and go fix it to use some form of parameterized queries.

Answer 3

LINQ to SQL greatly reduces the possibility of SQL Injection, but doesn't completely eliminate it. For example, if you are using Stored Procedures and call spExecuteSQL passing in a concatenated string inside of the SQL Stored Proc, you are still subject to SQL Injection. Of course, this applies regardless of the data access technology and shows that even stored procs can't eliminate the possibility of SQL Injection.

In addition, the LINQ to SQL DataContext does offer the ability to pass a SQL Pass-through query as a string which can be injectable. For example, the following returns all rows in the Authors table:

string searchName = "Good' OR ''='";
TypedDataContext context = this;

string sql = @"Select ID, LastName, FirstName, WebSite, TimeStamp    " +
          "From dbo.Author " +
          "Where LastName = '" + searchName + "'";

IEnumerable<Author> authors = context.ExecuteQuery<Author>(sql);

LINQ to SQL does allow for a Injection safe version if you use string parameter place holders and pass the parameters into the overloaded version of ExecuteQuery accepting a paramarray of objects:

string sql = @"Select ID, LastName, FirstName, WebSite, TimeStamp    " +
          "From dbo.Author " +
          "Where LastName = {0}";

IEnumerable<Author> authors = context.ExecuteQuery<Author>(sql, searchName);

Thankfully, if you're standard LINQ methods, you're safe from SQL Injection with LINQ to SQL. The Entity Framework on the other hand actually does have some other potential injectionable areas if you're thinking of going down that route.