XF 1.5 Find the IPs of Login Attempts

Dan Allen

Active member
How do we find the IP for each failed login attempts? The IP column is a blob. I tried opening one and all I got was unintelligible characters.
 
Last edited by a moderator:
ok, how do we enable this in phpmyadmin? I added to the config file per an article I found, but it changed nothing.
Showing it as a normal string won't convert it to a human readable IP address. You will want to use a hex to IP convertor tool. For example: http://sami.on.eniten.com/hex2ip/

This depends on the version of phpMyAdmin you're using. Older versions of phpMyAdmin have an option when you click on the "+ Options" that will convert the hex to a human readable string. This option was removed in a phpMyAdmin update. Adding this to your config.inc.php should fix it:

PHP:
$cfg['DisplayBinaryAsHex'] = false;

This option was broken in phpMyAdmin 4.0, fixed in 4.1 then removed sometime after that.
 
Right... but it will then display it as binary (unreadable characters), not an IP address. @Dan Allen wants to see the IP address, not the raw binary (how it's stored). At least displaying it as hex, you can use a tool to convert it to an IP.
 
FWIW your MySQL may well have a function available which will convert the IP to a readable IPv4 address:
Code:
SELECT attempt_id, login, attempt_date,
    INET_NTOA(CONV(HEX(ip_address), 16, 10)) AS ip_address
FROM xf_login_attempt
Chris, thank you for removing potentially sensitive data. I did not know it was sensitive, because I thought the values shown in the IP column could not be decoded without other data from other tables. I do not understand very well how that data is works.

Something I do understand: the query you posted worked, displaying IPs how I need them, thank you for that also.

Question, if you have time: Why is the ip stored as binary hex instead of a readable ip?

p.s. I just looked up INET_NTOA and now I see what is going on. I thought the IP column is a blob, but now I see it is varbinary. That column is displayed as links, that is what made me think they are blobs. I realize I am exposing immense ignorance here. I have not delved into binary data within a database before.
 
Last edited:
Binary data takes ~8x less space than storing as a string. It also allows doing things like ranged searches (for example a range of IPs), which storing it as a sting does not. The IPs XenForo stores are intended to be used with the PHP functions XenForo has to convert to/from human readable form.
 
Binary data takes ~8x less space than storing as a string. It also allows doing things like ranged searches (for example a range of IPs), which storing it as a sting does not. The IPs XenForo stores are intended to be used with the PHP functions XenForo has to convert to/from human readable form.
That makes sense. IP as a string needs let's say 16 characters. So I could save 1G of disk with only 60million records containing IP addresses. Well worth the extra time it takes for using the special functions and such. Up till now, I have always been partial to storing data in a readable form. I guess I thought that was a way to keep things simple and easy to work with, which is necessary, due to the limitations of my brain.
 
8x ?
Why is that?
Oops, not 8x... not sure what I was thinking... but here's the storage space broken down...

As an example, let's say you store this IP address as a string: 123.123.123.123

Stored as a string, it's 15 bytes (15 characters). Stored as a 32-bit number (which really is what IP addresses are), it requires 4 bytes to store. 8 bits per byte, so you need 4 bytes to store a 32-bit number.

An IPv6 address is a 128-bit number. So, let's say your IPv6 address is: FE80:0000:0000:0000:0202:B3FF:FE1E:8329

Stored as a string, it's 39 bytes (for it's 39 characters). Stored as a 128-bit number, it requires 16 bytes (again, 8 bits per byte).

The reason XenForo stores it as raw binary instead of a 128-bit number, is because MySQL doesn't support 128-bit numbers.

And stored as a number (or binary) allow you to do ranged queries (like if you wanted to see users who are on the same IP block).
 
Last edited:
Top Bottom