CODEWITHSUNDEEP
c#sqlentity-frameworksecuritysql-injection
Tolga Evcimen
Tolga Evcimen

Reputation: 7352

Get query from user input

I need to implement a rest endpoint which should accept a query as an input. Like below:

api.com/api/v1/users/(registrationDate eq '2018-07-01') AND ((height gt 160) OR (height lt 68))

There won't be any joins needed for this type of querying. And all the fields of the entity can be included in the query.

First thing which came to my mind is converting this query input directly to where clause of an sql query,

Dictionary<string, string> Operators = new Dictionary<string, string>
{
    { " eq ", " = " },
    { " ne ", " != " },
    { " gt ", " > " },
    { " gte ", " >= " },
    { " lt ", " < " },
    { " lte ", " <= " }
};

public string SQLify<T>(string query)
{
    if (query == null)
    {
        return $"SELECT * FROM [{typeof(T).Name}]";
    }

    foreach (var op in Operators)
    {
        query = query.Replace(op.Key, op.Value);
    }

    return $"SELECT * FROM [{typeof(T).Name}] WHERE ({query})";
}

and using it like so:

query = queryService.SQLify<User>(query);
var users = DbContext.Users.SqlQuery(query);

But as you might guess, I am terribly concerned about the security implications this can have. I actually tried writing few validation methods, checking the nesting of brackets, and checking the mentioned fields are actually members of class T, but I'm sure the security they'll provide will be insufficient.

What is the best approach to go with in this case?

Upvotes: 2

Views: 294

Answers (2)

Egret
Egret

Reputation: 799

You should build the endpoint so that it uses parameterized queries for the parameters, and white list the other elements (tables, operators and columns).

You've already provided a white list for the operators, but rather than replacing the existing string you should build up the SQL from scratch, and discard (or error out) on anything that doesn't match the whitelist.

If there is data authorization (not everyone has access to all data from the endpoint) you need to consider that in the endpoint as well.

Upvotes: 1

Liam Kernighan
Liam Kernighan

Reputation: 2525

Maybe WebAPI with OData endpoints should do the trick.

Upvotes: 1

Related Questions