Excluding IP-ranges

[Snedker]

Hi there,

I really need some input of how to approach my little assignment.

A customer wants to exclude all US IP-ranges from accessing part of
his website. From http://www.ipaddresslocation.org/ I've collected
about 16,000 ranges in the format

208.202.120.0 208.203.111.255
208.203.114.0 208.203.244.127
....

I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.

Now, when a user hits that certain webpage a lookup should be
performed, checking if user is in a range. If he is, he's banned from
that part of the page.

My idea is to save to a table (MSSQL) that contains 4 columns - one
column for each part of the IP. When I import the ranges I save each
IP in that interval, ending up with all (or so) current US IP-
addresses. Each of the four columns are indexed.

A lookup will then use a phonebook-kinda-lookup (SELECT COUNT(*) FROM
myTable WHERE col1=62 AND col2=63 AND col3=64 and col4=65). If it
returns 0, user is allowed.

Is that a crappy approach, or should I consider something else?


Thanks in advance for any input.

Regards /Snedker

 

[Gaurav Vaish \(a.k.a. MasterGaurav\)]

Hi there,

I really need some input of how to approach my little assignment.

A customer wants to exclude all US IP-ranges from accessing part of

I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.

Check with www.apnic.net

 

[Göran Andersson]

Hi there,

I really need some input of how to approach my little assignment.

A customer wants to exclude all US IP-ranges from accessing part of
his website. From http://www.ipaddresslocation.org/ I've collected
about 16,000 ranges in the format

208.202.120.0 208.203.111.255
208.203.114.0 208.203.244.127
...

I don't yet know how many individual IP's then ranges totals, but I
reckon we speak millions.

Now, when a user hits that certain webpage a lookup should be
performed, checking if user is in a range. If he is, he's banned from
that part of the page.

My idea is to save to a table (MSSQL) that contains 4 columns - one
column for each part of the IP.

First of all, you should convert each IP address to a single 32 bit
value. There is no reason to compare four values when you can compare one.

When I import the ranges I save each
IP in that interval, ending up with all (or so) current US IP-
addresses. Each of the four columns are indexed.

If only 10% of the IP range is allocated to US, that makes a table with
430 million records...

You should definitely store the ranges, not the individual IP addresses.