Day 2. Protect your WordPress Database

First of all – do not run multiple WordPress installations on the same database!

A very simple advice for you – do not run multiple WordPress installations on the same database. Really — if someone gets access to your database he gets access to all your websites. You can find this recommendation in official WordPress codex as well. This is not about WordPress Multisite because WP Multisite is a single WP install.

Why not Change Administrator ID in Database?

In the previous post of this series we changed and made hidden the administrator login. But what about the ID? Everyone knows that the main administrator is the user with ID=1.

So, I feel myself really uncomfortable when my administrator ID is 1. Many of the SQL-injections are based on this principle. So, let’s open phpMyAdmin and run the following queries (making backups before doing changes is highly recommended).

UPDATE wp_users SET ID = 5487 WHERE ID = 1;
UPDATE wp_posts SET post_author = 5487 WHERE post_author = 1;
UPDATE wp_comments SET user_id = 5487 WHERE user_id = 1;
UPDATE wp_usermeta SET user_id = 5487 WHERE user_id = 1;
ALTER TABLE wp_users AUTO_INCREMENT = 5488

The above code works pretty well for any WordPress. For the multisite your have to duplicate lines 2,3 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;

But what if you have hundreds, or even thouthands blogs in your multisite network? Well, I think in that case you could run the queries in the loop using PHP and $wpdb.

These changes won’t affect your superadmin permissions, which are connected to your login.

Of Course We Will Change Database Prefix Too

Best practice – do it during WordPress installation process

Look at the screenshot below — it is so simple — and nothing else you have to do.

The simplest way to change wp_ prefix is during installation process.
Here you can see the simplest way to change default wp_ prefix — during installation process. Just set it here

What if it is too late?.. I mean how to change wp_ prefix on existing WordPress website, maybe with WooCommerce store, and maybe even with Multisite install.

But what to do if your WordPress is already installed? Your website filled with the content and you can not just reinstall it. No panic, of course this way is a little more difficult but you can do it.

Step 1. wp-config.php

Please open your configuration file (yes, it is wp-config.php, you can find it directly in your WordPress installation folder) find the line with wp_ prefix and change it to anything you want.

$table_prefix  = 'wp_hj87ka_';

Step 2. Rename default database tables, and keys in wp_options and wp_usermeta

First of all, let’s rename all default WordPress database tables.

RENAME TABLE wp_comments TO wp_hj87ka_comments;
RENAME TABLE wp_commentmeta TO wp_hj87ka_commentmeta;
RENAME TABLE wp_links TO wp_hj87ka_links;
RENAME TABLE wp_options TO wp_hj87ka_options;
RENAME TABLE wp_postmeta TO wp_hj87ka_postmeta;
RENAME TABLE wp_posts TO wp_hj87ka_posts;
RENAME TABLE wp_terms TO wp_hj87ka_terms;
RENAME TABLE wp_termmeta TO wp_hj87ka_termmeta;
RENAME TABLE wp_term_relationships TO wp_hj87ka_term_relationships;
RENAME TABLE wp_term_taxonomy TO wp_hj87ka_term_taxonomy;
RENAME TABLE wp_usermeta TO wp_hj87ka_usermeta;
RENAME TABLE wp_users TO wp_hj87ka_users;

Second. Perform replacements in wp_hj87ka_usermeta and wp_hj87ka_options for any wp_ instances (former wp_usermeta and wp_options tables accordingly).

UPDATE wp_hj87ka_options SET option_name = REPLACE(option_name, 'wp_', 'wp_hj87ka_') WHERE option_name LIKE 'wp_%';
UPDATE wp_hj87ka_usermeta SET meta_key = REPLACE(meta_key, 'wp_', 'wp_hj87ka_') WHERE meta_key LIKE 'wp_%';

Step 3. Some additional queries for WooCommerce

Please, remember, each plugin you use can create its own tables in database. You have to rename these tables as well. WooCommerce is a widely used shopping plugin for WordPress, so I decided to mention queries for it.

RENAME TABLE wp_woocommerce_api_keys TO wp_hj87ka_woocommerce_api_keys;
RENAME TABLE wp_woocommerce_attribute_taxonomies TO wp_hj87ka_woocommerce_attribute_taxonomies;
RENAME TABLE wp_woocommerce_downloadable_product_permissions TO wp_hj87ka_woocommerce_downloadable_product_permissions;
RENAME TABLE wp_woocommerce_order_itemmeta TO wp_hj87ka_woocommerce_order_itemmeta;
RENAME TABLE wp_woocommerce_order_items TO wp_hj87ka_woocommerce_order_items;
RENAME TABLE wp_woocommerce_payment_tokenmeta TO wp_hj87ka_woocommerce_payment_tokenmeta;
RENAME TABLE wp_woocommerce_payment_tokens TO wp_hj87ka_woocommerce_payment_tokens;
RENAME TABLE wp_woocommerce_sessions TO wp_hj87ka_woocommerce_sessions;
RENAME TABLE wp_woocommerce_shipping_zones TO wp_hj87ka_woocommerce_shipping_zones;
RENAME TABLE wp_woocommerce_shipping_zone_locations TO wp_hj87ka_woocommerce_shipping_zone_locations;
RENAME TABLE wp_woocommerce_shipping_zone_methods TO wp_hj87ka_woocommerce_shipping_zone_methods;
RENAME TABLE wp_woocommerce_tax_rates TO wp_hj87ka_woocommerce_tax_rates;
RENAME TABLE wp_woocommerce_tax_rate_locations TO wp_hj87ka_woocommerce_tax_rate_locations;

Step 4. Ok, but what if you’re running WordPress Multisite?

More queries are coming :)

RENAME TABLE wp_blogs TO wp_hj87ka_blogs;
RENAME TABLE wp_blog_versions TO wp_hj87ka_blog_versions;
RENAME TABLE wp_registration_log TO wp_hj87ka_registration_log;
RENAME TABLE wp_site TO wp_hj87ka_site;
RENAME TABLE wp_signups TO wp_hj87ka_signups;
RENAME TABLE wp_sitemeta TO wp_hj87ka_sitemeta;

For each site of the network repeat the following replacements:

RENAME TABLE wp_2_comments TO wp_hj87ka_2_comments;
RENAME TABLE wp_2_commentmeta TO wp_hj87ka_2_commentmeta;
RENAME TABLE wp_2_links TO wp_hj87ka_2_links;
UPDATE wp_2_options SET option_name = REPLACE(option_name, 'wp_', 'wp_hj87ka_') WHERE option_name LIKE 'wp_%';
RENAME TABLE wp_2_options TO wp_hj87ka_2_options;
RENAME TABLE wp_2_postmeta TO wp_hj87ka_2_postmeta;
RENAME TABLE wp_2_posts TO wp_hj87ka_2_posts;
RENAME TABLE wp_2_terms TO wp_hj87ka_2_terms;
RENAME TABLE wp_2_termmeta TO wp_hj87ka_2_termmeta;
RENAME TABLE wp_2_term_relationships TO wp_hj87ka_2_term_relationships;
RENAME TABLE wp_2_term_taxonomy TO wp_hj87ka_2_term_taxonomy;

Add here WooCommerce queries for each site of the network where WooCommerce is installed.

Step 5. Some additional queries for my Multisite plugin

If you use this plugin for your Multisite Network and now you want to change the database prefix, don’t forget to run the following queries as well.

RENAME TABLE wp_network_log TO wp_hj87ka_network_log;
RENAME TABLE wp_network_postmeta TO wp_hj87ka_network_postmeta;
RENAME TABLE wp_network_posts TO wp_hj87ka_network_posts;
RENAME TABLE wp_network_rebuildqueue TO wp_hj87ka_network_rebuildqueue;
RENAME TABLE wp_network_terms TO wp_hj87ka_network_terms;
RENAME TABLE wp_network_term_relationships TO wp_hj87ka_network_term_relationships;
RENAME TABLE wp_network_term_taxonomy TO wp_hj87ka_network_term_taxonomy;

So much steps… But I think it is the most completed tutorial on changing database prefix :)

The Proper Way of Using SQL-queries in WordPress to Prevent SQL-injections

Maybe you heard that almost all WordPress security problems comes from bad-written themes and plugins. And when you create your own plugin or a theme you can create another security hole, like an ability of SQL-injection.

Please remember these simple tips to stay protected.

  1. Always use $wpdb to connect MySQL.
  2. It is required to run $wpdb->query(), $wpdb->get_col(), $wpdb->get_var(), $wpdb->get_row(), $wpdb->get_results() with $wpdb->prepare() !
  3. On the other hand, $wpdb->insert(), $wpdb->update(), $wpdb->replace(), $wpdb->delete() shouldn’t be wrapped with $wpdb->prepare() because it is already inside them.

The example of $wpdb->get_results() with $wpdb->prepare():

// How to get all published pages
global $wpdb;
 
// these variables we get earlier somewhere in the code, maybe it is form data
$page_author_id = 1;
$post_type_name = 'page';
 
// the usage of $wpdb->prepare() statement
$pages = $wpdb->get_results( $wpdb->prepare( 
	"
	SELECT post_title, post_content 
	FROM $wpdb->posts
	WHERE post_author = '%d' 
	AND post_type = '%s'
	",
	$page_author_id, // %d because it is number
	$post_type_name // %s because it is string
) );
 
// print the page titles to see the result
if( $pages ) {
	foreach ( $pages as $page ) {
		echo $page->post_title;
	}
}

Do you know some other ways to protect WordPress database? Please, share in comments.

#5DaysToProtectYourWordPress

Need some help with WordPress?

If you need some professional developer help, I will be happy to assist you.

Contact me Who I am?

Leave a comment

php js HTML CSS Code