CODEWITHSUNDEEP
mysqlbit-manipulationipv6
Milananas
Milananas

Reputation: 116

Proper way to find IPv6 in CIDR Range MySQL

I have a MySQL blacklist table, with either single IPv4, IPv6 or CIDR ranges of one of both types stored in it.

My table looks somewhat like this:

 +-----------+-------------+
 | Name      | Type        |
 +-----------+-------------+
 | IpAddress | VARCHAR(46) |
 +-----------+-------------+
 | Mask      | INT(2)      |
 +-----------+-------------+

Now, I'd like to check whether a given IP address is in one of the saved CIDR ranges. I already accomplished this after a few days of researching a whole lot of pages, and finally just ported the following solution to MySQL: PHP5 calculate IPv6 range from cidr prefix?

So I transform a IP + CIDR mask to the first and last IP in that range, which I then convert to numbers using INET6_ATON and compare it with the BETWEEN operator.

My implementation:

Get last ip

FUNCTION (`Ip` VARCHAR(46), `Mask` INT(2) UNSIGNED) RETURNS varchar(39)
BEGIN
    DECLARE IpNumber VARBINARY(16);
    DECLARE Last VARCHAR(39) DEFAULT '';
    DECLARE FlexBits, Counter, Deci, NewByte INT UNSIGNED;
    DECLARE HexIp VARCHAR(32);

    SET IpNumber = INET6_ATON(Ip);
    SET HexIp    = HEX(IpNumber);
    SET FlexBits = 128 - Mask;
    SET Counter  = 32;

    WHILE (FlexBits > 0) DO
        SET Deci    = CONV(SUBSTR(HexIp, Counter, 1), 16, 10);
        SET NewByte = Deci | (POW(2, LEAST(4, FlexBits)) - 1);
        SET Last    = CONCAT(CONV(NewByte, 10, 16), Last);

        IF FlexBits >= 4 THEN SET FlexBits = FlexBits - 4;
        ELSE SET FlexBits = 0;
        END IF;

        SET Counter  = Counter - 1;
    END WHILE;

    SET Last = CONCAT(SUBSTR(HexIp, 1, Counter), Last);

    RETURN INET6_NTOA(UNHEX(Last));
END

Get first ip

FUNCTION (`Ip` VARCHAR(46), `Mask` INT(2) UNSIGNED, `WithMask` BOOLEAN) RETURNS varchar(39)
BEGIN
    DECLARE First VARCHAR (42) DEFAULT '';

    SET First = INET6_NTOA(UNHEX(RPAD(SUBSTR(HEX(INET6_ATON(Ip)), 1, Mask / 4), 32, 0)));

    IF (WithMask = 1) THEN
        SET First = CONCAT(First, '/', CAST(Mask AS CHAR));
    END IF;

    RETURN First;
END

This works fine! I just have the idea that it could be done way more efficient using some clever bit operations. I've read tons of questions about this topic, but I didn't really found a concrete solution Any help in the right direction would be really appreciated!

NOTE: Only for IPv6, I've got IPv4 implemented the correct way.

Upvotes: 3

Views: 2022

Answers (2)

Zoltán Süle
Zoltán Süle

Reputation: 1694

I found a bug in your Get last ip function.

instead of this:

SET FlexBits = FlexBits - 4;

use this:

IF FlexBits >= 4 THEN SET FlexBits = FlexBits - 4;
ELSE SET FlexBits = 0;
END IF;

Otherwise you will get this kind of error message:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(FlexBits@4 - 4)'

When you try to get the last IP of these IPv6 ranges (where the mask cannot be divided by 4):

SELECT getLastIp("2001:200:1::", 47);
SELECT getLastIp("2001:200:0:8000", 49);
SELECT getLastIp("2001:470:1f06:2000::", 51);
SELECT getLastIp("2001:470:0:284:2::", 79);
SELECT getLastIp("2001:470:1f08:415::8:0", 109);
SELECT getLastIp("2001:550:0:1000::8275:8000", 113);
SELECT getLastIp("2001:550:0:1000::9a19:300", 123);
SELECT getLastIp("2001:550:0:1000::9a19:320", 126);

Upvotes: 1

manuel
manuel

Reputation: 11

I've implemented this for MySQL the same way about a year ago. So the idea is fine. However there's a small but rather significant bug in your code.

The line:

SET Last = CONCAT(Last, CONV(NewByte, 10, 16));

should rather be:

SET Last = CONCAT(CONV(NewByte, 10, 16), Last);

Upvotes: 1

Related Questions