可能的SQL注入与用户定义的功能和实体框架？

My ASP.NET MVC 4 application uses MS-SQL user defined functions to do a fulltext search. I followed this post and created following code:

我的ASP.NET MVC 4应用程序使用MS-SQL用户定义的函数进行全文搜索。我按照这篇文章创建了以下代码：

in Model Class:

在模型类中：

if (suchstring.Trim() != "")
{
    //search for each piece separated by space:
    var such = suchstring.Split(' ');
    int index = 0;
    foreach (string teil in such)
    {
         index++;
         if (teil.Trim() != "")
         {
              res = res.Join(db.udf_FirmenSucheMultiple(string.Format("\"{0}*\"", teil), index), l => l.ID, s => s.KEY, (l, s) => l);
         }
    }
}

Mapping function:

映射功能：

[EdmFunction("TQCRMEntities", "udf_AnsprechpartnerFirmaSuche")]
public virtual IQueryable<udf_AnsprechpartnerFirmaSuche_Result> udf_AnsprechpartnerFirmaSucheMultiple(string keywords, int index)
    {
        string param_name = String.Format("k_{0}", index);

        var keywordsParameter = keywords != null ?
            new ObjectParameter(param_name, keywords) :
            new ObjectParameter(param_name, typeof(string));

        return ((IObjectContextAdapter)this).
            ObjectContext.CreateQuery<udf_AnsprechpartnerFirmaSuche_Result>(
            String.Format("[TQCRMEntities].[udf_AnsprechpartnerFirmaSuche](@{0})", param_name), keywordsParameter);
    }

SQL User defined function:

SQL用户定义函数：

create function udf_AnsprechpartnerFirmaSuche
    (@keywords nvarchar(4000))
returns table
as
return (select [KEY], [rank] from containstable(AnsprechpartnerFirma, *, @keywords,     LANGUAGE 1031))

If I try to search for " I get a 500 Server Error (Syntaxerror from the SQLServer).

如果我尝试搜索“我得到500服务器错误（SQLServer中的语法错误）。

My question is if my app is vulnerable to SQL injections and how I should protect against them.

我的问题是，我的应用程序是否容易受到SQL注入，以及我应该如何防范它们。

Is it save to just remove * and " from the input?

是保存只是从输入中删除*和“？

1 个解决方案

#1

From http://msdn.microsoft.com/en-us/library/ms189760.aspx

来自http://msdn.microsoft.com/en-us/library/ms189760.aspx

CONTAINSTABLE is used in the FROM clause of a Transact-SQL SELECT statement and is referenced as if it were a regular table name. .It performs a SQL Server full-text search on full-text indexed columns containing character-based data types.

CONTAINSTABLE在Transact-SQL SELECT语句的FROM子句中使用，并被引用，就像它是常规表名一样。。它对包含基于字符的数据类型的全文索引列执行SQL Server全文搜索。

If you read Quassnoi's answer with regard to searching the full-text index for double quotes:

如果您阅读Quassnoi关于搜索双引号的全文索引的答案：

Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."

标点符号被忽略。因此，CONTAINS（测试，“计算机故障”）匹配一行的值为“我的计算机在哪里？未能找到它将是昂贵的。”

Documentation can be found here.

文档可以在这里找到。

See his answer for an alternative using the LIKE operator.

使用LIKE运算符查看其替代方案的答案。

To answer your questions:

回答你的问题：

My question is if my app is vulnerable to SQL injections and how I should protect against them.

我的问题是，我的应用程序是否容易受到SQL注入，以及我应该如何防范它们。

You are using parameters properly in your UDF. It should be safe from SQL injection.

您正在UDF中正确使用参数。 SQL注入应该是安全的。

Is it save to just remove * and " from the input?

是保存只是从输入中删除*和“？

No. Never try to blacklist characters in an attempt to prevent SQL injection. You will almost certainly fail.

不会。尝试将字符黑名单以防止SQL注入。你几乎肯定会失败。

See OWASP SQL Injection Prevention for details.

有关详细信息，请参阅OWASP SQL注入预防。

#1