• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.5 Find the IPs of Login Attempts

Dan Allen

Active member
#1
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:

Jake B.

Well-known member
#6
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.
 

digitalpoint

Well-known member
#7
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

XenForo developer
Staff member
#8
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.
 

Dan Allen

Active member
#10
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:

digitalpoint

Well-known member
#11
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.

Well-known member
#12
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

Active member
#13
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.
 

digitalpoint

Well-known member
#15
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: