使用cfqueryparam与常量 [英] Using cfqueryparam with constants

问题描述

我们在我们的SQL查询中虔诚地使用 cfqueryparam 。

我的一些前辈似乎有点

不是

  record_is_deleted_bt =< cfqueryparam cfsqltype =cf_sql_bitvalue =0> 
  

overkill？我的意思是，没有机会进行SQL注入，我不认为使用绑定变量这对提高数据库性能有帮助。

  record_is_deleted_bt = 0 
  

？

使用 cfqueryparam 在这样的情况下，除了习惯使用它吗？是否有缺点？

解决方案

不，这不是过度。 cfqueryparam的第一个工作是数据绑定。它有助于在sql注入预防只是附加的奖金。通过数据绑定准备的语句执行速度更快。您错误地假设它只在帮助sql攻击预防。

重要注意：
我添加了@Dan Bracuk提供的测试用例oracle db。

 < cfquery name =withoutdatasource =burns> 
 select count（*）
 from burns_patient 
其中patientid = 1 
< / cfquery> 
 
< cfquery name =withdatasource =burns> 
 select count（*）
 from burns_patient 
其中patientid =< cfqueryparam cfsqltype =cf_sql_integervalue =1> 
< / cfquery> 
 
< cfscript> 
 TotalWithout = 0; 
 TotalWith = 0; 
< / cfscript> 
 
< cfloop from =1to =1000index =istep =1> 
 
< cfquery name =withoutdatasource =burnsresult =resultwithout> 
 select count（*）
 from burns_patient 
其中patientid = 1 
< / cfquery> 
 
< cfquery name =withdatasource =burnsresult =resultwith> 
 select count（*）
来自burns_patient 
其中patientid =< cfqueryparam cfsqltype =cf_sql_integervalue =1> 
< / cfquery> 
 
< cfscript> 
 TotalWithout + = resultwithout.executiontime; 
 TotalWith + = resultwith.executiontime; 
< / cfscript> 
 
< / cfloop> 
 
< cfdump var =总计是＃TotalWith＃，没有总数是#TotalWithout＃。> 
  

总计的范围从700到900总毫秒。 without总的范围从1800到4500毫秒。无总数总是至少是总数的两倍。

We religiously use cfqueryparam in our SQL queries.

Some of my predecessors seem to have been a little overzealous when using it with direct values rather than variables.

Isn't

record_is_deleted_bt = <cfqueryparam cfsqltype="cf_sql_bit" value="0">

overkill? I mean, there's no chance for SQL injection and I don't think that using a bind variable here does anything helpful vis-à-vis improving performance in the database. Wouldn't it be just as reasonable to do

record_is_deleted_bt = 0

?

Is there any advantage to using cfqueryparam in such an instance, besides ingraining the habit of using it? Is there a disadvantage?

解决方案

No, this is not overkill. cfqueryparam's first job is data binding. It helps in sql injection prevention is just the add-on bonus. The prepared statements through data binding execute faster. You are wrong to assume that it is there to help on sql attack prevention only.
Important Note: I am adding Test case provided by @Dan Bracuk on an oracle db.

<cfquery name="without" datasource="burns">
select count(*)
from burns_patient
where patientid = 1
</cfquery>

<cfquery name="with" datasource="burns">
select count(*)
from burns_patient
where patientid = <cfqueryparam cfsqltype="cf_sql_integer" value="1">
</cfquery>

<cfscript>
TotalWithout = 0;
TotalWith = 0;
</cfscript>

<cfloop from="1" to="1000" index="i" step="1">

  <cfquery name="without" datasource="burns" result="resultwithout">
    select count(*)
    from burns_patient
    where patientid = 1
  </cfquery>

  <cfquery name="with" datasource="burns" result="resultwith">
    select count(*)
    from burns_patient
    where patientid = <cfqueryparam cfsqltype="cf_sql_integer" value="1">
  </cfquery>

  <cfscript>
    TotalWithout += resultwithout.executiontime;
    TotalWith += resultwith.executiontime;
  </cfscript>

</cfloop>

<cfdump var="With total is #TotalWith# and without total is #TotalWithout#.">

The with total ranges from 700 to 900 total milliseconds. The without total ranges from 1800 to 4500 milliseconds. The without total is always at least double the with total.

这篇关于使用cfqueryparam与常量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！