Skip to main content
Ben Nadel at Hal Helms' Real World OO (Feb. 2009) with: Steve Good and Joakim Marner and Charlie Griefer and Ezra Parker
Ben Nadel at Hal Helms' Real World OO (Feb. 2009) with: Steve Good ( @stevegood ) Joakim Marner Charlie Griefer ( @charliegriefer ) Ezra Parker

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

By on
Tags: ,

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.

Want to use code from this post? Check out the license.

Reader Comments

15,640 Comments

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.

4 Comments

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.

28 Comments

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.

8 Comments

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!

28 Comments

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!

3 Comments

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

47 Comments

@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?

47 Comments

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.

15,640 Comments

@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.

5 Comments

@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?

3 Comments

@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.

3 Comments

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. --->

1 Comments

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

1 Comments

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>

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel