How to determine if a SQL query is a SELECT?

Question

Morning SO.

EDIT

I would like to do some validation on sql queries to verify that this query is a SELECT and not an UPDATE or a DELETE or an INSERT or any sql weird statement.

I know that the easiest way is to match "^SELECT" BUT :

• a query can start with "(" like

  (SELECT * FROM blah WHERE id > 1 LIMIT 3) UNION (SELECT * ...)

• a query can start with " WITH RECURSIVE "

  WITH RECURSIVE cte AS (SELECT * FROM blah)

I Would like to determine if a SQL query is a SELECT. Well I don't know if there is some weird queries I have to know before writing a regexp.

Any help is appreciated.

EDIT: I want to check if it's a PURE Select query :)

Answer 1

The normal way to handle this is with permissions - you grant the user running the query db_reader permissions in sql server, but not db_writer or anything else. Then you handle the error/exception if the query fails.

Answer 2

You can also make update statemtents that run their own SELECTs inside to find out data to update, or many other ways to embed statements into eachother... Assuming you don't use "SELECT" as data or field-names just run a regex for /\bselect\b/i otherwise you will need a full blown parser.

Edit: also: /\b(insert|update)\b/i invert that to make sure there are none of them in it.