Country IP Blocks™

When it comes to storing IP addresses within a database you have several options.

Some people store IP addresses in a single field as DATATYPE VARCHAR in dotted decimal format.

IPfield: 192.168.1.0

Others break up the dotted decimal format into 4 quads and store each quad as DATATYPE VARCHAR , SMALLINT or TINYINT, in a separate field:

Quad1: 192
Quad2: 168
Quad3: 1
Quad4: 0

You could also store the data in binary.

The above options will certainly allow you to store your data, but with limited usability. At Country IP Blocks we prefer to store our IP network data in decimal format as DATATYPE INT UNSIGNED.

This allows us to store 192.168.1.0 as the integer 3232235776.

There are many benefits to storing your IP data as an integer. Integers are more easily searchable.

If your data gathering is nothing more than storing single IP addresses, then by all means store the data anyway you want. But, if you are looking to store data related to complete networks, consider storing your IP data in decimal format as DATATYPE INT UNSIGNED.

Storing your IP data in this manner makes it searchable and attributable. By searchable we mean more than locating an address. We mean identifying and attributing it to its specific network, country, region, etc. or locating it within a specific network (as is this address between address A and address B.

The question is how do we convert a dotted-decimal IP address into a decimal or integer address?

Converting from IP to decimal and decimal to IP is simple. It can be done several ways, but we generally prefer using MySQL functions to perform the conversions.

INET_ATON

Use INET_ATON to return the numeric value of an IP address: $ip = 192.168.1.0.

$query = “INSERT INTO iptable VALUES (”,inet_aton(‘$ip’)”; mysql_query($query);

INET_NTOA

Use INET_NTOA to return the IP address from a numeric value:

$query = (“SELECT inet_ntoa(ip) FROM iptable”); $results = mysql_query($query);

The above are examples on using INET_ATON and INET_NTOA to convert dotted decimal and integer formatted IP addresses.

In summation, you can store you IP addresses in a variety of ways, but for usability purposes we believe converting and storing your addresses in decimal will be more meaningful and useful.

href="http://www.bidvertiser.com/bdv/BidVertiser/bdv_xml_feed.dbm">search

feed
In the past we have released many top 10 lists showing countries in the order of spam production or other similar criteria. One of the latest lists orders the results like this:

1. United States
2. China
3. Russia
4. United Kingdom
5. Germany
6. Japan
7. Brazil
8. Romania
9. Ukraine
10. Turkey

While the order is technically accurate when considering the volume of spam or the number of networks involved, we decided to examine the number of malicious networks involved as a percentage of the total number of networks in a given country. The results. showing the percentage of networks containing malicious activity, including spammers, hackers, comment spammers or other detrimental outgoing traffic, are astonishing.

Here are the results based on percentage of infected networks:

1. Brazil 89%
2. Turkey 54%
3. Romania 39%
4. China 32%
5. Russia 11%
6. United Kingdom 11%
7. Japan 10%
8. Ukraine 9%
9. Germany 6%
10. United States 6%

Keep in mind, these results show only countries that are listed as the current top 10 global spammers. A full review of all countries might lead to an even more dramatic result. Is it 15 times more likely an email from a network in Brazil will contain spam or other malicious content than from a network in Germany or the United States? Maybe, but maybe not.

We will continue to study the issue and release our findings in the near future.

Canada and the United States comprise nearly 50,000 networks and 1.7 billion public IP addresses. Aggregation of these networks will substantially reduce the size of your Access Control Lists.

As of today, November 30, 2011, through network aggregation, joining contiguous networks in the United States and Canada, we can reduce the size of this ACL by approximately 90%, down to 4,750 network range blocks.

Aggregation lessens the strain on your hardware and software resources.

As a courtesy we are including the aggregation of United States and Canadian IPv4 networks (in network range format) below.

Please remember, it costs us money to produce our services.  Make a donation to Country IP Blocks through PayPal today.