5

I am writing a program in java. In a dialog a user need to input MySQL SELECT statement. Program must to validate the statement an continue to run. My question is: Is there a way and how to validate the statement with regular expressions. I need "only" regular expression pattern. Thanks.

T. Popović
  • 245
  • 3
  • 12
  • Check [Php Regex to find if string is mysql select statement](http://stackoverflow.com/questions/25092364/php-regex-to-find-if-string-is-mysql-select-statement) post. It will be a good start. However, I do not believe you can use regex to check every SELECT statement (at least with Java regex). – Wiktor Stribiżew Jul 07 '15 at 09:43
  • The syntax is very well [specified here](https://dev.mysql.com/doc/refman/5.6/en/select.html). Converting it as a regular expression should be strightforward (albeit somewhat tedious). Where are you stuck? – RandomSeed Jul 07 '15 at 11:28
  • Don't forget the lexical parts -- strings, escapes in strings, embedded commends, end-of-line comments, etc. – Rick James Jul 07 '15 at 17:09

4 Answers4

4

Well, maybe for extended regex, but for the original meaning of "regex" that stands for "Regular Expression", for "Regular Language" - no.

Each SELECT statement is:

SELECT x FROM y WHERE z

However, since y itself can be a SELECT statement, this is at least as hard as the language of balanced parenthesis, which is irregular.

amit
  • 175,853
  • 27
  • 231
  • 333
3

If your objective is to allow only SELECT statements than grant only this operation to database user that opens the database connection. After that just handle the SQLException.

You really don't want to write and maintain validation code by hand because there are too many things to remember. For example if your JDBC connection is using allowMultiQueries=true parameter then one can execute multiple statements within String like SELECT * FROM table; DROP TABLE table;.

Karol Dowbecki
  • 43,645
  • 9
  • 78
  • 111
2

If it is SELECT statement then it should start with SELECT. Below code is to match anything which starts with SELECT.

String sa = "THIS SELECT * from table;";
System.out.println(sa.matches("(?i)^select .*")); //FALSE as the input string is not valid select statement
sa = "SELECT * from table;";
System.out.println(sa.matches("(?i)^select .*")); //TRUE as the input string is  valid select statement
Santanu Sahoo
  • 1,137
  • 11
  • 29
  • This is way too simplistic (and wrong). It even fails for `sa = "SELECT * form table;";` (note form instead of from) – amit Jul 07 '15 at 09:53
2

That's no place for a regex usage.

Elegant way to solve this is using BNF for Java as validator of your select statements. If you will feed it SQL grammar, like this one sql-99.bnf, you will have your statement validator in no time and for free.

Daniel Kec
  • 529
  • 2
  • 8