Select CIDR from IP Range in MySQL

Question

I have a table of IP ranges, and I need to create a list of networks for rejection for a specific country.

Therefore, I can generate a list of ip ranges from my db using this.

SELECT ip_from, Inet_ntoa(ip_from), ip_to, Inet_ntoa(ip_to) FROM ip_address_data WHERE country_code = 'XX' LIMIT 1 

which generates this result

 ip_from inet_ntoa(ip_from) ip_to inet_ntoa(ip_to) 16777472 1.0.1.0 16778239 1.0.3.255 

But I need this output in CIDR format, and sometimes the range will contain more than one line like this.

 1.0.1.0/24 1.0.2.0/23 

Is there a way to dynamically generate them using the select statement? This syntax would be awesome, but I guess it should be a stored procedure if it will return more than one output line for each line of input.

 SELECT CONCAT('/sbin/route add -net ', CONVERT_TO_CIDR(ip_from,ip_to), ' reject;') AS command FROM ip_info.ip_address_data WHERE country_code='XX' 

Answer 1

This is where a python script is executed to convert. You just need to convert this to a stored procedure.

Convert from IP Range to CIDR Mask