Validate SQL Query with Regular Expression

Question

I want to validate a string that contains following words: SELECT and FROM but do not contain a group of words like CREATE, DROP, UPDATE etc.

To be more specific, i want to ensure that a user will execute only SELECT query statements on my system.

What Ii've got so far is the following regex:

^(?!.*(CREATE|DROP|UPDATE|INSERT|ALTER|DELETE|ATTACH|DETACH)).*$

but how can i know if the string has SELECT and FROM in the correct order -> SELECT .... FROM .....

More requirements for the regex. I want to regex to be valid if the query is like : 1. SELECT * FROM TABLE WHERE NAME ='ALTER' 2. SELECT * FROM TABLE WHERE FILENAME ='ATTACHMENT' 3. Actually the regex needs be invalid if there is any word from the group: ALTER, DROP, etc with a " "(space) before and after each word

Regarding the first bullet : i'm thinking if the name of someone is 'ALTER JOHN' then the query will be invalid -> which is not true

I appreciate that you guys are telling me that is a bad idea. I agree and i know. There's no risk, each user will have their own DB. The question was regarding the REGEX. Thanks ! Also, the query will run on SQLITE database

---

Thanks in advance

Answer 1

You may add a positive lookahead which checks for the presence of SELECT ... FROM:

^(?=.*SELECT.*FROM)(?!.*(?:CREATE|DROP|UPDATE|INSERT|ALTER|DELETE|ATTACH|DETACH)).*$

While this answers your question, I am worried, because you tagged your question with C#, implying that you are needing to do this from your C# application. In general, you should not ever allow raw SQL code coming in from the outside to execute in your C# code. Instead, always use a prepared statement, where user inputs can be safely sterilized before they run in a query.

If you want a case insensitive match, then use the RegexOptions.IgnoreCase flag when creating your regex:

Regex rgx = new Regex(@"^your pattern$", RegexOptions.IgnoreCase);