SQL: Binary to IP Address

Question

I'm trying to convert A binary IP to a human-readable IP

SELECT HEX( `ip_bin` ) FROM `log_metadata`

gives me 4333D26E000000000000000000000000

And

SELECT INET_NTOA(0x4333D26E)

gives me 67.51.210.110

So I tried:

SELECT
  SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 ) AS `A`
, INET_NTOA( 
  SUBSTRING( CONVERT(HEX(`ip_bin`), CHAR(32)), 1, 8 ) 
                                                     ) AS `B`
, INET_NTOA(hex(`ip_bin`))  AS `C`
, INET_NTOA(`ip_bin`)       AS `D`
FROM `log_metadata`

But I only get

+----------+------------+------------+---------+
| A        | B          | C          | D       |
+----------+------------+------------+---------+
| 4333D26E | 0.0.16.237 | 0.0.16.237 | 0.0.0.0 |
+----------+------------+------------+---------+

Any suggestions?

Solution 1

mysql> select inet_ntoa(conv('4333d26e', 16, 10));
+-------------------------------------+
| inet_ntoa(conv('4333d26e', 16, 10)) |
+-------------------------------------+
| 67.51.210.110                       |
+-------------------------------------+
1 row in set (0.00 sec)

Check if it works there too =)

Edit

The problem is that inet_ntoa seems to parse from decimal strings number representation, not hexadecimal ones, or from hexadecimal integers. Compare:

mysql> select inet_ntoa(0x4333d26e);
+-----------------------+
| inet_ntoa(0x4333d26e) |
+-----------------------+
| 67.51.210.110         |
+-----------------------+
1 row in set (0.02 sec)

mysql> select inet_ntoa('0x4333d26e');
+-------------------------+
| inet_ntoa('0x4333d26e') |
+-------------------------+
| 0.0.0.0                 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Edit

This is simpler and seems to work too:

SELECT INET_NTOA(CONV(ip_bin, 2, 10)) FROM log_metadata

Solution 2

When using Mysql 5.6.3 or later, it is easier to just use INET6_NTOA - it takes a binary string and returns the human readable format for it. It also supports both IPv4 and IPv6 addresses and returns the format accordingly. So in your example you would use:

SELECT INET6_NTOA( `ip_bin` ) FROM `log_metadata`

And should get the human readable result.

Solution 3

I found I had to call HEX to convert my binary field to a hex string first, so the following worked for me:

select inet_ntoa(conv(HEX(ip_bin), 16, 10)) from log_metadata

Solution 4

FYI~ this works in newer version of mysql

 SELECT INET6_NTOA( `ip_bin` ) FROM `log_metadata`

which is the same as

SELECT INET_NTOA( CONV( SUBSTRING(HEX(`ip_bin` ), 1, 8), 16, 10 )) FROM log_metadata;

View more solutions

Comments

[redolent]

I ended up using INET_NTOA( CONV( SUBSTRING(HEX( ip_bin ), 1, 8), 16, 10 ))

[Niloct]

Try this: SELECT INET_NTOA(CONV(ip_bin, 2, 10)) FROM log_metadata

[redolent]

Didn't work. The problem is that the bits are on the left for some reason - 0x4333D26E000000000000000000000000

[Niloct]

You can try shifting those to the right. Mysql has shift operators.

[redolent]

MySQL seems to hate those, however. My best guess is it parses the value to be integer max

[bansal]

SELECT INET_NTOA(CONV(ip_bin, 2, 10)) FROM log_metadata , not working for me.

[Niloct]

@Abhi post a new question with more details of your problem.

[hanshenrik]

- and the appropriate storage type is VARBINARY(16) - VAR because it's 4 for ipv4 and 16 for ipv6