CF queries using form variables entered by a user

Question

I am trying to do a query matching my trans_location column with the form data the user enters. I have a dropdown that lets the user choose multiple locations. When they choose multiple it places commas in between each location. When I choose only one location the results come back correct with the correct location. But when I choose more than one location it does not find any of the locations. Do the commas make it only one name and it will not search each location?

<cfset result = {} /> 
<cftry>
    <cfset date1 = #CREATEODBCDATETIME(form.StartDate & '00:00:00')#>
    <cfset date2 = #CREATEODBCDATETIME(form.EndDate & '23:59:59')#>

    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT *
        FROM cl_checklists
        WHERE date >= #date1# AND date <= #date2#
        AND trans_location = '#form.Location#'
    </cfquery>

<cfoutput>#date1#</cfoutput>
<cfoutput>#date2#</cfoutput>

<cfdump var="#GetLocationInfo#">

    <cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry>

I also tried doing something like this:
AND trans_location = <cfqueryparam value='#form.Location#' />

Not related to your question, but I find select controls that allow multiple selections to really easy to mess up. You might consider checkboxes instead. — Dan Bracuk, Apr 06 '16 at 14:39
Side note, if you need to filter on entire dates, the correct comparison is `WHERE Col >= {startDateAtMidnight} AND Col < {dayAfterEndDateAtMidnight}`]. See [this thread](http://stackoverflow.com/a/27062906/104223) for more details and the proper cf_sql_type. — Leigh, Apr 06 '16 at 17:25

score 3 · Accepted Answer · edited Apr 06 '16 at 14:12

You need to use the IN operator in conjunction with cfqueryparam with the list="true" attribute. (Here is a quick helpful tutorial for cfqueryparam: https://www.petefreitag.com/item/677.cfm)

Lastly: always, always, always use cfqueryparam when sending parameters to the database.

<cfset result = {} />
<cftry>
    <cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
    <cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT  *
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

<cfoutput>#date1#</cfoutput>
<cfoutput>#date2#</cfoutput>

<cfdump var="#GetLocationInfo#">

    <cfcatch type="any">
        <cfset result.error = CFCATCH.message >
        <cfset result.detail = CFCATCH.detail >
    </cfcatch>
</cftry>

CF queries using form variables entered by a user

1 Answers1