911 Hack Removal

How to Secure WordPress Database?

Updated on: June 20, 2023

How to Secure WordPress Database?

Article Summary

For any WordPress website the database is the biggest asset. It stores all the crucial information of a website i.e. user info, site URLs, posts, pages, comments, custom fields, etc. An improperly secured WordPress database is like an open invitation to hackers. In this article, we will discuss how to secure WordPress database.

To efficiently secure WordPress database we should know How WordPress uses the database, the database vulnerabilities that are frequently exploited by hackers and how these vulnerabilities can be avoided.

How WordPress uses Database?

WordPress uses PHP as a scripting language (to store and retrieve data from database) and MySQL is used for database management, using SQL queries within the PHP markup. For example, if you log into a WordPress powered business website, It is SQL that actually logs you in, extracts your user ID and validates it, and ensures that the correct profile data is displayed on the front-end.

PHP and SQL work hand-in-hand. This helps WordPress to create a dynamic content-based experience for users. It allows you to customize content specific to certain users, such as admins, editors, and subscribers.

Plugins and themes also use WordPress database to store data. They use SQL within PHP markup to query the database and output content dynamically. Other plugins like WP-DB Manager, can be used to easily manage the database.

Related article: 15 WordPress configuration tricks to save your time

Get the ultimate WordPress security checklist with 300+ test parameters

How to secure WordPress Database?

WordPress Database is the brain of a WordPress website as it stores all the information about and on the website like posts, pages, comments, tags, users, categories, custom fields, and other site options. This makes it a juicy target for malicious actors. Spammers and hackers run automated codes for SQL injections. Here is how you can secure the WordPress database.

Related article: 25 Best WordPress Security Practices

1. Change Administrator Username

Like every other CMS, WordPress also has a default administrator login. Not changing the default admin login makes it easier for malicious actors to illegitimately gain access to your website and database.

In WordPress the default username is admin. Change it now if you haven’t already.

  1. Go to phpMyAdmin.
  2. Run the following query. This changes the username from admin to anything.
    UPDATE {database_prefix}users SET user_login='anything' WHERE user_login='admin';
  3. In case of a WordPress multisite, you can use the grant_super_admin() function to grant super admin access and super admin privileges.

2. Change Administrator ID

In WordPress, the default admin name is admin and default admin user ID is 1. Many SQL-injection attacks have exploited this. So, you should change this in the earliest to secure WordPress database. To change the admin user ID,

  1. Go to phpMyAdmin
  2. Run the following queries.
UPDATE wp_users SET ID = 2807 WHERE ID = 1; UPDATE wp_posts SET post_author = 2807 WHERE post_author = 1; UPDATE wp_comments SET user_id = 2807 WHERE user_id = 1; UPDATE wp_usermeta SET user_id = 2807 WHERE user_id = 1; ALTER TABLE wp_users AUTO_INCREMENT = 2808

TIP: It is highly recommended that you create a back up before making any changes. And, see the working of the website after each change to ensure that the alterations are not interfering with any other site function.

3. In the case of WordPress multisite, you have to duplicate a few lines for each site of the network like this:

UPDATE wp_2_posts SET post_author = 5487 WHERE post_author = 1; UPDATE wp_2_comments SET user_id = 5487 WHERE user_id = 1;

You could also run the queries in the loop using PHP and $wpdb.

3. Change Database Prefix

The default WordPress Database prefix is wp_ . For a secure WordPress database, it is highly recommended that you change the default prefix during the WordPress installation process itself.

secure wordpress database
Change WordPress Database Prefix

If you haven’t already, do it now. Follow these steps:

  1. Access your website through an FTP client
  2. Navigate to your configuration file wp_config.php in the root directory
  3. Find the line with wp_ prefix and change it.
    $table_prefix = 'wp_ga2807_';
  4. Rename all default WordPress database tables.
    RENAME TABLE wp_comments TO wp_ga2807_comments;
    RENAME TABLE wp_commentmeta TO wp_ga2807_commentmeta;
    RENAME TABLE wp_links TO wp_ga2807_links;
    RENAME TABLE wp_options TO wp_ga2807_options;
    RENAME TABLE wp_postmeta TO wp_ga2807_postmeta;
    RENAME TABLE wp_posts TO wp_ga2807_posts;
    RENAME TABLE wp_terms TO wp_ga2807_terms;
    RENAME TABLE wp_termmeta TO wp_ga2807_termmeta;
    RENAME TABLE wp_term_relationships TO wp_ga2807_term_relationships;
    RENAME TABLE wp_term_taxonomy TO wp_ga2807_term_taxonomy;
    RENAME TABLE wp_usermeta TO wp_ga2807_usermeta;
    RENAME TABLE wp_users TO wp_ga2807_users;
  5. Replace all instances of wp_ in wp_ga2807_usermeta and wp_ga2807_options (former wp_usermeta and wp_options tables respectively).
    UPDATE wp_ga2807_options SET option_name = REPLACE(option_name, 'wp_', 'wp_ga2807_') WHERE option_name LIKE 'wp_%';
    UPDATE wp_ga2807_usermeta SET meta_key = REPLACE(meta_key, 'wp_', 'wp_ga2807_') WHERE meta_key LIKE 'wp_%';
  6. Plugins create their own tables in the database. It is important to rename these tables as well. For example WooCommerce is a widely used WordPress plug-in. To rename tables in WooCommerce run the following queries.
    RENAME TABLE wp_woocommerce_api_keys TO wp_ga2807_woocommerce_api_keys;
    RENAME TABLE wp_woocommerce_attribute_taxonomies TO wp_ga2807_woocommerce_attribute_taxonomies;
    RENAME TABLE wp_woocommerce_downloadable_product_permissions TO wp_ga2807_woocommerce_downloadable_product_permissions;
    RENAME TABLE wp_woocommerce_order_itemmeta TO wp_ga2807_woocommerce_order_itemmeta;
    RENAME TABLE wp_woocommerce_order_items TO wp_ga2807_woocommerce_order_items;
    RENAME TABLE wp_woocommerce_payment_tokenmeta TO wp_ga2807_woocommerce_payment_tokenmeta;
    RENAME TABLE wp_woocommerce_payment_tokens TO wp_ga2807_woocommerce_payment_tokens;
    RENAME TABLE wp_woocommerce_sessions TO wp_ga2807_woocommerce_sessions;

Note: You can change the database prefix with the help of a plugin as well. ‘Change Table Prefix’ is a convenient plugin for this.

4. Strict Database User Privileges

Strict user privileges better secure WordPress database. MySQL user specified in the wp-config.php file should have strict privileges. During installation, database user has all privileges to set necessary tables and objects but it should be a temporary measure. After installation, the MySQL user needs only DATA READ and DATA WRITE privileges.

5. Create Backups

Create back up before making any changes to your website or database. I repeat, create back up! Creating regular backups can be very helpful in case of an infection. You can simply restore the backup and remove the infected files.

But even with the back up restored the vulnerability that the attacker exploited remains unaddressed. You can signup for the Astra vulnerability assessment and penetration testing to uncover all the hidden backdoors and security vulnerabilities in your website.

6. Delete Custom Tables

It is recommended that you delete custom tables from your database after removing a website extension from your site, otherwise over the lifetime you’ll collect a heap of unused tables in your database. Some plugins do come with the option to auto-delete all its data from the website and database when you uninstall it.

Note: Delete the tables only for plugins that you won’t install again for sure because once the custom tables are deleted there is no going back.

Related article: 7 common WordPress mistakes that everyone makes

On average, a website is attacked by malware 44 times per day. Safeguard yours now with the Intelligent Firewall and Malware Scanner.

See Pricing
Join thousands of sites that trust Astra to manage their security.

Conclusion

Even with all the correct measures, 100% security can not be guaranteed. Hackers are finding new ways to gain access to website and databases. Installing a firewall above all is the best way you can save your WordPress from any coming threats requests. With Astra’s Firewall taking care of your website’s security you can direct your focus to grow your business.

Found this article helpful? Share it with your friends on Facebook, Twitter, LinkedIn.

Mahima Maheshwari

She is an Embedded Systems Engineer and a cybersecurity enthusiast. She spends most of her free time researching & reading. And loves to spread knowledge through blogs.
Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments

Psst! Hi there. We’re Astra.

We make security simple and hassle-free for thousands
of websites and businesses worldwide.

Our suite of security products include a vulnerability scanner, firewall, malware scanner and pentests to protect your site from the evil forces on the internet, even when you sleep.

earth spiders cards bugs spiders

Made with ❤️ in USA France India Germany