Replace mysql query table prefix in php

Question

I am writing a database query manager class.In my class table prefixes are characterized by #__. I want to replace them to table prefix by a function.

The function that i wrote works well but it is very slow. i want a optimized function (maybe regex or another solution).

Notice: please remember that #__ should not be replaced in quotes.

examples :

SELECT p.*, m.member_name, m.member_alias
FROM #__posts AS p
LEFT JOIN #__members AS m ON m.member_id=p.post_author
WHERE p.post_approve = '1' AND p.post_date <= '1438252218'

OR

INSERT INTO `#__posts` (`post_title`, `post_text`) 
VALUES ('post title (maybe include #__ )'  , 'post text. it also can include #__')

my function:

protected function replace_prefix($sql, $prefix = '#__') 
{
    $done = null;
    $single = false;
    $double = false;
    $found = false;
    $i = 0;
    while (strlen($sql) > 0)
    {
        if ($sql[$i] == null)
        {
            return $done.$sql;
        }
        if (($sql[$i] == "'") && $sql[$i-1] !='\\')
        {
            $single = !$single;
        }
        if (($sql[$i] == '"') && $sql[$i-1] !='\\')
        {
            $double = !$double;
        }

        if ($sql[$i] == $prefix[0] && !$single && !$double)
        {
            $found = true;
            for ($j=0; $j < strlen($prefix); $j++)
            {
                if ($sql[$i+$j] != $prefix[$j])
                {
                    $found = false;
                    break;
                }
            }
        }
        if ($found)
        {
            $done .= substr($sql, 0, $i).$this->prefix;
            $sql = substr($sql, $i+$j);
            $found = false;
            $i = 0;
        }
        else
        {
            $i++;
        }

        if ($i >= strlen($sql))
        {
            return $done.$sql;
        }
    }
    return $done;
}

one trick would be, instead of going char by char.. just replace all "#_ and '#_ to a temp string.. then replace all #_ with your value and finally replace back temp strings to '#_ or "#_ — Krish
– Krish, Commented Jul 30, 2015 at 10:00
what if it was in a string for example : "This #__ is prefix". — Mohammad Sadegh
– Mohammad Sadegh, Commented Jul 30, 2015 at 10:04
can you list all possible $SQL string? maybe time to use regex and a slicing function — Krish
– Krish, Commented Jul 30, 2015 at 10:33
you can still use above trick with regex and little function. I'm not PHP expert but this solution should help you: stackoverflow.com/a/10524283/1684486 just replace all #_ within quotes to a temp string using above function then replace all #_ to your value and then replace back temp strings — Krish
– Krish, Commented Jul 30, 2015 at 10:55

Kristian Vitozev · Accepted Answer · 2015-07-30 10:23:39Z

1

Do you know the names of the tables? For example, if you have a table #__Contacts, you can do the following:

$sql2 = str_replace( $prefix . $table, $new_prefix . $table, $sql );

If you're using replace_prefix() method for trivial queries (not complex ones) it should do the trick.

answered Jul 30, 2015 at 10:23

Kristian Vitozev

5,9716 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mohammad Sadegh Over a year ago

No i don't. This class is used in a modular CMS. so anyone can add or delete a table.

Kristian Vitozev Over a year ago

Can you send us few queries which will be passed by your replace_prefix() method?

Kristian Vitozev Over a year ago

Well, did you measure execution time of your script? Can you give us a estimated time for first sample query. I though about solution using SHOW TABLES, but I am not sure if it will have better performance impact or will make things worse.

Mohammad Sadegh Over a year ago

no show tables is a bad solution. how if we find quotes and reaplace them with something then use str_replace ???

Mohammad Sadegh · Accepted Answer · 2015-07-31 11:39:05Z

finally i found a good way! this function is 30 times faster that the old:

protected function replace_prefix($sql, $prefix = '#__') 
{
    $array = array();
    if($number = preg_match_all( '#((?<![\\\])[\'"])((?:.(?!(?<![\\\])\1))*.?)\1#i', $sql, $matches))
    {
        for ($i = 0; $i < $number; $i++)
        {
            if (!empty($matches[0][$i]))
            {
                $array[$i] = trim($matches[0][$i]);
                $sql = str_replace($matches[0][$i], '<#encode:'.$i.':code#>', $sql);
            }
        }
    }

    $sql = str_replace($prefix, $this->prefix , $sql);

    foreach ($array as $key => $js)
    {
        $sql = str_replace('<#encode:'.$key.':code#>', $js, $sql);
    }

    return $sql;
}

Collectives™ on Stack Overflow

Replace mysql query table prefix in php

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related