Cogito ergo sum

How to change the prefix of WordPress database tables using MySql in phpMyAdmin

For a long time, people have thought that changing the prefixes of WordPress database tables will prevent hackers from performing SQL injection on the database, or hacking the website. Well, that’s a wrong thought and it has been explained very well in this post by Wordfence (DISCLAIMER, I’m not a Wordfence affiliate, but I’m one of their customers and I must admit since I have installed the premium version of the plugin on websites I manage, my life has become easier and I don’t have to worry about security).

Anyway, if you still believe that changing the prefix can be beneficial in securing your website, or you need to change the prefix for whatever reason, then this tutorial will come in handy!

Since I want to make this happen with minimal human intervention, I have created a stored procedure to perform the name change. All you need is to provide the database name, the old and new table prefix.  You can copy and paste the following code in the SQL editor of the phpMyAdmin that belongs to your WordPress database. Please don’t forget to change the values of database_name , old_prefix and new_prefix  before executing the stored procedure.

Remember to :

  1. Take a backup of your database before executing the stored procedure. 
  2. Change the prefix of your tables may break some plugins since some plugins store the table names once they are installed. Make sure to search through your database for any possible statically mentioned table names with the old prefix.
  3. Change the value of the variable $table_prefix in wp-config.php to match the value you have chosen in the SQL script above.
use information_schema;

DROP PROCEDURE IF EXISTS change_wp_tables_prefix ;
DELIMITER $$

CREATE procedure change_wp_tables_prefix()
BEGIN
    # Replace YOU_DATABASE_NAME with the correct database name
    DECLARE database_name VARCHAR(100)  DEFAULT 'YOU_DATABASE_NAME';
    #The currently used prefix in the WordPress database, change it if needed 
    DECLARE old_prefix VARCHAR(50)  DEFAULT 'wp_';
    
    #The wanted prefix, change it if needed 
    #DECLARE new_prefix VARCHAR(50)  DEFAULT 'scnl_';

    DECLARE done INT DEFAULT FALSE;
    DECLARE old_table_name VARCHAR(100)  DEFAULT '';
    DECLARE new_table_name VARCHAR(100)  DEFAULT '';
    
    DECLARE table_names CURSOR FOR SELECT TABLE_NAME 
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = database_name; 
 	
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN table_names;
        allTables: LOOP 
            FETCH table_names INTO old_table_name;
            IF done = TRUE THEN
                LEAVE allTables;
            END IF;
            SET new_table_name = REPLACE (old_table_name,old_prefix,new_prefix);
			SET @rename_statment = CONCAT('RENAME TABLE ', database_name,'.',old_table_name,' TO ',new_table_name);
            PREPARE prepared_stmt FROM @rename_statment;
            EXECUTE prepared_stmt;
            DEALLOCATE PREPARE prepared_stmt;
        END LOOP allTables;
     CLOSE table_names;
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = database_name;     
# perform this to get the table names with the new prefix.
SELECT rename_statment;
END$$
DELIMITER ;

The stored procedure can be used with any other MySql database; it’s not only for WordPress. However, WordPress is specified here because I needed to perform the name change on a WordPress database.

Thanks for reading, and I hope you enjoyed this article! Don’t hesitate to ask me any questions 🙂

About the author

Peshmerge Morad

Data Science student and a software engineer whose interests span multiple fields.

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Cogito ergo sum