Definitely use <cfqueryparam> for input values in dynamic SQL (with the <cfquery> tag). This will prevent SQL injection attempts.
<cfqueryparam> automatically escapes quote characters for strings (varchar), and can also be set to only allow numeric values as well. Example:
No matter what I put in the URL string with any injection attempt, <cfqueryparam> will make it look like all one value to the database.
<cfquery name="myQuery" datasource="db">
SELECT col FROM table
WHERE someString = <cfqueryparam value="#url.someString#" cfsqltype="CF_SQL_VARCHAR">
Even if I put in: &someString='; DELETE FROM users
Then the resulting SQL will look like this:
Here, the red quotes have been escaped, and the rest of the "SQL" just becomes a normal string value to compare the someString column to, as far as the database is concerned.
SELECT col FROM TABLE
WHERE someString = '''; DELETE FROM users'