Check if a statement will update records before executing it

Question

I have a system that takes a random SQL statement and returns the result of the execution. I would like to limit the system to execute read-only statements and reject any statements that would update/insert/delete etc. any records.

Is there a way to ask the database if a statement is a read-only statement without actually executing it?

Notes:

• The system is only accessible to a few "trusted" users who have full access to the database anyway - so what I want is an "oops" safeguard - I understand that this is not the way to handle SQL injection attacks etc.

• Regex validation is probably too complicated, because "forbidden words" are ok if they are in comments or column names for example.
  This should be a valid input:

  SELECT
    a AS update_value,
    'INSERT FOR FUN' AS q
  FROM t --comment: should we delete stuff?
  /*why not as long as you don't drop database*/
  

  But this should be rejected:

  UPDATE t SET a = 1
  

• I'd rather not create a separate read-only user

Answer 1

Old thread, but there is a better solution which seems to be so elegant I wanted to add it anyway: instead of wrapping your SQL-Statement in a select(), wrap it in a "begin transaction" and "rollback". You still get all the results from your query, but whatever (possibly evil things) happen, they will not persist

Answer 2

Not very elegant, and certainly not bullet proof - but what we implemented once was a SELECT wrapper around the suspicious SQL Statement.

So if your User types

UPDATE t SET a = 1

Then we wrap this in:

SELECT TOP 1 * FROM 
(
UPDATE t SET a = 1
)

Which will generally produce an error if anything inside the SELECT wrapper is an update,delete or an insert (And more importantly DROP TABLE, TRUNCATE, etc). Then you avoid actually executing any SQL statements that modify a table to a large degree. But if the SQL Statement is a genuine SELECT statement, then there is no error, and 1 row is returned. (Obviously you can modify or remove the TOP 1 to suit)

Not very useful with Stored Procedure (etc) though, and has many limitations, but in our context it was the perfect solution. (We had given Users the ability to type SQL free hand in our application)

Answer 3

I believe that there are two solutions for this:

1. You may use parsers such sqlparser or ANTLR4 to parse the input. Then it is easy to decide what kind of command you have. SQLParser has examples in samples that shows how to categorize SQL commands. If you use ANTLR4 than you get a parse tree after successful parsing using a gramar. If you have a SELECT statement then you have to have the select_statement gramar rule in the parse tree.

2. You may use SET SHOWPLAN_XML ON to get the estimated plan in XML. In the XML returned by the SQL Server you have StatementType attribute and it should have the SELECT value in your case.