Buliding up a coldfusion query problem with like

Question

I am trying to build a dynamic sql statement with this line

<cfset SQL = "SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title from events where title LIKE '%#form.event_name#%' ">

<cfquery name="results" >
#SQL#
</cfquery>

Seems there is a problem with the like clause. Any ideas? Do I need to escape the %?

Thanks

Answer 1

Within a CFQUERY, ColdFusion will replace single quotes in #SQL# with double quotes automagically.

So in theory you would have to write your query like this:

<cfquery name="results" >
#PreserveSingleQuotes(SQL)#
</cfquery>

BUT... It's very dangerous to accept a form variable and use it without further validation directly in your query. Seems like an invitation for SQL injection attacks to me.

I'd rather use <cfqueryparam> like so:

<cfquery name="results" >
SELECT url, MONTH(event_date) AS months, YEAR(event_date) AS year, event_date, title 
from events 
where title LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.event_name#%"> 
</cfquery>