2

I have a form field where user types in a comma-delimited list of values, a,b,c. Is it possible to run a query like this?

select col1, col2
from T
where col3 IN ('a','b','c') <----- cf list from the form
Community
  • 1
  • 1
epipko
  • 473
  • 5
  • 18
  • 5
    Possible duplicate of [ColdFusion: SQL Select from a Query](https://stackoverflow.com/questions/42239150/coldfusion-sql-select-from-a-query). Use `` – SOS Mar 20 '18 at 17:34
  • 1
    Also, if you're new to S.O. generally you want to include the code you've tried so far and the result (error, etc..). Please see [Ask]. – SOS Mar 20 '18 at 17:55
  • 1
    Yes, Ben Nadel has a blog post about this https://www.bennadel.com/blog/425-coldfusion-cfqueryparam-list-attribute-is-sweeet.htm – user9263373 Mar 20 '18 at 18:07
  • 1
    In addition to the answer being available in the link from the first comment, why would you expect users to type in comma delimited lists? – Dan Bracuk Mar 20 '18 at 18:54

1 Answers1

2

Answering my own question (with help of comments made here)

<!---setting a list of form field values (comma-delimited as a,b,c)--->
<cfset form_style_list = #FORM.style_id#>

<!---formatting previous list--->
<cfset final_style_list = listQualify(form_style_list,"'")>

<!---using final list in a query--->
<cfquery name="q1" datasource="#REQUEST.test#">
    SELECT count(*) row_count
    FROM STYLE a
    WHERE trim(a.style_id) in (

                <cfqueryparam
                    value="#final_style_list#"
                    cfsqltype="cf_sql_char"
                    list="yes" />
            )         
</cfquery>
epipko
  • 473
  • 5
  • 18
  • 1
    Just an FYI, any time I see `#FORM.x#` or `#URL.x#` or any other untrusted data, I treat it like it has fleas. _**Sanitizing is your friend.**_ – Shawn Mar 20 '18 at 20:16
  • Also, for things like `form_style_list = #FORM.style_id#`, you don't need those second `#`s. `form_style_list = FORM.style_id` – Shawn Mar 20 '18 at 20:18
  • 1
    And one last thing, `trim(a.style_id)` may be messing up any index you have on `style_id`. If you can, `trim()` the `style_id` before you `INSERT` it. – Shawn Mar 20 '18 at 20:32
  • 1
    You don't need the `listqualify()`. The queryparam tag takes care of that for you. – Dan Bracuk Mar 20 '18 at 21:28
  • 1
    Also, is the data type of `style_id` really `char` or something else like `varchar`? – SOS Mar 20 '18 at 22:46
  • The data type of style_id is char(14). I can't trim it as it gets inserted into the table via application. BTW, rtrim() or trim() will invalidate index use, so the only choice I have, it appears, is to pad each value in the list to 14 with NULLs. The query runs fast, so I decided not bother. – epipko Mar 21 '18 at 16:32
  • Does it have to be char(14)? If you don't expect `STYLE` to become a large table, you'll be fine. Also a SQL FYI: `WHERE x IN (1,2,3)` is the same as `WHERE x = 1 OR x=2 OR x=3`. – Shawn Mar 21 '18 at 17:52
  • I am aware, thank you. Once again, I cannot touch style table. Application inserts/updates data in it. I only report from it. Also, just curious how is FYI: 'WHERE x IN (1,2,3)' is the same as 'WHERE x = 1 OR x=2 OR x=3' helping me with what I am trying to do? – epipko Mar 21 '18 at 19:41