CODEWITHSUNDEEP
coldfusioncfqueryparam
Fish Below the Ice
Fish Below the Ice

Reputation: 1271

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?

Upvotes: 5

Views: 432

Answers (1)

CFML_Developer
CFML_Developer

Reputation: 1605

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.

Upvotes: 4

Related Questions