Login

MySQL Table Prefix Changer Tool in PHP

If you are a web developer, you are undoubtedly aware that there are constant threats to your site. SQL injections are one type of threat that you must be aware of and make every attempt to prevent.

In a SQL injection, a malicious user is able to execute queries against your database through form fields on your web site. In some cases, they are even able to append them as POST data directly in the address bar.

This is especially a risk when you are running publicly available software packages such as phpBB. Most users do not change the default settings, so once a vulnerability is exposed, malicious users are able to target sites running the software much more effectively.

I’ve used phpBB as an example because it is well-known for its security vulnerabilities. Sites running phpBB often become the target of SQL injection attacks. Of the many ways to secure a phpBB installation, one of the most effective is to change the MySQL table prefixes away from the defaults set by the phpBB installer.

This makes it that much harder for malicious users to effectively target your database. While this in and of itself does not prevent SQL injections or increase your site’s security, it does make these attacks far less effective.

While I designed this tool with this purpose in mind, it doesn’t stop there. Perhaps you are migrating a site from one host to another and you need to change table prefixes as a result. Or maybe you are restoring data from an old backup into a new installation that uses different settings from the previous.

In any case, changing these prefixes can be a tedious job if you had to do it manually. Even tools like phpMyAdmin don’t provide a clean, quick method of doing this. But with a little help from PHP, we are able to create our own tool very quickly.

{mospagebreak title=Constructing the tool}

This tool should take a very simple approach. It should display a form where you can enter all of the required information for your database. When the form is submitted, it should connect to the database and make the necessary changes.

That seems simple enough, wouldn’t you say?

Please take into consideration that this is designed to be a single use tool. This file should not be left on your web server after use to prevent malicious behavior. However, we’ll attempt to add a little security just in case it is.

The code begins quite simply by creating a page. This is pretty standard for any PHP utility. After setting up the HTML page, we do a quick check for POST data. This lets us know whether we should display the form itself, or process its data.

Here we’re using a pretty standard POST data check. Then we move into a conditional statement. The first block of code should be displayed if no post data was found. That means that we should be displaying our HTML form.

Here we have a simple form with a few input boxes for the necessary information. There’s certainly nothing fancy here and I’ve even elected not to include any type of validation. Since this should only be used by you, the site administrator, it’s safe to assume the user would know what is going on, but you could certainly add some validation if you wished.

The important thing to note here is the addition of the hidden “action” field. This is used by our POST data check. If this field is omitted, the script will not function and will continually display this form no matter how many times you choose to submit it. You should also note that the form’s action is set back to this script.

{mospagebreak title=Editing your current database tables}

Now it’s time to get into the meat and potatoes of this thing. We need to break into our else block and construct the code that should be executed in the event that POST data is found.

<?php

} else {

$mysql_db = $_REQUEST[‘d’];

$mysql_user = $_REQUEST[‘u’];

$mysql_pass = $_REQUEST[‘p’];

$table_prefix = $_REQUEST[‘n’];

We’re going to begin by grabbing the POST data set back by our form and pushing that into a few variables for later use.

// Open MySQL link

$link = mysql_connect(‘localhost’, $mysql_user, $mysql_pass);

if (!$link) {

die(‘Could not connect: ‘ . mysql_error());

}

echo ‘Connected successfully<br><br>';

Next, we make a MySQL connection using the information provided by the user form submission.

// Select database and grab table list

mysql_select_db($mysql_db, $link) or die ("Database not found.");

$tables = mysql_list_tables($mysql_db);

Then, we connect to the specified database and get a list of its table names. The mysql_list_tables() function provides a quick mechanism for this.

// Pull table names into an array and replace prefixes

$i = 0;

while ($i < mysql_num_rows($tables)) {

$table_name = mysql_tablename($tables, $i);

$table_array[$i] = $table_name;

$i++;

}

Now, we want to process the results of our table list query. We’re going to parse each table name and put that information into a string array. This will make processing them much more efficient. A While loop serves our needs perfectly.

// Pull table names into another array after replacing prefixes

foreach ($table_array as $key => $value) {

$table_names[$key] = replace_prefix($value, $table_prefix);

}

Finally, we are going to take each element from this array and build a new array after changing the prefix. We will need to create the replace_prefix() function that makes this all possible.

{mospagebreak title=Putting the database back together}

At this point in execution we are left with an array full of table names that have been reconstructed using the new table prefix. Now we need to write that information back to our database.

To do that, we will use a Foreach loop to iterate through each of our array elements. Then it’s just a matter of forming a query that renames the table. Now you can see why we created a duplicate array with our new table names instead of simply editing the existing one.

We have two side-by-side arrays: one with old names and one with new, in the same order. By moving through each of them at the same time we can get the old and new table names without ever having to know how many tables we’re even working with.

// Free the resources

mysql_close($link);

}

All of the dirty work is done. All that’s left is to close our database connection and create the replace_prefix() function that actually determines the new table names for us.

function replace_prefix($s, $prefix) {

$pos = strpos($s, "_");

$s = substr($s, $pos + 1);

$s = sprintf("%s_%s", $prefix, $s);

return $s;

}

?>

Our custom replace_prefix() function is actually pretty simple. It accepts two parameters: the original table name and the new prefix. A few simple string functions parse out the old prefix and replace it with the new.

Voilà! We’re all done. Save this with a .php extension and upload it to your web server. Access your script in your web browser to see it in action. Do not forget to update your site’s configuration settings with the new table prefix information BEFORE running this script; otherwise, you may not be able to access it afterward.