Max Length for Client Ip Address

Max length for client ip address

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

Maximum length of the textual representation of an IPv6 address?

45 characters.

You might expect an address to be

0000:0000:0000:0000:0000:0000:0000:0000

8 * 4 + 7 = 39

8 groups of 4 digits with 7 : between them.

But if you have an IPv4-mapped IPv6 address, the last two groups can be written in base 10 separated by ., eg. [::ffff:192.168.100.228]. Written out fully:

0000:0000:0000:0000:0000:ffff:192.168.100.228

(6 * 4 + 5) + 1 + (4 * 3 + 3) = 29 + 1 + 15 = 45

Note, this is an input/display convention - it's still a 128 bit address and for storage it would probably be best to standardise on the raw colon separated format, i.e. [0000:0000:0000:0000:0000:ffff:c0a8:64e4] for the address above.

Size for storing IPv4, IPv6 addresses as a string

Assuming textual representation in a string :

• 15 characters for IPv4 (xxx.xxx.xxx.xxx format, 12+3
  separators)
• 45 characters for IPv6

Those are the maximum length of the string.

Alternatives to storing as string:

• IPv4 is 32-bits, so a MySQL data type that can hold 4 bytes will do, using INT UNSIGNED is common along with INET_ATON and INET_NTOA to handle the conversion from address to number, and from number to address

SELECT INET_ATON('209.207.224.40');
        -> 3520061480

SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'

• For IPv6, unfortunately MySQL does not have a data type that is 16 bytes, however one can put the IPv6 into a canonical form, then separate them into 2 BIGINT (8 bytes), this however will use two fields.

What's the minimum length of an IP address in string representation?

1.1.1.1 would be a valid IP address, so the answer to your question is "7".

What is the most appropriate data type for storing an IP address in SQL server?

Storing an IPv4 address as a binary(4) is truest to what it represents, and allows for easy subnet mask-style querying. However, it requires conversion in and out if you are actually after a text representation. In that case, you may prefer a string format.

A little-used SQL Server function that might help if you are storing as a string is PARSENAME, by the way. Not designed for IP addresses but perfectly suited to them. The call below will return '14':

SELECT PARSENAME('123.234.23.14', 1)

(numbering is right to left).

What is the maximum possible length of a query string?

RFC 2616 (Hypertext Transfer Protocol — HTTP/1.1) states there is no limit to the length of a query string (section 3.2.1). RFC 3986 (Uniform Resource Identifier — URI) also states there is no limit, but indicates the hostname is limited to 255 characters because of DNS limitations (section 2.3.3).

While the specifications do not specify any maximum length, practical limits are imposed by web browser and server software. Based on research which is unfortunately no longer available on its original site (it leads to a shady seeming loan site) but which can still be found at Internet Archive Of Boutell.com:

• Microsoft Edge (Browser)
  
  The limit appears to be around 81578 characters. See URL Length limitation of Microsoft Edge

• Chrome
  
  It stops displaying the URL after 64k characters, but can serve more than 100k characters. No further testing was done beyond that.

• Firefox (Browser)
  
  After 65,536 characters, the location bar no longer displays the URL in Windows Firefox 1.5.x. However, longer URLs will work. No further testing was done after 100,000 characters.

• Safari (Browser)
  
  At least 80,000 characters will work. Testing was not tried beyond that.

• Opera (Browser)
  
  At least 190,000 characters will work. Stopped testing after 190,000 characters. Opera 9 for Windows continued to display a fully editable,
  copyable and pasteable URL in the location bar even at 190,000 characters.

• Microsoft Internet Explorer (Browser)
  
  Microsoft states that the maximum length of a URL in Internet Explorer is 2,083 characters, with no more than 2,048 characters in the path portion of the URL. Attempts to use URLs longer than this produced a clear error message in Internet Explorer.

• Apache (Server)
  
  Early attempts to measure the maximum URL length in web browsers bumped into a server URL length limit of approximately 4,000 characters, after which Apache produces a "413 Entity Too Large" error. The current up to date Apache build found in Red Hat Enterprise Linux 4 was used. The official Apache documentation only mentions an 8,192-byte limit on an individual field in a request.

• Microsoft Internet Information Server (Server)
  
  The default limit is 16,384 characters (yes, Microsoft's web server accepts longer URLs than Microsoft's web browser). This is configurable.

• Perl HTTP::Daemon (Server)
  
  Up to 8,000 bytes will work. Those constructing web application servers with Perl's HTTP::Daemon module will encounter a 16,384 byte limit on the combined size of all HTTP request headers. This does not include POST-method form data, file uploads, etc., but it does include the URL. In practice this resulted in a 413 error when a URL was significantly longer than 8,000 characters. This limitation can be easily removed. Look for all occurrences of 16x1024 in Daemon.pm and replace them with a larger value. Of course, this does increase your exposure to denial of service attacks.

Why is INET6_ADDRSTRLEN defined as 46 in C?

Why is INET6_ADDRSTRLEN defined as 46 in C?

Because POSIX defines it to be 46:

INET6_ADDRSTRLEN

46. Length of the string form for IPv6.

While you are right that longtest IPv6 address takes 39 bytes, with IPv4 tunneling, the longest form can be 45 bytes:

ffff:ffff:ffff:ffff:ffff:ffff:255.255.255.255

And the 46th byte is for the terminating nul byte (in C a string). This explains how it came to be 46.

Datatype for storing ip address in SQL Server

The technically correct way to store IPv4 is binary(4), since that is what it actually is (no, not even an INT32/INT(4), the numeric textual form that we all know and love (255.255.255.255) being just the display conversion of its binary content).

If you do it this way, you will want functions to convert to and from the textual-display format:

Here's how to convert the textual display form to binary:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
go

And here's how to convert the binary back to the textual display form:

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @str AS VARCHAR(15) 

    SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
                + CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );

    RETURN @str
END;
go

Here's a demo of how to use them:

SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go

Finally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.

---

UPDATE:

I wanted to add that one way to address the inherent performance problems of scalar UDFs in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
    SELECT CAST(
               CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            +  CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
                AS BINARY(4)) As bin
        )
go

Here's it in the example:

SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

And here's how you would use it in an INSERT

INSERT INTo myIpTable
SELECT {other_column_values,...},
       (SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))

---

Related Topics