Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: RichardCooper
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: RichardCooper

ColdFusion CFQueryParam Varchar-List Puts In Single Quotes For You

By
Published in — Comments (28)

I love ColdFusion's CFQueryParam tag. I use it in almost all of my queries. One of the CFQueryParam features that I love is the List attribute; the List attribute, which I have blogged about before, allows you to use one CFQueryParam tag to bind a list of values to your query. Most of the time, I use this with a list of IDs:

WHERE
	id IN (
		<cfqueryparam
			value="1,2,34"
			cfsqltype="cf_sql_integer"
			list="true"
			/>
	)

This has always worked great. The other day, however, it did trip me up a bit. I started using it with a list of Varchar values rather than Integer value. In a particular query, I wanted to get all contacts from a list of given states:

<!--- Select contacts from NY and MA. --->
<cfquery name="qContact" datasource="#DSN.Source#">
	SELECT
		c.id,
		c.first_name,
		c.last_name
	FROM
		contact c
	INNER JOIN
		contact_information ci
	ON
		(
				c.contact_information_id = ci.id
			AND
				ci.state IN (
					<cfqueryparam
						value="'NY','MA'"
						cfsqltype="cf_sql_varchar"
						list="true"
						/>
				)
		)
</cfquery>

Notice that I am passing the state abbreviation list, 'NY','MA' into the CFQueryParam tag. This looked good to me, but nothing came back in my query. I knew there were contacts in the database that were in NY and MA, but yet, no results. This actually took me a good 15 minutes to debug (especially because it was behind an AJAX call which isn't the easiest thing to debug - I had to use CFThrow tags rather than CFDump tags to get any data echoed). As it turns out, ColdFusion's CFQueryParam puts in the single quotes for you; so, instead of passing in:

'NY','MA'

... all I needed to do was pass in:

NY,MA

... and the combination of cf_sql_varchar and list="true" would know to create a varchar list (using single quotes).

I wonder if I have made this mistake before on other applications and just never noticed since it didn't error out? Hmmmmm.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/1267

Reader Comments

27 Comments

I'm just going from memory on this so someone correct me if I'm wrong ... I believe that CF will convert a boolean value to 0 or 1 if the cfsqltype is set to "bit". This eliminates the need to do any condition statements to get the correct value to the database. It also allows for boolean expressions to be put directly into the queryparam value field.

6 Comments

cfqueryparam list maybe the greatest thing ever....well ok that maybe a bit much but still very cool and very useful.

2 Comments

Hm,

When I do this in my query:

Flag = <cfqueryparam value="#arguments.bean.getFlag()#" <cfsqltype="cf_sql_bit">

and then dump the query, It shows that FLAG = "TRUE".

So it does not appear that it is casting the boolean to a bit.

4 Comments

Ben,

Thanks for this CFQueryParam List post and your previous one, it has really helped me out.

But I don't know if I've found another issue that is related to this. But here's what I'm trying to do.

I have two tables. Members and Companies. A Member can have multiple companies. So I have a field in the members table called company_id its a varchar and can contain one or a list of ids separated by commas that matches up to the company_id (id, integer, auto number) in the Companies table.

I want to select all the companies a member has listed. I'm doing some thing like this.

<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members, companies
WHERE member_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
AND companies.company_id IN (<cfqueryparam value="members.company_id" cfsqltype="cf_sql_varchar" list="true" separator=",">)
</cfquery>

It returns nothing. But if I change members.company_id in the cfqueryparam tag to an actual list like 1,3,7. It works.

If I change cfsqltype to cf_sql_integer it throws an error.

Any thoughts?

15,848 Comments

@Jeff,

The CFQueryParam tag is used to bind ColdFusion data to the query. You cannot use it to bind SQL data to the query because, for one reason, the SQL data is not available at the time the CFQueryParam data executes.

Remving the CFQueryParam tag is the way to do what you want.

6 Comments

@Jeff

Two things:
<cfqueryparam value="members.company_id" cfsqltype="cf_sql_varchar" list="true" separator=",">

the value would need to be wrapped with ## like this: <cfqueryparam value="#company_id#" cfsqltype="cf_sql_varchar" list="true">

Also you might rewrite that query using joins:

<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members
inner join companies on members.company_id = companies.company_id
WHERE member_id = <cfqueryparam value="#id#" cfsqltype="cf_sql_integer">
</cfquery>

forgive the typos, hopefully you get the point.

4 Comments

@Ben,

Thanks for the quick reply. Didn't get a chance to mess with this code till today.

What you said works. Kind of? Maybe I can't do this with Natural joins?

I now have this code...

<cfquery name="GrabInfoForEmail">
Select members.firstname, members.lastname, companies.company_name
FROM members, companies
WHERE companies.company_id IN (members.company_id)
</cfquery>

members.company_id is a field (within the members table) with a list of IDs (1,3,4).

It only returns the info for the first in the list. So what ever company_id 1 is.

But if I change the code to "companies.company_id IN (1,3,4)" It works and returns all company info for 1, 3, and 4.

Am I missing some thing? Can you not do this with Natural joins? I've been looking around trying to find info on this, haven't found much.

@Scott

Thanks for the reply. What you suggested works too. But it does the same thing as the natural join from above.

15,848 Comments

@Jeff,

To be honest, I have never worked with a list of IDs stored in a field AND used it in a JOIN logic. It's possible that the database engine doesn't work that way.

I think the problem is that the database field is a VARCHAR and, in a way, you are trying to do an implicit conversion of data types to a list of integers.

I'll try to do some thinking on this.

8 Comments

This was the perfect solution to an issue I have been having trouble with in regards to single quotes in a query variable. Thanks very much.

8 Comments

@Leon,

Sure, but I basically just did what Ben outlined above, I certainly didn't reinvent the wheel :) I was dealing with a form field with a multi-select list of cities. I did:

WHERE municipality IN
(
<cfqueryparam value="#FORM.city#" cfsqltype="cf_sql_varchar" list="true" />
)

Cheers

15,848 Comments

@Existdissolve,

I know this sounds crazy, but I don't think I even noticed that other people mentioned the separator attribute. I don't remember knowing that that was even in the possible tag attributes. I'll have to play around with this.

4 Comments

@Ben--

The docs are a little weird on this attribute, IMO. They say that "separator" is required if you "specify a list in value attribute." Does that mean it's required if you specify a list in the value attribute WITHOUT using the list attribute? And does "required" mean if you don't use it, the value attribute will be treated as a regular string, and not as a list (e.g., single quotes around list items)?

I tried passing my pipe-delimited list to an IN() without the list attribute and, as expected, it passed a regular string to SQL (e.g., 'item one,item two,item three'), apparently ignoring the separator attribute.

<cfqueryparam value="#mylist#" separator="|" />

When I added the list attribute back in, CF passed a nice set of quoted list items to SQL (e.g., 'item one','item two','item three').

<cfqueryparam value="#mylist#" separator="|" list="true" />

So I guess I take a bit of exception to the language of "required." As your example shows, cfqueryparam will work just fine with a combination of value and list, so long as the list is comma-delimited. Separator is only really "required" if your delimiter is not a comma.

Am I missing something here?

And since I'm already complaining :), the term "separator" is strange. Since it's only used in conjunction with lists, I think "delimiter" makes more sense.

Ok, enough complaining. Even though the documentation is a bit wonky, I'm just happy that this functionality exists :). It's a prime example of ColdFusion doing what ColdFusion does best--making web application development super fast and easy.

15,848 Comments

@Existdissolve,

Yeah, the documentation is a bit funky. It's a bit odd to say something is required AND give it a default value. Although I am not sure about the rest of the docs - maybe they use the same approach.

I want to play around, see if I can get this to do anything interesting.

1 Comments

Hello, I am not sure why this not working. I am using mysql db and my query didn't run when I supply two values

<cfquery name="dff" datasource="#request.dsn#">

select * from courseregistration where COURSEID <> <cfqueryparam value="139,132" cfsqltype="cf_sql_numeric"
list="true"
/>
</cfquery>

Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '132' at line 1

4 Comments

@Jameson,

Since you are using a list you have to use the IN operator. In your case NOT IN.

Try

<cfquery name="dff" datasource="#request.dsn#">
select * from courseregistration where COURSEID NOT IN (<cfqueryparam value="139,132" cfsqltype="cf_sql_varchar" list="true" separator=","/>)
</cfquery>

I'm assuming COURSEID is an integer. But I think (correct me if I'm wrong) you still need to do cfsqltype="cf_sql_varchar" because you are working with a list. If not, try cfsqltype="cf_sql_integer"

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel