SqlCommand-class force SELECT only?

Question

Hi,

I'm developing a application that's for SQL-surveillance, it's just a console application that will run each 5min on a bunch of servers. It has a config file with connection string and sql query. The thing is I would like the program to accept SELECT queries only.

I colleague told me he thought I could set something in the SqlCommand-class but I've been googling for a while without any success. Anyone got a clean solution on my problem? I've thought about searching the query for different words but it's to much that can go wrong.

Any suggestions are welcome!

Answer 1

This isn't going to be possible unless your application builds the entire SELECT statement itself.

The issue is that even if you ensure the first word is SELECT there is nothing stopping a malicuous user terminating the command and then issuing an EXEC or UPDATE statement also.

The best solution is to ensure that the user account referenced in your connection string has limited permissions to the SQL objects and thus only SELECT from supported objects will work.

Answer 2

The database table itself can be restricted.
you can revoke all permission but select from the user of your application.
search for REVOKE/GRANT commands for SQL-SERVER.

Answer 3

Granting privileges would be the better way to go, but you can still parse the statement, even if its a series of SQL commands, using something like this:

bool OkToRun = true;
foreach(string s in TheQueryString.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries))
    if (!s.Trim().ToUpper().StartsWith("SELECT "))
        OkToRun = false;