Regular expression to match common SQL syntax?

Question

I was writing some Unit tests last week for a piece of code that generated some SQL statements.

I was trying to figure out a regex to match SELECT, INSERT and UPDATE syntax so I could verify that my methods were generating valid SQL, and after 3-4 hours of searching and messing around with various regex editors I gave up.

I managed to get partial matches but because a section in quotes can contain any characters it quickly expands to match the whole statement.

Any help would be appreciated, I'm not very good with regular expressions but I'd like to learn more about them.

By the way it's C# RegEx that I'm after.

Clarification

I don't want to need access to a database as this is part of a Unit test and I don't wan't to have to maintain a database to test my code. which may live longer than the project.

Answer 1

Regular expressions can match languages only a finite state automaton can parse, which is very limited, whereas SQL is a syntax. It can be demonstrated you can't validate SQL with a regex. So, you can stop trying.

Answer 2

SQL is a type-2 grammar, it is too powerful to be described by regular expressions. It's the same as if you decided to generate C# code and then validate it without invoking a compiler. Database engine in general is too complex to be easily stubbed.

That said, you may try ANTLR's SQL grammars.

Answer 3

As far as I know this is beyond regex and your getting close to the dark arts of BnF and compilers.

http://savage.net.au/SQL/

Same things happens to people who want to do correct syntax highlighting. You start cramming things into regex and then you end up writing a compiler...

Answer 4

I had the same problem - an approach that would work for all the more standard sql statements would be to spin up an in-memory Sqlite database and issue the query against it, if you get back a "table does not exist" error, then your query parsed properly.

Answer 5

Off the top of my head: Couldn't you pass the generated SQL to a database and use EXPLAIN on them and catch any exceptions which would indicate poorly formed SQL?

Answer 6

Have you tried the lazy selectors. Rather than match as much as possible, they match as little as possible which is probably what you need for quotes.

Answer 7

To validate the queries, just run them with SET NOEXEC ON, that is how Entreprise Manager does it when you parse a query without executing it.

Besides if you are using regex to validate sql queries, you can be almost certain that you will miss some corner cases, or that the query is not valid from other reasons, even if it's syntactically correct.

Answer 8

I suggest creating a database with the same schema, possibly using an embedded sql engine, and passing the sql to that.

Answer 9

I don't think that you even need to have the schema created to be able to validate the statement, because the system will not try to resolve object_name etc until it has successfully parsed the statement.

With Oracle as an example, you would certainly get an error if you did:

select * from non_existant_table;

In this case, "ORA-00942: table or view does not exist".

However if you execute:

select * frm non_existant_table;

Then you'll get a syntax error, "ORA-00923: FROM keyword not found where expected".

It ought to be possible to classify errors into syntax parsing errors that indicate incorrect syntax and errors relating to tables name and permissions etc..

Add to that the problem of different RDBMSs and even different versions allowing different syntaxes and I think you really have to go to the db engine for this task.

Answer 10

There are ANTLR grammars to parse SQL. It's really a better idea to use an in memory database or a very lightweight database such as sqlite. It seems wasteful to me to test whether the SQL is valid from a parsing standpoint, and much more useful to check the table and column names and the specifics of your query.

Answer 11

The best way is to validate the parameters used to create the query, rather than the query itself. A function that receives the variables can check the length of the strings, valid numbers, valid emails or whatever. You can use regular expressions to do this validations.

Answer 12

I am assuming you did something like .\* try instead [^"]* that will keep you from eating the whole line. It still will give false positives on cases where you have \ inside your strings.

Answer 13

public bool IsValid(string sql)
{
string pattern = @"SELECT\s.*FROM\s.*WHERE\s.*";
Regex rgx = new Regex(pattern, RegexOptions.IgnoreCase);
return rgx.IsMatch(sql);
}