Say that I have a web application that lets users to type in a SQL query (SQL Server 2008+). I want users to be able to run any SELECT query and read whatever is in the database, but I do not want them to be able to insert/update/drop/truncate/exec etc. (in short: only select). Please note that there are no data or even database metadata that are considered confidential in the entire database.
The rules currently in place:
- No ";" character. If it is there, it must be exactly one and at the end of the trimmed query string.
- Query must begin with select (case ignored, white-spaces are part of regex).
- All newlines are removed from the query.
Is this enough? Is there any way to do anything else than just select?
EDIT: Thank you Thomas Stringer for the note about SELECT ... INSERT. This is exactly the information I was after. I will remove all the newlines from the query string, which should help solve this problem. Is there any other way to get around this and be able to modify the data?
NOTE: CTE is not relevant for this case. Lets assume that query has to begin with SELECT.