coldfusion - 对于存储过程，是否需要 cfSqlType？

Question

为了防止sql注入(inject)，我在ColdFusion的介绍中读到我们要使用cfqueryparam标签。

但是当使用存储过程时，我将我的变量传递给 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

问:调用存储过程时包含 cfSqlType 有什么值(value)吗？以下是我目前在 Lucee 中的做法:

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

Answer 1

这个问题是在另一个线程上间接提出的。该线程是关于查询参数的，但同样的问题也适用于过程。总而言之，是的，您应该始终键入查询和过程参数。解释另一个答案:

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).