Build a dynamic cfquery using if statements - ColdFusion

Question

I am looking for the best way to create a query based on the values submitted from a form in coldfusion.

I was going to create it using <cfif> statements but started running into issues of adding commas after an AND statement but not adding if its the last AND in the query. As well as making sure the first WHERE is included in the query.

See my example below:

<cfquery name="reportInfo" datasource="DataSource1">
SELECT  *
FROM    Basic_Info

<cfif company_name NEQ "false">
WHERE   Sold_to_Party = '#company_name#',
<cfelse>
WHERE   Sold_to_Party != '',
</cfif>

<cfif user_type EQ "both">

<cfelseif user_type EQ "rittal">

AND   Sold_to_Party != 'Distributor Submission',

<cfelseif user_type EQ "distro">

AND   Sold_to_Party = 'Distributor Submission',

</cfif>

<cfif status NEQ "false">

AND   Status = '#status#'

<cfelse>

AND   Status != ''

</cfif>


ORDER BY ID
</cfquery>

You can see I added elseif statements in so that I could add the same statement without a comma.

You are correct I was mixing it up with insert statements. And that is why I was getting the comma error. What about the WHERE part of the statement? — Denoteone
Yeah it is working now I just wasnt sure if that was the best way to do it. — Denoteone
Make sure to parameterise you data vales rather than hard-coding them in your SQL statement. — Adam Cameron

John Whish John Whish · Accepted Answer · 2015-05-01T08:38:45

You don't need commas in the WHERE clause - it's not valid SQL.

When building dynamic SQL WHERE clauses, then a quick tip is to just have a 'dummy' where clause. Here's a quick example (I've not included all your clauses):

<cfquery name="reportInfo" datasource="DataSource1">
    SELECT  *
    FROM    Basic_Info
    WHERE 1 = 1

    <cfif company_name NEQ false>
       AND Sold_to_Party = <cfqueryparam value="#company_name#" cfsqltype="CF_SQL_VARCHAR">
    <cfelse>
        AND Sold_to_Party != <cfqueryparam value="" cfsqltype="CF_SQL_VARCHAR">
    </cfif>
    <cfif form.user_type EQ "rittal">
        AND Sold_to_Party != <cfqueryparam value="Distributor Submission" cfsqltype="CF_SQL_VARCHAR">
    <cfelseif form.user_type EQ "distro">
        AND Sold_to_Party = <cfqueryparam value="Distributor Submission" cfsqltype="CF_SQL_VARCHAR">
    </cfif>
    <cfif status NEQ "false">
        AND Status = <cfqueryparam value="#status#" cfsqltype="CF_SQL_BIT">
    </cfif>
    ORDER BY ID
 </cfquery>

Something else to be aware of is SQL injection attacks. In short, always use cfqueryparam when you are passing in values. cfqueryparam protects you from SQL injection and also allows the database engine to create and reuse the execution plan when you pass in different values to the parameter.

If you're not sure which cfsqltype value to use then there is a handy list of them here (for MySQL): http://cfsearching.blogspot.co.uk/2010/01/cfqueryparam-matrix-for-mysql-5.html

Build a dynamic cfquery using if statements - ColdFusion

1 Answers