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> </td> <td> </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.
Bookmark to:
Please use the trackback link when linking to this post.
Related Posts:
March 26th, 2009 at 2:50 am
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!
May 17th, 2009 at 5:52 am
Thank you!
Worked perfectly for me and saved a LOT of time! Much appreciated!
May 17th, 2009 at 7:08 am
That’s great! I’m glad that worked for you.