"Also - you are forgetting the cfqueryparam is more than just a security thing. It also speeds up the query execution (for databases that support it)."

That's really interesting. What are databases do you know that support it?

Any database that supports bound parameters. Off the top of my head, MySQL, MsSQL and Oracle. Also I think Postgres as well. Basically anything "enterprise" level - I seriously doubt Access (haven't touched that in five years).

The reason that query params speed up query execution is because the aforementioned databases generate what is called an "execution plan" before actually performing the query. If you pass in query parameters, then since the actual SQL doesn't change as often, the database can often cache the execution plan across queries. It's as if you were creating a SQL "template" and passing in values to it-- all the database has to do is to cache the template, plug in the values, and run the query. It can skip the execution plan, and that translates to improved speed.

Thanks for the follow up guys - been swamped!

Oracle really likes to have things in cfqueryparam so that it can make Bind variables. If not it has to parse through what I pass it to figure out what it is. This is a big problem when a page is hit thousands of times. We have a ton of code here that never used cfqueryparam and it is really hitting Oracle hard with having to parse all of the data.

Also bear in mind, that while you should not do it, if you have a query that uses SELECT * FROM...and use <cfqueryparam> in a WHERE clause and the table gets modified, say, you add a column, the new column won't show up in your query object right away.

Back at the security-in-depth idea, it's good practice to validate at every level for the inevitable situation in which a previously single-use function/query/cfc gets repurposed in a new context - perhaps one that doesn't check as rigorously before passing parameters....

<cfqueryparam /> also prevent SQL injections--which <cfargument /> would do nothing against.

I'd suspect sybase to accept bound parameters as well. since mssql is pretty much a clone of sybase in the first place.

CFQUERYPARAM can be used with any database. From the documentation:

"If a DBMS does not support bind parameters, ColdFusion validates and substitutes the validated parameter value back into the string. If validation fails, it returns an error message."

So, yes you can use cfqueryparam with any DBMS system you have. Personally I use them all the time. If nothing more than just for the fact that I know what kind of information I'm throwing into the database.

The biggest gotcha with using them though is that you can't cache the query. Also using them in conjunction with SELECT * (which should NEVER be used) results in JDBC throwing errors if you change the underlying tables.

'Also using them in conjunction with SELECT * (which should NEVER be used) results in JDBC throwing errors if you change the underlying tables.'

This actually depends on teh changes made to the underlying tables. If you add a column, no error will be thrown, but the new column will not be included in the query object. (It will eventually, but I am not sure of how long or after what event, though I know recycling the ColdFusion service will do the trick)