With stored procedures, is cfSqlType necessary?

Question

To protect against sql injection, I read in the introduction to ColdFusion that we are to use the cfqueryparam tag.

But when using stored procedures, I am passing my variables to corresponding variable declarations in SQL Server:

DROP PROC Usr.[Save] 
GO 
CREATE PROC Usr.[Save] 
(@UsrID Int 
,@UsrName varchar(max) 
) AS 
UPDATE Usr  
SET UsrName = @UsrName 
WHERE UsrID=@UsrID  
exec Usr.[get] @UsrID

Q: Is there any value in including cfSqlType when I call a stored procedure? Here's how I'm currently doing it in Lucee:

storedproc procedure='Usr.[Save]' {
    procparam value=Val(form.UsrID);
    procparam value=form.UsrName;
    procresult name='Usr';
}

Answer 1

This question came up indirectly on another thread. That thread was about query parameters, but the same issues apply to procedures. To summarize, yes you should always type query and proc parameters. Paraphrasing the other answer:

Since cfsqltype is optional, its importance is often underestimated:

• Validation: ColdFusion uses the selected cfsqltype (date, number, etcetera) to validate the "value". This occurs before any sql is ever sent to the database. So if the "value" is invalid, like "ABC" for type cf_sql_integer, you do not waste a database call on sql that was never going to work anyway. When you omit the cfsqltype, everything is submitted as a string and you lose the extra validation.

• Accuracy: Using an incorrect type may cause CF to submit the wrong value to the database. Selecting the proper cfsqltype ensures you are sending the correct value - and - sending it in a non-ambiguous format the database will interpret the way you expect.

  Again, technically you can omit the cfsqltype. However, that means CF will send everything to the database as a string. Consequently, the database will perform implicit conversion (usually undesirable). With implicit conversion, the interpretation of the strings is left entirely up to the database - and it might not always come up with the answer you would expect.

  Submitting dates as strings, rather than date objects, is a prime example. How will your database interpret a date string like "05/04/2014"? As April 5th or a May 4th? Well, it depends. Change the database or the database settings and the result may be completely different.

The only way to ensure consistent results is to specify the appropriate cfsqltype. It should match the data type of the target column/function (or at least an equivalent type).