Is using cfsqltype good practice?

Question

When coding a cfqueryparam or cfprocparam, cfsqltype is optional. However, I've usually seen it coded. Are there any benefits to specifying cfsqltype?

Answer 1

The main benefit is an additional level of sanity checking for your query inputs, prior to passing it into your query. Also, in the case of date time values, I believe CF will properly translate datetime strings into the proper database format, if the cfsqltype="CF_SQL_DATE" or ="CF_SQL_TIMESTAMP" is specified.

In addition, I think it makes it more clear for future developers to see the types excepted when they read your code.

Answer 2

I would add to Jake's comment. In most RDBMS the database will need to run your variable through a type lookup to insure it's the proper type or can be cast to the proper type implicitly. A DB doesn't just throw a variable of "type Any" at a table or view. It has to build the proper typing into the execution plan. So if you don't provide a type you are asking the DB to "figure it out".

Whereas, when you specify the type you are pre-empting or pre-qualifying the data type. The engine knows the driver is presenting a variable of a certain type and can then use it directly or derive it directly.

Remember that, while security is a good reason to use cfqueryparam, it's only one reason. The other reason is to create correctly prepared statements that can executed efficiently - and ideally "pop" the execution plan cache on the DB server.