Wordfence DB Tables encoding

Hi @soupia18, thanks for reaching out to us!

This looks related to the way the import is done. We’ve seen a similar case with some phpmyadmin installations generating files that won’t import correctly on other hosts. In those cases, it seemed that some hosts didn’t handle binary content correctly, while we could still import the files for testing.

One way around the issue is to use the argument --hex-blob when running mysqldump. This will turn binary content into hex strings instead, which should be able to be imported. It will make the file size a bit bigger, but if the files are compressed for transfer, it shouldn’t be significant.

For your other question, Wordfence always uses the default collation of the database it is being installed on and does not enforce UTF-8.

Let me know how you get on!

Thanks,

Peter.

Hi @wfpeter – thanks for the prompt and helpful response.

One note is that all existing data in the database of the production site looks like this and not only the dumped data.

So for example currently all IPs in any WF db table are stored like this ” ??.é “.

Is this normal to see the data stored in the database like this?

And does this make any difference to what you have suggested above?
Will the –hex-blob argument will help with the dumped data?

Hi @soupia18, thank-you for getting back to me.

--hex-blob should make future imports work if you can’t import them when they have binary data, if you’re able to specify that option in the command line.

If you’re using something other than the command line to export the tables, like phpmyadmin or a backup plugin, the option may have a different name, but most tools should offer it.

Yes, it is normal to see binary values if you’re looking directly in the database. It’s more efficient to store and read them that way in PHP rather than converting them.

Thanks again,

Peter.

Hi @wfpeter!
Thanks once again for getting back.

The problem is that I don’t see binary values in the database of the live website. Shouldn’t those IPs be stored as HEX values or something?

Instead an IP looks like ??.é
Can this transformed back to a HEX represantation?

*What do you think if I would deactivate and delete WF, delete its tables and re-install?
Could that solve the issue with the values not being saved as binary?

Hi @soupia18,

IPs displaying like ??.é is what it looks like when MySQL tries to display binary values as text.

Reinstalling Wordfence won’t change it. Using --hex-blob when exporting will export them as hex, but imports will put them back in binary format, as they need to be. If you want a query to see the values, use something like select hex(ip), ... to see the values has hex, or select inet6_ntoa(ip) ... to see them as IP addresses.

Please note that we use IPv4-mapped IPv6 addresses, so the field can hold both types of addresses. IPv4 will look like ::ffff:10.0.0.1 (or 00000000000000000000FFFF0A000001 in hex).

Thanks,

Peter.