Use cf_sql_timestamp for MS SQL and MySQL date types

Hopefully, if you’re using ColdFusion, you’re already using cfqueryparam in your queries for better performance and security against SQL Injection attacks. If you’re not, you should read this.

When using cfqueryparam you specify a value and an SQL type. Dates can be assigned two different types, cf_sql_date and cf_sql_timestamp. cf_sql_date is intended to be used for date types in DB2 and Informix. If you’re using MS SQL Server, you should use cf_sql_timestamp.

cf_sql_timestamp maps to the datetime and smalldatetime data types in MS SQL Server. Although cf_sql_date works in many instances, it does not work in all instances. I ran into this issue when evaluating whether a date equaled a certain date value. For example:


SELECT *
FROM myTable
WHERE create_dt = 

When I ran the query in SQL Query Analyzer (minus the cfqueryparam of course), I received the expected results. When I ran it inside a ColdFusion page I received an empty result set. Once I changed to timestamp, I received my results.


SELECT *
FROM myTable
WHERE create_dt = 

I haven’t tested the code with mySQL, and mySQL isn’t listed in the livedocs cross-reference table so I don’t know what kind of effect can be seen there, though I’d be interested to find out.

UPDATE: I got two great tips from comments. According to sebastiaan cf_sql_timestamp should be used with MySQL as well as MS SQL Server. And Ben Nadel adds that cf_sql_longvarchar should be used for text fields. Great info. Thank you both!

CFQUERYPARAM with LIKE and IN Clauses

ColdFusion’s CFQUERYPARAM tag is important for several reasons, most notably data type validation, improved query performance, and foiling SQL injection attacks.

Basic usage is simple:


SELECT *
FROM tblCustomers
WHERE customer_id = 

The cfsqltype parameter validates the passed value before it is sent to the database server. Once validated, the query is sent to the database with a placeholder where the criteria would otherwise be. The criteria itself is passed separately and plugged in to the query where the placeholder exists. This allows the query to be cached by the database server, improving performance the next time the query is run.

CFQUERYPARAM also prevents SQL injection attacks that attempt to trick your code into running an extra query “injected” into the URL. SQL Injection attacks can do anything from modifying data in your tables to dropping your tables entirely if you don’t take preventative measures.

For further detail on CFQUERYPARAM, check out this post by Ben Forta.

Now, back to the point of my post. I simply want an easy place to find the correct syntax for a couple of the trickier clauses, namely “IN” and “LIKE”. I find myself researching the syntax repeatedly and it always seems time-consuming to find a post describing the correct method when I need it. So here they are:

IN Clause
The IN clause is used to find all of the records that match the criteria in a comma separated list of values. The SQL syntax is as follows:


SELECT *
FROM tblCustomers
WHERE customer_id IN (12345, 54321, 22222)

The query above will find customer records with IDs of 12345, 54321, or 22222.

To use cfqueryparam, place the parentheses outside the tag and use the “list” parameter:




SELECT *
FROM tblCustomers
WHERE customer_id IN ( )

LIKE Clause
The LIKE clause uses % as a wildcard symbol to find records that contain a certain string of text. The SQL syntax for the LIKE clause is as follows:


SELECT *
FROM tblCustomers
WHERE customer_lastname LIKE '%ski'

This query will find all of the customers whose last name ends in ‘ski’.

To use cfqueryparam, you concatenate the percent sign to the tag, like this:



SELECT *
FROM tblCustomers
WHERE customer_lastname LIKE '%' + 

Note that, while my example uses the wildcard at the beginning of the text string, it can also be used on both sides, or at the end only.

-rG