5

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?

Fish Below the Ice
  • 1,273
  • 13
  • 23
  • 1
    None of which I am aware. I used to be one of those overzealous people until one of my co-workers told me to smarten up. – Dan Bracuk Oct 01 '14 at 12:51

1 Answers1

4

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.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
CFML_Developer
  • 1,565
  • 7
  • 18
  • 2
    Feel free to prove the claim that using query parmameters instead of constants will improve performance. – Dan Bracuk Oct 01 '14 at 13:30
  • 1
    with cfqueryparam, an execution plan will be prepared while with a constant it won't, JDBC will be passing just the whole query string. I believe query with an execution plan will run faster. – CFML_Developer Oct 01 '14 at 13:39
  • I'd like a something a little more solid than "I believe". – Fish Below the Ice Oct 01 '14 at 13:40
  • Why would an execution plan not be prepared with a constant? – Dan Bracuk Oct 01 '14 at 13:40
  • There's also more than likely other `cfqueryparam`s in the query to deal with actual variables, so an execution plan would be generated regardless. – Fish Below the Ice Oct 01 '14 at 13:43
  • Execution plan generated every time, Yes. Captured/cached? No. That is what makes the difference. cfqueryparam helps in capturing/caching the execution. – CFML_Developer Oct 01 '14 at 13:58
  • I see few downvotes which are more on sheep herd mentality in our community. "If you are not binding your variable/constant, prepared statement/execution plan will not be cached." Give me a reason to falsify this statement. – CFML_Developer Oct 01 '14 at 14:33
  • 1
    @CFML_Developer I think the burden is actually on *you* to prove your position. Which I don't think you're doing. NB: I don't mean to suggest I disagree with you, but I think people asking you questions are asking *valid* questions which relate directly to the quality of your answer. You need to demonstrate what you're saying is true, rather than just something you happen to think, but without necessarily any basis for it (NB: I was not one of the ppl who downvoted you). – Adam Cameron Oct 01 '14 at 15:05
  • I ran CFML_Developer's test script on CF10 / MSSQL 2012. Results for my test databases were: 3211ms with cfqueryparam, 4461ms without cfqueryparam. I'd like to test a bit more, but I'm surprised! – bwhet Oct 01 '14 at 18:32
  • See, now that's useful. I'll see if I can do my own tests with SQL Server. – Fish Below the Ice Oct 01 '14 at 18:38
  • 1
    @DanBracuk is it normal to add your own answer to another answer? With out looking at the edit history it looks like CFML_Developer added the script. – Twillen Oct 01 '14 at 18:40
  • I've run the same sort of test on my own data in MS SQL Server (using `cf_sql_bit` instead of `cf_sql_integer`). At 100 iterations it was pretty much a wash. At 1000 the "without" result was **better** by a factor of 7. Oddly, `0` instead of `1` was basically a wash. (A difference of less than a percentage point.) On a different table I got the same results: `0` was basically identical "with" or "without"; `1` was _much_ better "without" (by a factor of 15). And I did run the tests multiple times. So while I appreciate the extra analysis here, it's not being borne out by what I'm seeing. – Fish Below the Ice Oct 01 '14 at 19:09
  • 3
    Curious. Are you testing the entire time to execute the query via ColdFusion or are you using SQL Profiler or something to test how fast the query itself runs? That is the real question right? Of course ColdFusion's execution is going to be a little slower with cfqueryparam because there is overhead in executing that tag. The real question should be, is the actual query at the database level faster or slower? – Sean Coyne Oct 01 '14 at 19:54
  • DanBracuk - That is a rather significant change. If you have a supporting answer, you should post it separately. – Leigh Oct 01 '14 at 19:57
  • My test results support @CFML_Developer's theory. Had they contradicted it, I would have presented the information in another way. – Dan Bracuk Oct 01 '14 at 21:39
  • @DanBracuk, Thanks for doing the test. I have added your test case in my answer, if you do not have any objection. Feel free to edit the answer and remove if you feel it appropriate. – CFML_Developer Oct 02 '14 at 11:15
  • 1
    DanBracuk - When a change adds significant content - that the original author did not actually write - it is usually an indication you should be creating a separate answer of your own. Unless the post is so poorly worded it is difficult for others to understand, which is not the case here. "Edits" are usually minor changes like fixing typos, grammar or adding links to the API. – Leigh Oct 02 '14 at 13:49