STR_REPLACE to add prefix to mysql query in php

Question

This is my query

$q = "
SELECT *  FROM tbl_accounts
 UNION 
SELECT * FROM tbl_transactions
 UNION 
SELECT * FROM tbl_jobcards
 UNION 
SELECT * FROM tbl_accounts
";

This is the function to add prefix to tables (This function is taken from MySqliDB class)

function rawAddPrefix($query,$prefix){
    $query = str_replace(PHP_EOL, null, $query);
    $query = preg_replace('/\s+/', ' ', $query);
    preg_match_all("/(from|into|update|join) [\\'\\´]?([a-zA-Z0-9_-]+)[\\'\\´]?/i", $query, $matches);
    list($from_table, $from, $table) = $matches;
    return str_replace($table[0], $prefix.$table[0], $query);   
}

When I echo rawAddPrefix($q,"demo_") I get only prefix added to tbl_accounts table only. I need to add prefix to all tables.

What's wrong here ?

Answer 1

That function above does only point and make changes to one occurrence.

I'd suggest a preg_replace_callback and str_replace:

function rawAddPrefix($query, $prefix){
    $query = str_replace(PHP_EOL, null, $query);
    $query = preg_replace('/\s+/', ' ', $query);
    return preg_replace_callback('/(from|into|update|join) [\'\´]?([a-zA-Z0-9_-]+)[\'\´]?/i', function($match) use ($prefix) {
        return str_replace($match[2], $prefix . $match[2] . PHP_EOL, $match[0]);      
    }, $query);
}

If for some reason the table names are user input you'll need to have a whitelist to make your query safe as well.