Change database prefix
Prevent SQL injections
Change admin ID

Database Security Tips

Updated on June 7, 2022

In this tutorial I prepared a couple of tips for you that allow to increase your WordPress website security with ease.

Change Database Prefix

Before we dive into this chapter of the tutorial, I think it is also worth mentioning that it is not recommended to run multiple WordPress installations on the same database. Really — if someone gets access to one of the databases he gets access to all your websites. You can find this recommendation in official WordPress codex as well. This is not about WordPress Multisite by the way.

Now let’s talk about table prefixes.

If you haven’t installed your WordPress website yet, of course you can set a custom prefix there. Just use anything except wp_ there.

Set custom database prefix in WordPress — So I changed `wp_` to `notwpplease_`.

Everything is simple here, but if your WordPress is already installed. It is possible to change table prefixes that way? Or is it too late?

Of course it is still possible to do but in multiple steps. First of all please open your wp-config.php and find these lines there:

/**
 * WordPress database table prefix.
 *
 * You can have multiple installations in one database if you give each
 * a unique prefix. Only numbers, letters, and underscores please!
 */
$table_prefix = 'wp_';

Let’s change the prefix there. But I don’t recommend you to save that file immediately because after that your WordPress website will start to suggest a clean installation on its every page. So, if it is live right now, this shouldn’t happen.

$table_prefix = 'wp_hj87ka_';

Why does it happen? Well, our database table still have old names like wp_posts, wp_options etc. So, our goal is to rename them first and only after that we have to save changes in wp-config.php.

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;

Once you run those queries and everything is ok, also run a couple more:

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_%';

Done? Save wp-config.php file now!

Please keep in mind that each plugin you’re using may have its own tables in the database. You have to rename those tables as well.

Change database prefixes on websites with WooCommerce installed

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;

Change database prefix within a WordPress Multisite network

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 or for any plugin with the custom tables you’re using.

Prevent SQL injections in WordPress

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

Please remember these simple tips to stay protected.

Always use $wpdb to connect to MySQL.
It is required to run $wpdb->query(), $wpdb->get_col(), $wpdb->get_var(), $wpdb->get_row(), $wpdb->get_results() with $wpdb->prepare() !
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;
	}
}

Change Administrator ID

In one of the previous posts we changed 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. But for WordPress 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.

# WordPress