How does LINQ-To-SQL prevent SQL-Injections?

Question

I'm currently doing a project with C# and LINQ-TO-SQL. This project has security as a high priority, so obviously I want to prevent SQL-Injections. I googled on the matter, but it's not turning up anything useful. Microsoft's own FAQ told me that Injections would be no problem, because of the way LINQ handles parameters, however seeing the code LINQ produces in a debugger and having read a bit about how LINQ-To-SQL just builds to SQL I'm not sure how this applies.

Does anyone have any literature/linkage that deals with this matter?

score 6 · Answer 1 · answered May 11 '11 at 20:04

6

It is pretty simple, really - the translation never injects variables without parameterising them; so:

var orders = from ord in ctx.Orders
             where ord.CustomerName = name
             select ord;

will become:

SELECT * FROM [dbo].[Orders] WHERE [CustomerName] = @p0

where p0 is a parameter with value taken from your captured name

nothing more, nothing less. But this avoids injection attacks. Contrast to an incorrect accidental:

var sql = "SELECT * FROM [dbo].[Orders] WHERE [CustomerName] = '" + name + "'";

which introduces huge risks. You can of course parameterise the above correctly, too:

var sql = "SELECT * FROM [dbo].[Orders] WHERE [CustomerName] = @name";

(and add a parameter @name with value from name)

answered May 11 '11 at 20:04

Marc Gravell

1,026,079
266
2,566
2,900

How so? Let's say I have an input field containing a String. Somebody enters ";Drop Database" or something similar. This evaluates to `SELECT * FROM [Orders] WHERE [CustomerId] = ;Drop Database`. Don't I have a problem then? – fk2 May 11 '11 at 20:07
1

@fk2 `@p0` ***is a parameter***; it is never evaluated as TSQL; it can be any string content, without any risk – Marc Gravell May 11 '11 at 20:15
@fk2 no it does not evaluate to that, sql parameters are not substituted for their values to build up a sql string to execute. parameters contain data and are not executed as sql. – Ben Robinson May 11 '11 at 20:17
@fk2 , kinda of a blast from the past , but what you said would be evaluated like `SELECT * FROM [Orders] WHERE [CustomerId] = ";Drop Database"` . If the is no `CUstomerId` with id `;Drop Database` , it will return an empty select.. – Sabyc90 Sep 20 '17 at 08:35

score 6 · Accepted Answer · answered May 11 '11 at 20:05

6

Linq to SQL automatically uses SQLParameters. User input is turned into parameter values instead of simply being a concatenated string (which is what allows for SQL injections). This happens serverside, IIRC, so you might just be seeing the client side code. If you want a bit more background and info, you can read the information here.

answered May 11 '11 at 20:05

keyboardP

68,824
13
156
205

Okay, Page 2 of the article cleared my question. Thank you for your answer! – fk2 May 11 '11 at 20:14

score 1 · Answer 3 · answered May 11 '11 at 20:07

1

It uses parmeterized queries. http://www.devx.com/dotnet/Article/34653/1954

answered May 11 '11 at 20:07

Kevin LaBranche

20,908
5
52
76

How does LINQ-To-SQL prevent SQL-Injections?

3 Answers3

Linked