Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using regular expressions for validating mysql statements

Tags:

java

regex

mysql

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.

like image 685
T. Popović Avatar asked Jul 07 '15 09:07

T. Popović


People also ask

How do you validate expressions in RegEx?

When you create a text question, along with word/character limits, you can validate for Regex pattern matching. To validate a field with a Regex pattern, click the Must match pattern check box.

Does MySQL like use RegEx?

The REGEXP_LIKE() function in MySQL is used for pattern matching. It compares whether the given strings match a regular expression or not. It returns 1 if the strings match the regular expression and return 0 if no match is found.


3 Answers

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.

like image 89
amit Avatar answered Oct 10 '22 03:10

amit


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;.

like image 44
Karol Dowbecki Avatar answered Oct 10 '22 04:10

Karol Dowbecki


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
like image 29
Santanu Sahoo Avatar answered Oct 10 '22 03:10

Santanu Sahoo