Convert IPv6 range in CIDR format to an IPv6 address range (start and end ips) in MySQL

Question

I am trying to import GeoLite2 ip to country csv file to my MySQL database. I was succesfully able to do that, however, to use it to search for IPs in my queries I need to create two more fields in both the ipv4 and ipv6 tables to store the start_ip and end_ip values for each CIDR range given (GeoLite csv files specify only the range in CIDR format).

I was able to convert this correctly for ipv4 address ranges using the solution given here Importing MaxMind's GeoLite2 to MySQL

So I used

INET_ATON(SUBSTRING(network, 1, LOCATE('/', network) - 1))

for start_ip and

INET_ATON(SUBSTRING(network, 1, LOCATE('/', network) - 1)) + (POW(2, (32-CONVERT(SUBSTRING(network, LOCATE('/', network) + 1), UNSIGNED INT)))-1)

for end_ip. network is the name of the field where the CIDR range is stored.

However this does not work for IPv6 address ranges in CIDR format. I tried using INET6_ATON instead of INET_ATON but it gives me a numeric value out of range error. I am not much knowledgeable about IPv6 addresses so finding a formula myself is not easy.

Any help appreciated.

Answer 1

I had this same issue and found the following blog post that suggests how to get what you are looking for. I took inspiration from that post but modified it some. Here is my solution:

    DELIMITER //

    -- Returns the network address using an IPv4 address and the network length.
    CREATE FUNCTION ipv4_subnet(ip BINARY(4), net_len int) RETURNS BINARY(4)
        DETERMINISTIC
    BEGIN
        DECLARE zeroIp BINARY(4) DEFAULT b'0';
        RETURN ip & (~zeroIp << (32 - net_len));
    END
    //

    -- Returns the host mask of an IPv4 address given the network length.
    CREATE FUNCTION ipv4_host_mask(net_len int) RETURNS BINARY(4)
        DETERMINISTIC
    BEGIN
        DECLARE zeroIp BINARY(4) DEFAULT b'0';
        RETURN ~zeroIp >> net_len;
    END
    //

    -- Returns the network address using an IPv6 address and the network length.
    CREATE FUNCTION ipv6_subnet(ip BINARY(16), net_len int) RETURNS 
    BINARY(16)
        DETERMINISTIC
    BEGIN
        DECLARE zeroIp BINARY(16) DEFAULT b'0';
        RETURN ip & ((~zeroIp << (128 - net_len)));
    END
    //

    -- Returns the host mask of an IPv6 address given the network length.
    CREATE FUNCTION ipv6_host_mask(net_len int) RETURNS binary(16)
        DETERMINISTIC
    BEGIN
        DECLARE zeroIp BINARY(16) DEFAULT b'0';
        RETURN (~zeroIp >> net_len);
    END
    //

Note that I plan to store both the IPv4 and IPv6 from and to addresses in the same table declared as a VARBINARY(16). Also, I switched to declaring a BINARY(4|16) for the functions I declared rather than calling INET6_ATON because I found that INET6_ATON('0.0.0.0'); returns 0x00 and declaring the return as BINARY guarantees it will be left padded with the zero's you want for the methods and doesn't depend on the behavior of the INET6_ATON function. I suspect that this implementation may be better in performance as well but I haven't done any testing to prove that, but it is simply more straightforward, in my opinion, to declare a BINARY of a certain size and set it to 0 than to depend on the behavior of another function that returns a VARBINARY to do that for you.

You then determine the range for an IPv4 addresses and IPv6 addresses similar to what is suggested in the blog post. If you wanted to define functions for them:

    -- Determine from range for IPv4 address in CIDR format.
    CREATE FUNCTION ipv4_from_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
        DETERMINISTIC
    BEGIN
        RETURN ipv4_subnet(ip, net_len);
    END
    //

    -- Determine to range for IPv4 address in CIDR format.
    CREATE FUNCTION ipv4_to_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
        DETERMINISTIC
    BEGIN
        RETURN ipv4_subnet(ip, net_len) | ipv4_host_mask(net_len);
    END
    //

    -- Determine from range for IPv6 address in CIDR format.
    CREATE FUNCTION ipv6_from_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
        DETERMINISTIC
    BEGIN
        RETURN ipv6_subnet(ip, net_len);
    END
    //

    -- Determine to range for IPv6 address in CIDR format.
    CREATE FUNCTION ipv6_to_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
        DETERMINISTIC
    BEGIN
        RETURN ipv6_subnet(ip, net_len) | ipv6_host_mask(net_len);
    END
    //

I know your question was only about IPv6, but since one response suggested you could do both IPv4 and IPv6 with INET6_ATON, I thought I'd answer his thought as well.

EDIT: 7/25/2022 I just realized that the functions to determine the "to" addresses don't need to call the subnet functions. In other words:

    -- Determine to range for IPv6 address in CIDR format.
    CREATE FUNCTION ipv6_to_addr(ip BINARY(16), net_len int) RETURNS BINARY(16)
        DETERMINISTIC
    BEGIN
        RETURN ip | ipv6_host_mask(net_len);
    END
    //

    -- Determine to range for IPv4 address in CIDR format.
    CREATE FUNCTION ipv4_to_addr(ip BINARY(4), net_len int) RETURNS BINARY(4)
        DETERMINISTIC
    BEGIN
        RETURN ip | ipv4_host_mask(net_len);
    END
    //

should suffice (and be slightly more efficient since you are doing fewer operations).

Answer 2

You can use my tool for converting MaxMind GeoLite2 country/city CSV to MySQL/PostgreSQL/Microsoft SQL Server 2019 from GitHub https://github.com/mbto/maxmind-geoip2-csv2sql-converter

You can read examples, or follow this steps:

1. Take a free license key to MaxMind API (if you don't have) at https://support.maxmind.com/account-faq/license-keys/how-do-i-generate-a-license-key/
2. Install Java 11 (if not installed) at adoptopenjdk.net or github.com/raphw/raphw.github.io or oracle.com/java
3. Download a tool from releases (.zip or .tar)
4. Unpack to yours directory
5. Copy/Paste .ini template bin/GeoLite2-Country-CSV.mysql.default.ini with your profile name, example bin/GeoLite2-Country-CSV.mysql.Your Project Name.ini or use default.
6. Open .ini template with Notepad and change [windows_loader] or [unix_loader] section (set MySQL host:port, user and password).
7. For unix: Execute chmod +x maxmind-geoip2-csv2sql-converter
8. Run converting: maxmind-geoip2-csv2sql-converter.bat -c "GeoLite2-Country-CSV.mysql.Your Project Name.ini" -k Your_License_Key -i 4,6
9. After converting, the scripts bin/converted/loader.bat and bin/converted/loader.sh will be available.
10. For unix: Execute chmod +x loader.sh
11. Execute loader.bat or loader.sh for importing schemas.

Done

Answer 3

Use a column of type VARBINARY(16) to store the values. And you can use INET6_ATON for both IPv4 and IPv6 addresses.

Reference: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton