CODEWITHSUNDEEP
sqlcoldfusion
user2938471
user2938471

Reputation: 31

How can I escape commas inside listQualify function?

I am a newbie at coldfusion. I need some help. My database has records that are company names as such: abc, Inc. For now I am using this query:

SELECT DISTINCT COMPANY FROM ComapanyTable WHERE (Company IN   
(#ListQualify(form.cCompanyList2, "'", ",")#))

This problem with this is that it separates the abc, Inc. 

SELECT DISTINCT Company FROM CompanyTableWHERE (Company IN ('abc',' 
Inc.','xyz','Inc.'))

I need to get the list as it is, i.e. as: 'abc, Inc.', 'xyz, Inc' So that I can later insert these values into a new table.

Insert code:

<cfquery name="insertPair" datasource="#DSN#" dbtype="ODBC">
        INSERT INTO tblChildCompanyToParent (ParentAccountID, ChildCompany)
            SELECT DISTINCT
            <cfqueryparam value = "#form.pCompanyList#" CFSQLType = "CF_SQL_VARCHAR">, Company
            FROM CompanyTable
            WHERE Company IN
            (<cfqueryparam value="#ValueList(insertSelect.Company,';' )#" CFSQLType = "CF_SQL_VARCHAR" list="true" separator=";">)      
    </cfquery>

Select list code

<select multiple name="cCompanyList2" id="cCompanyList2" class="selectCCompany" data-attribute="selCCompany" size="10">
  <cfloop query="childCompanyList"> 
    <option value="#childCompanyList.ChildCompany#">#childCompanyList.Company#</option> 
  </cfloop> 
</select>

Is there anyway to fix this.

Upvotes: 2

Views: 1097

Answers (2)

user2938471
user2938471

Reputation: 31

I fixed it using javascript and jquery

I added the the selected values to an empty array

var companyArray= [];
    $('#Company option').each(function() {
        companyArray.push($(this).val());
    });
    var theJoin = companyArray.join(';');
    //added it to an hidden input
    document.getElementById('hiddenInput').value = theJoin;
     //Delete the first semicolon that gets added before the first element
    theJoin.slice(1);

Then made these changes to the insert query

<cfquery name="insertPair" datasource="#DSN#" dbtype="ODBC">
        INSERT INTO tblChildCompanyToParent (Parent_Account_ID, Child_Account_ID)
            SELECT DISTINCT
            <cfqueryparam value = "#form.pCompanyList#" CFSQLType = "CF_SQL_VARCHAR">, Company
            FROM tblUser
            WHERE Company IN
            (<cfqueryparam value="#form.hiddenInput#" CFSQLType = "CF_SQL_VARCHAR" list="true" separator=";">)      
    </cfquery>

It will find the values in the hidden input in the form and then insert them into the database. The separator attribute in cfqueryparam will recognize that the list is not separated by the semicolon and add the elements in the database.

Upvotes: 0

Brad Wood
Brad Wood

Reputation: 3953

Instead of manually building your SQL "in" clause, you should be using the "list" attribute of the cfqueryparam tag.

http://www.bennadel.com/blog/425-coldfusion-cfqueryparam-list-attribute-is-sweeet.htm

<cfqueryparam value="#myList#" list="yes">

Now, this does not solve your core problem that you have a comma-delimited list where the values themselves may contain commas.

Unfortunately, ColdFusion does not allow list delimiters to be escaped inside a list value which is a major drawback of using lists in CFML. Typically the answer is to simply use a different delimiter that you are sure will not appear in your data, but since you are dealing with how multi-select form fields, the default delimiter is a comma.

So now, our only option is to ensure the values will never have a real comma in them. The first suggestion is to not use the company name, but rather a database ID such as an int or GUID as the dropdown value. (There are other reasons to use generated instead of natural keys in a database).

If you do not have that option in your database, you'll need to encode the select values. This could be anything-- a simple replace statement, or even something like base 64 encoding would clean the company names of commas, but leave it in a state that can be reversed on the server side to the original string.

Bear in mind, this "encoding" approach will disallow the use of the "list" parameter in cfqueryparam as each list item will need parsed and individually reverted to its original form. You'd basically need to iterate manually over the list, decode each value, and build out the "in" clause or change the list delimiter prior to decoding.

I really, REALLY would recommend using integer IDs in your database for primary keys as it really makes your life easier (and improves performance on index scans). Please, do it for the children.

Last note: If you're on ColdFusion 10 or higher, you can set this.sameformfieldsasarray=true; in Application.cfc which side-steps the entire List delimiter issue. You can still use the "list" attribute of cfqueryparam IF you convert to the array to a list using a different delimiter

<cfqueryparam value="#arrayToList( myArray, '~' )#" list="yes" separator="~">

http://www.raymondcamden.com/2012/06/19/ColdFusion-10-Missing-Feature-Form-Fields-and-Arrays

Upvotes: 5

Related Questions