Problem restoring database backup created using iThemes Security

@john O

You will probably need to find out which table the INSERT statement is failing on.
The last table created during import is probably the one you are looking for.

The iTSec backup (.sql) file structure is quite simple.
It repeats 3 statements for every table:

DROP TABLE IF EXISTS wp_table_name;

CREATE TABLE wp_table_name (
histid int(11) NOT NULL AUTO_INCREMENT,
user_caps varchar(70) NOT NULL DEFAULT ‘guest’,
action varchar(255) NOT NULL,
object_type varchar(255) NOT NULL,
object_subtype varchar(255) NOT NULL DEFAULT ”,
object_name varchar(255) NOT NULL,
object_id int(11) NOT NULL DEFAULT ‘0’,
user_id int(11) NOT NULL DEFAULT ‘0’,
hist_ip varchar(55) NOT NULL DEFAULT ‘127.0.0.1’,
hist_time int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (histid)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;

INSERT INTO wp_table_name VALUES(“1″,”administrator”,”logged_out”,”User”,””,”duhh”,”1″,”1″,”127.0.0.1″,”1442939160″);

It’s one of the INSERT statements that is probably failing.
Difficult to say anything more without additional info.

Once you know which table it is failing on post the above 3 commands for that table so we can have a look at it.

dwinden

I got the same problem!

It’s NOT possible to import an iThems Security database backup directly to phpMyAdmin as it is if the backup was exported from phpMyAdmin itself.

This is clearly a bug.

Hi,

It’s this table, so here are the SQL commands – I’ve not posted all the INSERT commands, just an example one, as there are a lot of records.

Also – in the IP` binary(16) NOT NULL DEFAULT field there are actually a series of slashes and zeroes that it will not let me paste here.

DROP TABLE IF EXISTS wp_wfThrottleLog;

CREATE TABLE wp_wfThrottleLog (
IP binary(16) NOT NULL DEFAULT ”,
startTime int(10) unsigned NOT NULL,
endTime int(10) unsigned NOT NULL,
timesThrottled int(10) unsigned NOT NULL,
lastReason varchar(255) NOT NULL,
PRIMARY KEY (IP),
KEY k2 (endTime)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO wp_wfThrottleLog VALUES(“ˇˇV?v?”,”1433493323″,”1435570972″,”8″,”Exceeded the maximum number of page not found errors per minute for humans.”);

@john O

It seems the origin of the wp_wfThrottleLog table is the WordFence plugin.

So I had a quick peek at how WordFence defines this table.
(The thought behind this is to have something to compare with).
Weird thing is the IP field datatype doesn’t match …
WordFence (6.0.20) defines it in the lib/wfSchema.php file as:

IP int UNSIGNED NOT NULL PRIMARY KEY

So it should be an ‘int UNSIGNED’ but according to your iTSec plugin backup file it is ‘binary(16)’ … with a weird DEFAULT value that you cannot paste into this forum …

If there is something wrong here that when corrected might have a positive impact on importing the iTSec plugin backup file I think that is well worth investigating …

Perhaps you are using an old wp_wfThrottleLog table definition which was never changed (upgraded) to the current one …

I would be interested to see a phpMyAdmin screenshot clearly displaying (Structure tab) this table’s definition in your database …

Perhaps there are even more WordFence tables with the same issue …

Are you using the latest WordFence plugin release (6.0.20) ?

dwinden

Yes running latest version of Wordfence – version 6.0.20

That is the table as created by Wordfence – I have not made any changes to it.

Here is a screenshot of the table in question. I have dropped it temporarily into a folder o my website rather than my client’s website, as I don’t think I can upload images here:

Screenshot here

I hope this helps.

As I said – when I manually export and import this database, I do not get the error. Only when I try to import the iThemes backup version.

@john O

Ok, it turns out WordFence executes the php\SQL command below after the table is created:

$db->queryWriteIgnoreError("ALTER TABLE {$prefix}{$ip_table} MODIFY IP BINARY(16)");

It even repeats this SQL command for another 12 tables …
Apparently the datatype change is required for supporting IPv6 (as of the 6.0.1 release).

So let’s get back on topic … Why is the iTSec plugin backup failing to import ?

#1062 – Duplicate entry ‘\xCB\x87\xCB\x87V\xC3\xA5v\xE2\x80\x9A’ for key ‘PRIMARY’

The error suggests there is a duplicate primary key issue with one of the insert statements.

I think we need to find out which insert fails.
Check the number of inserted rows in the wp_wfThrottleLog table after the import fails.
That number + 1 is probably the insert statement in the import file for this table we need to take a look at.

dwinden

I think there is some incorrect conversion taking place for the values of the ID field when the iTSec plugin creates the backup.

I’m pretty sure the value of ID is not displayed as “ˇˇV?v?” when you look at this ID value in the original table while using phpMyAdmin …
What kind of IP address is “ˇˇV?v?” ? Makes no sense …

When you export from phpMyAdmin the resulting exported ID value looks completely different compared to what the iTSec plugin is exporting.

So the bottom line could be that the iTSec plugin is not correctly exporting binary datatype fields.

Just a couple of additional thoughts …

dwinden

Correction …

WordFence stores IP addresses using the PHP inet_pton() function (or if inet_pton() is not available a custom replacement function), converting a human readable IP address to its packed in_addr representation.

For a v4 IP address this results in a 4 char string.
For a v6 IP address this results in a 16 char string.

WordFence converts the 4 char string to a 16 char string using:

$pton = str_pad(inet_pton($ip), 16, "\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\x00\x00\x00\x00", STR_PAD_LEFT);

I think the iTsec plugin Backup feature dumps the binary data as plain text converting hexs to utf8 chars.
It would probably be better to prevent this conversion and do something like this:

if ( $data['type'] = 'binary' ) {
	$data['value'] = '0x' . bin2hex($data['value']);
}

The binary IP values are then written just like phpMyAdmin export does:

0x00000000000000000000ffff56c3a576

instead of (replace % with 10 backslash zero values) :

“%ˇˇV?v?”

So in its current form you probably won’t be able to import the backup file. I haven’t tested my theory but it makes sense (to me) … ??

dwinden

Hi,

Sorry I have been away without access to my computer. Ok, so which should I try first?

How do I implement this:

if ( $data['type'] = 'binary' ) {
	$data['value'] = '0x' . bin2hex($data['value']);
}

Where in WP do I do that?

Thanks for all the help so far btw – I had no idea what to do!

Hi,

I fixed the import!

I found record 270 was one after the error, and it is this:

Screenshot link

I have added it as an image link as every time I paste it in, the content changes.

I deleted this record, and tried the import again and it worked fine.

Any idea what is wrong with this record?

Thanks for your help with this.

Ok great, though I would not call that a fix. More cheating … haha ??

Somehow that ID value already exists.
It was already inserted by one of the previous 270 inserts …

Please verify whether there is an identical ID value in one of the previous 270 inserts in the backup file …

Then compare the ID values of these 2 records as stored in the original table in the database while using phpMyAdmin.

If my theory is correct. The ID hex values are different in the database but the to UTF8 converted values in the backup file are identical.
This would proof binary data should not be dumped as plain text like the iTSec plugin Backup feature is doing right now.

Testing fixed Backup code as we speak …

dwinden

You are indeed correct!

I found several records with identical primary keys because of the conversion from hex to UTF8. The ID hex values are different in the database but the UTF8 converted values in the backup file are identical.

Hi – just to clarify, when you say:

Testing fixed Backup code as we speak …

Does this mean there will be an update to the plugin that will resolve this issue?

Thanks.

@john O

You can log a bug with iThemes here.

Meanwhile simply replace the better-wp-security/core/modules/backup/class-itsec-backup.php file with this fixed release 5.1.0 copy.
It’s a fake .zip file to facilitate the download.
Once downloaded simply rename it from class-itsec-backup_510.zip to class-itsec-backup.php

Tested the fix and it should work. Would still be interested in receiving your confirmation though.

dwinden

Hi,

Sorry I misunderstood, I thought you were an iThemes support chap! I will report the bug.

What should I write in the bug report to make sure it is clear what the problem is?

And thank you again for all your help with this.