Select CIDR from IP Range in MySQL

Question

I have a table of IP Ranges and I need to generate a list of networks to reject for a specific country.

So 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 that output in CIDR format and sometimes the range will be more than one row returned like this.

1.0.1.0/24
1.0.2.0/23

Is there any way to dynamically generate these using a select statement? This syntax would be awesome but I'm assuming it'll have to be a stored procedure if it's going to return more than one output row per input row.

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

Here's a python script to do the conversion. Just need to convert this to a stored procedure.

Conversion from IP Range to CIDR Mask