CIDR and subnet masks in MySQL

[Floyd-ATC]

Have you ever needed to do IP address calculations in MySQL and gave up because you couldn't figure out how? Here's a few tricks to help you on the way.

Start off by creating a table showing the most common subnet masks.:

Kode: [Velg]

CREATE TABLE netmasks (
  cidr int not null unique primary key,
  netmask varchar(15) not null,
  size BIGINT
);

INSERT INTO netmasks (cidr) VALUES
( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23), (24), (25), (26), (27), (28), (29),
(30), (31), (32);

UPDATE netmasks SET netmask = inet_ntoa(pow(2,32)-pow(2, 32-cidr)), size = POW(2, 32-cidr);
Now define two stored functions to let you convert between CIDR and ordinary (quad decimal octet) subnet masks:

Kode: [Velg]

DELIMITER //

CREATE FUNCTION cidr_of_mask(mask VARCHAR(15))
RETURNS INT
BEGIN
  DECLARE i INT;

  SELECT cidr INTO i
  FROM netmasks
  WHERE netmask = mask;

  RETURN i;
END //

CREATE FUNCTION mask_of_cidr(i INT)
RETURNS VARCHAR(15)
BEGIN
  DECLARE mask VARCHAR(15);

  SELECT netmask INTO mask
  FROM netmasks
  WHERE cidr = i;

  RETURN mask;
END //

DELIMITER ;
Finally, you'll probably want a function to check if a given IP address is within a certain subnet:

Kode: [Velg]

DELIMITER //

CREATE FUNCTION ip_in_subnet(ip VARCHAR(15), net VARCHAR(15), mask VARCHAR(15))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
  RETURN INET_ATON(ip) & INET_ATON(mask) = INET_ATON(net) & INET_ATON(mask);
END //

DELIMITER ;
Good! You can now convert subnet masks as easy as saying

Kode: [Velg]

mysql> select cidr_of_mask("255.255.255.240");             
+---------------------------------+
| cidr_of_mask("255.255.255.240") |
+---------------------------------+
|                              28 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select mask_of_cidr(21); 
+------------------+
| mask_of_cidr(21) |
+------------------+
| 255.255.248.0    |
+------------------+
1 row in set (0.00 sec)Note that non-contiguous subnet masks and invalid CIDR values will produce NULL as expected.

What about checking IP addresses then?

Kode: [Velg]

mysql> select ip_in_subnet("10.0.0.1", "10.0.0.0", "255.255.255.0");
+-------------------------------------------------------+
| ip_in_subnet("10.0.0.1", "10.0.0.0", "255.255.255.0") |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.15 sec)

mysql> select ip_in_subnet("192.168.1.1", "10.0.0.0", "255.255.255.0");       
+----------------------------------------------------------+
| ip_in_subnet("192.168.1.1", "10.0.0.0", "255.255.255.0") |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (0.01 sec)
And because we calculate subnet masks the proper way, it works properly with non-contiguous subnet masks as well:

Kode: [Velg]

mysql> select ip_in_subnet("10.150.37.5", "10.0.32.0", "255.0.248.0");   
+---------------------------------------------------------+
| ip_in_subnet("10.150.37.5", "10.0.32.0", "255.0.248.0") |
+---------------------------------------------------------+
|                                                       1 |
+---------------------------------------------------------+
1 row in set (0.01 sec)
Have fun :-)

[DJdur]

Bare lurer; er det egentlig noen engelske som leser forumet? :p

[Floyd-ATC]

Jeg poster disse tingene når jeg har jobbet lenge med dem uten å finne noe på google. Så kommer de og indekserer sånn at nestemann som lurer på det samme kan finne løsningen :-)

[Morder269]

+ det hjelper med pagerank om noen større sider gir deg noen linker

[Cybersyn]

Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester?

[Emilpoika]

Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester?

Et veldig godt poeng!