WordPress Database is like a brain for your entire WordPress site because every single information is stored in there thus making it hacker’s favorite target. Spammers and hackers run automated codes for SQL injections. Well, unfortunately many people forget to change the database prefix while they install WordPress. This makes it easier for hackers to plan a mass attack by targeting the default prefix wp_. By default Fantastico installation sets “wp_” as a prefix for each WordPress table name. Since this is a known vulnerability, malicious users can exploit your data easily. They specifically look for the wp_options table, because it will alter your WordPress site look. Through wp_options they can set the url to redirect to their sites, leaving you the impression that your site was defaced. If you already have a WordPress site, take a look at either your config.php file or go to phpMyAdmin in cPanel to check your tables names.
The smartest way you can protect your database is by changing the database prefix which is really easy to do on a site that you are setting up. But it takes a few steps to change the WordPress database prefix properly for your established site without completely messing it up.
We recommend that you backup your WordPress Database before you perform anything suggested in this tutorial. It is important to keep daily backups of your site, we recommend BackupBuddy plugin for doing that. Next thing we recommend is that you redirect your visitors to a temporary maintenance page.
#2. Change Table Prefix in wp-config.php
Open your wp-config.php file which is located in your WordPress root directory. Change the table prefix line from wp_ to something else like this wp_a123456_ So the line would look like this:
$table_prefix = 'wp_a123456_';
#3. Change all Database Tables Name
You need to access your database (most likely through phpMyAdmin), and then change the table names to the one we specified in wp-config.php file. If you are using the cPanel WordPress hosting, then you can find the phpMyAdmin link in your cPanel. There are a total of 11 default WordPress tables, so changing them manually would be pain. That’s why to make things faster, we have a SQL query that you can use.
RENAME table `wp_commentmeta` TO `wp_a123456_commentmeta`;
RENAME table `wp_comments` TO `wp_a123456_comments`;
RENAME table `wp_links` TO `wp_a123456_links`;
RENAME table `wp_options` TO `wp_a123456_options`;
RENAME table `wp_postmeta` TO `wp_a123456_postmeta`;
RENAME table `wp_posts` TO `wp_a123456_posts`;
RENAME table `wp_terms` TO `wp_a123456_terms`;
RENAME table `wp_termmeta` TO `wp_a123456_termmeta`;
RENAME table `wp_term_relationships` TO `wp_a123456_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_a123456_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_a123456_usermeta`;
RENAME table `wp_users` TO `wp_a123456_users`;
You may have to add lines for other plugins that may add their own tables in the WordPress database. The idea is that you change all tables prefix to the one that you want.
#4. The Options Table
We need to search the options table for any other fields that is using wp_ as a prefix, so we can replace them. To ease up the process, use this query:
SELECT * FROM `wp_a123456_options` WHERE `option_name` LIKE '%wp_%'
This will return a lot of results, and you need to go one by one to change these lines.
#5. UserMeta Table
Next, we need to search the usermeta for all fields that is using wp_ as a prefix, so we can replace it. Use this SQL query for that:
SELECT * FROM `wp_a123456_usermeta` WHERE `meta_key` LIKE '%wp_%'
Number of entries may vary on how many plugins you are using and such. Just change everything that has wp_ to the new prefix.
#6. Backup and Done
Securing WordPress involves securing your database. The default table prefix is well-known and targeted by nefarious scumbags across the Web. Changing your prefix to something obscure and difficult to guess is an easy way to stop automated attacks, malicious scripts, and other evilness from compromising your precious database. And remember – always, always, always keep recent backups. If something goes awry with your database, the easiest way to restore sanity is to upload a recent backup and call it done.