1

How can I check if an ipaddress is part of a subnet, given ipaddress can be ipv4 or ipv6?

I currently have it working for ipv4 only:

set @ip = '1.1.1.1';
set @subnet = '1.1.1.0/32';
set @xor_bw_ip_n_subnet = inet_aton(@ip) ^ inet_aton(substring_index(@subnet, '/', 1));
select ((@xor_bw_ip_n_subnet) >> 32-substring_index(@subnet,'/',-1)) = 0;   -- if ip in subnet, then should be 0

For ipv6, how can I use inet6_ntoa/inet6_aton to check if IPaddress is part of a subnet (basically trying to do bitwise operation on ipv6 address)?

I am using mysql v5.6.19

Thanks,

4
  • Recommend you do this processing in a programming language, not SQL.
    – Rick James
    Commented Jun 22, 2015 at 23:18
  • It makes it really slow if I do it in a programming language, since... I have close to a million subnets to be searched within
    – amulllb
    Commented Jun 23, 2015 at 4:10
  • Are you comparing 1 IP address to a million subnets? Do your subnets overlap?
    – Rick James
    Commented Jun 23, 2015 at 5:30
  • yes. I am comparing 1 ip address to a million subnets. the query perfectly works for ipv4 and is really quick. without this query, from python, its really slow. thats why i wish to do similarly for ipv6
    – amulllb
    Commented Jun 23, 2015 at 5:45

1 Answer 1

1

I assume your IPv6 is in BINARY(16)? Build a 128-row table of the BINARY(16) values for the subnet masks. (/128 is all 1's.) JOIN to the table and do masking on each half of the address --

WHERE (LEFT(ip, 8)  & LEFT(mask, 8))  == LEFT(ip, 8)
  AND (RIGHT(ip, 8) & RIGHT(mask, 8)) == RIGHT(ip, 8)

(No shifting.)

4
  • yes, i am actually storing all the ipaddresses in a table with columns: ipaddr (as varbinary(16)), ipprefixlength (as integer). this table stores ipv4 and ipv6 both addresses which needs to be searched against a ipaddr
    – amulllb
    Commented Jun 23, 2015 at 7:05
  • i am not that good at mysql, can you please provide an example?
    – amulllb
    Commented Jun 23, 2015 at 7:06
  • sorry, but dont LEFT and RIGHT only work on string?
    – amulllb
    Commented Jun 23, 2015 at 7:10
  • never mind, i solved it using javascript/node.js ip-addr programmatic approach... its decently fast
    – amulllb
    Commented Jun 23, 2015 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.