CODEWITHSUNDEEP
sql-servercoldfusion
brianh
brianh

Reputation: 23

cfquery Syntax Error

I'm receiving the following error and can not figure out why:

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'. The error occurred on line 141.

<cfquery datasource="mySource" name="getTravel">
    select traveler, customernumber, destination, tripdate, purpose, empid, pm_reportid, company
    from mySource.dbo.PM_Travel
    where pm_reportid in 
    (
        <cfloop from="1" to="#getRecentReports.recordcount#"  index="i">
            <cfif i lt getRecentReports.recordcount>
                 #getRecentReports.pm_reportid[i]#,         
            <cfelse>
                 #getRecentReports.pm_reportid[i]#
            </cfif>
         </cfloop>
    )
    order by customernumber
</cfquery>

Line 141 is:

where pm_reportid in (<cfloop from="1" to="#getRecentReports.recordcount#"  index="i"><cfif i lt getRecentReports.recordcount>#getRecentReports.pm_reportid[i]#, <cfelse>#getRecentReports.pm_reportid[i]#</cfif></cfloop>)

This was not written by me, and I'm new to CF. Any help is appreciated.

Upvotes: 1

Views: 526

Answers (2)

Leigh
Leigh

Reputation: 28873

(Follow up from the comments)

As I mentioned, the cfloop code is probably generating an invalid sql statement. Either due to the query containing zero (0) records, which would produce an empty clause:

 WHERE Col IN ( {nothing here} )

... or one of the id's is null, which would create an invalid clause with extra commas, like: 

 WHERE Col (11,22,33,{no value here},44)

Scott's answer shows one way to handle this. Another option is to use a JOIN instead of looping through the query results. Simply join the two tables on the matching column(s) and use the appropriate WHERE filters. The exact SQL depends on the relationship between the two tables, but below is the general idea. I do not know the exact names or data types of the columns involved, so modify as needed. 

<cfquery ....>
    SELECT t.traveler, t.customernumber, t.destination, ...more columns ...
    FROM  PM_Travel t INNER JOIN pm_reports r
              ON t.pm_reportid = r.pm_reportid
    WHERE  r.empID = <cfqueryparam value="#form.empid#" cfsqltype="cf_sql_integer">
    AND    r.weekID = <cfqueryparam value="#form.weekid#" cfsqltype="cf_sql_integer">
</cfquery>

Side note, I know this is an inherited application, but be sure to use cfqueryparam on all user supplied values. Both for enhancing performance, and to provide an extra layer of protection against a common form of sql injection, as Scott mentioned. You can find a mapping of the cfsqltypes by dbms in the CF documentation.

Upvotes: 4

Scott Stroz
Scott Stroz

Reputation: 7519

Try this:

where pm_reportid in ( <cfqueryparam value="#valueList(getrecentReports.pm_report_id)#" list="true" cfSqlType="CF_SQL_NUMERIC" /> )

You may need to change the value of cfSqlType to match the type of data that is contained in the 'pm_report_id column'. If it is text, try using CF_SQL_VARCHAR

To explain what is going on:

valueList() takes a query column, in the syntax of 'queryName.columnName', and converts it to a comma delimited list.

cfqueryparam parameterizes the query which will give you a performance boost on the SQL side and offers a bit of security as the data is passed as a certain data type.

Upvotes: 7

Related Questions