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

Discussion in 'XenForo Questions and Support' started by Dan Allen, Dec 29, 2015.

  1. Dan Allen

    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: Dec 29, 2015
  2. Jake B.

    Jake B. Well-Known Member

    They're hexadecimal, you can either enable the option to show it as a normal string in phpMyAdmin or find a hexadecimal converter :)
     
    Dan Allen likes this.
  3. whynot

    whynot Well-Known Member

  4. Dan Allen

    Dan Allen Active Member

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

    digitalpoint Well-Known Member

    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/
     
  6. Jake B.

    Jake B. Well-Known Member

    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.
     
    ozzy47 likes this.
  7. digitalpoint

    digitalpoint Well-Known Member

    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.
     
  8. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
    thedude and Dan Allen like this.
  9. Chris D

    Chris D XenForo Developer Staff Member

    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
     
    whynot, Dan Allen, wang and 1 other person like this.
  10. Dan Allen

    Dan Allen Active Member

    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: Jan 4, 2016
  11. digitalpoint

    digitalpoint Well-Known Member

    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. and Chris D like this.
  12. Jake B.

    Jake B. Well-Known Member

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

    Dan Allen Active Member

    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.
     
  14. whynot

    whynot Well-Known Member

    8x ?
    Why is that?
     
  15. digitalpoint

    digitalpoint Well-Known Member

    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: Jan 5, 2016
    wedgar, whynot and Dan Allen like this.
  16. Dan Allen

    Dan Allen Active Member

    That is a great explanation, thank you.
     
  17. whynot

    whynot Well-Known Member

    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.
     

Share This Page