I know how to execute queries from C# but I want to provide a dropdown list in which people can write a query and it will execute and populate the list.
A problem is that I want to forbid all queries that modify the database in any way. I have not managed to find a way to do this and I did my best with google.
The solution I can think of is that I will scan the query for INSERT, DELETE, UPDATE and only allow SELECT statements. However, I want to be able to allow users to call stored procedures as well. This means I need to get the body of the stored procedure and scan it before I execute it. How do I download a stored procedure then?
If anyone knows a way to only execute read only queries do share please! I have the feeling scanning the text for INSERT, DELETE, UPDATE doesn't prevent SQL injections.
The easiest way to do this might be to offload this job to the database. Just make sure that the database user that will be running the queries has read-access only. Then, any queries that do anything other than SELECT will fail, and you can report that failure back to the users.
If you don't go this route, the complexity becomes quite enormous, since you basically have to be prepared to parse an arbitrary SQL statement, not to mention arbitrary sequences of SQL statements if you allow stored procs to be run.
Even then, take care to ensure that you aren't leaking sensitive data through your queries. Directly input queries from site users can be dangerous if you're not careful. Even if you are, allowing these queries on anything but a specifically constructed sandbox database is a "whoops, I accidentally changed the user's permissions" away from becoming a security nightmare.
Another option is to write a "query creator" page, where users can pick the table and columns they'd like to see. You can then a) only show tables and columns that are appropriate for a given user (possibly based on user roles etc.) and b) generate the SQL yourself, preferably using a parameterized query.
Update: As Yahia points out, if the user has execute privilege (so that they can execute stored procs,) then the permissions of the procedure itself are honoured. Given that, it might be better to not allow arbitrary stored proc execution, but rather offer the users a list of procedures that are known to be safe. That will probably be difficult to maintain and error-prone, though, so disallowing stored procs altogether might be best.