All developers, of course, give lip-service to the importance of data validation.  But how many do a ridiculously thorough job of it?   If you’re like me, the answer is probably that you do the obvious validations that come to mind during development, and then wait for the other, less probable ones when an issue actually comes up.

This happened to me today at work.  I was working on a form that accepts–from the same field (not my design)–either an integer or a string.  The way the validation was setup is that if the search term evaluated as numeric, the resulting query would look to match on primary keys in the db; for strings, well, a simple “like” search of the title field.

The validations worked fine for months and months, but today through an error.  Here’s a simple recreation of the query:

<cfquery name="getstuff">
     select field1, field2
     from thetable
     where id = <cfqueryparm value="#arguments.searchterm#" cfsqltype="cf_sql_integer" />
</cfquery>

And the error I received was to the effect that the value of “#arguments.searchterm#” was not a valid integer based on the queryparam restraint.

So what happened?  Basically, the validation for “numeric” failed.  Well, it didn’t really fail…it just wasn’t looking for all the right stuff.  After all, while “1234”, “1234567”, and “1234534566789912345” will all pass as numeric, they are certainly not all valid integers.  So then, the code was happily allowing an invalid data type into the sanctuary of the query–and stuff blew up.  It passed by for so long simply because no one had–until today :)–entered a valid, numeric non-integer.  Hey, it happens…

Fortunately, there’s a simple fix.  In addition to checking to ensure that the search term is numeric, I simple added the following to additionally ensure that it is an integer:

if( round(numericval) == numericval) {...allow as integer...}

At first glance, this doesn’t seem like it’s doing anything.  However, try it out some time.  Take a super-long, valid and numeric string and compare its “rounded” version to its regular self.  If the value is a valid integer, the two will come out the same; if not, well, you’ll immediately notice a difference.

Wrapping Up

Ok, so this isn’t a particularly enlightening post.  There’s nothing here that everyone shouldn’t already be putting into practice.  So perhaps it can just serve as a challenge (primarily to me!): have you checked your validations today?  Unless you are a anal, validation super-nazi, there is probably room for improvement.  If you have a few minutes, take the time and do it–the bliss of error-free code for months and years to come is its own reward.  And besides, errors should always be saved for something much sexier than data validation :)