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