At runtime add columns in raw SQL from C#

Question

I have an SQL query in which the WHERE clause will be decided at runtime. It can be any combination of columns with OR and AND operators. Stored procedures are restricted in this database, so raw SQL will be used.

Get ris OData from postman can be any one of the below.

eq  Equals          /Employees?$filter=Name eq 'John'
ne  Does not equal  /Employees?$filter=Name ne 'John'
gt  Greater than    /Employees?$filter=Age gt 15
lt  Less than       /Employees?$filter=Age lt 15
ge  Greater than or equal to /Employees?$filter=Age ge 15
and /Employees?$filter=Name eq 'John' and Age gt 65
or  /Employees?$filter=Age gt 65 or Age lt 11
not /Employees?$filter=not(Name eq 'John')

Then a SqlDataReader will be used to fetch data from the database after the query is constructed and executed.

public ActionResult Get(Columns columns =null)
{
    StringBuilder str = new StringBuilder();
    str.Append("select firstNmae,lastname,gender,city from dbo.customer where ");

    if (columns != null)
    {
        if (columns.firstName != null || columns.firstName != "")
        {
            str.Append(String.Format("firstname = '{0}' ", columns.Name));
        }

        if (columns.lastName != null || columns.lastName != "")
        {
            str.Append(String.Format("lastName = '{0}' ", columns.lastName));
        }
    }
}

How can I include the above with this SQL?

select firstName, lastname, gender, city 
from dbo.customer 
where + columns which comes at runtime 1 or more with or and conditions

Answer 1

When building SQL like this, you have to be very careful about how you do it. It's stupid-easy to accidently end up wide open to SQL injection issues.

To do it safely (and this is one of those areas that's too important to do wrong, even in learning/practice/proof-of-concept/private work), there are two things you MUST do:

1. Pre-validate column names (and operators) vs what's actually in the database. You can do this by running quick (safe) SQL statements with the sys.columns view, or by including the set of allowed columns directly in your application. Then, provide the text for those items yourself, rather than directly from the user input.
2. Use query parameters instead of string concatenation for the filter data.

In this way, no user-provided text ever becomes part of the final SQL statement. This is important!

What I will NOT do here is speak to how you parse the columns conditions. This is not a code writing service, and the grammar described here will quickly get waaaaay beyond what you expect, akin to a fully-functional SQL parser.

But once that is done, you need to end up with something like this:

public ActionResult Get(Columns columns =null)
{
    StringBuilder str = new StringBuilder("select firstNmae,lastname,gender,city from dbo.customer where 1=1 ");
    var parameters = new List<SqlParameter>();
    SqlParameter p;

    // Assuming you already extracted your columns details
    //  from the input into separate variables used below
    //  (again: not a code writing service, you'll need to do this part)

    if (NameFilter.Length > 0)
    {
        str.Append(" AND Name = @Name");
        // use actual database types/lengths here
        p = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
        p.Value = firstNameFilter;
        parameters.Add(p);
    }

    if (AgeFilter.Length > 0)
    {
        //simplified
        var opMap = new Dictionary<string, string>() {{"lt", "<"}, {"le","<="},{"eq","="},{"ne","<>"},{"gt",">"},{"ge",">="}};
        var op = opMap[AgeOperator];

        str.Append($" AND Age {op} @Age");
        // use actual database types/lengths here
        p = new SqlParameter("@Age", SqlDbType.Int);
        p.Value = Convert.ToInt32(AgeFilter);
        parameters.Add(p);
    }

    // ...

    using var conn = new SqlConnection("connection string here");
    using var cmd = new SqlCommand(str.ToString(), conn);
    foreach(var parameter in parameters)
    {
        cmd.Parameters.Add(parameter);
    }
    conn.Open();
    using var rdr = cmd.ExecuteReader();    

    // do something with your reader ...

}

Note in the above code, text parsed from the columns input is ONLY ever used via an SqlParameter object's .Value property. All the other text used in the SQL command is pulled from string literals in the code.

To focus in on one of the examples:

if (AgeFilter.Length > 0)
{
    //simplified
    var opMap = new Dictionary<string, string>() {{"lt", "<"}, {"le","<="},{"eq","="},{"ne","<>"},{"gt",">"},{"ge",">="}};
    var op = opMap[AgeOperator];

    str.Append($" AND Age {op} @Age");
    // use actual database types/lengths here
    p = new SqlParameter("@Age", SqlDbType.Int);
    p.Value = Convert.ToInt32(AgeFilter);
    parameters.Add(p);
}

In the above code, after parsing the columns input we determine there is a filter on the Age column via the if() conditional statement.

Inside this if() block we don't just accept the user-provided column name. Instead, we provide the column name separately.

Likewise, we allowed the user to tell us which operator to use, but we did not accept what was provided directly. Rather we transformed that to a known-good value via the opMap dictionary.

Finally, we did accept exactly what the user gave us for the actual age value... but only included it in the query via a parameter, so it remains quarantined from the SQL command statement.

Then we put it all together ourselves, so we control the structure of the result.

This is the only safe way to build dynamic queries.