How Secure is your WordPress Database?

Database is the base component for any WordPress website. It stores all the important information of a website like user details, site URLs, blog posts, pages, comments, custom details, etc. Let’s learn, how WordPress uses the database, the database key elements & its vulnerabilities that are very prone to be exploited in uncontrolled environment and how these vulnerabilities can be fixed.

Database role in WordPress site

WordPress sites use a database to store and retrieve the content to display various information. More precisely, WordPress sites uses a database that stores your website’s data into tables, rows and columns. In fact, without default database, no WordPress sites can have its existence. WordPress is purely a database driven; open-source CRM coded in PHP. WordPress uses MySQL database, which is a popular open-source relational database management system (RDBMS) that is very closely coupled with WordPress.

WordPress when gets installed by default, the database is created automatically. Although you can customize database installation like using a database that already exist.

Default Database structure and tables

While installing WordPress, it asks for basic information like database name, username, and password. This information gets stored in WordPress wp-config.php file. By default, with every database installation, the following 12 database tables are created, and default content is stored inside them.

• wp_users & wp_usermeta: table contains list of users on your WordPress website and its metadata information about registered users. Metadata like unique user ID, meta key, meta value.
• wp_terms & wp_termmeta: contains the categories for posts and links along with their tags & metadata information.
• wp_term_relationships: This table defines the relationships between taxonomies and post types.
• wp_term_taxonomy: This table differentiates between taxonomies (category, tag and link) for entries in wp_terms.
• wp_posts & wp_postmeta: This table stores all posts, pages and custom post types entries and their metadata.
• wp_comments & wp_commentmeta: All the comments along with their metadata are stored in this table.
• wp_links: This table was used to include blog roll links in earlier WordPress versions.
• wp_options: This table contains site settings, activated plugins, time format, admin email, etc.

How do I access WordPress Database?

You can access the WordPress database through PhpMyAdmin tool available at your cPanel web hosting system. Whenever you want to reset, repair or optimize your database, you can connect via PhpMyAdmin tool which allows you to manage the MySQL database of your website.

7 important tips to secure your WordPress Database

1. Change Administrator ID & Username: First things first; WordPress by default create an Administrator Login and it is highly recommended to change the default admin to more specific user name. In WordPress, default admin name is ‘admin’ & default admin user id is 1; change it now if you haven’t already. So, let’s take the backup of your database before any updating activity and you are ready to open phpMyAdmin and execute the following queries.

   
   In the case of WordPress multisite, you have to run few extra queries for each site of the network like:

2. Change Database Table Prefix: When you install the WordPress, by default WordPress Database & its tables, defined with prefix as ‘wp_’. If everybody knows that there is predefined naming convention in the database tables, anyone with bad intension & skills, can misuse this information. Moreover, there is no point in keeping the tables with predefined prefix as ‘wp_’ when we can make them more secure. It is highly recommended that you change the default prefix during the WordPress installation process itself.If you missed that part and already installed your WordPress & database, still you can change the prefix by following these steps:
   1. Manually changing the table prefix: Access your website through an FTP client, navigate to your configuration file ‘wp_config.php’ in the root directory and change the line containing ‘wp_’ prefix like:$table_prefix = ‘vin_107_’;

      

      All default WordPress database tables prefix can be changed by following SQL query:

      RENAME TABLE wp_comments TO vin_107_comments,
      wp_commentmeta TO vin_107_commentmeta,
      wp_links TO vin_107_links,
      wp_options TO vin_107_options,
      wp_postmeta TO vin_107_postmeta,
      wp_posts TO vin_107_posts,
      wp_terms TO vin_107_terms,
      wp_termmeta TO vin_107_termmeta,
      wp_term_relationships TO vin_107_term_relationships,
      wp_term_taxonomy TO vin_107_term_taxonomy,
      wp_usermeta TO vin_107_usermeta,
      wp_users TO vin_107_users;

      Replace all instances of wp_ in vin_107_usermeta and vin_107_options (earlier these tables were named as wp_usermeta and wp_options).

      UPDATE vin_107_options SET option_name = REPLACE(option_name, ‘wp_’, ‘vin_107_’) WHERE option_name LIKE ‘wp_%’;

      UPDATE vin_107_usermeta SET meta_key = REPLACE(meta_key, ‘wp_’, ‘vin_107_’) WHERE meta_key LIKE ‘wp_%’;

   2. Change table prefix through Plugins: You can change the database table prefix with the help of a plugin as well. ‘iThemeSecurity Pro’ is a convenient plugin for this.
3. Backup Your Database: Just imagine the hours of work that you’d need to put into rebuilding your entire website from the scratch, in case it becomes completely inaccessible. In addition to that, all of the business you’ll lose during the time your website isn’t functional. A timely backup of your database is the sure shot guarantee that your site can be completely restored. With a proper backup strategy & backup policy, you’ll be able to fully restore the most recent working version of your website, just prior to the incident. WordPress database backup is the most important aspect that you must consider to handle all these inevitable and devastating problems. And it can be done within a few clicks. You can also utilize the services of popular backup plugin like ‘Backup Buddy’ for its comprehensive backup features.
4. Database User Privileges: MySQL user specified in the ‘wp-config.php’ file should have strict privileges. For various website operations, such as blog posts, uploading media files, creating new WordPress users, posting comments and after installing WordPress plugins, the MySQL user needs only DATA READ and DATA WRITE access to the MySQL databases. After configuration and setup of database, the MySQL user does not need to modify the database structure or any other type of access permissions on the database. Strict user privileges are the way forward for a secure WordPress database.
5. Use a Strong Password: Whenever any site gets hacked, weak password is one of the culprits that needs major attention from the administrator of the sites.Many a times, the common mistakes users make while choosing their passwords, are to rely on something they can easily remember like their phone numbers or simple obvious strings like birthdays, vehicle number, pets’ names, hometowns or even the names of your family members. Moreover, as a general human tendency, peoples use same set of passwords for multiple sites including bank accounts, which is very dangerous.You should always use a strong password for your WordPress administrator account. A strong password is a minimum of 12 characters, using a combination of alphanumeric and ASCII characters. Using only lower-case letters limits the pool of possible characters to 26, so it is vital to include alphanumeric, upper-case letters and common ASCII special characters to increase the pool of characters needed to crack the password to 92.To make a password truly secure, even more characters or more than one uppercase letter, number or symbol can be added. A twelve-character password with one uppercase letter, one number and one symbol, is almost unbreakable, taking a computer 34,000 years to crack. Don’t believe, google it. Passwords exist as a key to your website, quite literally.
6. Prevent SQL injections in WordPress: SQL injection is a popular hack terminology through which malicious code get inserted into websites by targeting & compromise the SQL database which holds the confidential information. As the name suggests, SQL injection attacks SQL databases, the backbone of a website.The loopholes in the website coding are the invitation to the hackers to abuse the websites. These attacks are very common because most of website’s database are based on SQL and all these websites have input fields to collect information from the visitors. An attacker can easily abuse the input fields by inserting malicious line of code that could execute SQL commands and can create, retrieve, update, and even delete the data in the database.We must take following proactive measures to minimize the risks of security threats against SQL injections.
   1. Choose your WordPress hosting provider that protect your site against malicious attacks.
   2. Scan for SQL Injection Vulnerabilities through various online tools like WordPress Security Scan, Sucuri SiteCheck etc.
   3. Ensure the latest version update for WordPress core, PHP, themes and plugins
7. Database Optimization: Database is the backbone for any Website and it require better optimization for faster response times. Each action performed in WordPress is connected directly or indirectly with its database: from themes to plugins and comments to posts everything. Optimization of WordPress Database has its benefits like improved performance and user experience. You can utilize your phpMyAdmin tool for the optimization. Database tables can be optimized by running a simple SQL query likeOptimize Table ‘wp_posts’;Next, you can clean the useless Old Plugins data. Similarly, the posts data can also be cleaned up using ‘Delete’ SQL queries likeDelete from wp_postmeta where meta_key = ‘META-KEY-NAME’;you can also define the number of revisions in wp-config.php file, to be allowed for the posts.

   define (‘WP_POST_REVISIONS’,3);

   It is also recommended to remove Spam comments, Unused Tags, Pingbacks & Trackbacks etc. using SQL queries.

   DELETE FROM wp_comments WHERE comment_type = ‘pingback’;

   DELETE FROM wp_comments WHERE comment_type = ‘trackback’;

   Optimizing your WordPress database can give a boost in performance to any website, regardless of having problems with your dashboard.

Summary

For any WordPress website the database is the important component. A loosely secured WordPress database is highly prone to the hacking incidents and it’s like an open invitation to hackers.

Even with all our best efforts & corrective measures, 100% security cannot be guaranteed but we can be confident enough to acknowledge & address the threats (if any). Hackers keep on finding new ways to gain access to website and databases, similarly we must evolve ourselves for the latest challenges.

All above security measures, you can consider installing a firewall for an additional level of security which can shield your WordPress system as a whole, from any potential threats.

With WordPromise comprehensive security plan which offers genuine care for your website’s security so that you can primarily focus on your core business.

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