RegEx to insert a string before each table in a MySQL query

Question

I need to take a MySQL query and insert a string before each table name. The solution doesn't need to be one line but obviously it's a regex problem. It will be implemented in PHP so having programming logic is also fine.

Rationale and Background: I'm revamping my code base to allow for table prefixes (eg: 'nx_users' instead of 'users') and I'd like to have a function that will automate that for me so I don't need to find every query and modify it manually.

Example:

SELECT * FROM users, teams WHERE users.team_id = teams.team_id ORDER BY users.last_name

Using the prefix 'nx_', it should change to

SELECT * FROM nx_users, nx_ teams WHERE nx_ users.team_id = nx_ teams.team_id ORDER BY nx_ users.last_name

Obviously it should handle other cases such as table aliases, joins, and other common MySQL commands.

Has anybody done this?

Answer 1

How big of a code base are we talking about here? A regular expression for something like this is seriously flirting with disaster and I think you're probably better off looking for every mysql_query or whatever in your code and making the changes yourself. It shouldn't take more than the hour you'd spend implementing your regex and fixing all the edge cases that it will undoubtedly miss.

Answer 2

First off, regular expressions alone are not up to the task. Consider things like:

select sender from email where subject like "from users group by email"

To really do this you need something that will parse the SQL, produce a parse tree which you can modify, and then emit the modified SQL from the modified parse tree. With that, it's doable, but not advisable (for the reasons Paolo gave).

A better approach would be to grep through your source looking for either the table names, the function you use to sent SQL, the word from, or something like it at script something to throw you into an editor at those points.

Answer 3

Using a regex to rewrite code is going to be problematic.

If you need to dynamically change this string, then you need to separate out your sql logic into one place, and have a $table_prefix variable that is appropriately placed in every sql query. The variable can then be set by the calling code.

$query = "SELECT foo from " . $table_prefix . "bar WHERE 1";

If you are encapsulating this in a class, all the better.

This example does not take into consideration any escaping or security concerns.