294

Possible Duplicate:
Maximum length of the textual representation of an IPv6 address?

What would you recommend as the maximum size for a database column storing client ip addresses? I have it set to 16 right now, but could I get an ip address that is longer than that with IPv6, etc?

Community
  • 1
  • 1
Tony Eichelberger
  • 7,044
  • 7
  • 37
  • 46
  • Its already covered here. Check http://stackoverflow.com/questions/1038950/what-is-be-the-most-appropriate-data-type-for-storing-an-ip-address-in-sql-server – Arnkrishn Jul 02 '09 at 21:23
  • 5
    Actually, that post is not very helpful. We are not using Sql Server and the answers to this post have been concise and to the point, exactly what I was looking for. – Tony Eichelberger Jul 02 '09 at 21:40
  • 7
    @Andriyev that post refers to IPv4 only. – Android Eve Oct 21 '10 at 19:40
  • 2
    For anyone coming here later - if you happen to be blessed with the ability to use Postgres, they have a [built-in IP data type](http://www.postgresql.org/docs/9.4/static/datatype-net-types.html), so there's that. – Wayne Werner Feb 11 '15 at 14:38
  • IPv6 addresses is plain 8 groups of 4 hex digits, separated with 7 `:` (39 chars). It can be written out in some other formats, depending on use. Internally it is still always 128 bits. But you might want to also store how you want to use it, and optional device name, used for link local addresses (they start with `fe80:` and can be appanded with `%devname`). Yes, you can also write out the last 32 bits as an IPv4 format, so replace those 9 chars with 15 chars of IPv4 (45 chars). But as said, they are still just 128 bits, written in another format for users. Which depends on context. – Anders Sep 27 '22 at 17:30

8 Answers8

471

There's a caveat with the general 39 character IPv6 structure. For IPv4 mapped IPv6 addresses, the string can be longer (than 39 characters). An example to show this:

IPv6 (39 characters) : 

ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD

IPv4-mapped IPv6 (45 characters) : 

ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:192.168.158.190

Note: the last 32-bits (that correspond to IPv4 address) can need up to 15 characters (as IPv4 uses 4 groups of 1 byte and is formatted as 4 decimal numbers in the range 0-255 separated by dots (the . character), so the maximum is DDD.DDD.DDD.DDD).

The correct maximum IPv6 string length, therefore, is 45.

This was actually a quiz question in an IPv6 training I attended. (We all answered 39!)

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
Deepak
  • 4,888
  • 1
  • 14
  • 7
  • 6
    It seems to me that this is not correct. An IPv6 mapped address is maximum 22 characters long according to: https://tools.ietf.org/html/rfc5952#page-10 and https://tools.ietf.org/html/rfc4291#section-2.5.5 as they are always of the format ::ffff:000.000.000.000 and they must be shortened – oarsome Nov 02 '17 at 09:07
  • 1
    I wonder if he really meant "ipv6-compatible ipv4 address" as per https://tools.ietf.org/html/rfc4291#section-2.5.5.1. I don't ever remember seeing those in the wild. – Dan Pritts Mar 06 '18 at 15:23
  • You could have argued on your quiz that you store IPv4 over IPv6 in :hex form for which 39 is correct again. – Joshua Mar 23 '21 at 18:35
  • 1
    IPv6 address doesn't have to be shorten, but they _can_ be shorten. And all those addresses are just 128 bits, even if they are written with this IPv4 format part. – Anders Sep 27 '22 at 17:33
284

For IPv4, you could get away with storing the 4 raw bytes of the IP address (each of the numbers between the periods in an IP address are 0-255, i.e., one byte). But then you would have to translate going in and out of the DB and that's messy.

IPv6 addresses are 128 bits (as opposed to 32 bits of IPv4 addresses). They are usually written as 8 groups of 4 hex digits separated by colons: 2001:0db8:85a3:0000:0000:8a2e:0370:7334. 39 characters is appropriate to store IPv6 addresses in this format.

Edit: However, there is a caveat, see @Deepak's answer for details about IPv4-mapped IPv6 addresses. (The correct maximum IPv6 string length is 45 characters.)

Matt Bridges
  • 48,277
  • 7
  • 47
  • 61
  • 5
    Some databases (postgres at least) have a native IP column type, and does the conversion for you. – gnud Jul 02 '09 at 21:32
  • 4
    I would avoid mixing IPv4 and IPv6 in the same filed of a database for "a while." IPv4 is still the default standard, and will continue to be used for years to come. In legacy applications I have worked with, when it became necessary to add IPv6 addresses to the database, this was done as a separate entry. This allowed existing code that expected IPv4 address to be left in place, and allowed code to continue to only get an IPv4 address. For new portions of the code, they had an option to specifically get IPv4, IPV6, or a mix from the query. – Stan Graves Jul 02 '09 at 22:07
  • If you were going to store IPv4 as a single byte, wouldn't it be more a question of what data structures are accepted by the client that reads? If you were to store it as a single INT, and the client accepted binary, yeah that wouldn't be good... I think the point about having a larger type-of might be a good concern, depending on the number and sophistication of the applications. You might have apps that accept just one format, or both. You might want to have a related field that had a type-of-addressing label. – benc Jul 26 '09 at 20:26
  • 1
    Why **39 bytes** when IPv6 is 128 bit, ie takes at most **16 bytes**? – Christian Jun 05 '12 at 12:51
  • 7
    Same reason you don't store IPv4 as 4 bytes. – Matt Bridges Jun 06 '12 at 12:43
  • 1
    @MattBridges How do you know I don't? There is absolutely no utility in storing IPs in plain-text readable format. In fact, I'd argue if you kept it in raw format, you could easily do math as opposed to regular expressions and whatnot in an sql query. – Christian Feb 14 '14 at 17:08
  • 2
    If you plan to query via bitmask, then obviously store as bytes. If The extra 23 bytes per row are an issue for you, then obviously store as bytes. Not sure what the problem is here. – Matt Bridges Feb 17 '14 at 01:42
  • @StanGraves I think it's a bad idea to separate between ipv4 and ipv6. Operating systems, supporting libraries and the IETF have worked hard to provide compatibility. If applications don't follow suit and work to be IP version agnostic, then problems will remain for years to come. –  Apr 26 '14 at 17:06
  • 1
    @Melvyn...to be fair, I did give that advice nearly 5 years ago, and qualified it with "...for 'a while'..." Even today, among the applications that I work with, there is a clear separation between IPv4 and IPv6, with (almost) all defaulting to IPv4. – Stan Graves Apr 28 '14 at 01:12
  • @MattBridges It seems to me that the correct answer is 39 not 45 as ipv4 mapped ipv6 addresses are always leading zeros and then ones so: ::ffff:192.168.256.256 according to this: https://en.wikipedia.org/wiki/IPv6#IPv4-mapped_IPv6_addresses and it MUST be shortened: https://tools.ietf.org/html/rfc5952#page-10 https://tools.ietf.org/html/rfc4291#section-2.5.5 – oarsome Nov 02 '17 at 09:01
  • How about scope zone aka scope ID? E.g. `%2` or `%eth0` that might be tacked onto the end of an IPv6 address, in particular `FE80:` addresses. – Craig McQueen Nov 30 '18 at 00:08
  • I would avoid applications where a device can only have one address, be it IPv4 or IPv6. We stop using that limitation back in the 1990:th. So devices can have multiple addresses, and IPv6 devices HAVE multiple addresses, at least two (link local and public). So don't store binary data, like IPv4 or IPv6 addresses with netmask as printed format. Always convert to/from suitable formats (notice many formats) when talking to humans, depending on use. (Everyone have forgot to mention netmask, and CIDR format) – Anders Sep 27 '22 at 17:47
28

If you want to handle IPV6 in standard notation there are 8 groups of 4 hex digits:

2001:0dc5:72a3:0000:0000:802e:3370:73E4

32 hex digits + 7 separators = 39 characters.

CAUTION: If you also want to hold IPV4 addresses mapped as IPV6 addresses, use 45 characters as @Deepak suggests.

Community
  • 1
  • 1
David J. Liszewski
  • 10,959
  • 6
  • 44
  • 57
  • 1
    Which actually are just that, an 128 bit number, where you have different choices to print it out. Where you also can add device `%device` to the address. But yes, the IPv6 text format can be stored in 39 characters or less, if you compress the address. But added `%device` and extra `(4*3+3) - (2*4+1)` characters (or some bits to suggest which format to use when printing). – Anders Sep 27 '22 at 17:42
13

Take it from someone who has tried it all three ways... just use a varchar(39)

The slightly less efficient storage far outweighs any benefit of having to convert it on insert/update and format it when showing it anywhere.

Neil N
  • 24,862
  • 16
  • 85
  • 145
  • Most full grown databases has a format for storing IPv4 and IPv6 addresses WITH netmask. I would use that. And always translate to the format that is probably the right for the user. And yes, IPv6 is 128 bit long, and so are the netmask (but could be reduced to a 7 bit number up to 128 for IPv6, not for IPv4). – Anders Sep 27 '22 at 17:50
8

As described in the IPv6 Wikipedia article,

IPv6 addresses are normally written as eight groups of four hexadecimal digits, where each group is separated by a colon (:)

A typical IPv6 address:

2001:0db8:85a3:0000:0000:8a2e:0370:7334

This is 39 characters long. IPv6 addresses are 128 bits long, so you could conceivably use a binary(16) column, but I think I'd stick with an alphanumeric representation.

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
4

If you are just storing it for reference, you can store it as a string, but if you want to do a lookup, for example, to see if the IP address is in some table, you need a "canonical representation." Converting the entire thing to a (large) number is the right thing to do. IPv4 addresses can be stored as a long int (32 bits) but you need a 128 bit number to store an IPv6 address.

For example, all these strings are really the same IP address: 127.0.0.1, 127.000.000.001, ::1, 0:0:0:0:0:0:0:1

user410119
  • 41
  • 1
2

IPv4 uses 32 bits, in the form of:

255.255.255.255

I suppose it depends on your datatype, whether you're just storing as a string with a CHAR type or if you're using a numerical type.

IPv6 uses 128 bits. You won't have IPs longer than that unless you're including other information with them.

IPv6 is grouped into sets of 4 hex digits seperated by colons, like (from wikipedia):

2001:0db8:85a3:0000:0000:8a2e:0370:7334

You're safe storing it as a 39-character long string, should you wish to do that. There are other shorthand ways to write addresses as well though. Sets of zeros can be truncated to a single 0, or sets of zeroes can be hidden completely by a double colon.

Chet
  • 21,375
  • 10
  • 40
  • 58
  • Don't forget to store the netmask too. On IPv4, you need 32 more bits, on IPv6 you only need a byte (0-128). And you might also want to store the preferable format to print it, like the format used to enter it to the computer. – Anders Sep 27 '22 at 17:52
1

People are talking about characters when one can compress an IP address into raw data.

So in principle, since we only use IPv4 (32bit) or IPv6 (128bit), that means you need at most 128 bits of space, or 128/8 = 16 bytes!

Which is much less than the suggested 39 bytes (assuming charset is ascii).

That said, you will have to decode and encode the IP address into/from the raw data, which in itself is a trivial thing to do (I've done it before, see PHP's ip2long() for 32-bit IPs).

Edit: inet_pton (and its opposite, inet_ntop()) does what you need, and works with both address types. But beware, on Windows it's available since PHP 5.3.

Christian
  • 27,509
  • 17
  • 111
  • 155
  • @Elipticalview I don't think you read my answer at all. I've never even mentioned 45 bytes anywhere! If you convert any IPv4 value (even bad ones like you mentioned) to raw data, they should never exceed 4 bytes - because no matter the amount of dots you have, they should always amount to 4 numbers each up to at most 255. – Christian Feb 14 '14 at 17:04
  • You can also print out IPv6 addresses with the last 32 bits written out as an IPv4 address. That replace 2*4+1 characters with 4*3+3 characters. But as mentioned, you could store it as 128 bit address, netmask (value can be up to 128), optional device (length depends on OS) and some bits for preference format to print to users. – Anders Sep 27 '22 at 17:39