Home » Internet » Blogging » How to improve your WordPress blog security by changing the prefixes of MySQL database tables

How to improve your WordPress blog security by changing the prefixes of MySQL database tables



wordpress-security

When you install WordPress for the first time you need to enter certain information in the wp-config.php file and if you scroll down a bit further you will notice a variable that is defined something like

$table_prefix = “wp_”;

Basically the purpose of this variable is to enable you to install multiple WordPress blogs in a single database. In ideal conditions you don’t have to change this variable and whatever MySQL tables are created by WordPress they begin with the prefix “wp_” so that you have table names like “wp_posts”, “wp_comments” and so on.

If you plan to install multiple WordPress blogs in a single database (it is easier to take backups and maintain your blogs) you just change the prefix to something like “wp1_”. You can also make them more specific by using something like “htp_” (for example if we want to create separate tables for the HowToPlaza blog).

In fact even if you don’t plan to create multiple blogs using the same database it is advisable that you use some different prefix to make your blog more secure, something more unique to your blog. This is because hackers initially assume that you are using the default table names in your MySQL database while maintaining your WordPress blog. So instead of using the standard “wp_” you can use something like “wp_htp34d3_” so that all your WordPress database tables begin with this prefix. This will make it harder for those who want to hack into your blog and create mischief.

But what if you have already created your blog using the standard “wp_” prefix and you have already been publishing your blog for quite a while? Simply changing the prefix in the wp-config.php won’t work: it will in fact prompt you to create a new blog. You will have to manually change the table names. It may seem like an uphill task but considering how much more secure it can make your WordPress blog it is worth the effort.

Take the latest backup of your blog posts

First of all, and DON’T SKIP THIS, take a full backup of your WordPress blog database. For a quick primer on how to backup your WordPress blog database go through our blog post titled how to backup your WordPress blog posts. There is just one method mentioned there but it is the easiest and safest way of backing up your WordPress blog database.

Once you have made sure that you have backed up all your WordPress blog posts you can proceed towards changing the prefix of the tables of your WordPress database.

Upload a temporary maintenance index file

You can quickly create an index file carrying the message that your blog is being maintained and upload the file to your root directory so that people visiting your blog can be notified that something is going on and that is why currently they are not able to access your blog. Otherwise they will needlessly encounter some database related error and they may think that your website is down.

Start changing the prefix of your WordPress database tables

You will need to access the phpMyAdmin section of your hosting service. You should be able to do this through the control panel interface provided by your web host. Once you are there, and in case there are multiple databases already defined, click the database that you are using with this particular blog.

Once you have selected the database make a list of all the tables starting with “wp_”; these are the names that you’re going to change. For example if you want to change the name of the table “wp_comments” you will execute the following SQL query (you will have to go to the SQL tab):

rename table wp_comments to wp_htp34d3_comments;

You can either repeat this query for every table beginning with “wp_” or you can create a list of queries using a text file and then execute them in bulk (separated by “;”).

In case you have added some plug-ins and those plug-ins too have created WordPress tables beginning with “wp_” you may have to change the names of those tables too.

The Options table may contain many fields containing text that begins with “wp_” and you will need to change that to ” wp_htp34d3_”. For that in the query box you can run

select * from wp_htp34d3_options where option_name like “%wp_%”;

Then you can manually change the fields that come up in the result.

The next table is UserMeta that may also contain fields containing the prefix “wp_”. Again, execute the following SQL very in the SQL box:

select * from wp_htp34d3_usermeta where meta_key like “%wp_%”

Here too in the result you will need to change the prefixes manually.

Finally you need to change the prefix variable in the wp-config.php file and re-upload it wherever you have installed your blog:

$table_prefix = wp_htp34d3_;

Using the steps listed above you have changed the names of all the WordPress MySQL tables of your existing blog. Now the final step would be to delete that temporary index file you just uploaded. Once you have deleted that file your WordPress blog with all its table names changed will be active and publicly available, and it will be now more secure than before.