Extract all tables from an SQL query

Question

I want to add a prefix to tables and have recently written a PHP script to extract all tables in a string SQL query.

$sql = 'UPDATE festivals SET desc = "Starts from July"';
preg_match_all('/(from|into|update|table|join) (`?\w+`?)\s/i', $sql, $matches);

It works good but the only problem is that it extracts July because it does not distinguish between a SQL value and a real table name, so it assumes that July would be a table too.

Now I think the solution should be something to prevent extract what wrapped in a single or double quotation but don't know how to do that.

You should really consider this SO question: http://stackoverflow.com/questions/281041/regular-expression-to-find-all-table-names-in-a-query. — nvartolomei, Oct 14 '12 at 13:33
Thanks, but that's another issue because i want to add a prefix to tables and don't care about white spaces, aliases or etc. — Omid, Oct 14 '12 at 14:02

score 0 · Answer 1 · answered Oct 14 '12 at 13:35

0

If you were more strict in your query-writing, you would wrap all your database, table and column names in backticks ` and they would be extremely easy to extract - just get the first match of a string between them: just make the backticks required instead of optional.

That said, I'm not entirely sure how your regex is matching from July since the July is not followed by whitespace...

answered Oct 14 '12 at 13:35

Niet the Dark Absol

320,036
81
464
592

1

true, didn't pick that up. maybe he didn't actually test it and is just presenting a hypothetical scenario. – Asad Saeeduddin Oct 14 '12 at 13:42
@Kolink Using backticks would be a good idea but just when you are the only developer and not applicable for teamwork. And about `July` this is just an example – Omid Oct 14 '12 at 13:47
If you (or your team) can't follow proper standards, then you can't process the task with a regular expression. You would have more luck parsing the query yourself and extracting the tables. Alternatively, you might be able to put `explain` at the start of the query, run it, and then look for the tables listed in the query summary. – Niet the Dark Absol Oct 14 '12 at 14:12

score 0 · Accepted Answer · edited Dec 03 '12 at 08:00

0

Your regex is better off this way:

"/((?:^select .+?(?:from|into))|^update|^table|join) (`?\w+`?)\s/I"

But I still agree with nvartolomei.

edited Dec 03 '12 at 08:00

Omid

4,575
9
43
74

answered Oct 14 '12 at 13:37

Asad Saeeduddin

46,193
6
90
139

It's working for your test case in ruby flavor. What flavor are you using? – Asad Saeeduddin Oct 14 '12 at 16:32
i don't know how to make a room, but this is my yahoo id `lwl_kahroba_lwl`, also available ok skype as `omidamraei` – Omid Oct 14 '12 at 16:55
[Here you go](http://chat.stackoverflow.com/rooms/18012/extract-all-tables-from-an-sql-query-chat) – Asad Saeeduddin Oct 14 '12 at 16:59

Extract all tables from an SQL query

2 Answers2