3

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

Michael Commons
  • 773
  • 2
  • 9
  • 28
  • Replace `$` with `SELECT.*FROM` (as is shown [here](https://stackoverflow.com/a/4389666/3832970)) – Wiktor Stribiżew Sep 20 '18 at 07:54
  • 4
    Honestly, a better way to approach solving the problem of only allowing the user to do SELECT statements is to create a user in the sql server that [only has SELECT permissions](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authorization-and-permissions-in-sql-server). – Scott Chamberlain Sep 20 '18 at 07:58
  • Possible duplicate of [Regular expression to match common SQL syntax?](https://stackoverflow.com/questions/139926/regular-expression-to-match-common-sql-syntax) – Liam Sep 20 '18 at 08:01
  • 1
    Don't do this. It's a bad idea. – Liam Sep 20 '18 at 08:01
  • 1
    _each user will have their own DB_ ... oh dear. You should ask another question about that as well :-) – Tim Biegeleisen Sep 20 '18 at 08:06
  • *There's no risk* The risk is you **think** this will stop doing SQL injection. [It won't](https://stackoverflow.com/questions/52420198/validate-sql-query-with-regular-expression#comment91783789_52420241) and you can't assume that this will. The illusion of this being "safe" is more dangerous than knowing that it's not. The correct solution here is to not inject SQL into your code and use parameterised queries. – Liam Sep 20 '18 at 08:11
  • About your update, when a professional contractor asks you *"A client has asked me to build and install a custom shelving system. I'm at the point where I need to nail it, but I'm not sure what to use to pound the nails in. Should I use an old shoe or a glass bottle?"* you should answer "[neither, your approach is wrong and you should be using a hammer to do this kind of work.](https://weblogs.asp.net/alex_papadimoulis/408925)" – Scott Chamberlain Sep 20 '18 at 08:12
  • So is the new requirement that the query has to contain one of two possible `WHERE` clauses? – Tim Biegeleisen Sep 20 '18 at 08:46
  • No , what i want right now is like : ^(?=.*SELECT.*FROM)(?!.*(?:CREATE |DROP |UPDATE |INSERT |ALTER |DELETE |ATTACH |DETACH )).*$ with one " " space after each word. How do i manage to look for that? – Michael Commons Sep 20 '18 at 09:49

2 Answers2

4

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);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I know what are you meaning but this is what i actually want. Thanks a lot! Regarding your answer, that regex is case sensitive, how can i remove that? – Michael Commons Sep 20 '18 at 07:57
  • Thanks! I need to wait 10 min before i can accept an answer. – Michael Commons Sep 20 '18 at 08:01
  • 4
    `declare @sql varchar(max); set @sql = 'DRO'; set @sql = @sql + "P TABLE SomeImportantTable"; sp_executesql @sql` just sayin. – Scott Chamberlain Sep 20 '18 at 08:03
  • @ScottChamberlain I gave the caveat that, if being done from a C# application, statements should be used. In that case, the user wouldn't even be able to choose with _statement_ gets executed, beyond also not being able to inject anything into a query. – Tim Biegeleisen Sep 20 '18 at 08:04
  • 1
    @TimBiegeleisen the comment was more to reinforce your warning to the OP, not to disagree with your answer. – Scott Chamberlain Sep 20 '18 at 08:06
0
const regex = /(\s*([\0\b\'\"\n\r\t\%\_\\]*\s*(((select\s+\S.*\s+from\s+\S+)|(insert\s+into\s+\S+)|(update\s+\S+\s+set\s+\S+)|(delete\s+from\s+\S+)|(((drop)|(create)|(alter)|(backup))\s+((table)|(index)|(function)|(PROCEDURE)|(ROUTINE)|(SCHEMA)|(TRIGGER)|(USER)|(VIEW))\s+\S+)|(truncate\s+table\s+\S+)|(exec\s+)|(\/\*)|(--)))(\s*[\;]\s*)*)+)/i;

This regex check if input contains SQL query. https://regex101.com/r/XxO2J3/5

Adriaan
  • 17,741
  • 7
  • 42
  • 75
Mandeep Kumar Sharma
  • 1
  • 1