Changing Your WordPress Database Prefix to Improve Security
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 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. If you missed the option in the famous 5-minute install, not to worry. I’ll also cover how to make the change manually with phpMyAdmin, SQL queries, the wp-config.php file as well as the much easier, one-click method using our Defender plugin.
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 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 sign up for a premium account for free and ask away.
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 the 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.
When this happens, a hacker 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 they way, making a wide range of changes from making your site turn into spam central and stealing personal information to infecting thousands of others 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 its prevention by checking out The Ultimate Guide to WordPress Spam.
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
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 factors and one major one that’s left out of the picture here.
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 such as 1.5.1 where one of the first known mentions of renaming the prefix to thwart attacks was published.
Vulnerabilities have been consistently squashed since WordPress’ 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 the 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 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 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 reupload 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:
wp to whatever you want, but it’s recommended that you keep the underscore after it intact. Your new prefix can contain letters, numbers and even underscores. For example, you could choose something like
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 into 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:
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 usermeta 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 infiltrate 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.
If you would rather not touch any code, you can use our Defender plugin to update your database table prefix in one click and to also secure your site further to make sure you don’t get burned by hackers in the future.
To change your prefix in Defender, install and activate it, create a full backup of your site, then go to Defender > Hardener and click on the Change default database prefix bar to reveal more information.
You should see some more information and an option dynamically appear.
Enter the new prefix you want to include and update in your database, then click the Update button. That’s all you need to do. At this point, Defender works to change your database and wp-config.php file for you and it should only take a couple seconds.
When the job’s done, you’ll see the option moved to the Resolved section toward the bottom of the page. Be sure to create another backup when you’re done.
If you run into troubles along the way, feel completely free to ask our support team. After all, that’s what they’re there for and there’s someone available to help you 24/7. If you need to ask a question in the middle of the night, don’t worry, we don’t mind one bit!