Skip to content

[WordPress] Change the table prefix to improve security

WordPress is an extremely popular website builder tool, so much so that there are many automatic scripts that try to hack WordPress database tables. By default WordPress create several tables which all have the prefix “wp_”. If the prefix remains the same, this represents a significant security risk.

This article presents a simple method to change this prefix (if it was not done during installation) and thus improve the security of a WordPress site.

Change of prefix in WordPress tables

Risks of leaving prefixes by default

Hackers use automated scripts that attempt SQL injections on known vulnerabilities in WordPress or certain WordPress plugins. Knowing that the database contains virtually all of the site’s information, these flaws can be exploited for one of the following reasons:

  • Add content to the site (example: links to illegal sites).
  • Add spam comments
  • Destroy site content
  • Crash the site
  • Extract information (retrieve the list of emails from people who left a comment)

Knowing that scripts are automated, they attempt the most probable requests possible. In other words, the scripts generally use SQL injections with the name of the tables which have the default prefix (see “wp_”). By leaving the table prefixes by default, this indirectly facilitates the work of these hackers.

Which prefix to use?

Of course, we have just understood that the default prefix is ​​to be avoided, but then which prefix to use? Using a name that is too common is not very secure either. For example, a prefix such as “mysite_” can be too easily guessed. To really complicate the life of hackers it is better to use a prefix mixing characters and numbers, such as “fym39dtz_”.

Tip: by using a prefix that starts with “wordpress” (then other characters) it is easier to recognize tables that match the WordPress installation in case your database contains other tables that are not related to this CMS. For example “wordpress_fym39dtz_”.

Warning : as Julio from @BoiteAWeb (thanks to him), it is not recommended to use a prefix that continues to start with “wp_”, even if the prefix is ​​“wp_fym39dtz_”. Indeed, there may be concerns if you have several WordPress “wp_site1_”, “wp_site2_”, “wp_site3_” and “wp_” installations and you want to use a plugin that changes the prefix by executing a request such as:

... WHERE table LIKE 'wp_%'

Preface: perform a backup

Before attempting to modify the database, it is advisable to make a backup (cf. a backup) of the entire database. It is possible to save tables easily in PhpMyAdmin.

Edit the wp-config.php file

Before making any changes in the database you just need to make a small modification in the wp-config.php file which is located at the root of the WordPress installation. This file contains a variable which indicates to the PHP code the prefix of the tables. The line looks like this:

$table_prefix = 'wp_';

This code should be replaced with the prefix of your choice. For the purposes of the tutorial, the prefix will be “fym39dtz_” and the line will therefore become:

$table_prefix = 'fym39dtz_';

You must then remember to save the modification and send it to the server with your usual FTP software.

Change the name of the tables

To modify the name of the tables it is possible to modify the name thanks to the interface of PhpMyAdmin but it is a bit long. To save time, simply copy / paste the SQL queries below (adapting the prefix to your needs):

RENAME TABLE `wp_commentmeta` TO `fym39dtz_commentmeta`;
RENAME TABLE `wp_comments` TO `fym39dtz_comments`;
RENAME TABLE `wp_links` TO `fym39dtz_links`;
RENAME TABLE `wp_options` TO `fym39dtz_options`;
RENAME TABLE `wp_postmeta` TO `fym39dtz_postmeta`;
RENAME TABLE `wp_posts` TO `fym39dtz_posts`;
RENAME TABLE `wp_termmeta` TO `fym39dtz_termmeta`;
RENAME TABLE `wp_terms` TO `fym39dtz_terms`;
RENAME TABLE `wp_term_relationships` TO `fym39dtz_term_relationships`;
RENAME TABLE `wp_term_taxonomy` TO `fym39dtz_term_taxonomy`;
RENAME TABLE `wp_usermeta` TO `fym39dtz_usermeta`;
RENAME TABLE `wp_users` TO `fym39dtz_users`;

Multi-site features enabled

If the multi-site function of WordPress is activated, there are some additional tables which also deserve to have a suitable prefix. In this case, you should run these lines:

RENAME TABLE `wp_blogs` TO `fym39dtz_blogs`;
RENAME TABLE `wp_blog_versions` TO `fym39dtz_blog_versions`;
RENAME TABLE `wp_registration_log` TO `fym39dtz_registration_log`;
RENAME TABLE `wp_signups` TO `fym39dtz_signups`;
RENAME TABLE `wp_site` TO `fym39dtz_site`;
RENAME TABLE `wp_sitecategories` TO `fym39dtz_sitecategories`;
RENAME TABLE `wp_sitemeta` TO `fym39dtz_sitemeta`;

Modify the data of certain tables

In addition to changing the name of the tables, it is also necessary to modify certain data contained in the WordPress tables to ensure the proper functioning of the site.

Basic queries

To modify the data of a simple WordPress installation, it is necessary to perform a few queries.

Modify the contents of the (newly renamed) wp_options table:

UPDATE `fym39dtz_options`
SET `option_name` = REPLACE( option_name, 'wp_', 'fym39dtz_' )
WHERE `option_name` LIKE 'wp_%';

This request will normally modify at least one record (see the value of “wp_user_roles”).

Modify the content of the (newly renamed) wp_usermeta table:

UPDATE `fym39dtz_usermeta`
SET `meta_key` = REPLACE( meta_key, 'wp_', 'fym39dtz_')
WHERE `meta_key` LIKE 'wp_%';

This request will normally modify at least 3 records (including “wp_capabilities” or “wp_user_level”).

Find other data to modify

It is not impossible that some plugins have created other tables or inserted data into the default WordPress tables. The simplest solution to detect the other data to modify is to connect to the interface of PhpMyAdmin and to manually search for the tables which start with “wp_”. Tables that start with “wp_” have probably been added by some plugin. You have to rename the tables manually using the “Operation” tab of PhpMyAdmin.

Then, the idea is to use the “Search” tab (or “Search” if the interface is in English) of PhpMyAdmin. This tab allows you to search for a term contained in the records by searching on all the tables in the database. We must then look for the term “wp_”. If there are records that contain the term “wp_” then the result will indicate so. The records concerned must then be modified one by one.

Last step: make a last backup and test

Before finishing it is necessary to think of creating a fresh backup of your new base to be able to restore your site if necessary. Be careful, avoid overwriting the first backup which can still be used.

Finally, we must perform site tests. You have to make sure that the site is working properly. Here is a small non-exhaustive list of features to test on your site:

  • Test the different types of pages on the site (home page, classic page, article, category page, etc.)
  • Test the pages of the administrator interface
  • Try to add a comment on an article or send a message on a form (if applicable)
  • Carefully test the site’s plugins. These are likely to have been impacted by the modification of the prefix (it also depends on the quality of the plugin code)