Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Secure your WordPress installation by changing the table prefix

Tuesday, April 30th, 2013 by Servage

Content is still king on the net. All WordPress authors know this. What they may not know is that all their WordPress posts are actually stored in a database table, via records, that the WordPress software calls from to display content on the front end. Seamlessly and nicely displaying our wonderfully written ideas.

WordPress databases are of the MySQL variety. So WordPress can call on the these tables correctly, a prefix gets added during installation in the default form of ‘wp_’. An actual table then appears as ‘wp_content’ or ‘wp_options.’ Therefore a database table prefix is simply an identifier.

There are generally two ways for a WordPress Admin to check this sort of information: first, in our site files, particularly the configuration file, and second, via our online view of the databases, usually utilizing the administration tool known as phpMyAdmin.

The two common reasons for changing a table prefix are to distinguishing multiple site information among a single database, or for added layer of security. The security idea is considered secondary as adjusting the prefix doesn’t prevent clever hackers from getting at content in the database.  Nonetheless, it will help by adding a simple layer and no longer making the site an easy target.

The super easy way to change the prefix information is to do it upon installation of the blog. As that is not always done with installs of WordPress site, this article is assuming the adjustment to the prefix is needed after the installation and content has been added. Here are the steps for that process.

Preparation

First is to know what you are doing and why. As first part of this article address that, we can move on to what is the mantra for all situations of tinkering with your WordPress site on the backend – backup your data. This is something to do before and after the prefix adjustment is made, and allows you to tinker with allowance for possible error. With backup copy, you can always return to ‘working copy.’ Nothing quite matches the added layer of security that comes from a full site backup.

Configuration

First technical step is to adjust the site’s configuration file. In the main directory of your WordPress site, you’ll find a file called wp.config.php. This has info for the table prefix and you open this via a code editor like NotePad or TextWrangler (both free). If not familiar with opening such a file, it can be daunting at first, but this tutorial focusses on a couple lines.

Around halfway down the lines of code there is information for table prefix. The line we are focussed on appears as:

$table_prefix =

What is after the equals sign is what gets changed. If doing this from default installation, then it would appear as $table_prefix = ‘wp_’ and the small quotes and underscore stay in there. It is the wp part that changes. So keep the underscore and add in a string of numbers or letters that work for you.

For this procedure, you’ll want to make note of exact prefix you inserted here, but outside of this procedure, there isn’t a need to retain this sort of data. The config file stores this information. After prefix has been adjusted, then save the change. Before closing the file, just take a quick look at the top line of this file where it references the ‘DB_Name.’ In the next step this will matter.

Changing Database Tables

Things get a wee bit more technical at this stage, but once you familiarize yourself with a tool like phpMyAdmin, it becomes less intimidating. Opening phpMyAdmin, leads to virtual view of all your databases, and you select the one that is associated with this WordPress Installation.

Selecting the link of your specific database, calls up the structure, showing a list of all tables that make up that database. The default installation of WordPress has 11 basic tables, all of which start with ‘wp_’ as a prefix.

It is possible your database is showing more tables as you may have added plugins to your installation. If so, just make note of ones that are not the basic ones.

Now we rename these tables, changing the prefix only by initiating a command query. So you click on the tab “SQL Query” (right next to Structure) and paste the code from below.

Rename table wp_commentmeta to examplePrefix_commentmeta;
Rename table wp_comments to examplePrefix_comments;
Rename table wp_links to examplePrefix_links;
Rename table wp_options to examplePrefix_options;
Rename table wp_postmeta to examplePrefix_postmeta;
Rename table wp_posts to examplePrefix_posts;
Rename table wp_terms to examplePrefix_terms;
Rename table wp_term_relationships to examplePrefix_term_relationships;
Rename table wp_term_taxonomy to examplePrefix_term_taxonomy;
Rename table wp_usermeta to examplePrefix_usermeta;
Rename table wp_users to examplePrefix_users;

This code tells the database to rename the 11 tables, though of course you must adjust it to match any additional tables you have. And change the prefix to replicate what you put in the configuration file. When all information is entered, you click “go “ and phpMyAdmin will take full list of your tables and rename them with the new prefix. This ought to show up immediately on the pane on the left, plus it ought to be in effect when you return to the Structure tab.

You might think you’re all done at this point, but there are 2 other places in the database where particular records of a table are still calling up information that assumes a prefix of what just was, rather than what you just renamed.

Changing Specific Records

The Options table and the Usermeta table are where the next key changes are implemented.

From the Structure tab, click on the “options” table. At this point it is no longer “wp_options” but instead ought to be _options with the prefix you just added. This changes to a Browse tab and the change is with a record found in the column for ‘option_name.’ There are a few methods to call this record up, but the one I find most simple is to click on the Search tab at the top, which ought to open a window like the one in the image below.

You see I highlighted the option name in the image and in the text field to the right, you’ll add %wp_% and then click on Go. In all likelihood this will call up just one record called wp_user_roles. Now you just click on “edit” which opens the Insert Tab. Here we are just changing the option name with the new prefix and click Go to save.

Next do the same with the Usermeta table, which is one of the original 11 tables from the Structure tab. Same steps as before, click on the table name, then on the Search field and this time in the meta_key text field enter %wp_% and then click on Go. This time, a bunch of records are likely to pop up (at least 4, maybe as much as 10). With this step, you do have to edit each item to update to the new prefix.

Done and Almost Done

The technical work is complete at this point. You can step away from phpMyAdmin and config files and realize the hard stuff is all done. But there are just 2 more steps that are suggested at this point. First is to test your site to make sure these changes are not causing any problems. So go into Admin area of your site, make a post or two, add a comment or two and just ensure that the site works as you expect it to. Second and last item is to make another full backup of the site.

References & More Reading

 

Secure your WordPress installation by changing the table prefix, 4.5 out of 5 based on 2 ratings
Categories: Guides & Tutorials

Keywords: ,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.