Bind acl file generate from CSV ip list using bash

Question

I would like to generate geoiplist.acl file from a csv file. acl file format:

acl "A1" {
    31.14.133.39/32;
    37.221.172.0/23;
acl "A2" {
    5.145.149.142/32;
    57.72.6.0/24;
......

the csv file: http://download.db-ip.com/free/dbip-country-2016-09.csv.gz

Here are sample lines from CSV file with IP_Start, IP_End and Country columns.

"0.0.0.0","0.255.255.255","US"
"1.0.0.0","1.0.0.255","AU"
"1.0.1.0","1.0.3.255","CN"
"1.0.4.0","1.0.7.255","AU"
"1.0.8.0","1.0.15.255","CN"
"1.0.16.0","1.0.31.255","JP"
"1.0.32.0","1.0.63.255","CN"
"1.0.64.0","1.0.127.255","JP"
"1.0.128.0","1.0.255.255","TH"
"1.1.0.0","1.1.0.255","CN"

I got some references from here: http://geoip.site/ but their acl don't have complete list.

Anyone can help me to do this in bash code please. Thanks in advance.

You need to show some efforts what you have already tried. Also paste sample CSV here. — P...., Sep 18 '16 at 06:27
Please post one or two lines (as much as relevant) from the csv file here so that somebody could guide you in mapping your csv to acl format. — blackpen, Sep 18 '16 at 06:28
@user3850712, is your problem that their ACL file is not complete? They have published a script there (**Recursive Version (fastest)**) that you can run to generate a latest ACL file. Isn't that what you want? — blackpen, Sep 18 '16 at 08:01

parkamark · Accepted Answer · 2020-04-18T20:51:12.880

The issue here is that DB-IP provide the begin and end value of each range in human readable IP address format. Why they have done this, I'm not sure, because the more universal (easier to process) format is to simply present these values in integer form.

In any case, I have modified the Python script on http://geoip.site/ to handle this and included the DB-IP database URL within the script. The ACL file generated from their CSV file is now also available to download from http://geoip.site/download/DB-IP/GeoIP.acl

Note I have already identified some issues with this database:

The entry "::","2001:1ff:ffff:ffff:ffff:ffff:ffff:ffff","US" exists in it. This is obviously complete rubbish and also broke the Python script (I've improved the error detection code to handle this) and is also one of the reasons for point 4 below.
"224.0.0.0","255.255.255.255","CH" is an interesting entry. I'm not entirely sure how they have deemed the entire multicast block of the IPv4 address space to be delegated to Switzerland, or why it exists in their database at all.
The statistical analysis (available on http://geoip.site/) suggests that their DB/CSV file spans 100% of the IPv4 address space outside 224.0.0.0/3 (multicast). That's 3,758,096,384 addresses. But we already know that several address blocks should not exist in here, the obvious ones being 10.0.0.0/8, 172.16.0.0/12 and 192.168.0.0/16 (and indeed others; further investigation reveals the entry "192.168.0.0","192.169.31.255","US" exists, which covers 192.168.0.0/16 and beyond). So this result looks questionable.
The statistical analysis also reports that their DB/CSV spans 100% of the IPv6 address space. This is primarily because they have mapped 3000::/4 (and various other smaller address blocks) to the US, which is wrong (see http://www.iana.org/assignments/ipv6-unicast-address-assignments/ipv6-unicast-address-assignments.xhtml where 3000::/4 is listed as RESERVED). This mapping originates from the entry "2c10::","ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff","US". The other 2 databases on http://geoip.site/ are nowhere close to this magnitude of coverage across the IPv6 address space (both are currently less than 0.1%), so this result also looks questionable.

Given all of the above, I would question the accuracy of their database and contact them about it. But feel free to download the http://geoip.site/download/DB-IP/GeoIP.acl file if you wish to use it.

Finally, I would not have even attempted this in BASH. The conversions required to produce this file from their CSV file are only available in more advanced languages like Python; BASH just wouldn't cut this (well, not my BASH).

I hope this has helped resolved your query/problem.

UPDATE

As of the December 2016 version of their database, DB-IP have introduced a ZZ acl to cover IPv4 networks that are outside the realm of being mapped to any specific country. This certainly resolves some of the issues I raised above.

acl ZZ {
    0.0.0.0/8;
    10.0.0.0/8;
    100.64.0.0/10;
    127.0.0.0/8;
    169.254.0.0/16;
    172.16.0.0/12;
    192.0.0.8/29;
    192.0.0.16/28;
    192.0.0.32/27;
    192.0.0.64/26;
    192.0.0.128/25;
    192.0.2.0/24;
    192.88.99.0/24;
    192.168.0.0/16;
    198.18.0.0/15;
    198.51.100.0/24;
    203.0.113.0/24;
};

Bind acl file generate from CSV ip list using bash

1 Answers1

UPDATE