Skip navigation
Lance-b
Currently Being Moderated

Removing <cfqueryparam improves perfromance

Feb 16, 2012 4:41 PM

Oracle 10g

Field type varchar2( 1 byte) 'Y' or 'N'

I have a slow performing query that runs up to 20 times faster when I remove <cfqueryparam> from the field

 

there are 6 filters in the where clause all have <cfqueryparam>

if I remove <cfqueryparam value = #selog#> and replace with

 

fp.org = '#selog#'  2011 ms

vs

fp.org = <cfqueryparam value = #selog#> 45028 ms

 

I have tried setting the cfsqltype thru various settings and quoting the value "#selog#".  I also have this same filtered applied in othere queries without issue.  My question is there cases cfqueryparam can negatively impact performance.

 
Replies
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Feb 17, 2012 7:41 AM   in reply to Lance-b

    My question is there cases cfqueryparam can negatively impact performance.

     

    Yes, absolutely. Overall, using CFQUERYPARAM usually improves performance, but there are plenty of cases where using a prepared statement (which is what CFQUERYPARAM does) is slower than using ad-hoc SQL and letting the query analyzer build a new execution plan.

     

    Of course, we don't just use CFQUERYPARAM for performance, but also for security, so you might want to use it even in those cases where queries are slower. If there is any chance that a value used in a query can be unsafe, it makes sense to use CFQUERYPARAM. In the case you mention above, though, you might not need it - if, for example, there are two possible values (Y, N) you could easily handle those values safely other ways, by using literal values.

     

    Dave Watts, CTO, Fig Leaf Software

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 17, 2012 10:02 AM   in reply to Lance-b

    Just out of curiousity, are those results consitent?  I often see the same sort of difference simply by running the same query twice.

     

    Regarding Dave's comment about using queryparam for security, anyone who does that is probably not validating user inputs.  They might also not realize that using queryparam allows javascript to be stored in your db, which then, might execute.

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Feb 18, 2012 9:58 AM   in reply to Dan Bracuk

    Regarding Dave's comment about using queryparam for security, anyone who does that is probably not validating user inputs.

     

    No, actually, I am quite confident this is not true. Validating user inputs only gets you so far - if strings are valid inputs, how do you guarantee that your string has nothing that will be interpreted as executable code by your database? The short answer is, you can't. Relying solely on validation at the application server level is insufficient to guarantee database safety.

     

    They might also not realize that using queryparam allows javascript to be stored in your db, which then, might execute.

     

    That is certainly true, because SQL injection and XSS are two completely different things. Prepared statements protect you, completely, from SQL injection vulnerabilities. They don't protect you from XSS, CSRF, chafing in your pants, or anything else. Of course, validation is often an incomplete answer for XSS as well. There is a school of thought that allows the storage of strings that are potentially unsafe on the client, as long as you make them safe at display time (which is pretty easy to do), rather than trying to guarantee that strings are safe prior to storage (which is not so easy to do).

     

    Dave Watts, CTO, Fig Leaf Software

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 18, 2012 12:57 PM   in reply to Dave Watts

    Regarding:

    Validating user inputs only gets you so far - if strings are valid inputs, how do you guarantee that your string has nothing that will be interpreted as executable code by your database?

     

    How do query parameters help you with that?

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Feb 18, 2012 4:31 PM   in reply to Dan Bracuk

    How do query parameters help you with that?

     

    That is, by definition, the only thing they do - and exactly what they do. They build a prepared statement, which tells the database "this part is SQL code, and this other part is not". The parameters are not SQL code, but simply literal values that the database knows it can plug in.

     

    Dave Watts, CTO, Fig Leaf Software

     
    |
    Mark as:
  • Currently Being Moderated
    Feb 18, 2012 7:52 PM   in reply to Dave Watts

    A few years ago I played around with sql injection attempts.  The only time I was able to get some sql to execute was with sql server and numeric fields.  That can be sorted out with input validation.

     

    With varchar datatypes, any sql was simply part of the string being sent to the db.  Query parameters added no security.

     
    |
    Mark as:
  • Dave Watts
    747 posts
    Mar 11, 2003
    Currently Being Moderated
    Feb 18, 2012 8:23 PM   in reply to Dan Bracuk

    A few years ago I played around with sql injection attempts.  The only time I was able to get some sql to execute was with sql server and numeric fields.  That can be sorted out with input validation.

     

    Did you try comments? Unicode character sequences? Did you know that, in some cases, some versions of CF will validate something as a number when it contains non-numeric characters? Did you test every possibility? I suspect you did not.

     

    Using prepared statements, on the other hand, categorically guarantees that the database won't treat inputs as executable code. Failure to consistently use prepared statements is one of the top audit flags for web applications developed using any language, against any standard database platform, and for good reason.

    With varchar datatypes, any sql was simply part of the string being sent to the db.  Query parameters added no security.

     

    If you build a prepared statement, any SQL commands, or anything that would break your existing SQL commands, will not be executed by the database for the contents of the parameters. So I'm not sure I understand the above statement at all.

     

    Dave Watts, CTO, Fig Leaf Software

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points