How do I prevent malicious SQL injection attacks while allowing benign SQL injection?

Question

I'm writing an ASP.NET MVC4 application which ultimately builds a SQL SELECT statement dynamically to be stored and executed at a later time. The structure of the dynamic SQL is determined by user configuration in a user-friendly manner, with standard checkboxes, dropdowns, and freeform-entry textboxes. It would be simple enough to validate the input and build the SQL string using parameterized queries, except that I need to allow advanced users the ability to enter custom SQL to be injected directly into the SELECT and WHERE clauses. So what techniques can I use to cleanse the custom SQL expressions or otherwise guard against unwanted input from a clever user? I can easily enough parse the string for suspect keywords and blacklist insert/update/delete/etc., but something tells me that's not going to protect me 100%.

I'm happy to provide more details about exactly what I'm doing here, but I'm not sure what other details would be helpful, since I feel like my problem, while probably not common, is pretty generic.

Answer 1

Contrary to other views, it is possible to do this safely (just look at Data Explorer). Here are the four things you can do to make it happen:

Account Security

Sql Server will let you restrict permissions available to the account used to make the connection to the database. Only grant read permissions, and only to the appropriate tables. Then someone could inject malicious code 'til they're blue in the face, but it will fail at the compile step because they don't have enough permissions. That may mean using a different connection string for this access than for other parts of the application.

Note that this is good for sql code, but there are other kinds of injection as well. If you ever show the query on the page (in full or part) back to the user before running it, you should also look out for javascript injection attacks such as cross site scripting.

Query Governor

You also want to defend against denial of service attacks. Sql databases make it easy for these to happen even by accident, simply from constructing an inefficient query. To combat this threat, you should take a look at the query governor feature in Sql Server. Note that tuning this guy is tricky.

Quarantine

The safest course is to also use a dedicated, sanitized reporting database, hosted on a dedicated server. This ensures that no query can impact production, either in terms of performance or a breached server or account. There are features in sql server such as SSIS that you can use to automate populating your reporting DB from production.

Use a Product

One of the things about security is that you never want to find yourself building your own secured system. It's easy to create something that seems to work, passes tests, but is flawed in subtle ways that lead to breaches later. You want to rely on a product from a vendor that does this kind of thing as a core competency, and can provide support and a level of indemnification. Normally I talk about this in terms of authentication systems, but this rule can apply to sql injection defense as well.

In this case, it might be worth checking out Stack Overflow's Data Explorer. This is a tool to allow the construction of arbitrary queries by untrusted users. The project is open source, so you can see for yourself what they've done to ensure safety, or even just fork that project for your own use. It's worth mentioning again that a big part of this tool's safety is that it is intended for use on a dedicated, sanitized database, so it does not exempt you from the other items.

When all is said and done, though, I think the comment to set up some views and provide access via reporting services is probably your best bet.

Answer 2

I think you can only achieve what you need by not providing a direct way to specify SQL.

That doesn't have to be a problem however. Basically you only need a subset of SQL for defining expressions (computed solumns in the SELECT) and predicates (for filtering in the WHERE). This can be handled with a self-made parser which has just enough power to do everything needed, but then "compiles" the expressions into real SQL code, without actually injecting anything directly.

This then validates the syntax and makes sure that no constructs can be fabricated which would allow for any injection. Also, it allows you to properly let the code generator parametrize the queries you generate as needed (for instance, all literals could be passed in as query parameters by your code generator).