在查询中参数化WHERE子句 [英] Parameterize WHERE Clause in Query

问题描述

环境:

• C#
• Visual Studio 2012
• .NET Framework 3.5

嗨

我可以在SQL Server中参数where子句吗?

Could I parameterize where clause in SQL Server?

在我的情况下，一旦输入WHERE子句字符串，应用程序会将其连接到查询的其他部分并在SQL Server中执行，然后返回结果.

In my scenario, once a WHERE clause String is input, application will concatenate it to other part of query and execute in SQL Server then return the result.

例如，

• 用户输入"[烹饪时间]< 30和[费用]< 20"
• 应用程序创建查询从[Recipes]中选择[RecipeID]，其中[CookingTime] <30，[Cost]< 20"并在SQL Server中执行.
• 应用程序将结果返回给用户.

出于安全原因，我想将整个WHERE CLAUSE用作参数.但是我不知道如何实现.

For security reason, I would like to make whole WHERE CLAUSE as parameter. But I have no idea how to achieve.

谢谢.

推荐答案

整个WHERE子句作为参数将以任何方式成为sql注入的受害者.为避免这种情况，您最好:

The whole WHERE clause as parameter will be a victim of sql injection in any way. To prevent this you'd better to:

设置适当的权限.因此，即使在注入sql的情况下，用户也无法访问未授予的任何内容.在这种情况下，@ Dhaval的示例更好，因为封装在存储过程中的动态sql生成需要较少的执行权限.

Setup proper permissions. So even in case of sql injected user can't access anything not granted. In this case sample of @Dhaval is better, because dymanic sql generation incapsulated in stored procedure requires less permissions to execute.

检查语句以进行sql注入.最简单的方法是检查分号，以避免在批处理中出现其他语句.更复杂，更精确的方法是使用t-sql DOM解析器.例如:

Check the statement for sql injection. The simplest way is to check for semicolons in order to avoid another statements in the batch. More complex and more precise way is to use t-sql DOM parser. For example:

using Microsoft.SqlServer.TransactSql.ScriptDom;

TSql110Parser parser = new TSql110Parser(true);
IList<ParseError> errors = null;
var condition = "a > 100; delete from [Recipes]";
var script = parser.Parse(new StringReader("select [RecipeID] from [Recipes] where " + condition), out errors) as TSqlScript;

if (errors.Count > 0)
{
    throw new Exception(errors[0].Message);
}

foreach (var batch in script.Batches)
{
    if (batch.Statements.Count == 1)
    {
        var select = batch.Statements[0] as SelectStatement;
        if (select != null)
        {
            QuerySpecification query = select.QueryExpression as QuerySpecification;
            if (query.WhereClause is BooleanBinaryExpression)
            {
                ...
            }
        }
        else
        {
            throw new Exception("Select statement only allowed");
        }
    }
    else
    {
        throw new Exception("More than one statement detected");
    }
}

这篇关于在查询中参数化WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！