Search code examples
mysqlmariadbipv6

Check if ipv6 is inside range

I have the maxmind's ipv6 data. Here's my current table (with sample data):

+---------------+------------+
|    network    | geoname_id |
+---------------+------------+
| 2001:208::/32 |        123 |
| 2001:218::/32 |       4312 |
+---------------+------------+

Using their converter, I can create a network_start_ipand network_last_ip columns:

+------------------+----------------------------------------+------------+
| network_start_ip |            network_last_ip             | geoname_id |
+------------------+----------------------------------------+------------+
| 2001:200::       | 2001:200:ffff:ffff:ffff:ffff:ffff:ffff |        123 |
| 2001:208::       | 2001:208:ffff:ffff:ffff:ffff:ffff:ffff |       4312 |
+------------------+----------------------------------------+------------+

I was expecting that something like this would work (even though it would probably be slower than other methods):

SELECT b.geoname_id FROM blocks b 
WHERE HEX(INET6_ATON('2001:201:ffff:ffff:ffff:ffff:ffff:ffff')) BETWEEN HEX(b.network_start_ip) AND HEX(b.network_last_ip)

So, what am I missing? Also, what is the best way to store ipv6 addresses (ranges)

Thank you

Solution

  • Here's how I've got it working:

    1. Created another table with the same columns, but network_start_ip and network_last_ip are VARBINARY(16)
    2. Populated that table with this statement: INSERT INTO blocks SELECT INET6_ATON(b2.network_start_ip), INET6_ATON(b2.network_last_ip), b2.geoname_id FROM blocks_copy b2;
    3. Then, to check if the IPv6 address is in the range, I just need to run this query: SELECT geoname_id FROM blocks b WHERE INET6_ATON('2a01:4ff:ffff:ffff::ffff') BETWEEN b.network_start_ip AND b.network_last_ip