Your premium source for custom modification services for phpBB

HomeForumsBlogMOD ManagerFAQSearchRegisterLogin

Comments August 13, 2008

Decoding IP Addresses from phpBB2

Filed under: Database Tips — Dave Rathbun @ 10:46 am CommentsComments (7) 

The IP address information for a poster is stored in the phpbb_posts table in phpBB2. In my Checkbox Challenge MOD it’s also stored during registration attempts. The IP address stored after being converted to hex and is then stored as an 8 character string. This is then decoded on the fly when requested. Sometimes you might want to decode the IP from the character string by using MySQL directly. It turns out there is a very simple formula to do that.

To encode an IP use this expression:

mysql> select conv(inet_aton(''),10,16);
| conv(inet_aton(''),10,16) |
| 7F000001                           |
1 row in set (0.00 sec)

To decode an IP address use this expression:

mysql> select inet_ntoa(conv('7F000001',16,10));
| inet_ntoa(conv('7F000001',16,10)) |
|                         |
1 row in set (0.01 sec)

The functions used here are conv() and then the pair of inverse functions called inet_ntoa() and inet_aton(). The conv() function converts from base 10 to base 16 (or base 16 to base 10) based on the order of the arguments. The inet_ntoa() and inet_aton() functions are used to convert from an “alpha” representation of the IP address to a “numeric” representation. So the inet_ntoa() is “number to alpha” and the inet_aton() is an “alpha to number” translation.

So to summarize: the IP address comes in as and the inet_aton() function converts that dotted notation into a number. Then the conv() function converts that number from base 10 to base 16 (hexidecimal) format, and which point it’s stored in the table. To reverse that process I have to convert from base 16 back to base 10 and then apply the reverse function inet_ntoa() to return the IP address in proper dotted notation.

I’m not sure but I think I have been told that phpBB3 does not store IP addresses using this same encoding. I’m not sure why not, as by storing the IP address encoded as hex you can use wildcards and substring operations to do some interesting queries. If the IP address is stored in dotted notation then those advantages go away. I am not sure I can come up with a good advantage to storing the IP address in dotted notation at this time. Anyone else have an idea?


  1. I know why. Try accessing a phpBB 2 board with an IPv6 address (which I have done with my own Mac on localhost).
    On v2, your IP will register as On v3, it will register as a normal v6 address.

    So, they had to support IPv4 and v6 and decided not to encode.

    Comment by Dog Cow — August 13, 2008 @ 11:43 am

  2. What is the format for IPv6? I assumed (perhaps a bad thing to do) that it was xxx.xxx.xxx.xxx.xxx.xxx instead of just xxx.xxx.xxx.xxx for IPv4. If that’s the case, a very simple change to extend the character field size to handle the extra two octets would have worked fine. There’s no need to drop the encoding strategy.

    For example, try to find every IP address that matches the wildcard 127.XXX.XXX.1 with an encoded value and it’s quite easy, you match 7f____01 and you’re done. With unencoded data you have to allow for strings of different lengths.

    I should probably do more research on exactly what IPv6 looks like, as it might change my opinion. But for now, I’ll stick with the idea that encoding would be better for pattern matching and any process (like banning) that requires that process would be more efficient.

    Comment by Dave Rathbun — August 13, 2008 @ 1:21 pm

  3. IPv6 is in the following format: 0000:0000:0000:0000:0000:0000:0000:0001. This is the same as the familiar That’s why on phpBB.com, you see NeoThermic and me with ::1, which is the shorthand for 0000:0000:0000:0000:0000:0000:0000:0001, or It is all rather confusing, I know. I still don’t understand it all. :P

    Comment by Micheal — August 13, 2008 @ 3:44 pm

  4. Micheal, so what does a real IP address look like then? Are the leading values the “new” values? It would seem so, if the 0001 comes at the end. But it also seems that they haven’t just added a couple of octets… they’ve changed the entire structure.

    Hm. More research is required, it seems.

    Wiki on IPv6


    Comment by Dave Rathbun — August 13, 2008 @ 4:49 pm

  5. *puts on his arrogant face*

    I can’t believe you don’t know much about IPv6. I mean, even I didn’t a few months ago, but I got a new Mac and was checking out the Ethernet settings and noticed that it had IPv6 option, so I turned it on and connected to the localhost forums with it. I also strung a cross-over cable to my Mac G3 and loaded web pages across it with IPv6. WIth my network configuration, the IP addresses are when not connected to a network, and then they are only assigned when connected (to avoid conflicts!). I don’t know off-hand what the IPv6 address for my Mac is, since I’m not at it right now, though.

    Anyway, if your machine has IPv6 ability, I recommend enabling it and seeing the results. :)

    IPv6 is completely new. It has no relation to IPv4, so it’s not “added-on” or anything. I personally can’t wait until most of the entire world switches to v6. It has so many benefits which aren’t technical, such as the fact that since there are more addresses available, logically that would follow that buying an IPv6 address should cost less money.

    Comment by Dog Cow — August 13, 2008 @ 7:51 pm

  6. My assumption was that it had to be evolutionary rather than revolutionary or the entire Internet would have to be upgraded all at once. I don’t see that happening. :) Therefore there had to be some backward compatibility available. As to why I don’t know much, I’ve never needed to know. I provided the wiki link as a note to myself as much as anything else so I can go back out and read it further.

    Comment by Dave Rathbun — August 13, 2008 @ 10:59 pm

  7. The backward compatibility is built not into the protocol itself, but rather the machines, network hardware, and software that support it.

    Comment by Dog Cow — August 14, 2008 @ 2:40 pm

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress