PHP - Regex for prepending table names within SQL

Question

I am looking for an unobtrusive way to find and replace table names based on their position in an SQL query.

Example:

$query = 'SELECT t1.id, t1.name, t2.country FROM users AS t1, country AS t2 INNER JOIN another_table AS t3 ON t3.user_id = t1.id';

I essentially need to prepend client name abbreviations to table names and then have my CMS handle that change. So, going from 'users' to 'so_users' (If Stack Overflow was a client) but not have to add curly braces around all query table names like Drupal. An example is how WordPress will allow you on setup to prepend table names, but the way WordPress handles this issue is not ideal for my means.

For my example I want the output of some method to be:

$query = 'SELECT t1.id, t1.name, t2.country FROM so_users AS t1, so_country AS t2 INNER JOIN so_another_table AS t3 ON t3.user_id = t1.id';

('so_' in prepended to table names)

Thank you.

Kris

Answer 1

This should work for your given example.

A word of caution though,as others have mentioned allready, Regexes are not the best tool for what you need. Given regex works for your example, nothing more, nothing less. There are lots of SQL constructions imaginable where this regex will not make the replacements you need.

$result = preg_replace('/(FROM|JOIN|,) ([_\w]*) (AS)/m', '$1 so_$2 $3', $subject);

---

# (FROM|JOIN|,) ([_\w]*) (AS)
# 
# Match the regular expression below and capture its match into backreference number 1 «(FROM|JOIN|,)»
#    Match either the regular expression below (attempting the next alternative only if this one fails) «FROM»
#       Match the characters “FROM” literally «FROM»
#    Or match regular expression number 2 below (attempting the next alternative only if this one fails) «JOIN»
#       Match the characters “JOIN” literally «JOIN»
#    Or match regular expression number 3 below (the entire group fails if this one fails to match) «,»
#       Match the character “,” literally «,»
# Match the character “ ” literally « »
# Match the regular expression below and capture its match into backreference number 2 «([_\w]*)»
#    Match a single character present in the list below «[_\w]*»
#       Between zero and unlimited times, as many times as possible, giving back as needed (greedy) «*»
#       The character “_” «_»
#       A word character (letters, digits, etc.) «\w»
# Match the character “ ” literally « »
# Match the regular expression below and capture its match into backreference number 3 «(AS)»
#    Match the characters “AS” literally «AS»

Answer 2

Using a query builder class would be the best solution, as you don't want to make any assumption about the pattern you want to replace with regex. If you don't find any existing library suitable for your particular need, roll out your own. It's not hard to make a simple query builder.

Answer 3

Regex does not have the power to parse SQL. Think of constructions like:

SELECT 'SELECT * FROM users';
SELECT * FROM users; -- users
SELECT '* -- users' FROM users;
SELECT '\' FROM users; -- ';               -- differs in My/Pg vs others
SELECT users.name FROM country AS users;   -- or without AS
SELECT users(name) FROM country;           -- users() is procedure
SELECT "users"."name" FROM users;          -- or ` on MySQL, [] in TSQL

and so on. To parse SQL you need a proper SQL parser library; trying to hack it after the fact in regex will only make weird mistakes.