WordPress database tables all begin with the wp_ prefix by default. Security gurus often recommend changing the prefix to secure your site, but is it really worth doing? Or is it just all sizzle and no steak?
The idea is that changing the database prefix helps avoid nasty SQL injections since hackers usually use queries with the standard settings in their attack, but if the database details are unique the hacker would get an error.
While updating the fix is by no means the best or only solution for protecting your site, it’s one step you can take to make your WordPress install that little bit more secure.
So in this post, I’m going to run you through why and how changing your WordPress database prefix can add to your site’s security.
What’s Prefix Got to Do with It?
To understand why changing your database’s tables prefix could help as a part of your site’s preventive measures against attacks, it’s important to explore both sides of the argument.
Knowing the reasoning behind the amendment should give you a better idea of what it takes to keep your site secure.
That’s all well and good, but why is it a popular practice to change the table prefix in the first place?
SQL Injections and the Common Recommendation
When you’re checking out the pros and cons, it’s important to note that the main reason why it’s widely recommended to rename your table prefix is to prevent a hacker from overtaking your site with a successful SQL injection.
In short, when your site is vulnerable to an SQL injection, it means that a hacker can execute an SQL query from an unsanitized form on your site without your permission or knowledge.
If a hacker can successfully run an unauthorized SQL query on your site, it means that there’s an open connection to the target database.
When this happens, a hacker can change whatever they want and gain full access and control over your site. Meaning they can change and do whatever they want with your site since the database is fully exposed to them.
They wouldn’t necessarily have access to your files, but they would have full reign over your database.
They could gain full access and control to your site and the first task is usually changing the admin login credentials.
This locks you out and lets them continue going about their way, making a wide range of changes from making your site turn into spam central and stealing personal information to infecting thousands of other sites where they can spam it up and steal all the more.
It’s certainly something you want to protect your site from and you can get more details on SQL injections and their prevention by checking out The Ultimate Guide to WordPress Spam.
Cons
The main reason against renaming anything in the database is that if a hacker can execute an SQL injection on your site, there’s already an open connection to your database.
This means that if their first SQL query fails, it’s easy enough for the hacker to run a query searching for the second half of the table names that are almost always identical to the default.
The second query could search for names including usermeta and postmeta.
The search would turn up the full table name and voilà! Your hacker reads your new prefix. From there, they could continue doing what they wanted.
This is also assuming they didn’t have access to your files as well. If they did, they could easily find your new prefix there as well.
At the end of the day, if your site and database aren’t vulnerable with an open connection, then it doesn’t matter what your table prefix happens to be and if there is an open connection, a hacker could easily figure out what you named your prefix making it useless.
Pretty cut and dry, right? Well, not exactly.
There are a couple of factors and one major one that’s left out of the picture here.
Pros
While it may seem like a pointless endeavor after considering the cons, it’s not like there’s no precedent for thinking renaming the prefix works.
For one, it’s a suggestion in the WordPress Codex for Security Hardening. Also, code injection vulnerabilities have been detected in WordPress starting as early as the first version 0.7 and again in future versions.
Vulnerabilities have been consistently squashed since WordPress’s inception, but they started out being available on numerous sites for hackers to exploit.
That’s why adding a little precaution can be a valuable asset to your many security measures.
There’s also a reason why renaming your table prefix has been lumped into the “security essentials” label for over ten years.
Hackers rarely find out about a site’s vulnerability by visiting them through a known browser like the rest of us do every day.
Instead, they commonly use a program they create that’s commonly referred to as a bot. They start up the bot and it systematically goes to hundreds if not thousands of sites in a matter of minutes to automatically search for vulnerabilities.
Once a vulnerability is found such as the one that allows for an SQL injection, the bot then submits queries to hack all the vulnerable sites at the same time. Out of the sites that are successfully hacked, the bot continues from there.
But on the other hand, if some site admins have changed their table prefix, then the bot wouldn’t be able to successfully execute their first query since it only accounted for the default settings and would move onto sites where the query worked.
If your site were one of the ones where the prefix was changed, then your site would essentially be “safe” for the time being.
Keep in mind that in this situation, your site would still have a major vulnerability so if it’s not patched up immediately, it’s potentially only a matter of time until a hacker gets their squirmy fingers in there – so to speak.
This is why changing your database table prefix isn’t your be-it-end-all security solution. Far from it, actually.
While it can help the fight against hackers, it’s crucial to have a security plugin installed like our free Defender to truly secure your site.
While changing your database table prefix isn’t the be-all-end-all of security measures, it’s still worth the effort of applying it to your site, among your other security essentials. Read on to find out how to make the change to your site.
Like I’m Gonna Lose You
Since you’re going to be changing your database in a fundamental way that could completely break your site, it’s important to make a backup of your entire site as if you were going to lose everything.
That way, if you do, you can easily restore your site so you don’t end up losing everything for good.
For details on how to create a full backup of your site, check out some of these articles:
- How to Backup Your WordPress Website (and Multisite) Using Snapshot
- Creating a Manual Backup of WordPress When It’s Down or Locked
- Backup Plugins Aren’t About Backing up, They’re About Restoring
- 4 Top WordPress Multisite Backup Solutions Tested and Reviewed
- 7 Top Premium and Freemium WordPress Backup Plugins Reviewed
- 11 Best Free Quality Backup Plugins for Protecting Your WordPress Site
Once you have created a backup of your site, you can start changing your table prefix.
The Files, They Are A-Changing
To rename your table prefix, first, start with your wp-config.php file. You can either download a copy to edit it, then re-upload it to your site via FTP or the command line and SSH or you edit it directly in cPanel.
After logging into cPanel, go to Files > File Manager and locate your site’s files. Click on the wp-config.php file from the list, then click Edit at the top of the page.
In the editor, locate the following line:
Change wp to whatever you want, but it’s recommended that you keep the underscore after it is intact. Your new prefix can contain letters, numbers, and even underscores.
For example, you could choose something like wp2748, il0ved0gs, sg7bn40dg or ug44_db87, but don’t use these exact examples since they’re public and everyone can see them including hackers.
Once you’re happy with your changes, save the file. If you used FTP or SSH, upload the file back to your site and replace the old version.
You’re not done yet and you may even notice your site is completely broken now. That’s normal since your database’s data doesn’t match your changes.
To fix this and finish renaming your prefix completely, go ahead and move on to updating your database.
Rolling in the Database
To start making the change to your prefixes in your database, log in to phpMyAdmin so you can run some queries.
For details on how to run SQL queries in phpMyAdmin, check out that section in one of our other posts 10 Tips for Keeping a Squeaky Clean WordPress (and Multisite!) Database.
You can enter your queries, line-by-line to change the main tables in your database. The structure you should follow for all your tables is below:
Don’t forget to replace tablename with the actual name of the table as well as change newprefix to the new prefix you saved to your wp-config.php file.
For single installs of WordPress, you can enter these queries, but don’t forget to change newprefix to the real prefix you created:
For Multisite networks, also add on these queries remembering to also replace newprefix with the new prefix you chose:
Beyond these queries, you also need to add any custom tables that plugins on your site have created as well as all the tables that are created for each subsite in your network.
Here’s a list of the site tables you should also include in your queries list:
- wp_#_commentmeta
- wp_#_comments
- wp_#_links
- wp_#_options
- wp_#_postmeta
- wp_#_posts
- wp_#_terms
- wp_#_term_relationships
- wp_#_term_taxonomy
Be sure to replace # with the site ID of your subsites. For example, you would include wp_2_commentmeta in your query for your subsite with the site ID of two.
For more information on database tables, check out one of our other posts MySQL and WordPress: Understanding How Databases Work.
Some Table That I Used to Know
When this part is done, you still need to update some entries in the options and usermeta tables mentioned above.
Update Options Table Entries
There are going to be quite a few entries you need to update in the options table and you can search for them with a query to make the process easier.
Use the following query to run a search on the options table and don’t forget to update newprefix with the actual prefix you chose:
The search should retrieve a list of all the tables that need to be updated. You can click the edit buttons for each one to make the change.
Update Usermeta Table Entries
Similar to the options table, the user meta table also needs many of its entries updated. Instead of looking for them one-by-one, you can search for them as well.
Here’s the query you can use to search for entries that include the default prefix:
Be sure to rename newprefix to the real prefix you wrote into your wp-config.php file.
Finally, edit all the entries that appear in the search results to reflect your new prefix.
Won’t Get Fooled Again
You’re pretty well done updating your database table prefix to something completely custom, but the final step is to create one more full backup.
Once that’s done, you can go to your site and bask in its glory. While nothing has changed on the front end, you made a step toward a more secure site.
Although it’s not the only security measure you should take, it’s an added measure that can help protect you against hack bots that systematically try and infiate sites and their databases en masse.
If you visit a site and you get a “permissions” or “database connection not established” error, you forgot to rename one of the tables or entry prefixes.
It’s important to go back into phpMyAdmin and search for the tables or entries you forgot to change.
You can also check out How to Fix “Error Establishing Database Connection” for WordPress to get more details.
Some plugins rely on the default database prefix so if you’re sure you haven’t missed updating and tables or entries, you can disable all your plugins and re-enable them one-by-one until your site breaks.
The last plugin you activated is the culprit and you should consider deleting it and finding an alternative.
And Data Do It
Now that you know how to change your WordPress database prefix and your security should be beefed up and bots and hackers will be heading elsewhere.
Keep in mind that at any point, if you get stuck trying anything in this post or anything, ever for that matter, you can ask our expert support team for 24/7 help. If you’re already a WPMU DEV member, you’re all set to ask your question. If you’re not a member yet, you can take one of our premium memberships for a free trial and ask away.
Also, to really boost up your WordPress security, be sure to check out our free Defender plugin. Defender blocks hackers at every level with two-factor authentication, login masking, login lockout, 404 detections, and more!
You can read all about his malware scanning, login security, firewall, and getting the most out of him here.
Source: wpmudev.com