Page 1 of 1

Regex for extracting tablename from SQL

Posted: Wed May 24, 2006 6:11 pm
by LiveFree
Hey All you RegEx Pros :)

Well I am developing a SQL-error fix-er (detects errors then attempts to parse install.sql if table is not found or column is missing) and I need a way to get all the tables in the FROM section of a MySQL query

Well, I am not great with Regex (I can match simple strings) but basically I need the PREG regex for this psuedo query:

SELECT data FROM nuke_users WHERE id= '1'

I need everything listed in the FROM section (so if they were listed like nuke_users, nuke_bbposts, etc then I'd use explode() to get the into an array)

Thanks!

Posted: Wed May 24, 2006 6:27 pm
by feyd
take a look at phpMyAdmin's code.

Posted: Wed May 24, 2006 6:36 pm
by LiveFree
Thanks feyd, I wouldnt have though of that since I always thought that it just ran the queries and spit out the error

Im thinking of something like this.

^SELECT (.*?) FROM[[:space:]](.*?)

But the problem with that is that would tell me if the string matched, I need a way to actually split the query apart and get all table names after FROM and before GROUP BY, WHERE, LIMIT, or the other modifying operators

Would a preg_replace be in order to replace All before FROM with '' and all after with '' ?

Posted: Wed May 24, 2006 7:56 pm
by LiveFree
Well, I figured this out in about 2 hours time...

For anyone with the same problem

1) Use stristr to seperate everything from SELECT -> FROM
2) Use str_replace to take out the FROM
3) use this regex in a preg_replace (it with just "")

/(WHERE|LIKE|ORDER|GROUP BY)[[:space:]].{1,}/i

And then explode it by the ',' to get all the table names (if there is more than one)

:) Thanks for your reply feyd