CODEWITHSUNDEEP
mysqlcoldfusionleft-joininfinite-loop
frequent
frequent

Reputation: 28513

Why does this Left Join in Coldfusion/MySQL cause an endless loop?

I'm running a search query in Coldfusion8 (MySQL 5.0.88).

The query constructs a LEFT JOIN in Coldfusion like so:

<cfquery datasource="db" name="q">
    SELECT ...
</cfscript>

and loops over the results to create the MySQL query syntax:

<cfset variables.pl= "LEFT JOIN pricelists p ON ">
<cfloop query="q" >
    <cfscript>
    if( q.listitem IS '' )    
        variables.pl = variables.pl & '(a.iln = p.iln AND p.pl= "-Standard-" AND p.ean = a.ean AND p.iln = "#q.iln_verkaeufer#") OR ';
    else
        variables.pl= variables.pl & '(a.iln = p.iln AND p.pl= "#q.pl#" AND p.ean = a.ean AND p.iln = "#q.userID#") OR
    </cfscript>
</cfloop>
<cfset variables.pl= variables.pl& "(1=0)">

So the final will look something like this:

LEFT JOIN plist p ON (p.iln = a.iln AND p.pl= "-Standard-" AND p.iln = "1111111111111") OR (p.iln = a.iln AND p.pl= "I" AND p.iln = "1111122222221") OR (1=0)

Which is fed into the actual query synatx:

SELECT art, count(*) as number
    FROM article a
    <cfqueryparam value="#variables.pl#" cfsqltype="cf_sql_longvarchar">
WHERE ...

If I try to trigger this, the server just hangs up and I need to restart the browser tab to end the session. I have removed all other search criteria from my select. The error is caused be the cfqueryparam feeding my text contstruct.

However I don't know what the problem could be, because I never get an error. The browser just goes dead, so I guess I'm creating some kind of endless loop.

Question
Can anyone tell from my code what I'm doing wrong?

Thanks!

EDIT: I managed to go from Server hangup to a commit error. But since the script is triggered by an Ajax call and I cannot remotely access CFadmin, I need to blindguess what the problem could be.

Upvotes: 1

Views: 288

Answers (1)

Busches
Busches

Reputation: 1964

What you're doing here isn't the purpose of cfqueryparam and I'd suggest reading up on the proper usage of cfqueryparam - http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

Refactoring your code to something like below should work as you have intended. I replaced the script for tags, just at my preference, feel free to use script if that's your preference.

<cfquery ...>
SELECT art, count(*) as number
FROM article a
LEFT JOIN pricelists p ON
<cfloop query="q" >
<cfif q.listitem IS ''>
    (a.iln = p.iln
    AND p.pl= "-Standard-"
    AND p.ean = a.ean
    AND p.iln = <cfqueryparam value='#q.iln_verkaeufer#' />
    )
<cfelse>
    (a.iln = p.iln
    AND p.pl= <cfqueryparam value="#q.pl#"/>
    AND p.ean = a.ean
    AND p.iln = <cfqueryparam value="#q.userID#" />
    )
</cfif>
<cfif NOT q.isLast()>OR</cfif>
</cfloop>
WHERE ...
</cfquery>

Upvotes: 4

Related Questions