When CFQueryParam is NOT Required

Unlike a simple RegEx, cfqueryparam actually tells the database that this is a parameter which stops injected SQL from being executed even if it includes the traditional SQL characters for ending a command and adding a drop table (or whatever).

However, if you think about it, the only time this is really important is if you are saving to a string based field (varchar, nvarchar, text, ntext, char, nchar, etc.). For bits, floats, integers, datetimes and other relatively constrained data types, it is perfectly valid to just use a RegEx to strip any unacceptable characters before calling the db script.

I personally tend to prefer this approach as I'd rather get my own error from my validation library than having to catch, parse and handle the error thrown by the db library (as an aside, does anyone have sample code that will catch common database errors thrown by a cfquery in a try/catch block and parse them so you can throw something useful in terms of the element of the SQL that the db didn't like? I *think* this is a hard problem to solve but am not sure - heck I don't even know if the syntax of the error message is consistent across db drivers).

Not to say you *shouldn't* use cfqueryparam, but just that you don't necessarily have to except for when saving to string type fields (varchar, nvarchar, text, ntext, char, nchar, etc.). Of course this means you MUST validate every field before saving it to a db, but with custom data types and a simple validation library this is should be a default behavior for all of your fields all of the time as it takes almost no effort.

Any thoughts/opinions?!