问题描述
为了防止sql注入，我在ColdFusion的介绍中读到我们要使用cfqueryparam标签。
To protect against sql injection, I read in the introduction to ColdFusion that we are to use the cfqueryparam tag.
但是当使用存储过程时，变量到SQL Server中的相应变量声明：
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
当我调用存储过程时，包括cfSqlType的任何值？
这里是我目前在Lucee中做的：
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';
}
推荐答案
另一个线程。该线程是关于查询参数，但相同的问题适用于过程。总而言之，是的，你应该总是键入query和proc参数。改写其他答案：
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:
-
验证：
ColdFusion使用选定的cfsqltype（日期，数字等）验证值。这发生在之前任何sql被发送到
数据库。因此，如果值无效，例如类型为
cf_sql_integer的ABC，则不会浪费在sql上的数据库调用，而从未
将无法工作。当您省略cfsqltype时，一切都是以字符串形式提交的
，您将失去额外的验证。
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.
准确度：
。选择合适的 cfsqltype 确保您是
发送正确的值 - 并且 - 以不含糊的格式发送它
数据库将解释您的方式期望。
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.
再次，技术上你可以省略 cfsqltype 。然而，
意味着CF会将所有内容作为字符串发送到数据库。
因此，数据库将执行隐式转换
（通常不受欢迎）。使用隐式转换，字符串的解释
完全取决于数据库 - 并且它可能
不总是出现您期望的答案。
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.
提交日期作为字符串，而不是日期对象，是一个
的例子。你的数据库如何解释一个日期字符串，如
05/04/2014？ 4月5日或5月4日？嗯，这取决于。更改
数据库或数据库设置，结果可能完全与
不同。
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.
确保一致结果的唯一方法是指定
适当的 cfsqltype 。它应该匹配目标
列/函数（或至少一个等效类型）的数据类型。
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).
这篇关于有了存储过程，cfSqlType是否必要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！