I'd like to find a way to do a SQL query that will calculate the cidr (bit representation) of a subnet mask stored in the database. So for example, I've got either 255.255.255.0 or its decimal value (4294967040) stored in the database. I'd like to do a select and get back /24 representation via the query.
I've done things like the following to determine the last IP of a subnet so I'm hoping to do something similar to determine the cidr representation of a mask.
select concat(inet_ntoa(ip_addr),'-',
inet_ntoa(ip_addr+(POWER(2,32)-ip_mask-1))) range
from subnets
order by ip_addr
Preferably this would be a SQL statement that would work under mysql, postgres, oracle etc.
I think I have found the solution to my issue. Here is what I have done:
select CONCAT(INET_NTOA(ip_addr),'/',32-log2((4294967296-ip_mask))) net
from subnets
order by ip_addr
Basically I take my decmial mask and subtract it from the maximum decimal value. I then to a log2 on that value to get the logarithm value. Then simply subtract that from 32 (the maximum bit available).
Hope that helps others.
Thanks
SQL queries don't have a procedural looping construct (notwithstanding procedural language), but you can compare one set of rows to another set of rows, which is kind of like a loop.
You only have 32 possible subnet masks. In cases like this, it makes sense to create a small table that stores these 32 masks and the associated CIDR number.
CREATE TABLE cidr (
bits INT UNSIGNED PRIMARY KEY,
mask INT UNSIGNED NOT NULL
);
INSERT INTO cidr (bits) VALUES
( 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 cidr SET mask = ((POWER(2,32)-1)<<(32-bits)) & (POWER(2,32)-1);
SELECT CONCAT(s.ip_addr, '/', c.bits)
FROM cidr c JOIN subnets s ON (c.mask = inet_aton(s.ip_mask));
e.g. you need to convert 255.255.255.252 netmask into bit mask.
I always use this simple query (PostgreSQL) :
SELECT 32-length(trim(((split_part('255.255.255.252','.',1)::bigint*(256^3)::bigint +
split_part('255.255.255.252','.',2)::bigint*(256^2)::bigint +
split_part('255.255.255.252','.',3)::bigint*256 +
split_part('255.255.255.252','.',4)::bigint)::bit(32))::text,'1'));
not as nice as could be, but it's short and working like a charm..
--
-- Dumping routines for database
--
/*!50003 DROP FUNCTION IF EXISTS `INET_ATOC` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ALLOW_INVALID_DATES' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `INET_ATOC`(`paramNETMASK` varchar(15)) RETURNS int(2) unsigned
DETERMINISTIC
COMMENT 'Converts an IPv4 netmask in dotted decimal notation to a CIDR integer between 0 and 32'
BEGIN
DECLARE `netmask` int unsigned;
DECLARE `cidr` int unsigned;
SET `netmask` = INET_ATON(`paramNETMASK`);
IF (`netmask` IS NULL)
THEN
RETURN NULL;
ELSE
SET `cidr` = 0;
countNetBits: WHILE (`cidr` < 32)
DO
IF ( (0x80000000 & `netmask`) = 0x80000000 )
THEN
SET `netmask` = 0xFFFFFFFF & (`netmask` << 1);
SET `cidr` = `cidr` + 1;
ELSE
LEAVE countNetBits;
END IF;
END WHILE;
IF (`netmask` != 0)
THEN
RETURN NULL;
END IF;
RETURN `cidr`;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP FUNCTION IF EXISTS `INET_CTOA` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ALLOW_INVALID_DATES' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `INET_CTOA`(`paramCIDR` int) RETURNS varchar(15) CHARSET utf8
DETERMINISTIC
COMMENT 'Converts a CIDR suffix (integer between 0 and 32) to an IPv4 netmask in dotted decimal notation'
BEGIN
DECLARE `netmask` int unsigned;
IF ( (`paramCIDR` < 0) OR (`paramCIDR` > 32) )
THEN
RETURN NULL;
ELSE
SET `netmask` = 0xFFFFFFFF - (pow( 2, (32-`paramCIDR`) ) - 1);
RETURN INET_NTOA(`netmask`);
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `getSubnet` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getSubnet`(INOUT `paramADDR` VARCHAR(15), INOUT `paramCIDR` INT, OUT `paramMASK` VARCHAR(15), OUT `paramNETWORK` VARCHAR(15), OUT `paramBROADCAST` VARCHAR(15), OUT `paramNUMHOSTS` INT) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE `numaddrs` int unsigned;
DECLARE `ipaddr` int unsigned;
DECLARE `netmask` int unsigned;
DECLARE `wildcard` int unsigned;
DECLARE `network` int unsigned;
DECLARE `broadcast` int unsigned;
DECLARE `numhosts` int unsigned;
SET `ipaddr` = INET_ATON(`paramADDR`);
IF (`ipaddr` IS NULL) OR (`paramCIDR` < 1) OR (`paramCIDR` > 30)
THEN
SELECT
NULL, NULL, NULL, NULL, NULL, NULL
INTO
`paramADDR`, `paramCIDR`, `paramMASK`, `paramNETWORK`, `paramBROADCAST`, `paramNUMHOSTS`;
ELSE
SET `numaddrs` = pow( 2, (32-`paramCIDR`) );
SET `numhosts` = `numaddrs` - 2;
SET `netmask` = 0xFFFFFFFF - (`numaddrs` - 1);
SET `wildcard` = 0xFFFFFFFF & (~`netmask`);
SET `network` = `ipaddr` & `netmask`;
SET `broadcast` = `ipaddr` | `wildcard`;
SELECT
INET_NTOA(`ipaddr`), `paramCIDR`, INET_NTOA(`netmask`), INET_NTOA(`network`), INET_NTOA(`broadcast`), `numhosts`
INTO
`paramADDR`, `paramCIDR`, `paramMASK`, `paramNETWORK`, `paramBROADCAST`, `paramNUMHOSTS`;
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
