score:1

Accepted answer

If you want to build your own provider, you must know that it is not that easy. Consider things like nested select, nested where, etc. There are great blog posts on this topic.

But you are interested in protecting your database against SQL injection. So if you look at the sample code on this page and the VisitConstant method, that's the place where you run into constants of value type (string, int, etc.) or IQueryable.

Protection against SQL injections is not complicated, you just create new SQLParameter or you call method DbProviderFactory.CreateParameter described here. You will need some collection to store your parameters while you are traversing the expression tree. So the modified code will look like this:

protected override Expression VisitConstant(ConstantExpression c) {
    IQueryable q = c.Value as IQueryable;
    if (q != null) {
        // assume constant nodes w/ IQueryables are table references
        sb.Append("SELECT * FROM ");
        sb.Append(q.ElementType.Name);
    }
    else if (c.Value == null) {
        sb.Append("NULL");
    }
    else {
        switch (Type.GetTypeCode(c.Value.GetType())) {
            case TypeCode.Boolean:
                param = dbProvider.CreateParameter();
                param.Name = "@param" + paramsList.Count;
                param.Value = (((bool)c.Value) ? 1 : 0;
                paramsList.Add(param);
                sb.Append(param.Name);
                break;
            case TypeCode.String:
                param = dbProvider.CreateParameter();
                param.Name = "@param" + paramsList.Count;
                param.Value = c.Value; // you don't have to care about escaping or formatting
                paramsList.Add(param);
                sb.Append(param.Name);
                break;
            ...
            case TypeCode.Object:
                throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));
            default:
                sb.Append(c.Value);
                break;
        }
    }
    return c;
}

So while you are travesing the expression tree, you are building the SQL string and collecting the SQL parameters.

score:2

From the blog post it looks like IQ toolkit (or the initial version of the toolkit) is not safe from SQL injection attacks. But you can verify it by yourself - execute a query, capture the generated SQL and see if there are parameters used.


Related Articles