MySQL Table Prefix Changer Tool

January 1st, 2009

In early 2008, I created a simple PHP driven tool for changing the prefix on a MySQL table. The was originally designed to change the default prefix used by phpBB installations to help prevent SQL injection attacks. The tool has gained quite a bit of popularity and I’ve been receiving a lot of searches for it so I decided to release it on Nilpo.com.

You can download the original version here as PHP, zip, or tar.gz.

The tool asks you to provide some basic information about your database as well as a new prefix. Clicking the submit button will cause the script to continue without confirmation. Be sure that you double check your entries before continuing!

This tool is provided AS IS under the GNU GPL as outlined in the file. I’m not responsible if things go wrong. If you have questions or problems, please leave a comment on this post and I’ll attempt to help you out.

Here’s the source:

< ? php /*
    MySQL Table Prefix Changer
    Copyright © 2008 by Robert Dunham <http://www.nilpo.com>
    Version 1.1
    Original version: <http ://www.nilpo.com/pub/examples/prefix.php>
                                  <http ://www.nilpo.com/pub/examples/>
 
    Description:
        This script can be used to change all of the table prefixes
        in a database.  This can be useful as a security precaution
        when using preset table names like with phpBB.  This can help
        prevent sql injections.
 
    License:
        This program is free software: you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation, either version 3 of the License, Or
        (at your option) any later version. This notice must remain intact.
 
        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.
 
        You can read the GNU General Public License online at
        <http ://www.gnu.org/licenses/>.
*/?>
 
<html>
<head>
<title>MySQL Table Prefix Changer</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></meta>
</head>
 
<body>
< ? php
// Check for POST data
$action = isset($_REQUEST['action'])?$_REQUEST['action']:false;
 
if (!$action) {
?>
<form name="form1" method="post" action="prefix.php">
	<table width="50%" border="0" cellspacing="2" cellpadding="2">
		<tr>
			<td>Enter server address<span style="color:red">*</span>:</td>
			<td><input name="s" type="text" id="d" size="50" value="localhost"/></td>
		</tr>
		<tr>
			<td colspan="2" align="center"><span style="color:red">*</span> If you are unsure, do not change this value.</td>
		</tr>
		<tr>
			<td>Enter database name:</td>
			<td><input name="d" type="text" id="d" size="50"/></td>
		</tr>
		<tr>
			<td>Enter database user:</td>
			<td><input name="u" type="text" id="u" size="50"</td/>
		</td></tr>
		<tr>
			<td>Enter database password:</td>
			<td><input name="p" type="password" id="p" size="50"/></td>
		</tr>
		<tr>
			<td>Enter New Prefix:</td>
			<td><input name="n" type="text" id="n" size="50" value="(Do not include the trailing underscore)"/></td>
		</tr>
		<tr>
			<td>&nbsp;</td>
			<td>&nbsp;</td>
		</tr>
		<tr>
			<td colspan="2" align="center"><input type="submit" name="Submit" value="Change Table Prefixes"/>
				<input name="action" type="hidden" id="action" value="data"/></td>
		</tr>
	</table>
</form>
< ? php
} else {
 
$mysql_server = $_REQUEST['s'];
$mysql_db = $_REQUEST['d'];
$mysql_user = $_REQUEST['u'];
$mysql_pass = $_REQUEST['p'];
$table_prefix = $_REQUEST['n'];
 
 
// Open MySQL link
 
 
 
$link = mysql_connect($mysql_server, $mysql_user, $mysql_pass);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br><br />';
 
 
// Select database and grab table list
mysql_select_db($mysql_db, $link) or die ("Database not found.");
$tables = mysql_list_tables($mysql_db);
 
 
// 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++;
}
 
 
// Pull table names into another array after replacing prefixes
foreach ($table_array as $key => $value) {
	$table_names[$key] = replace_prefix($value, $table_prefix);
}
 
 
// Write new table names back
foreach ($table_array as $key => $value) {
	$query = sprintf('RENAME TABLE %s TO %s', $table_array[$key], $table_names[$key]);
	$result = mysql_query($query, $link);
	if (!$result) {
		$error = mysql_error();
		echo "Could not $query : $error<br />";
	} else {
		$message = sprintf('Successfully renamed %s to %s in %s', $table_array[$key], $table_names[$key], $mysql_db);
		echo "$message<br />";
	}
}
 
 
// Free the resources
mysql_close($link);
}
 
function replace_prefix($s, $prefix) {
	$pos = strpos($s, "_");
	$s = substr($s, $pos + 1);
	$s = sprintf("%s_%s", $prefix, $s);
	return $s;
}
?>
</body>
</html></http>

Please read my original article on DevShed.

[donotprint] [/donotprint] Please use the trackback link when linking to this post.

Related Posts:

Add to Technorati Favorites

18 Responses to “MySQL Table Prefix Changer Tool”

  1. Nur Says:

    Hi,

    Nice script, BUT…

    Could not RENAME TABLE users_roles TO finance_roles : Table ‘finance_roles’ already exists

    This is not a good solution for this kind of errors!
    Perhaps, you need to include “existing header” to the code!

  2. Shaun Says:

    Thank you!

    Worked perfectly for me and saved a LOT of time! Much appreciated!

  3. Nilpo Says:

    That’s great! I’m glad that worked for you.

  4. Jav Says:

    Excellent tool… I started reading about securing my site and changing the prefix was on top of the list. This saved me a lot of time as I used it on 2 sites with no problem. Thank you!

  5. Nilpo Says:

    Hello, Jav. I’m glad you found this tool useful. Thanks for stopping by.

  6. Ines Says:

    Won-der-ful!!!
    Simply wonderful.
    I was nearly giving up, when I found these so useful lines of code.
    Thank you very much indeed.

  7. Giuseppe Says:

    Thank you, this is a very useful tool, saved the time to do manually by exporting the database on a text file

  8. Amina Ebbesen Says:

    Good post, i really had fun reading it. I look forward to seeing more.

  9. Reuben Says:

    CAREFUL WITH THIS FOLKS.. Backup your DB first. I just totally mangled by DB while trying to add prefixes to the tables that didn’t have prefixes before.

  10. Nilpo Says:

    @Reuben: You should always back up your DB before ANY operation.

    The script mangled your database because you should not have run it. As the title says, it’s a prefix changer tool. It’s not meant to run on databases without table prefixes.

  11. Josh Lewis Says:

    Awesome! It worked perfect and saved me a lot of troubles. Thanks for making this.

  12. Migrating from Joomla 1.5 to Joomla 2.5 : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  13. Migrating from Joomla 1.5 to Joomla 1.6+ : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  14. Upgrade 1.5 to 1.7 : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  15. Tutorial:Migrating from Joomla 1.5 to Joomla 1.6 : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  16. Migrating from 1.5 to 1.6 : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  17. Migration : News Of Open Source! Says:

    […] finished regulating a script; a “MySQL Table Prefix Changer Tool” accessible during Nilop is one that worked […]

  18. Bagaimana Says:

    great tool… very useful tool. thanks for ur program…

Leave a Reply

.htaccess Apache article articles by Nilpo ASP ASP Free automation clipboard copy desktop Dev Shed docx Internet Explorer message box Microsoft PHP registry remove script scripting text tutorial tweak tweets Twitter UAC updates User Account Control VBS VBScript vista volume Windows Windows 7 Windows Guru Windows Script Windows Script Host Windows Scripting Windows Vista Windows XP Word WScript wscript.exe WSH XP