XF 1.5 Find the IPs of Login Attempts

[Dan Allen]

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.

 

[Jake B.]

They're hexadecimal, you can either enable the option to show it as a normal string in phpMyAdmin or find a hexadecimal converter

 

[whynot]

4b17a693 > IP = 75.23.166.147

 

[Dan Allen]

They're hexadecimal, you can either enable the option to show it as a normal string in phpMyAdmin or find a hexadecimal converter

ok, how do we enable this in phpmyadmin? I added to the config file per an article I found, but it changed nothing.

 

[digitalpoint]

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/

 

[Jake B.]

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:

$cfg['DisplayBinaryAsHex'] = false;

This option was broken in phpMyAdmin 4.0, fixed in 4.1 then removed sometime after that.

 

[digitalpoint]

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.

 

[Chris D]

FYI I have removed the screenshot of the email addresses and IP addresses in case this is leaking potentially sensitive private information.

If this was dummy data feel free to re-add the attachment.

 

[Chris D]

FWIW your MySQL may well have a function available which will convert the IP to a readable IPv4 address:

SELECT attempt_id, login, attempt_date,
    INET_NTOA(CONV(HEX(ip_address), 16, 10)) AS ip_address
FROM xf_login_attempt

 

[Dan Allen]

FWIW your MySQL may well have a function available which will convert the IP to a readable IPv4 address:

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.

 

[digitalpoint]

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.

 

[Jake B.]

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.

phpMyAdmin has (well, had now) an option to display binary data in human-readable form

 

[Dan Allen]

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.

 

[whynot]

Binary data takes ~8x less space than storing as a string.

8x ?
Why is that?

 

[digitalpoint]

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).

 

[Dan Allen]

That is a great explanation, thank you.

 

[whynot]

That was a shock!
I have experience with C, just could not understand why is PHP so much hungry!
Thank you @digitalpoint for the explanation.