Menu

As simple as possible, as complex as necessary

Saving IP addresses as integers with ColdFusion ORM and MySQL

2 April 2013

Ever since reading High Performance MySQL, some years ago I'd been storing IP addresses in the database as integers. Why? As the authors explain:

People often use VARCHAR(15) columns to store IP addresses. However, an IP address is really an unsigned 32-bit integer, not a string. The dotted-quad notation is just a way of writing it out so that humans can read it more easily. You should store IP addresses as unsigned integers. MySQL provides the INET_ATON() and INET_NTOA() functions to convert between the two representations.

Not only is it a more appropriate data type, it can also make a difference in terms of performance and efficiency: integers are faster to process and take up less space than strings. I remember seeing logging tables with tens of thousands of rows shrink noticeably the first time I switched.

But having moved to ORM in the last couple of years, I've found myself reverting to varchar columns for IP storage. Although HQL does support a range of common SQL functions, it doesn't include vendor-specific ones such as INET_ATON() and INET_NTOA().

Possible workarounds

Of course I could have added normal SQL queries to store the IP data separately each time an entity was saved, but this seemed like unnecessary extra work.

Another option might have been to create a "pre-processing" function to convert the IP string to an integer before saving, as attempted a while ago by Ben Nadel, but again the complexity didn't seem worthwhile.

Revisiting Ben's post and his generously titled follow-up recently, I realised that a simple solution was staring me in the face: why not use the built-in MySQL functions to pre-process the values? While it still involves an extra database connection, there is no write transaction, and more importantly the code can be packaged up as a single generic utility function.

<cffunction name="ipAddressToOrFromNumber" output="false" hint="I convert IPv4 address to/from integers for more efficient DB storage. NOTE: Requires CF9+ and a MySQL DSN to be configured in Application.cfc.">
	<cfargument name="ipAddress" required="true" hint="integer or string">
	<cfquery name="local.q">
		SELECT
		<cfif IsValid( "Integer",arguments.ipAddress )>
			INET_NTOA( <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.ipAddress#"> )
		<cfelse>
			INET_ATON( <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ipAddress#"> )
		</cfif>
		AS result
	</cfquery>
	<cfreturn local.q.result>
</cffunction>

Update 3 November 2013

On reflection, the dependency on a MySQL connection is not ideal. Although Ben's approach is (by his own admission) a bit convoluted, there are simpler ways of doing the conversion in CF, such as Troy Pullis's UDFs ip2long and long2ip on CFLib.

I've now replaced my UDF with the following based on Troy's functions:

any function ipAddressToOrFromNumber( required ipAddress )
	int="Takes an IPv4 address as dotted quad string or integer"
{
	var ipArray	=	ListToArray( ipAddress,"." );
	if( ArrayLen( ipArray ) EQ 4 )
		return ( ipArray[ 1 ]*256^3 + ipArray[ 2 ]*256^2 + ipArray[ 3 ]*256 + ipArray[ 4 ] );
	var result	=	"";
	for( var i=3; i>=0; i-- )
	{
		result &= Int( ipAddress / 256^i );
		ipAddress = ipAddress - Int( ipAddress / 256^i ) * 256^i;
		if( i > 0 ) 
			result &= ".";
	}
	return result;
}

Note: the function just converts - it doesn't check the validity of the IP values passed in.

With this available as a UDF, the following Visitor entity....

<cfcomponent persistent="true" accessors="true" table="visitors">

	<cfproperty name="ID" fieldtype="id" generator="assigned">
	<cfproperty name="lastIpAddressAsString">
	<cfproperty name="lastIpAddressAsNumber">

</cfcomponent>

... can be persisted in one fell swoop:

ipAddress = cgi.remote_addr;
ipAddressAsNumber = ipAddressToOrFromNumber( ipAddress );
visitor = EntityNew( "Visitor" );
visitor.setID( 1 );
visitor.setLastIpAddressAsString( ipAddress );
visitor.setLastIpAddressAsNumber( ipAddressAsNumber );
transaction
{
	EntitySave( visitor );
}
WriteDump( var=visitor,showUdfs=false );

Called from my local development host address (127.0.0.1), this outputs:

screenshot of the Visitor object cfdump

Exceptional address

After suitable local and remote testing I deployed this to a live ORM app recently and all seemed well at first. But then an isolated exception occurred which had me scratching my head:

coldfusion.runtime.Cast$OutOfBoundsException: Cannot convert the value 3.241952418E9 to an integer because it cannot fit inside an integer.

A particular IP address (193.60.68.162) had been converted into a number that was apparently too large for the "unsigned int(10)" column I'd been using for years to store addresses. But according to the MySQL docs this was the correct column type for converted IPv4 addresses. A quick non-ORM SQL test to save the address in question confirmed this.

So it was clearly Hibernate not MySQL which wasn't happy.

Now you may have noticed in my Visitor entity that there are no data types specified for the properties. Arguably you should always add them, but I've tended not to since I prefer to define my data structure in the database itself and let the ORM pick up the column types from there, which it will do if you leave the useDBForMapping setting to its default value of true. Adding data types to properties therefore seems to me like a violation of DRY (but I am open to persuasion).

What seemed to be happening then is that although the ORM was recognising the property as an int, it disagreed with MySQL on the maximum possible value. I'm not sure why, but perhaps the "unsigned" aspect wasn't being detected.

Long shot?

Happily the solution is straightforward: just tell Hibernate the column is a long. No need to adjust the MySQL column type.

<cfcomponent persistent="true" accessors="true" table="visitors">

	<cfproperty name="ID" fieldtype="id" generator="assigned">
	<cfproperty name="lastIpAddressAsString">
	<cfproperty name="lastIpAddressAsNumber" ormType="long">

</cfcomponent>

Posted on . Updated

Comments

  • Formatting comments: See this list of formatting tags you can use in your comments.
  • Want to paste code? Enclose within <pre><code> tags for syntax higlighting and better formatting and if possible use script. If your code includes "self-closing" tags, such as <cfargument>, you must add an explicit closing tag, otherwise it is likely to be mangled by the Disqus parser.
Back to the top