在动态LINQ中安全的动态列名 [英] Safe dynamic column name in dynamic LINQ

问题描述

我正在尝试使用LINQ创建一个动态的WHERE子句。我有一个工作示例，但我担心SQL注入不安全。

以下LINQ代码：

  var oQuery = _db.People.Where（FirstName.Contains（@ 0），kev）; 
  

生成以下SQL：

  SELECT 
 [Extent1]。[FirstName] AS [[FirstName]，
 [Extent1]。[LastName] AS [[LastName]，
 WHERE [Extent1 ] [[FirstName] LIKE'％kev％'
  

这很好，但现在我想也可以使用动态列名。所以我以为我会执行以下操作：

  var oQuery = _db.People.Where @ 0.Contains（@ 1），strSelectedColumn，kev）; 
  

但这会产生以下SQL：

  SELECT 
 [Extent1]。[FirstName] AS [[FirstName]，
 [Extent1]。[LastName] AS [[LastName]，
 WHERE N'FirstName'LIKE N'％kev％'} 
  

这显然是错误的，给出0行因为他正在比较2个字符串。通过使用参数，LINQ可能会在构建查询时将params作为字符串注入，而在构建期间不会使用有效的列名。

解决方案是仅使用以下LINQ查询：

  var oQuery = _db.People.Where（strSelectedColumn +.Contains （@ 0），kev）; 
  

但是这可能导致可能不安全的SQL，可以用来注入SQL。

如何使用我的动态LINQ列，仍然可以获得安全的代码？

解决方案>

这行

  var oQuery = _db.People.Where（strSelectedColumn +.Contains（@ 0）， 千电子伏）; 
  

生成安全的SQL代码，因为在生成SQL查询之前，动态linq解析字符串表达式并创建表达式树。所以如果在 strSelectedColumn 不是有效列，那么动态linq会在生成sql查询之前引发解析异常。

  var oQuery = _db.People.Where（@ 0.Contains（@ 1），strSelectedColumn，kev） ; 
  

你得到

 code> WHERE N'FirstName'LIKE N'％kev％'
  

因为你不t检查字段的值，尝试检查字符串参数的值。

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?

解决方案

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.

这篇关于在动态LINQ中安全的动态列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！