IP Address Binary to Human Readable

Question

I have IP addresses stored in a DB using a command like this:

cast(INET6_ATON(trim(:ipbinary)) as binary(16)))

my column is:

varbinary(16)

I've tried using the mysql function INET6_NTOA to convert it back to the IPv4 format but haven't had any luck with that.

The IP I need is:

66.249.64.90

The DB value is:

42f9405a000000000000000000000000

The INET6_NTOA gives me:

42f9:405a::

and INET6_NTOA(UNHEX( gives me a NULL response. I'm using PHP as my scripting language so if there is a function there as well I also could use that.

Here's my full query:

SELECT delete_ip, INET6_NTOA(ip_binary), ip_binary FROM `stats`

and here's the response:

Thanks.

(I can't just use delete_ip because as the name implies the column is going to be dropped.)

Can you tell us why you're using ipV6 functions to handle ipV4 addresses? — O. Jones, Apr 08 '18 at 20:52
The `ip_binary` is suppose to hold a binary representation of the IP address that came to the page, regardless of IPV4 or IPV6. The manual, https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html, has them using IPV4 and IPV6. Is that incorrect? — user3783243, Apr 08 '18 at 21:27

score 3 · Accepted Answer · edited Apr 12 '18 at 11:59

3

It's not converting back to IPv4 human readable format, it's converting to IPv6 because the argument to INET6_NTOA (the binary value) is 16 bytes.

The function is seeing it as a representation of an IPv6 address, not an IPv4 address, which is only four bytes.

I think the issue can be traced back to the first line of SQL in the question, the cast to BINARY(16). Which is returning a fixed length of 16 bytes. Starting with the four bytes returned for the IPv4 address, and then padded on the right with zeros up to a length of 16 bytes.

What happens if we remove the cast to fixed length, and allow the result of INET6_ATON function to be just four bytes?

What happens when the value stored in the database is just four bytes?

What if we correct the contents of the stats table, to change that 16 byte binary value (representation of an IPv6 address) to a four byte binary representation of an IPv4 adddress

UPDATE `stats` 
   SET ip_binary = INET6_ATON('66.249.64.90')
 WHERE ip_binary = CAST(INET6_ATON('66.249.64.90') AS BINARY(16))

--or--

UPDATE `stats` 
   SET ip_binary = X'42f9405a' + 0 
 WHERE ip_binary = X'42f9405a000000000000000000000000' + 0

Followup

Question says... "storing IP addresses in database [column] using [expression] like this:

 cast(INET6_ATON(trim(:ipbinary)) as binary(16)))

We don't need to use CAST. And we don't need to use CONVERT, HEX/UNHEX or SUBSTR. Convert IPv4 and IPv6 addresses with the same expression:

  INSERT ... ip_binary ... VALUES ( ... , INET6_ATON( :ip_string ) , ...

And convert them back to strings like this:

 SELECT ... , INET6_NTOA( ip_binary ) AS ip_string , ...

The rigmarole with CAST, CONVERT, SUBSTR, HEX/UNHEX is confusing, and is causing things not to work.

To correct values that are already stored in the database, we need a way to distinguish which of the 16-byte binary representations are actually IPv4 addresses, that should have been stored as 4 bytes.

If ip_delete contains the string representations, we can re-convert to the binary representation.

 UPDATE `stats`
    SET ip_binary = INET6_ATON( ip_delete )

Demonstration

CREATE TABLE `addr` (ip_string VARCHAR(45), ip_binary VARBINARY(16)) ;

INSERT INTO `addr` VALUES ( '66.249.64.90'         , INET6_ATON( '66.249.64.90'         ));
INSERT INTO `addr` VALUES ( '127.0.0.1'            , INET6_ATON( '127.0.0.1'            ));
INSERT INTO `addr` VALUES ( '192.168.1.1'          , INET6_ATON( '192.168.0.1'          ));
INSERT INTO `addr` VALUES ( '2001:4860:4860::8888' , INET6_ATON( '2001:4860:4860::8888' ));

SELECT ip_string, HEX(ip_binary), INET6_NTOA(ip_binary) FROM `addr` ;

ip_string             HEX(ip_binary)                    INET6_NTOA(ip_binary)
--------------------  --------------------------------  -----------------------
66.249.64.90          42F9405A                          66.249.64.90
127.0.0.1             7F000001                          127.0.0.1
192.168.1.1           C0A80001                          192.168.0.1
2001:4860:4860::8888  20014860486000000000000000008888  2001:4860:4860::8888

edited Apr 12 '18 at 11:59

marc_s

732,580
175
1,330
1,459

answered Apr 10 '18 at 20:30

spencer7593

106,611
15
112
140

Yes, you are correct. The IPV4 address is padded with `0`s`. How do I get it back with a select though? I need to allow for the 16 bytes so IPV6 IPs can go in this column as well. – user3783243 Apr 10 '18 at 21:42
Okay, I think I figured it out for ipv4 addresses, but how can I have it be interchangeable between 6 and 4 addresses? Here's my ipv4 solution `INET6_NTOA(UNHEX(SUBSTRING( CONVERT(HEX(ip_binary), CHAR(32)), 1, 8)))` maybe a `CASE` if the last X characters are all `0`, that seems hacky though. – user3783243 Apr 10 '18 at 21:53
@user3783243: ditch the rigmarole with `SUBSTRING`,`HEX`,`UNHEX`,`CONVERT`,`CAST` ... none of that is needed. The table column is `VARBINARY`. Just store 4 bytes for IPv4 addresses, and 16 bytes for IPv6 addresses. Let `INET6_ATON` do the conversion to binary. The return from `INET6_ATON` is `VARBINARY`. It returns four bytes for IPv4 addresses, and 16 bytes for IPv6. That's what should be stored in the table column. And then `INET6_NTOA` can convert it back from binary. "Followup" is added to my answer. – spencer7593 Apr 11 '18 at 13:30
The current method is a Percona recommendation. They said `Operator between: where $user_ip between ip_start and ip_end still works with binary and varbinary data. Unfortunately between operator require that number of allocated bytes will be the same. If address stored as 32bit, but mask is 128bit, false will be returned even if left bytes all zeroes. Cast from binary(4) to binary(16) adds zero bytes from the right side and concat with 12 bytes should be used instead` so I think we should keep it as it is, or is some of that incorrect? We use `between` elsewhere, hopefully not headed to an XY – user3783243 Apr 11 '18 at 14:37
@user3783243: If there's an issue with the `BETWEEN` operator, then I would address that issue in statements that actually use the `BETWEEN` operator. I would finagle with any casting/converting to fixed length where it's actually needed. I wouldn't muck with changing the binary representation of IP addresses for something that is probably not an actual problem. – spencer7593 Apr 11 '18 at 14:48
@user3783243: What "seems hacky though" is storing 16 binary bytes for an IPv4 address. If there is some sort of actual requirement for storing fixed length of 16 binary bytes for IPv4 addresses, I would be thinking in terms of storing an IPv6 translated IPv4 address; or if we actually need a hacky 16-byte representation with 12bytes of zeros tacked on at the end, I would be thinking in terms of an additional column. – spencer7593 Apr 11 '18 at 14:55
Thanks, that makes sense. Will award the bounty when the time constraint passes. – user3783243 Apr 11 '18 at 15:10
I'm curious about the Percona recommendation. To me, that's just another reason to avoid using the `BETWEEN` operator. We can implement this `a BETWEEN b AND c` as `a >= b AND a <= c`. And in my experience, a lot of times I see BETWEEN misused, with datetime and decimal values, where what we really want is an inequality on one side, so we avoid "double counting" on boundary conditions e.g. `BETWEEN 5.0 AND 6.0` and `BETWEEN 6.0 AND 7.0` both evaluate to true for a value of 6.0. And changing that to `BETWEEN 5.0 AND 5.99` fixes the overlap, but introduces a gap. – spencer7593 Apr 11 '18 at 15:24
We were using the integer value of IPV4 addresses so the `between` was easy there. We have since upgraded to using the suggestion of using the padded binary version which has worked fine computationally. We do `from stats where ip_binary between start_range and end_range` (the range columns are in a joined table but I don't think that's needed here. they are the same varbinary(16)). So using `from stats where ip_binary >= start_range and ip_binary <=end_range` won't matter if the byte length doesn't match? – user3783243 Apr 11 '18 at 16:33
1

Maybe this help to somebody in the same situation, I was getting CRAZY because mysql always returned IPv6 values even with iPv4 addresses, the "trick" was to change the IP row data type from fixed:BINARY(16) to not fixed:VARBINARY(16) – D.Snap Sep 24 '19 at 13:17
@D.Snap: The crux of the OP problem was padding a 4-byte binary IPv4 address with extra zeros. And that's going to happen when we store (or cast/convert) to a *fixed length* BINARY(16). The `INET6_NTOA` and `INET6_ATON` functions work as advertised... 4 bytes for IPv4 addresses and 16 bytes for IPv6 addresses. And as noted in your comment, we create the problem when we "pad" the 4 byte addresses to make them a length of 16 bytes. – spencer7593 Sep 24 '19 at 16:41

IP Address Binary to Human Readable

1 Answers1