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

Question

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

The following websites say: Providing data access using LINQ LINQ to SQL, when used exclusively for accessing data, eliminates the possibility of SQL injection in the application for one simple reason: every SQL query that LINQ executes on your behalf is parameterized . Any input provided to a query from any source is considered a literal when LINQ constructs an SQL query from the built-in query syntax.

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

So basically, Microsoft figured out how to prevent SQL injection?

Answer 1

It is not Microsoft that understood this. Almost every language supports parameterized queries.

One of the biggest risks for SQL injection injections comes from simple naive concats strings:

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

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

Other examples of language:

PHP data objects , mysqli :: prepare , Ruby on Rails Parameterized Queries and ActiveRecord

Using LINQ-to-SQL, EntityFramework, NHibernate will help to avoid these types of attacks.

There are other attack classes that you need to know, such as XSS / CSRF attacks. SQL injection is only half the battle. Microsoft has some other built-in infrastructure features for them, such as the 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 for SQL injection is available from the classic asp (although it was much harder to use it then): just do not substitute the values ​​directly into the sql query string.

Instead, you can place a placeholder in sql code for each value, and the network protocol used to communicate with the database supports sending values ​​for each parameter as separate elements from the sql command itself. Then the database gets sql and knows how to build a query execution plan using only placeholders ... before adding data.

So data is data, code is code, and two should never occur. As long as there is no place in your application where the values ​​of the query parameters are substituted directly into the sql code before sending it to the server, you are 100% safe from SQL injections. This approach is available for most databases and in most languages.

Linq-to-sql is just one way (and not the only way) to use this system. He builds queries with placeholders where necessary behind the scenes.

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

Answer 3

LINQ to SQL significantly reduces the capabilities of SQL Injection, but does not completely eliminate it. For example, if you use stored procedures and call spExecuteSQL on a concatenated string inside SQL Stored Proc, you are still exposed to SQL Injection. Of course, this applies regardless of data access technology and shows that even stored procs cannot eliminate the SQL Injection feature.

In addition, LINQ to SQL DataContext offers the ability to pass an SQL Pass-through query as a string that can be embedded. 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 allows you to use the safe version for injection if you use string parameter holders and pass parameters to the overloaded ExecuteQuery version, which accepts a pararray parameter for objects:

 string sql = @"Select ID, LastName, FirstName, WebSite, TimeStamp " + "From dbo.Author " + "Where LastName = {0}"; IEnumerable<Author> authors = context.ExecuteQuery<Author>(sql, searchName); 

Fortunately, if you are standard LINQ methods, you can avoid SQL injection with LINQ to SQL. On the other hand, in the Entity Framework, on the other hand, there are some other potential areas that you can use if you are thinking of going down this route.

Answer 4

SQL Injection is solved at the ADO.NET level. If you use parameters in your commands, then you are safe.