Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with:

Converting An IP Address To An Integer Using MySQL (Thanks Julian Halliwell)

By Ben Nadel on
Tags: ColdFusion, SQL

The other day, as an exercise in bit-manipulation, I tried using bit shifting to convert IP address values to integer numbers. In the comments to that blog, Julian Halliwell mentioned that MySQL has built-in functions for performing these conversions. I had never heard of these functions before, so I thought I would do a little playing. In the following demo, I'm pulling an IP address string into the MySQL engine and then using the two methods, INET_ATON() and INET_NTOA(), to convert the IP address to a number and then back to an IP address again.

  • <!--- Set the IP address. --->
  • <cfset ipAddress = "170.112.108.147" />
  •  
  •  
  • <!--- Convert IP address using MySQL functions. --->
  • <cfquery name="ipConversion" datasource="#request.dsn#">
  •  
  • <!--- Get the IP address into the MySQL engine. --->
  • SET @ipAddress = '#ipAddress#';
  •  
  • <!--- Convert IP address to an integer. --->
  • SET @ipAsNumber = INET_ATON( @ipAddress );
  •  
  • <!--- Convert the IP integer BAC to an address. --->
  • SET @ipAsAddress = INET_NTOA( @ipAsNumber );
  •  
  •  
  • <!--- Select both values for debugging. --->
  • SELECT
  • ( @ipAddress ) AS original,
  • ( @ipAsNumber ) AS ipAsNumber,
  • ( @ipAsAddress ) AS ipAsAddress,
  •  
  • <!---
  • For some reason, the converted IP address is
  • represented as a byte array. As such, we might
  • need to cast it as a CHAR value.
  • --->
  • CAST( @ipAsAddress AS CHAR ) AS ipAsAddressString,
  •  
  • <!---
  • Here, we are testing to see if original IP address
  • is considered equal to the byte-array version of
  • the IP address.
  • --->
  • ( @ipAddress = @ipAsAddress ) AS isEqual
  • ;
  •  
  • </cfquery>
  •  
  •  
  • <!--- Dump out IP addresses as converted by MySQL. --->
  • <cfdump
  • var="#ipConversion#"
  • label="MySQL IP Conversion"
  • />
  •  
  •  
  • <br />
  • <br />
  •  
  • <!--- Output the IP values. --->
  • IP Number: #ipConversion.ipAsNumber#<br />
  •  
  • <!---
  • When outputting the converted IP address, you have to convert
  • it to string otherwise ColdFusion will throw a casting error.
  • --->
  • IP Address: #toString( ipConversion.ipAsAddress )#<br />
  •  
  • IP Address (as string): #ipConversion.ipAsAddressString#<br />

As you can see, I'm using the function INET_ATON() - "address to number" - to convert the IP address to a number; then, I'm using the function INET_NTOA() - "number to address" - to convert the number back to an IP address. When converting back to an IP address using INET_NTOA(), MySQL represents this value as a byte array. While inside the MySQL context, you case see that byte array IP Address is considered equal to the original string representation; however, if you need to use this value outside of the SQL context, you can either convert it to a CHAR value within the SQL statement or, you can convert it to a string once you are back inside a ColdFusion context (using ToString()).

When we run the above code, we get the following output:

 
 
 
 
 
 
Convert An IP Address To A Number Using MySQL. 
 
 
 

As you can see, it works perfectly! A big thanks to Julian for pointing these MySQL functions out to me.




Reader Comments

Why would you need to convert an IP address to a number, and vise versa? Out of curiosity ...

Reply to this Comment

From what I gather, this is typically for IP address range comparison. I'll see if I can write up something else on that aspect.

Reply to this Comment

Erika: these functions were in MySQL for years now. Those times 1GB of hosting space was unbelievably expensive. I believe NTOA and ATON were introduced to store IP addresses in more compact way, saving 10 bytes on each record. Sound ridiculous now, I know :) For example storing long web-site access logs (with client IPs) could take less disk space.

Reply to this Comment

@Rodion,

Saving 10 bytes :) I'm so happy we don't have to worry about that kind of stuff any more.

Reply to this Comment

In addition to the storage savings, its also much more efficient to query an integer in the DB as opposed to a string.

Reply to this Comment

I've come across this IP address conversion recently in the free GeoIP database. Makes it easy to convert your IP into a number, and find which range of IPs it lies in using BETWEEN.

Of course, I'm using MSSQL, so had to write my own function (actually, a stored procedure) to do the conversion.

Reply to this Comment

Don't forget that storing IP's as numbers is faster for searching in DB's... You might want to check if a user registration which states he/she is from the UK also matches the IP addresses allocated for the UK...

If you store the IP as a varchar, you need to search by text and it's hard (CPU/Memory intensive) to look up a string in a range, instead of a numeric in between.

Funny that you posted this, I was investigating this myself a few days ago!

Reply to this Comment

Here are two MS SQL functions to do the conversions: (hope the pre tags work in these comments...)

<pre>
ALTER FUNCTION [dbo].[ip2number]
(
@ip varchar(15)
)
RETURNS bigint
AS
BEGIN
DECLARE @numrep bigint

DECLARE @start int,
@end int,
@exp int

SET @exp = 3
SET @start = 1
SET @numrep = 0

WHILE @exp >= 0
BEGIN
SET @end = CHARINDEX('.', @ip, @start)
IF @end = 0 SET @end = DATALENGTH(@ip)+1
SET @numrep = @numrep + ( CAST(SUBSTRING(@ip, @start, @end - @start) AS bigint) * POWER(256,@exp) )
SET @start = @end + 1
SET @exp = @exp - 1
END

RETURN @numrep
END
</pre>

<pre>
ALTER FUNCTION [dbo].[number2ip]
(
@numrep bigint
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @ip varchar(15)

DECLARE @part1 int,
@part2 int,
@part3 int,
@part4 int

SELECT @part4 = @numrep%256
SELECT @part3 = @numrep%(256*256)/256
SELECT @part2 = @numrep%(256*256*256)/(256*256)
SELECT @part1 = @numrep/(256*256*256)

SET @ip = CAST(@part1 AS varchar(3)) + '.' + CAST(@part2 AS varchar(3)) + '.' + CAST(@part3 AS varchar(3)) + '.' + CAST(@part4 AS varchar(3))

RETURN @ip
END
</pre>

I'm not sure if this is the same conversion process as in MySQL, but this is the format used in the free GeoIP data, so I expect it is!

Reply to this Comment

You're very welcome, Ben. I learnt about these functions in the excellent O'Reilly book "High Performance MySQL".

The fact that you get a byte array back from the INET_NTOA() function seems to be specific to CF9, or the MySQL drivers that ship with it. On CF8 you get a string as expected. This seems to happen with a number of MySQL functions with CF9, one of which I've had verified as a bug by Adobe:

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=80629

Reply to this Comment

@Ben, I was wondering if you had an idea of possible collisions with these functions or do they always give you a guaranteed unique result?

Reply to this Comment

Forget the comment i just posted, I went to the mysql docs and seen that this is specific for IP addresses. I was looking to convert varchars to integers.

Reply to this Comment

@Julian,

Sounds like a great book; I'll have to check it out. On a side note, even if the methods return a byte array, at least it can still be used like a string in within the context of the SQL.

@Hatem,

No problem my man.

Reply to this Comment

@Julian,

I book marked this post so I could make a very similar post. A few years ago I experimented quite a bit with the INET functions and BaseN conversions in MySQL after finding coldfusion limited or even a bit broken for some stuff I was tinkering with. I've never noticed anything return as a byte array in CF6.1 or CF8.

@Ben, I also noticed that your query won't run in CF 8. Does CF9 now allow multiple queries in one CFQUERY? AFAIK in CF8 and earlier you have to separate each query into it's own tag.

When I've used the INET functions I just did simple select queries. Like this... [CFQUERY]select INET_NTOA(2066563929) As IP_char, INET_ATON('123.45.67.89') As IP_Int;[/CFQUERY] Are there any benefits to your method?

Reply to this Comment

@Rob

Yes, as I said it's CF9 that seems to return byte arrays from this and certain other MySQL functions such as GREATEST().

To run Ben's code as is you just need to enable multiple queries in your CF data source: allowMultiQueries=true.

Reply to this Comment

I am running CF9 and I am not sure if this is the same error as stated above about CF9.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @ipAsNumber = INET_ATON( @ipAddress ); SET @ipAsAddress = INET_NTOA( @i' at line 4

The error occurred in C:\inetpub\wwwroot\ipCode.cfm: line 9
7 :
8 : <!--- Get the IP address into the MySQL engine. --->
9 : SET @ipAddress = '#ipAddress#';
10 :
11 : <!--- Convert IP address to an integer. --->

Reply to this Comment

I have been so impressed with your article that i have printed it recently-cood literary effort.i have bookmarked it to keep in much.i have been checking new posts here.i would be thankful if u continue

Reply to this Comment

This site has a really amazing information.Thanks for sharing this one I will ask my fiends and their friends also so that this site could really discover by many people.I am so excited to get update with this.
http://ipaddresshq.com/

Reply to this Comment

Easy way with MySql

To insert ip address as integer
<cfquery datasource="#request.dsn#" name="insertIpAddress">
INSERT INTO table (
ipAddress
)
VALUES (
INET_ATON('#cgi.REMOTE_ADDR#')
)
</cfquery>

To get integer as ip address
<cfquery datasource="#request.dsn#" name="getIpAddress">
SELECT INET_NTOA(ipAddress)
FROM table
</cfquery>

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.