How to Change the WordPress Database Prefix @ TwentySixForty

Tutorial Overview.

In this tutorial, I will explain a little about the WordPress database and what an SQL injection is. Then I will explain why it’s important to change the WordPress database prefix and how to do it.

Quickies.

About the WordPress Database.
What is an SQL Injection?
Why Does it Matter if I Change the Database Prefix?
How to Change the WordPress Database Prefix Upon Install.
How to Change the WordPress Database Prefix on a Live Website.
How to Change the WordPress Table Prefix in the Database.

About the WordPress Database.

WordPress uses MySQL as its database management system and it happens to be one of the things hackers love to target. The database is the brains of WordPress and without it, WordPress cannot function. By default, WordPress creates all of its database tables with a prefix of wp_. If hackers know the names of your database tables, it is much easier for them to attack your database using an SQL injection.

What is an SQL Injection?

First, SQL stands for Structured Query Language and it is a language used to interact with a database. An SQL injection is when someone enters a malicious SQL statement into an input field on a website and the statement is executed by the database. If a hacker gains access to your database they can basically do anything they want to your website or application. They can steal data, change, and create new credentials, impersonate users, and add, modify and/or delete anything in your database. This is why it is important to secure all forms of input on your website. For example, if you have an insecure form on your website, a hacker could enter a SQL statement into one of the fields that can create administration-level credentials. When the form submission reaches the database, the malicious code is executed and the hacker will gain admin-level access to your website.

Why Does it Matter if I Change the Database Prefix?

Here is a simple example of an SQL statement:

SELECT id FROM wp_users WHERE username=’username’ AND password=’password’ OR 2=2;

In this case, the important piece of this code is the FROM part. This is the part of the code that defines what table we are trying to access. Notice that the prefix of this table is wp_. If we can change the prefix to something that the hacker doesn’t know, they will not be able to access the database. If you change the prefix of your database to asdf_, and the hacker tried to run the above statement, the statement would fail because there is no table named wp_users in your database anymore.

How to Change the WordPress Database Prefix Upon Install.

The best and easiest time to change the WordPress database table prefix is at the time of installation. If you need to install WordPress check out this tutorial: How to Install WordPress.

As you are going through the WordPress installation process, change the Table Prefix field when you come to the screen below. The table prefix must be made of numbers, letters or underscores and nothing else.

WordPress database details section

How to Change the WordPress Database Prefix on a Live Website.

IMPORTANT: BE SURE TO BACKUP YOUR DATABASE BEFORE YOU MAKE ANY CHANGES TO IT. IF THESE CHANGES ARE MADE INCORRECTLY, YOUR WEBSITE WILL BREAK.

Open the wp-config.php file in a text editor. This file is located in the root directory of your website. You can do this via FTP: How to Make an FTP Connection with FileZilla. Or you can access it through cPanel.

wp-config file

Locate the $table_prefix variable.

wordpress database table prefix

Change the prefix and Save the file. Your website will no longer work at this point until you change all occurrences of it in the database.

rename table prefix

How to Change the WordPress Table Prefix in the Database.

Login to cPanel.
In the Database section click on phpMyAdmin.

databases

Click on the database. If you have multiple databases and don’t know which one belongs to your site, go back to the wp-config.php file and look for the DB_NAME field. This is the name of the database that your site is using.

wordpress database name

In phpMyAdmin, click on the correct database.
You are now looking at all of the tables that make up your WordPress database.

wordpress database tables

Click on SQL in the top navigation menu.

Enter the following SQL statement into the query box.

The SQL statements below are for the 12 default WordPress tables. If you have more tables, you need to create and run SQL statement for them.

RENAME TABLE wp_commentmeta TO asdf_commentmeta;
RENAME TABLE wp_comments TO asdf_comments;
RENAME TABLE wp_links TO asdf_links;
RENAME TABLE wp_options TO asdf_options;
RENAME TABLE wp_postmeta TO asdf_postmeta;
RENAME TABLE wp_posts TO asdf_posts;
RENAME TABLE wp_terms TO asdf_terms;
RENAME TABLE wp_termmeta TO asdf_termmeta;
RENAME TABLE wp_term_relationships TO asdf_term_relationships;
RENAME TABLE wp_term_taxonomy TO asdf_term_taxonomy;
RENAME TABLE wp_usermeta TO asdf_usermeta;
RENAME TABLE wp_users TO asdf_users;

run sql queries

If there are no errors in the statements, click Go.
I have Gravity Forms installed on this site so I am running these additional SQL statements.

database queries

Run additional statements to cover tables that you have not changed yet.

This is the basic structure of the statement you want to use. Replace current_name and new_name with your table info.

RENAME TABLE current_name TO new_name;

Check the Options Table For All Fields Using wp_.

After you change all of the table prefixes you need to check the options table for any references to wp_.

Run the following statement in the same box you just used to run the above queries. Adjust this statement to fit your new table prefix.

SELECT * FROM asdf_options WHERE option_name LIKE '%wp_%';

Go through each of these results and edit all of the ones starting with wp_ to your new prefix. This may take some time but it is critical that it is done.

Check the UserMeta Table For All Fields Using wp_.

After you change all of the table prefixes you need to check the usermeta table for all references to wp_.

Run the following statement in the same box you just used to run the above queries. Adjust this statement to fit your new table prefix.

SELECT * FROM asdf_usermeta WHERE meta_key LIKE '%wp_%';

Go through each of these results and change all of the ones starting with wp_ to your new prefix. This may take some time but it has to be done.

Additional Posts.

WordPress

How to Change the WordPress Database Prefix.