Changing Your WordPress Table Prefix…
This blog was crippled last week 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.
- 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.
- 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.
- 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.
IMPORTANT: 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.
Ðámn.
But, it’s still OK. Not far to go. Many blog owners have run into the same problem and give you the 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 tell me during my switch (and that I had to 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 information is provided as-is, without any warranty of any kind, express, implied or otherwise, including without limitation, any warranty of merchantability or fitness for a particular purpose. In no event shall I be liable for any special, incidental, consequential or indirect damages of any kind, or any damages whatsoever resulting from loss of; use, data or profits, whether or not advised of the possibility of damage, and on any theory of liability, arising out of or in connection with the use or performance of this information. This worked perfectly for me, but it might not work for you. You backed your stuff up, right?
[...] Change the WordPress MySQL Database Table Prefix < Rambling Thoughts Blog - Neerav Bhatt Richard’s Ramblings