Change Your Database Prefix to Improve Security
One of the awesome things about WordPress is that it’s a dynamic publishing system that uses a database to store your site’s information: posts, options, plugin and theme settings – all of this data is stored in your site’s database. It’s like the brain of your WordPress installation.
Unfortunately the WordPress database is also a prime target in many website attacks. Spammers and other bad guys target various database tables with automated scripts, SQL injection, and other malicious code. Needless to say it’s critical to protect your database and keep recent backups. One of the smartest ways to protect your site’s database is to change the default table prefix to something obscure and difficult to guess. Sort of like a password.
Default WP database tables
By default, during installation, WordPress creates the database with all of the tables prefixed with “wp_”. There are 11 tables created in the default installation procedure, and all of them will prefixed with wp_:
Install WordPress out-of-the-box and that’s what you’re going to get. And would-be attackers understand this perfectly. Automated scripts that target the WordPress database aim for these default table names during their attacks. I think it’s fair to assume that a vast majority of WordPress databases are using the default wp_ prefix. This is bad because it makes attacking WordPress sites easier for the bad guys.
Fortunately you can improve your site’s security by changing the default table prefix to something completely random and unique. There are two ways to change your database prefix: the easy way and the hard way. Which you use will depend on if you’ve already installed your WordPress site or not..
Changing default table prefix before installing WordPress
First let’s look at the easy way. Before installing WordPress, while configuring the wp-config.php configuration file with your database credentials, scroll down the file a bit until you see this:
/**
* WordPress Database Table prefix.
*
* You can have multiple installations in one database if you give each a unique
* prefix. Only numbers, letters, and underscores please!
*/
$table_prefix = 'wp_';
Just replace the “wp_” with a string of random, unique characters and you’re all set: continue with the installation as normal and your database prefix will have been changed to something more secure. Here’s an example of a strong database prefix generated at Random.org:
wp_VzQCxSJv7uL_
Notice two things that will help keep your database nice and organized:
- begin the prefix with “
wp_” to keep things orderly - end the prefix with an underscore (“
_”) so the actual table names (e.g.,posts,users,meta) stand out and are easily recognizable.
But really you can use whatever prefix you want – the take-home message here is that you should obscure your tables’ prefix and it’s easiest to do before installing WordPress.
But wait! I’ve already installed WordPress and have been using it for all sorts of stuff.. is it still possible to change my prefix? Absolutely there is, but it takes quite a bit more time to get it done.
Changing default table prefix after installing WordPress
If you’ve already installed WordPress and want to change your database prefix, you’re stuck with the hard way. But it’s really not that hard, just hard compared to changing a single line in your wp-config.php (as shown above). To change your prefix after installing, set aside around ten minutes and follow these steps:
Step 1: Preparations
Before changing your table prefix, make sure you have a recent backup and about 10 minutes of downtime for your site. It may be a good idea to redirect visitors to a temporary maintenance page.
Step 2: Change table prefix
Change your database table prefix in wp-config.php from wp_ to something more secure, like wp_VzQCxSJv7uL_ or something.
Step 3: Change all WordPress database tables
Go to your database (using phpMyAdmin or whatever) and rename all WordPress table prefixes from wp_ to whatever you specified in your wp-config.php file. Here are SQL commands to rename the 11 default WP tables:
RENAME table `wp_commentmeta` TO `wp_VzQCxSJv7uL_commentmeta`;
RENAME table `wp_comments` TO `wp_VzQCxSJv7uL_comments`;
RENAME table `wp_links` TO `wp_VzQCxSJv7uL_links`;
RENAME table `wp_options` TO `wp_VzQCxSJv7uL_options`;
RENAME table `wp_postmeta` TO `wp_VzQCxSJv7uL_postmeta`;
RENAME table `wp_posts` TO `wp_VzQCxSJv7uL_posts`;
RENAME table `wp_terms` TO `wp_VzQCxSJv7uL_terms`;
RENAME table `wp_term_relationships` TO `wp_VzQCxSJv7uL_term_relationships`;
RENAME table `wp_term_taxonomy` TO `wp_VzQCxSJv7uL_term_taxonomy`;
RENAME table `wp_usermeta` TO `wp_VzQCxSJv7uL_usermeta`;
RENAME table `wp_users` TO `wp_VzQCxSJv7uL_users`;If there are other WordPress-related tables from plugins or whatever, just rename them too. The goal here is to rename all of the tables that begin with the default prefix. If you’re using something like phpMyAdmin to interface with your database, you can execute multiple commands at the same time, so edit the above code with your table prefix, paste it into the SQL field, and WHAM! – all tables changed in the blink of an eye.
Step 4: Edit the WordPress options table
Now search the options table for any instances of the old prefix. To do this, enter the following SQL query:
SELECT * FROM `wp_VzQCxSJv7uL_options` WHERE `option_name` LIKE '%wp_%'That search will return the wp_user_roles option along with any other options created by plugins, custom scripts, etc. The goal here is to rename any options that begin with wp_ to the new prefix.
Step 5: Edit the usermeta table
Now search the usermeta for all instances of the old wp_ prefix. Here is an SQL command to accomplish this:
SELECT * FROM `wp_VzQCxSJv7uL_usermeta` WHERE `meta_key` LIKE '%wp_%'Executing that query on a recently installed WordPress database, the following usermeta fields were returned:
The number of fields that you need to rename may vary depending on plugins and other factors, but as before, just remember to rename any entry that begins with the default WordPress table prefix, wp_.
Final Step: Test, backup, and done!
Ideally at this point, all instances of the old table prefix (wp_) have been replaced with the new (wp_VzQCxSJv7uL_ in our example). Once this is done, go check your site for proper functionality. Test the Admin, pages, posts, search, and everything else you can think of (or have time for). If your site seems to be working as before, chances are good that the surgery was a success. Now make another database backup for good measure.
Want more SQL recipes like this one? Check out Wizard’s SQL Recipes for WordPress — includes an entire chapter on optimizing the WP database!
Wrap Up
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.
About the author
Jeff Starr is a professional web developer and book author with over 15 years of experience. Jeff works with WordPress every day, designing themes, developing plugins, and securing sites. His books include Digging Into WordPress, WordPress Themes In Depth, and The Tao of WordPress.
31 responses
-
I’ve heard this a number of times, and even practice it myself, but this seems kind of like putting a bandaid on a broken arm. Security through obscurity is (largely) a lost cause. If someone has access to your database, it likely won’t matter what you name your tables: you’re compromised. If someone doesn’t have access to your database, it doesn’t matter what you name your tables because they don’t have access to your database.
There are certainly exploits that work without proper access to the database (SQL injection, for instance), but wouldn’t it be far better to plug those exploits than hope no one guesses your table names? Or discovers them in another fashion; database table names aren’t exactly the most secure pieces of information.
-
Colin HelvensteijnI agree. Security through obscurity is no security at all. Once a system is compromised, no level of obscurity is going to do you any good.
Of course, there’s nothing inherently wrong about obscuring table names, but you’ll only be making it difficult for script kiddies, and for that, changing the prefix to
pw_would already suffice.The time and effort could better be spent making sure the code that accesses the database isn’t exploitable, and the system itself is secure.
-
Jeff Starr Post AuthorChanging your table prefix before installing WordPress takes a few seconds at most, and will improve security by protecting against automated database attacks. At least that’s my experience. As mentioned previously, this measure should be used as part of an overall security strategy that addresses larger issues.
-
Colin Helvensteijn
True, but as I mentioned, changing the prefix to something like
pw_(or getting rid of it altogether) would serve that purpose just as effectively, without making things unnecessarily difficult for yourself in case you need to manage the database from the command line. ;-)
-
-
-
Jeff Starr Post Author
Automated scripts target the WordPress database using the default table prefix,
wp_. Changing this to something else (anything really) protects your site against such attacks. Definitely worthwhile and effective. Of course no single security measure will protect against the determined hacker, but changing the default prefix will stop tons of the automated stuff.-
Okay, but your database should be secured so that it can’t be accessed by those scripts at all.
Last I checked, databases had usernames, passwords, limitation of actions based on user type (root vs. read-only), etc. etc. etc.. If someone’s accessing your database with a script, you should really rethink your database security structure. And if your database security structure is sound, the scripts (which changing the prefix protects against) won’t have access to your database to begin with.
-
-
-
Daniel15A little typo… I think this (under step 5):
SELECT * FROM `wp_usermeta` WHERE `meta_key` LIKE '%wp_%'Should be this:
SELECT * FROM `wp_VzQCxSJv7uL_usermeta` WHERE `meta_key` LIKE '%wp_%'-
Jeff Starr Post Author
Fixed! – Thank you! :)
-
Andrew NacinAlso, the
LIKE '%wp_%'should beLIKE 'wp_%', as you only want to match meta keys that start with wp_.On a large install, you’re going to have quite a bit of keys, so you’d probably want to automate that find/replace via SQL. And then multisite introduces a whole other complexity layer to it.
-
-
-
Martti LaineHuge thanks for this! I didn’t imagine it would be this hard to do this on a already running installation. Didn’t the old WordPress installation ask for prefix?
-
Jeff Starr Post Author
It’s always been possible to change the prefix for new installs using the
wp-config.phpfile, but I don’t recall WP ever asking for a custom prefix during installation..
-
-
Edward de LeauStep 6: take care of specific plugins or code you have written that targets specific tables e.g. my perl wp-delicious sync script that I am running for 6 years in cron without downtime…, where you also have to set the prefix in the perl script.
-
Julianis the value of
global $wpdb
$wpdb->prefixhave the same value of the changed prefix, for example
wp_VzQCxSJv7uL_or still have the default value of
wp_thanks
-
Jeff Starr Post Author
Correct, that is why plugins should use
$wpdb->prefixinstead of assumingwp_. Weird timing, I am currently editing a plugin for this very reason.
-
-
SleepydogI imagine changing the prefix for an existing Network install is too difficult to consider?
-
IsraelThis is the biggest fake security fail I have ever seen and a terrible idea.
First of all… anyone that is exploiting SQL Injection knows the command “show tables”………… which will display all your “annoying to work with” tables” that you tried so hard to hide.
Secondly.. just use prepared statements w/php and be SQL Injection immune.
-
Jeff Starr Post Author
Easy tiger, no reason to freak out about anything. If you don’t think changing the default prefix helps to improve security then don’t do it. It certainly isn’t going to hurt anything, if that’s your concern.
-
-
Matthew JuhlSorry, but this is not going to add any security what so ever. Anyone who can perform an SQL injection attack is smart enough to know how to see the table names in your DB. ‘SHOW TABLES’
-
Jeff Starr Post Author
Anyone who can perform an SQL injection attack is smart
So not true. And there are other types of attacks to consider. Changing your database prefix protects against any automated attacks that target default database names.
-
Matthew Juhl
Yeah but if a hacker can get in your DB to the point where all he needs is the table names he can get them with ease. It doesn’t matter what you name them. That functionality can easily be added to any automated attack. Consider this pseudocode for an automated attack:
foreach (tablenames as tablename)
executeQuery('drop ' + tablename)If you don’t want your site to get hijacked, you need to prevent SQL injection anyway, so changing your table names is pointless. If you’re worried about other attacks (which you should be), escape all your inputs and check session IDs to prevent XSRF.
Take an hour of your life and fix it the right way.
-
Jeff Starr Post Author
I hear where you’re coming from, but I have to disagree that changing your default prefix is pointless. Changing it will help protect your site against any attacks targeting the default table names. If that isn’t something you’re interested in, it’s perfectly okay.
-
I also wonder about the random plugin (and maybe even pseudo-smart themes) that might be reliant on or at least expecting that the tables be prefixed “wp_” by default.
Think it’s anything to consider?
Another question – Does WordPress.com or VIP do anything like this? – I’m guessing not.
-
Matthew Juhl
The point is that any attack on your site that can be performed using the table names can be performed just as easily without the table names with one simple, extra step.
If you are considering changing your table names for security reasons, there is a really good chance that your database is vulnerable to attacks. If you simply obfuscate your table names, you’re still susceptible!! It will be maybe 1% more difficult to hack app. So in other words, simply obfuscating your table names will still leave a giant gaping whole in your site’s security. If you fix this problem correctly, even if I knew every single detail about your DB it wouldn’t matter. Therefore, IF you correctly prevent SQL injection it doesn’t matter who knows what about your DB and changing your tables names really is 100% pointless. If you aren’t going to correctly prevent SQL injection, then, yes I guess it’s not pointless, but it’s like painting your house camouflage instead of putting locks on your doors.
I’m really not trying to be a jerk, but I just want to make sure that I’m making my point clear. Security is important. People get fired over it.
-
-
-
-
Another quick security fix is to relocate the config file to outside the root? WP can do this from vs2.6
-
That’s not for security and has very minimal security impact. The file is still going to be accessible via the server, which is going to be the point of entry. More important is to lock down the file permissions, though ultimately you are at the mercy of your host if the file permissions are accurate.
(Its real purpose, FWIW, is for using WordPress as an SVN external.)
-
-
JustinWow. Can I join in? Okay then, my junk is bigger than yours! I am a WP novice, but I will use anything, especially a free tip on security. DigWP.com isn’t saying this tip is a cure all, just an extra step at helping security.
Thanks DigWP.com.
-
Oliver R. JamesI am attempting to add your website’s Rss to my own feed reader but it is not working effectively, you could wish to check it out to ensure its working effectively.
-
Jeff Starr Post Author
It seems to be working for me in several different readers. Also opens fine in a browser. Note that we’re using FeedBurner for feed delivery, so you should see the feed formatted as such when viewing in a browser.
-
-
Toffsy HeartThanks for this great information. I made an article about WordPress website security and I’ve included a link to this post since I was talking about this subject, too. Keep the great work, Jeff ;)
-
Latkóczy DávidMany thanks for this post! I did the hard way, changing default table prefix after installing WordPress. After removal a site I guessed the problems are caused by some old prefix in db, but I didn’t know where are these.
-
Philip StancilGreat post! Thank you for this. It helped me tremendously as I am trying to archive old versions of the same website using the same database. Thank you, Jeff!
