使用存储过程，是否需要 cfSqlType? [英] With stored procedures, is cfSqlType necessary?

问题描述

为了防止 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';
}

推荐答案

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

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:

由于 cfsqltype 是可选的，它的重要性常常被低估:

Since cfsqltype is optional, its importance is often underestimated:

• 验证:ColdFusion 使用选定的 cfsqltype(日期、数字等)来验证值".这发生在之前任何 sql 被发送到数据库.因此，如果值"无效，例如类型为ABC"cf_sql_integer，你不会在 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.

准确度:使用不正确的类型可能会导致 CF 向数据库提交错误的值.选择正确的 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.

将日期作为字符串而不是日期对象提交是一种最好的例子.您的数据库将如何解释日期字符串，例如2014 年 5 月 4 日"?是 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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！