The importance of CFQUERYPARAM
Why use it? When use it? How to use it?
Why doesn't my query work?
My Query worked fine, but now it doesn't, what’s wrong?
How can I insert dates in my database?
And on and on ... All of these question and more are easily and virtually instantly resolved with one little cf tag that has been around for quite a while. The wonderful tag is cfqueryparam, in case you missed the title of this article.
CFQUERYPARAM, added in Coldfusion Server 4.5.1, will solve virtually all of your database problems regarding data types and variables. It adds a level of security, and performance to your queries. I believe that it is the number one most under used or misunderstood ColdFusion tags provided in the language, yet it is one of the most useful, powerful, timesaving tags for working with queries.
A few ways that CFQUERYPARAM will help you:
Boolean Values
Coldfusion uses several values for Boolean. This includes yes/no, true/false, and the classic 1/0. Coldfusion will also recognize any non-zero number as true. Example: #yesNoFormat(-.001)# is YES, as is #yesNoFormat(100)#
CFQUERYPARAM becomes useful while working with Coldfusion Booleans, as it will convert any of the non 1/0 type Booleans to a 1/0 type that your DB can accept.
Data Binding
When we write a query, we are writing a simplified version of what the DB really would like. CFQUERYPARAM binds their values to the proper data type, enabling a faster, more efficient query.
Special Characters
CFQUERYPARAM also handles special characters well. There is no need to use preserveSingleQoutes() if you are using CFQUERYPARAM. It naturally keeps special characters in proper format.
Security
Because of the way CFQUERYPARAM handles variables that we pass it, SQL injection is virtually impossible while using it. For one, CFQUERYPARAM validates the data to the specified data type. (Specified with the 'cfsqltype' attribute.) So if a string is passed to a CFQUERYPARAM tag with the cfsqltype attribute set to "cf_sql_integer", you will throw an error.
The parameters that CFQUERYPARAM sends to the query are NOT executed EVER.
Lists
You can use CFQUERYPARAM with the 'list' attribute set to 'yes' if you are using IN() in your queries. Using the List attribute allows a comma separated list of integers to be used in your queries. If list="no", then the list is just a string, and would throw an error if being used as a list of integers.
So in the end, although using cfqueryparam seems slightly cumbersome at first, It saves you big time in strong data validation. It also makes inserting and updating much easier, as data types are checked for you.
One possible drawback to using CFQUERYPARAM is that a query using it will not be cached (Update! CFQP will be cache-able after MX7!), even if you specify that it should be in the cfquery tag. If you want to cache a query that does have a CF variable in it, make sure you check the values of the variable very well before letting it through to the query tag. Other methods of caching should be used if needed, such as placing the results in a persistent scope. If it is a id from the URL, make sure it is numeric with the 'isNumeric()' function.
