Using cfqueryparam with constants

问题:

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?

回答1:

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.