Most of us who have been using CF for any significant period of time are pretty familiar with the syntax of a SQL Query.

SELECT *
FROM tableName
WHERE column1 = ?#myValue#?

As of ColdFusion 4.5 (and remaining in MX), a new tag called <cfqueryparam> was introduced. I think it?s a given that this tag is not used by nearly as many CF developers as it should be (it should be used by all of them).

But why, CJ? I?m used to the simple query above. Why should I have to add more typing by doing:

SELECT *
FROM tableName
WHERE column1 = <cfqueryparam value=?#myValue#? cfsqltype=?cf_sql_char?>

Excellent question. Glad you asked. Otherwise I?d have nothing more to say, and this tutorial would be even more boring than it currently is.

Two good reasons to use <cfqueryparam>:

1) Added Security
2) Efficiency (speed) of queries

Let?s take a look at number 1 first:

Added Security

Have you ever passed a value via a URL (such as an ID) that became the value used in the WHERE clause of your query?

If you have http://www.mysite.com/page.cfm?itemID=4 as a URL, you?d probably expect a query on that page that looks something like this:

SELECT *
FROM tableName
WHERE idColumn = #URL.itemID#

What could possibly be unsecure about that? How can some malicious person hurt me or my site with that query? Simple. Some databases allow multiple SQL statements separated by a semi-colon. For example:

SELECT *
FROM tableName
WHERE column1 = ?#myValue#?; DROP TABLE tableName

Once this query selects the appropriate data, it will execute the next chunk of SQL, which will delete your table. Gone. Poof. No more table. All it takes is a semi-intelligent (and semi-malicious) user to decide he or she wants to start messing around with your database. This user would see the URL http://www.mysite.com/page.cfm?itemID=4, and could easily tack on the following: http://www.mysite.com/page.cfm?itemID=4; DROP TABLE tableName.

A longshot you say? Sure. Maybe. But do you really want to take that chance? Ask yourself what would be the consequences of your database suddenly disappearing. For many, it would be pretty close to catastrophic. 

Enter <cfqueryparam>. The cfsqltype attribute of the cfqueryparam tag specifies a datatype that is to be passed to the database. Since most ID values are numeric, the cfqueryparam would look something like this:

<cfqueryparam value=?#URL.itemID#? cfsqltype=?cf_sql_integer?>

If some unscrupulous sort were to append the DROP TABLE command to your URL parameter, the <cfqueryparam> would not execute the command (in fact, an error would be thrown, since the value of URL.itemID is no longer an integer, but now contains a string?this is where <cftry><cfcatch> and structure error handling become important).


SPEED (no, not the silly movie with the bus and the bomb)

What is it that we all want from our code? Aside from no errors?aside from the ability to generate millions of dollars because it?s the ?next big thing??.we all want speed. Our platform is the Web, and we know that if users have to wait for our code to execute on the server and display in their browser, they?re going to go elsewhere.

Here?s where <cfqueryparam> really shines. It will speed up your database queries exponentially. 

Why? 

In a ?normal? CF query, plain text is passed to the database (be it Access, SQL Server, Oracle, etc). The database must first parse that text, and ensure that the values being passed match the datatypes of the columns they reference. Any time a string needs to be parsed, time is being wasted (this is also why <cfscript> is faster than tags, and <cfif NOT structKeyExists(form, ?fieldName?)> is faster than <cfif isDefined(?form.fieldName?)>).

The use of cfqueryparam creates bind variables between your query and the database. In the most basic terms, it means that your query is handing the database the native code it expects (which would vary from db to db). As previously mentioned, this saves the db from having to parse the query string manually.

Hmmm?added security?increased speed?it sounds too good to be true! You don?t have to take my word for it. The easiest way to check for yourself is to turn debugging on in the CF Administrator for your IP Address. Run a query without the <cfqueryparam> tags, and check the execution time. Do this a few times to get an average. Then add the <cfqueryparam> tags, and repeat the process.

If you don?t have access to the CF Administrator (perhaps you?re in a shared hosting environment), you can use CF?s built in getTickCount() function.

To use getTickCount(), do the following:

<cfset beginCount = getTickCount()>

(your query here)

<cfset endCount = getTickCount()>

<cfset totalTime = endCount ? beginCount>
<cfoutput>#totalTime#</cfoutput>

Do this first with a query that doesn?t use <cfqueryparam>, and then repeat with a query that does.

If you?re as impressed with the results as I expect you?ll be?post a message in the easycfm.com forums and let others know how much faster your queries are now! If not, don?t say anything and pretend that this never happened (kidding?post to the forums ESPECIALLY if the results don?t show a significant improvement in speed?there may be a problem elsewhere, and we?ll all do what we can to figure out the issue).

Now that you?re convinced, let?s go over the syntax. This is the easy part. <cfqueryparam> takes two attributes.

1) the value (VALUE)
2) the datatype (CFSQLTYPE)

You?ve seen an example above?here are a few more just to show how ridiculously easy it is:

SELECT *
FROM MyTable
WHERE birthday = <cfqueryparam value=?#createODBCDate(form.birthday)#? cfsqltype=?cf_sql_date?>

SELECT *
FROM MyTable
WHERE username = <cfqueryparam value=?#session.username#? cfsqltype=?cf_sql_char?>

SELECT *
FROM MyTable
WHERE hits > <cfqueryparam value=?#URL.hits#? cfsqltype=?cf_sql_integer?>

As you can see, it?s quite simple. The value is simply the value you?re searching for. Notice also that you can use CF Functions within the value attribute (I used the createODBCDate() function in the first example above).

The cfsqltype attribute values are as follows:

• CF_SQL_BIGINT
• CF_SQL_BIT
• CF_SQL_CHAR
• CF_SQL_DATE
• CF_SQL_DECIMAL
• CF_SQL_DOUBLE
• CF_SQL_FLOAT
• CF_SQL_IDSTAMP
• CF_SQL_INTEGER
• CF_SQL_LONGVARCHAR
• CF_SQL_MONEY 
• CF_SQL_MONEY4 
• CF_SQL_NUMERIC 
• CF_SQL_REAL 
• CF_SQL_REFCURSOR 
• CF_SQL_SMALLINT 
• CF_SQL_TIME 
• CF_SQL_TIMESTAMP 
• CF_SQL_TINYINT 
• CF_SQL_VARCHAR

The cfsqltype attribute is NOT required (it defaults to cf_sql_char), but highly encouraged. You?ll likely find yourself using cf_sql_char, cf_sql_date, and cf_sql_integer the most frequently. The rest should be self-explanatory.

I hope you found this tutorial to be enlightening, and clear enough that it conveyed the importance of getting into the habit of using the <cfqueryparam> tag. As always, if there are any questions whatsoever, please post to the forums.