IQueryable LINQ provider and SQL injection?

Question

I'm working on a LINQ provider that uses the IQ Toolkit to tranlate LINQ queries to SQL queries. Are the classes provided by the IQ Toolkit safe from SQL injection attacks? If not, what I have to do to protect against SQL injection attacks, supposing that I'm using the IQ Toolkit and implementing my own LINQ provider. I read the LINQ to SQL uses SqlParameter, but it's still not clear to me what needs to be done with SqlParameter to protect against SQL injection.

score 2 · Answer 1 · answered Sep 19 '11 at 14:27

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.

answered Sep 19 '11 at 14:27

Karel Frajták

4,389
1
23
34

Thanks for your response, but it would help if you could show how to use parameters with the LINQ provider to protect against SQL injection. – T. Webster Sep 19 '11 at 14:32

score 1 · Accepted Answer · answered Sep 19 '11 at 14:55

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.

Thanks, that is quite a code snippet. I take it you have some experience with this. — T. Webster, Sep 19 '11 at 20:13
I tried to create my own LINQ provider, but the task was very difficult... And there were other projects to play with;) — Karel Frajták, Sep 20 '11 at 07:14

IQueryable LINQ provider and SQL injection?

2 Answers2