Storing IP Address in Mysql Database

Storing IP addresses is a common issue when developing web applications. It may be needed for variety of features like banning or resolving user localization. IP address is also used by many applications for improving security system (just try for one time to log into Gmail service from another country and you will be probably prompted with security question).

One of the main and most obvious questions while implementing those features is how to store that kind of data, as far as there is no dedicated IP address data type in MySQL engine.

 

The simplest way is to create column of varchar(15) type (or its variations depending on particular database engine). In other words we keep an IP address as a plain text. That is very simple solution but not too efficient. First of all searching by this column is quite slow, especially if we have millions of records. It is much more slower if we want to search using IP wildcard. In that situation LIKE operator is necessary (for example ‘192.168.0.%’).

The most efficient way to store IP address is to create UNSIGNED INT column. It is very fast especially with index created on it. Addresses are stored as simple numbers so if we want to make them more readable for humans MySQL provides two key functions: INET_ATON and its opposite INET_NTOA. The first one converts dotted-quad representation to the number and is very useful while executing sql insert with address in string format. Second function performs reverse operation converting number to human-readable format. Below are two simple examples of that functions:

SELECT INET_ATON('192.168.0.1') -> 3232235521

SELECT INET_NTOA(3232235521) -> 192.168.0.1

Getting addresses belonging to some range is also fast and simple. We just compare all addresses with bottom and top of our range like we do with standard numbers. Knowing that 192.168.0.0 is represented by 3232235520 and 192.168.0.0 is represented by 3232235775 to find all addresses matching 192.168.0.* mask we can execute following query:

SELECT ip FROM ip_table BETWEEN 3232235520 AND 3232235775

Talking about IP representation it is also good to mention how to perform IP string to number conversion from code layer. Almost all of decent high level languages have INET_ATON/INET_NTOA equivalents. In PHP they are respectively ip2long and long2ip. Low level languages like Java expect us to write them on our own or use external library. Below are two simple implementations of INET_ATON and INET_NTOA in Java language which I hope you may find useful:

public static Long StringIPToLong(String IP)
{
  String[] addressArray = IP.split("\\.");

  long result = 0;

  for (int i = 0; i < addressArray.length; i++)
  {
    int power = 3 - i;

    result += ((Integer.parseInt(addressArray[i]) % 256 * Math.pow(256, power)));
  }

  return result;
}

public static String LongIPToString(long IP) 
{
 return ((IP >> 24 ) & 0xFF) + "." + ((IP >> 16 ) & 0xFF) + "." + ((IP >> 8 ) & 0xFF) + "." +( IP & 0xFF);
}