开发者

How to determine if a SQL query is a SELECT?

Morning SO.

EDIT

I w开发者_开发百科ould 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 :)


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.


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.


if query starts by word "select" (case insensitive) then it's a select query

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜