Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    12

    IP address storage

    Is there a commonly accepted best practice for storing IP addresses in a database? I've seen a 4 column of bytes method, a single column INT method, and a single column CHAR(15) method.

    I know it is possible to do any of those and that there are pros and cons to them but which one is best? Also what is everyone going to do when IPv6 starts gaining more momentum?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what do you want to store the information for?
    if its a passive audit log, ie you just need to know what IP address was used then storing as an single number may make sense

    if you need to manipulate theat data then Id suggest storing as a string

    I don't think it actaully matters but your solution will need to handle IPv6 when it truly conmes into force..

  3. #3
    Join Date
    Aug 2007
    Posts
    12
    I am storing the IP addresses of systems that access a database. It needs the ability to be searched and and also converted to a human readable format. I was leaning towards have 4 fields IPoct1, IPoct2, IPoct3, IPoct4 all of tinyINT unsigned / Byte type. This way I can search by network and it retains human readability. But I wasn't sure if this was a proper way to store data (4 fields that technically hold data that 1 could).

    -Brian

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalmon
    I know it is possible to do any of those and that there are pros and cons to them but which one is best?
    the method where the pros and cons more closely fit your particular needs

    for searching, the INTEGER method is the fastest, because you always search top-down, i.e. you always start with the first octet, then the second, etc.

    you never search for all IPs where the 3rd octet is 155 -- that'd be silly

    the top-down search translates very easily into integer ranges

    regarding "proper way to store data (4 fields that technically hold data that 1 could)" this depends completely on what kind of data it is

    4 fields instead of 1 is much better if, for instance, you're talking about street, city, state, country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kalmon
    I know it is possible to do any of those and that there are pros and cons to them but which one is best?
    the method where the pros and cons more closely fit your particular needs

    for searching, the INTEGER method is the fastest, because you always search top-down, i.e. you always start with the first octet, then the second, etc.

    you never search for all IPs where the 3rd octet is 155 -- that'd be silly ™

    the top-down search translates very easily into integer ranges

    regarding "proper way to store data (4 fields that technically hold data that 1 could)" this depends completely on what kind of data it is

    4 fields instead of 1 is much better if, for instance, you're talking about street, city, state, country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2008
    Posts
    14
    Check if your DB vendor supports a native IP format, if so use it because it's properly optimized for doing queries against it. PG for example has one : http://www.postgresql.org/docs/8.3/i...net-types.html

    Otherwise use 4 fields of one byte, I believe hostip.org does the same.

    Ries

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    I could see searching on just the third octet. Around here, we have set up the network addresses to be on the 10.0.0.0 network, which is a 'non-network" according to the powers that be. As such, the third octet came out to be the location for us. My building has 10.0.101.0 and 10.0.102.0 for clients. Easy enough to trace.

  8. #8
    Join Date
    Aug 2007
    Posts
    12
    Thanks for all the replies. I am using Mysql 5.1 and I decided to go with 4 tinyint fields, although I did manage to get a working conversion to unsigned Int and back to dotted format.

    -Brian

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a working conversion? any chance you could share it?

    also, did you see the INET_ATON() and INET_NTOA() functions?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2007
    Posts
    12
    I'm off work until Monday so I dont have the exact code, but to sum it up:

    IPasINT = octet[X](X=0to3) * 256 ^ X(0to3)
    This creates an unsigned integer. It is important to note that this in not the same format that Windows uses, although if read in binary it would be... Windows uses a signed int therefor 128.0.0.0 and greater would be negative numbers as signed integers.

    Those NTOA and ATON functions are expecting the signed int I believe. I do know they just convert the order between network order and host order.

    *edited for correct math*
    The conversion to signed int went something like:
    Code:
    If IPasINT > 4294967296 Then IPasSINT = IPasINT - 4294967296
    If IPasINT <= 4294967296 THEN IPasSINT = IPasINT
    To revert it to a human readable format from an unsigned int was a little trickier. The correct way is to do modulus division but I did it like this:
    Code:
    Loop X from 3 to 0
      octet[X] = IPasINT / (256 ^ X) just ignore decimal part ALWAYS round down
      IPasINT = IPasINT - (octet[X] * (256 ^ X))
    Loop ends
    Last edited by kalmon; 03-31-08 at 13:15. Reason: Memory is not what it used to be and math was WAY off

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •