Caleb

Thanks, looking forward to a fix, since I really hesitate to install WordFence updates until it’s fixed. The latest coming down would UNDO my own fixes. 🙂

On what contributed to this case.

Looking into the WordPress code as well, I think the reason in this case is that WordPress is a little bit schizophrenic, when it comes to how prefixes are handled. In class wpdb::set_prefix() and especially in wpdb::get_blog_prefix().

In a new install, typically the first blog created becomes blog ID 1, and its private tables are in format {prefix}{blogid=1}’ ‘{tablename}.

When you go to network admin, you are then supposed to be operating without the blogid in the tables. Off domain/blog #1 as the main domain.
HOWEVER… What if things change over time?

In get_blog_prefix(), they have this code:

		if ( is_multisite() ) {
			if ( null === $blog_id )
				$blog_id = $this->blogid;
			$blog_id = (int) $blog_id;
			if ( defined( 'MULTISITE' ) && ( 0 == $blog_id || 1 == $blog_id ) )
				return $this->base_prefix;
			else
				return $this->base_prefix . $blog_id . '_';
		} else {
			return $this->base_prefix;
		}

Notice first off, that for a single install, as I suspected, all tables are off ‘base_prefix’. That last return in the code. So, prefix and base_prefix are the same. That’s why your code works for single installs.

Secondly, VERY IMPORTANT. Notice in the above code that for multisites, blogid=0(no ID) and blogid=1 (typically that very first blog) are treated special. In the multisite case, for blog 1, they ALWAYS return “base_prefix”. Which explains why most of your installs work, even when they are multi-sites. That first blog-domain is still alive and is running the Network Admin screens.

In my case, however, for historic reasons, blogid #1 is a deleted site, and BLOGID=3 is the controlling domain. As in, when you are Network Admin, invisibly to the user, the running domain is actually from blogid=3, which is what I saw in “db->prefix” when you were attempting to read the tables and all queries failed. it would always try to read off “ftrav_3_wfBlockedIPLog”, where the correct table is “ftrav_wfBlockedIPLog” (no blogid).

Independent of all that, when your tables are network global, the correct prefix IS “base_prefix”.. Only if you expect a query to be run of a blog level table should “prefix” be used. Since WordFence is network/globally installed, and wfSchema.php always uses “base_prefix” to install them.. It would then be a sound idea to try to find them again off that same prefix. 🙂

BTW.. Looking at the WordFence code, it’s history and probably different people have made naming schemes is something that might need some cleaning up while y’all are at it. I can understand how it happened, but also why people making changes to it get confused. 🙂

Some examples:
In some code, “$this->db” is a variable assigned directly to the global $wpdb, which is how accessing “prefix” and “base_prefix” works.
In other areas, “$this->db” is actually assigned the value of “new wfDB()”, your wrapper class. Which by fun coincidence has a routine “prefix()”, which always returns the value of “$wpdb->base_prefix”. To complete the naming confusion.

You even in the wfDb class have a function networkPrefix(), which always returns the value of get_blog_prefix(), which is how it all connects back to my explanation above about “set_prefix” and “get_blog_prefix” in the main WordPress code.

Your “networkPrefix” would then on a fresh network install always return the value of “base_prefix” (from get_blog_prefix(), because Network Admin is on Blogid=1)..
But in some installs, like mine, where the controlling domain is not blogid=1, but 3, you would get some other prefix, and include that blog-# in the table names.. But that is not where you installed your tables… 🙂

Between the various locations and uses, somewhat confusing.

The values of properties and function calls

“db->prefix” (a $wpdb property),
‘db->prefix()’ ( function in wfDb always returning base_prefix),
‘db->base_prefix” (a $wpdb property),
and networkPrefix() (sometimes returning base_prefix, sometimes NOT)

are used kind of randomly in various sections/functions/classes of your code, and to make the confusion even worse, WordPress’s own multisite decisions in set_prefix() and get_blog_prefix() then further interfere with the values of those fields. 🙂

If it was me, I would clean up that code some, and maybe refactor the naming schemes to make it more clear what is expected. Lest new programmers editing your code get confused. 🙂

Bottom-line is still.. If you install your tables off “base_prefix”, which you always do, then that is where they should be accessed later as well. 🙂

The WordPress DB user does not have limited privileges. It has full privileges. I used the same ID/login to manually add the BlockType field through MySQL WorkBench. It has never changed. I have no idea why it did not upgrade correctly back when. Your upgrade procedure in the plugin should have worked.

The issue of using ‘prefix’ versus ‘base_prefix’ is separate..

Per the WordPress Codex for the wpdb structure:

$prefix
The assigned WordPress table prefix for the site.
$base_prefix
The original prefix as defined in wp-config.php. For multi-site: Use if you want to get the prefix without the blog number appended

Since my freshly installed WordFence tables sure does not have a blog-id mixed into the table names (are global to the multisite) install, the correct prefix to use is “base_prefix”.

As also indicated by the fact that only two routines in wfActivityReport (wfActivityReport::getBlockedCount and wfActivityReport::getTopIpsBlocked) uses ‘prefix’ to access your tables. Both of them ONLY show data in the dashboard after I change them to use ‘base_prefix’. In addition, wfRecentFirewallActivity::run(), tries to use ‘prefix’. Also wrong.

All other routines in wfActivityReport.php correctly uses ‘base_prefix’.

Funny enough, your routine “rotateIPLog()’, which on a weekly basis rotates that same exact table with IP stats, uses ‘base_prefix’, not prefix, like getBlockedCount().
So, there are two different ways used for accessing the same table, in the same PHP class and file. 🙂

And lastly, in wfSchema.php, where you actually create all the tables, you use ‘base_prefix’, not ‘prefix’.
As in

		global $wpdb;
		$this->db = new wfDB();
		$this->prefix = $wpdb->base_prefix;

Surely, the prefix used to create/install the tables should be used to access them, lest they get lost. As they did. 🙂

It seems that at different times (and maybe by different people) sort of random decisions have been made on which prefix to use to access tables all installed in the same place (all installed global to the install, not per-blog). 🙂

Notice, that on all single (as in not multisite) installs out there, using either one might work fine. dbprefix and base_prefix would return the same value, I think, as long as there is no blog-id numbering involved in table naming. Which might explain how many others are not complaining. Single-blog installers would not see a difference.

However, if you search this forum you will see that others have questioned why their IPs blocked list is always empty. The question was never adequately answered.
But I can promise you, that after I changed it to use ‘base_prefix’, all my Top IPs blocked showed up in the dashboard immediately. 🙂

So, as it turns out, even after a complete reinstall, there was still an issue/bug. It was not merely invalid table structures.

At first, I thought that all the zeroes that still appeared in the dashboard was caused by maybe caching, and was planning to wait until the next daily email report.

However, looking at it again, it seems that there is an issue with the db prefix used when extracting certain numbers.

In wfActivityReport::getBlockedCount(), you use $this->db->dbprefix to generate the queries, which in fact is the wrong prefix. All the WordFence tables are off ‘base_prefix’. Similar to how you already do it in getTopCountriesBlocked() and others. So the queries silently failed and returned nothing, which when typecast to (int) became 0 (zero).

I verified it by changing ‘dbprefix’ to ‘base_prefix” and all the correct numbers then started showing in the dashboard.

The Dashboard Firewall status indicate “0” (zero) attacks blocked across the board. For the day, week, month.

The Login Attempts widget shows the failed logins. Like 3 (seemingly manual) attempts on June 26 to find admin accounts.. Those also show in the daily emailed report for June 26. I think the login attempts are counted correctly. Apparently few of the hacking attempts actually as far as actually trying a login/pw combo.

But despite both the Dashboard AND the email for today (June 27) reporting zero blocked accesses/attacks, on the Live Traffic page I gave up scrolling down to count all the blocked accesses (not actually getting to full login attempts). Just filtering on today’s attacks on wp-login and xmlrpc the blocked IPs from all over the world are so many. And I kind of assumed that they would have been counted.

If for nothing else for the advertising value of reporting how much is actually blocked. 🙂