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'

why downvote? Your reason doesn't work. I'm not looking for opinions this is a simple math problem that I need help with. — jbrahy, May 08 '17 at 17:30
This conversation might help http://stackoverflow.com/questions/595748/is-there-way-to-match-ip-with-ipcidr-straight-from-select-query?rq=1 — Johnny 3653925, May 08 '17 at 17:52

score 3 · Accepted Answer · edited May 23 '17 at 11:54

3

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

edited May 23 '17 at 11:54

Community

1
1

answered May 08 '17 at 17:54

Johnny 3653925

349
1
3
10

Thanks, that looks helpful. – jbrahy May 08 '17 at 17:55

Select CIDR from IP Range in MySQL

1 Answers1