Forums.ATC.no
Teknisk => Generelt teknisk => Emne startet av: Floyd-ATC på 09. April 2013, 23:00 pm
-
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.:
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:
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:
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
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?
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:
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 :-)
-
Bare lurer; er det egentlig noen engelske som leser forumet? :p
-
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 :-)
-
+ det hjelper med pagerank om noen større sider gir deg noen linker :)
-
Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester? ???
(http://bildr.no/thumb/ckVIbitN.jpeg) (http://bildr.no/view/ckVIbitN)
-
Tråden blir vel ikke indekset hvis forumet den ligger i ikke er synlig for gjester? ???
(http://bildr.no/thumb/ckVIbitN.jpeg) (http://bildr.no/view/ckVIbitN)
Et veldig godt poeng! :P