Removing <cfqueryparam improves perfromance

[Lance-b]

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.

[Dave Watts]

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

[Dan_Bracuk]

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.

[Lance-b]

Yes very consistent and very odd. This is a series of 3 different queries that are selected by the user. They are all similar and all use cfqueryparam. This is the only one that is consistently slow, unless I remove the cfqueryparam from the one filter.

[Dave Watts]

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

[Dan_Bracuk]

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?

[Dave Watts]

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

[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.

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

[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.

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