Calling Regular Expressions Masters

I’m working on a bit of code that prepends database table prefixes to a string of SQL. For example, I would take the following SQL:

select User.UserID, Permission.PermissionID from User
left join UserRole
     on User.UserID = UserRole.UserID
left join RolePermission
     on UserRole.RoleID = RolePermission.RoleID
left join Permission
     on RolePermission.PermissionID = Permission.PermissionID
     and Permission.Name = 'can_sign_in'
where User.Name = 'mark'
     and User.Password = md5('password')

… and turn it into …

select LUM_User.UserID, LUM_Permission.PermissionID from LUM_User
left join LUM_UserRole
     on LUM_User.UserID = LUM_UserRole.UserID
left join LUM_RolePermission
     on LUM_UserRole.RoleID = LUM_RolePermission.RoleID
left join LUM_Permission
     on LUM_RolePermission.PermissionID = LUM_Permission.PermissionID
     and LUM_Permission.Name = 'can_sign_in'
where LUM_User.Name = 'mark'
     and LUM_User.Password = md5('password')

I’ve got 99% of it working properly, but I am having difficulty with the “on” clause of the join. In a simple join, like the first join above, the following code works perfectly:

$DbPrefix = 'LUM_';
$On = 'User.UserID = UserRole.UserID';
$On = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $DbPrefix . "$1$2" . $DbPrefix . "$3", $On);
// Returns: LUM_User.UserID = LUM_UserRole.UserID

But the regex I have here is only good for one join. So, for example, this would be a problem:

$DbPrefix = 'LUM_';
$On = "RolePermission.PermissionID = Permission.PermissionID and Permission.Name = 'can_sign_in' ";
$On = preg_replace('|([\w\.]+)([\W\s]+)(.+)|', $DbPrefix . "$1$2" . $DbPrefix . "$3", $On);
// Returns: LUM_RolePermission.PermissionID = LUM_Permission.PermissionID and Permission.Name = 'can_sign_in'

As you can see, it only prepends the prefix on the first two. I’m afraid I’m at a loss as to how I can get it to keep matching on subsequent joins for that table. In this example you might say that I should put the second part of my join into the where clause; but that’s not what I’m trying to accomplish; so just pretend that it’s a regular table-to-table join and needs to be here.

Anyone regex-ey enough to conquer this one?

Thanks to Jeff, who solved it with:

$DbPrefix = 'LUM_';
$On = "RolePermission.PermissionID = Permission.PermissionID and Permission.Name = 'can_sign_in' ";
$On = preg_replace('/(\\w+\\.)/', $DbPrefix.'$1', $On);
// Returns: LUM_RolePermission.PermissionID = LUM_Permission.PermissionID and LUM_Permission.Name = 'can_sign_in'