How to format query with multiple optional AND condition in WHERE clause

Question

I have a user-input form which contains multiple user-input fields which needs to be supplied as parameters in Oracle query. As these are optional parameters and query contains "AND" in "WHERE" clause, it needs to declare many query string constant in C# class and in order to pass the parameter in query, add multiple if-else or switch cases. While it seems to work well, but it makes the code hard to manage. Is there a way to handle this situation using Query or Stored Procedure? Don't want to create queries dynamically using string operation which can give SQL vulnerability.

SomeConstant.cs

public const string Query1
public const string Query2
public const string Query3
public const string Query4

ConsumeQuery.cs

If(ConditionTrue){
  Query1;
}
else if(ConditionTrue)
{
  Query3;
}

and so on.......

If you parameterize the SQL queries, you can then create dynamic queries that are immune to injection. — David Bentley, Jul 25 '19 at 14:54
You could maybe use a `List<(bool, string)>` with the conditions and queries and iterate over it -> then take the first one where the condition is true. This will emulate your `if/else` structure. You can also use [`Predicate`s](https://stackoverflow.com/questions/556425/predicate-delegates-in-c-sharp) instead of `bool`s depending on your use case. — Joelius, Jul 25 '19 at 14:57

score 2 · Answer 1 · answered Jul 25 '19 at 15:12

You can dynamically create a parameterized query like this:

var parameters = new Dictionary<string, object>();
parameters["Field1"] = "Value1";
parameters["Field2"] = null;
parameters["Field3"] = 5;

StringBuilder builder = new StringBuilder("SELECT * FROM TABLE WHERE AlwaysUsedCondition=1 ");

SqlCommand cmd = new SqlCommand();

foreach (var parameter in parameters)
{
    if (!string.IsNullOrWhiteSpace(parameter.Value?.ToString()))
    {
        builder.Append(" AND " + parameter.Key + "=@" + parameter.Key);
        cmd.Parameters.Add("@" + parameter.Key, parameter.Value);
    }
}
cmd.CommandText = builder.ToString();

This will not cause any injection vulnerability as all user input is inside a parameter. Just make sure the keys are in your code and cannot be manipulated from the frontend.

How to format query with multiple optional AND condition in WHERE clause

1 Answers1