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 = "184.108.40.206" /> <!--- 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.
Want to use code from this post? Check out the license.