Safe dynamic column name in dynamic LINQ

Question

I'm trying to create a dynamic WHERE clause with LINQ. I have a working example but I'm worried that it's not safe from SQL injection.

The following LINQ code:

var oQuery = _db.People.Where("FirstName.Contains(@0)", "kev");

produces the following SQL:

SELECT 
[Extent1].[FirstName] AS [[FirstName], 
[Extent1].[LastName] AS [[LastName], 
WHERE [Extent1].[[FirstName] LIKE '%kev%'

This works great, but now I want to use a dynamic column name as well. So I was thinking I would do the following:

var oQuery = _db.People.Where("@0.Contains(@1)", strSelectedColumn,"kev");

But this produces the following SQL:

  SELECT 
    [Extent1].[FirstName] AS [[FirstName], 
    [Extent1].[LastName] AS [[LastName], 
    WHERE N'FirstName' LIKE N'%kev%'}

which obviously is wrong and gives 0 rows as result because he is comparing 2 strings. By using the params LINQ will probably just inject the params as string when the query is build and not use the effective column name during the build.

The solution is to just use the following LINQ Query:

var oQuery = _db.People.Where(strSelectedColumn + ".Contains(@0)", "kev");

But this result in possible unsafe SQL which can be used to inject SQL.

How can I use my dynamic LINQ columns and still get safe code?

Soloist

How do you offer field names from which the user can choose? – Longwinded

They can't really select dynamically, I use it to filter columns in a grid and I would like to re-use the grid on different pages. – Soloist

Answer 1

This line

var oQuery = _db.People.Where(strSelectedColumn + ".Contains(@0)", "kev");

generate safe SQL code, because before generating SQL query dynamic linq parse string expression and create expression trees. So if in strSelectedColumn not valid column then dynamic linq raise parse exception before generate sql query.

when you use this

var oQuery = _db.People.Where("@0.Contains(@1)", strSelectedColumn,"kev");

you get

WHERE N'FirstName' LIKE N'%kev%'

because you don't check value of field, you try check value of string parameters.

Swab

Do you have any source or link on how LINQ valids that it's a valid column? If they do something like IF exists... with the string then you could still possibility use unsafe SQL – Soloist

@KevinCloet, you can see in source DynamicLinq all that concerns parsing – Swab

@KevinCloet, in a nutshell, exception raise on creating expression trees, when try get property from class where this property absent – Swab

Answer 2

Column names generally consist of nothing but letters, so you can apply "dumb" sanitization on the user input:

// user input: "abc';evil statement here"

strSelectedColumn = new string(strSelectedColumn.Where(c => char.IsLetter(c)).ToArray());
// abcevilstatementhere

var oQuery = _db.People.Where(strSelectedColumn + ".Contains(@0)", "kev");

Abshier