Regex to determine legal SQL?

Question

Possible Duplicate:
Regular expression to match common SQL syntax?

I am writing some helpers to help generate some very domain-specific sql. In my test-suite it would be nice to add a sanity check to ensure that the results are actually valid SQL. I'm not doing anything particularly fancy and this isn't production code so I think some regex would do but it's still annoyingly difficult to write by hand.

Is anyone willing to share a regex expression that would validate valid SQL at least for the 90% case?

I know we are closed here, but it should be doable to create a wrapper proc that runs your SQL Code, traps errors, and always rolls back. — JNK, Apr 12 '11 at 20:59
@JNK: nice idea. The proc could also try not to run the actual query but try an `EXPLAIN` on it (or equivalent). — ypercubeᵀᴹ, Apr 12 '11 at 21:17
@JNK - Not a bad idea at all, I think I already even have a sqlite instance spun up. If I get anything back but a "table does not exist" error I know the sql is malformed. — George Mauer, Apr 13 '11 at 17:26

score 8 · Accepted Answer · edited Jun 20 '20 at 09:12

8

SQL is not a regular language. Use a real validator, such as Mimer.

+1. Nice! I didn't know that existed. – David Apr 12 '11 at 18:26
3

@David: neither did I, until I Googled [`sql validator`](http://www.google.com/search?q=sql+validator). – Matt Ball Apr 12 '11 at 18:27
These links are certainly interesting, but I don't need to match all of sql, just whatever standard cases we can get. Certainly going out to a web-service is not appropriate for unit tests. – George Mauer Apr 12 '11 at 19:00
1

@George: personally, that's not how I'd write my unit tests. Unit tests should test **actual units of code.** If using a web service isn't right for you, why wouldn't you just test your SQL against your actual database? – Matt Ball Apr 12 '11 at 19:12

Regex to determine legal SQL?

1 Answers1

See also:

Linked