Converting An IP Address To An Integer Using MySQL (Thanks Julian Halliwell)
Posted January 29, 2010 at 8:30 AM by Ben Nadel
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:
| | | | | |
| |  | | ||
| | | |
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 ...
How would one use this in practice?
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.
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.
@Rodion,
Saving 10 bytes :) I'm so happy we don't have to worry about that kind of stuff any more.
In addition to the storage savings, its also much more efficient to query an integer in the DB as opposed to a string.
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.
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!
Any eqv functionality for MS SQL?
@Henry,
Talk to @Seb, it looks like he made stored procedures for MSSQL.
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!
@Seb,
Sorry about the PRE tags not working :( Maybe I'll try to update my comment formatting today.
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
@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?
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.
@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.
@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?
@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.
@Rob,
As @Julian pointed out, you need to enable multiple queries in order to run queries containing semi-colons. For more info on that, take a look at this post:
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. --->
