Parameterize WHERE Clause in Query

Question

Environment:

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

Hi

Could I parameterize where clause in 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.

For example,

• User inputs "[CookingTime] < 30 and [Cost] < 20"
• Application creates query "select [RecipeID] from [Recipes] where [CookingTime] < 30 and [Cost] < 20" and executes in SQL Server.
• Application returns result to user.

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

Thanks in advance.

Solution 1

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

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.

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");
    }
}

Solution 2

This is how it can be done

string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
    + "WHERE CustomerID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@ID", SqlDbType.Int);
    command.Parameters["@ID"].Value = customerID;

    // Use AddWithValue to assign Demographics. 
    // SQL Server will implicitly convert strings into XML.
    command.Parameters.AddWithValue("@demographics", demoXml);

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}