Sanitize dynamic SQL query created by user. only SELECT allowed (no INSERT,UPDATE,DELETE,DROP, EXEC, etc...)

Question

I am developping an ASP2.0 website with a Microsoft SQL server 2005 Database. I need to implement a functionality which allows users to create a select query (nothing too complex) so that the website displays a page with the result set of the query. My question is how can I sanitize the query to make sure there is no insert/update/delete/drop or other nefarious injection.

also, I need to encapsulate the query in a "with" clause so that I can add an identity column to the result set. (I need that to display the results correctly on the page)

my CSharp code to format the query looks like this (simplified a little):

string query = txtQuery.Text;
query = query.ToLower();
query = query.Trim();
int orderByIndex = query.LastIndexOf("order by");
string orderBy = "";
if (orderByIndex != -1)
{
    orderBy = query.Substring(orderByIndex);
    query = query.Replace(orderBy, "");
}
query = "with query as (" + query + ") select row_number() over(order by (select 0)) as rowID, * from query " + orderBy;

I want to create a stored procedure to execute the query. I was thinking of something like this:

CREATE PROCEDURE usp_execute_query
@sql nvarchar(max)
with execute as 'RestrictedUser'
as
begin
    exec sp_executesql @sql
end

with RestrictedUser looking like this:

CREATE USER RestrictedUser WITHOUT LOGIN
EXEC sp_addrolemember db_datareader, RestrictedUser

My questions are: Is there a way to check the roles of RestrictedUser within the stored procedure to make sure they haven't been tampered? And raiserror if they have. Do you think this whole thing is the right way to go? Any suggestion?

Answer 1

Danger Will Robinson! Any time you're allowing a user to pass arbitrary SQL, you're running into all sorts of security issues. The odds of you plugging every hole are slim. Plus, the ability to "sanitize" a query is predicated on your ability to parse the query. It is possible to parse SQL yourself, but it's not trivial by any stretch of the imagination. Also, because this is a web app, you're pitting your skills against a lot of people with probably more experience performing sql injection attacks.

Creating the user with very limited permissions is a good thing (and likely your best shot). However, you will need to be fastidious about what they have access to (system stored procs, system views, etc). In other words, db_datareader is not limited enough. You'll want to create an entirely new rule and only given them permission to very specific items.

Keep in mind that even though you might succeed in limiting what pieces of data the user (aka hacker in this scenario) can see, you're still vulnerable to DOS (Denial of Service) attacks.