1

I have a input checkbox field where user can select multiple checkboxes in a form, and depending on what they select, it will create a string of id's like 10,14,35,47, and that will be submitted to the database. I can get this submitted if I just submit it directly like below:

user_job_type_id="#form.user_job_type_id#",

But, if I try and wrap that in a cfqueryparam, which I would rather do to make it more secure, I get an error, regardless of what I do. Whether I set it to a list true or false, varchar, integer, everything throws an error like cfqueryparam doesnt accept the list.

For example, below will not work

user_job_type_id=<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.user_job_type_id#" list="Yes"/>,

What am I missing here?

Joel
  • 41
  • 1
  • 5
  • 1
    What is the error you get? – Dan Bracuk Apr 13 '21 at 00:19
  • 3
    If you're trying to search for multiple id's, the sql syntax is incorrect. The equals `=` operator only compares single values. For multiple values, use `IN (...)`. i.e. `where user_job_type_id IN ( )`. See your dbms documentation for more details. – SOS Apr 13 '21 at 00:23

1 Answers1

6

As @SOS mentioned, you need to use an IN statement when checking against a list of values in parenthesis. In addition, since the values are all integers, make sure to specify the correct cfsqltype. This ensures the correct date type check for each element in the list.

Wrong:

user_job_type_id = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.user_job_type_id#" list="Yes"/>,

Better:

user_job_type_id IN (<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.user_job_type_id#" list="Yes"/>),

Best:

user_job_type_id IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.user_job_type_id#" list="Yes"/>),
Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • Thank you, I am trying to insert to the database though, not query. So this would be an example: ``` update user_job set user_job_type_id= where user_job_id= #form.user_job_id# ``` This throws an error from the cfqueryparam usage. If I do not use cfqueryparam and do the below, it works fine, although Id rather use the cfqueryparam if possible: ``` update user_job set user_job_type_id=user_job_type_id="#form.user_job_type_id#" where user_job_id= #form.user_job_id#``` – Joel Apr 26 '21 at 15:10
  • Do you have a way to associate the items in #form.user_job_type_id# with the items in #form.user_job_id#? Then loop over the list to do your insert. – mykaf Apr 26 '21 at 15:42
  • The list is stored in a single cell in the database as a comma separated list. So it would end up like '49,56,87' for example. I cant figure out why cfqueryparam doesn't like the comma separated list. – Joel Apr 26 '21 at 18:05
  • In that case, nix the list attribute and just insert it as a string. The list attribute is more for `where x in ()` clauses. – mykaf Apr 26 '21 at 19:13