0

I have the next simple query :

SELECT code, description 
FROM   table 
WHERE  code in ( #list# )

The list is created from an XML feed with listAppend():

<cfset list= listAppend(list, data.data1[i].xmltext )>
<cfset qualifiedList1 = ListQualify(list, "'")>

With listQualify I wrap every element from the list in quotation marks for the query. The problem is that when I run the query, I don't get any results back.

If I dump the list the query look like this :

SELECT code, description 
FROM   table 
WHERE  code in ('''BG/NN1'',''BG/NL2'',''BG/NN3'',''BG/NN4'',''BG/NN5''')

Any ideas on how can fix this problem?

Update 1: I've fixed the problem.The problem was with ListQualify(list, "'")> Because list Qualify wraps every element in quotes the list attribute from cfqueryparam didn't recognized any of the values.Thank you!

user1673306
  • 19
  • 5
  • 3
    Search for "parametrize IN clause", there are plenty of answers (splitting, temp table, table_variable, dynamic-SQL, ...) – Lukasz Szozda Feb 14 '18 at 15:34
  • 2
    @lad2025 plenty of answers *daily* at that. – Jacob H Feb 14 '18 at 15:35
  • 3
    The list attribute of the cfqueryparam tag is the solution to your problem. – Dan Bracuk Feb 14 '18 at 17:46
  • 1
    As others have suggested, look at `cfqueryparm`. It does a good bit more than just properly formatting your list. But to put a little more to your issue, `('''BG/NN1'',....,''BG/NN5''')` isn't syntactically different than `('foo')`. Because of your quoting, you only have 1 element in your `IN` list. – Shawn Feb 14 '18 at 18:14
  • ... not to mention that CF auto escapes embedded quotes turning ('foo') into (''foo''). Bottom line, *don't* manually quote. use cfqueryparam list like Dan Bracuk said. – SOS Feb 14 '18 at 21:16
  • @DanBracuk Hi, I've tried already the list attribute but this is what the SQL code ends up looking like: Select code, description from table where code in (?,?,?,?,?) – user1673306 Feb 15 '18 at 10:59
  • 2
    @user1673306 - The "?" are placeholders representing the literal values sent to the db, BG/NN1,BG/NN5.... – SOS Feb 15 '18 at 16:10
  • So does it return results when using cfqueryparam? Make sure you're NOT quoting the list of values. – SOS Feb 15 '18 at 20:05
  • If cfqueryparam was the solution, you should ask @DanBracuk to promote his comment to an answer. – SOS Feb 20 '18 at 18:00

0 Answers0