Changing Your WordPress Table Prefix

This blog was recently crippled by a hacker using a known SQL injection exploit. Most likely, all that pain and suffering I went through during restoration could have avoided if I’d followed three simple procedures.

1. Keep up with patches. Relying on my old version of WordPress was a big mistake, especially after many security patches had been issued to fix several known exploitation methods.
2. Change the WordPress table prefix. Virtually all successful SQL injection exploits require knowledge of the database table prefix. Since at least 99.99% (a number I pulled out of thin air but is unfortunately realistic, if not low) of all WordPress installations still use the default “wp_” prefix, it’s not hard to guess. Even changing them won’t fool the more sophisticated hackers, but it will slow down or stop those who just know the basics or those relying on automated bots to quickly hack hundreds of blogs at the same time.
3. Remove any text that identifies WordPress or the version of WordPress being used, especially when using an older package. Why advertise your vulnerabilities? In my case, I write a lot about WordPress so I’m unfortunately committed to providing that bit of knowledge, but I stopped displaying version numbers long ago.

If you do decide to change the table prefix, set aside the proper time to make these modifications. You don’t want to have to quit in the middle, as your blog will be completely down until finished. Do not attempt these modifications if you are not at least reasonably familiar with phpMyAdmin (or a similar utility) or MySQL, or basic SQL commands — have a trusted friend do this instead. Before you do anything, back everything up. Deactivate all your plugins until finished.

Ready? OK, let’s change your table prefix now!

In your wp-config.php file, change the table prefix to whatever prefix you want. There may be a length limit, so don’t go nuts.

$table_prefix = 'wp_'; // Only numbers, letters, and underscores please!

Rename the tables names in your database with phpMyAdmin or your favorite MySQL admin tool to reflect the new prefix.

Yay! Done! The blog works exactly as it did before.

Until you try to log in. Then you get the “You do not have sufficient permissions to access this page” message. Ah, dåmn.

Many blog owners have run into the same problem and give you somewhat helpful advice to rename several options in the {new_prefix}_options and {new_prefix}_usermeta tables: In the {new_prefix}_options table , rename the “wp_user_roles” option_name to “{new_prefix}_user_roles”. In the {new_prefix}_usermeta table, rename the following three meta_keys with your new prefix: wp_capabilities, wp_user_level, and wp_autosave_draft_ids. If you don’t have wp_autosave_draft_ids, that’s OK. It only shows up if you’ve ever saved a draft while editing.

What other websites failed to mention during my switch (and that I had to painfully figure out for myself) is that the {new_prefix}_user_level meta_key may be missing. For some reason, it is required for the administrator account when using a new table prefix. The easy solution is to add a new row in the table for {new_prefix}_user_level with the meta_value field set to 10.

Now everything should work fine! You may still need to change more rows in the {new_prefix}_options table that were created by plugins that store the prefix as part of the option_name, but I can’t help you with that.

This how-to was written for WP 2.3.3, but should work with most future versions.