[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Coldfusion SQL and apostrophes

Posted on 2006-11-28
22
Medium Priority
?
933 Views
Last Modified: 2013-12-20
I have a query of a query that is pulling data from the active directory using sql and coldfusion. I want to get a result that has an apostrophe in it. my sql is below:

<cfquery dbtype="query" name="results3">
SELECT * FROM results WHERE lower(displayName) like lower('%#searchvars#%')

      
</cfquery>

The searchvars contains an example of o'brien. I can't seem to get that to show up in the outputted query. Any ideas?
0
Comment
Question by:asaworker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +2
22 Comments
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 18032248
try this...

<cfquery dbtype="query" name="results3">
SELECT * FROM results WHERE lower(displayName) like lower('%#preservesinglequotes(searchvars)#%')
</cfquery>
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 18032307
it sounds like maybe the quotes are throwing it off, however when you're doing a "like" it should return similar matches regardless of quotes, have you dumped out the data you want to compare to make sure the values are there, and to see what would be there for a comparrison? sometimes that helps to figure out why something isn't matching up as well...
0
 

Author Comment

by:asaworker
ID: 18037282
I think I put my question in wrong. I am using a query to get results out of an active directory using ColdFusion. The results are an Intranet phone directory. I am trying to search on those results. SOme results have an apostrophe in them. When I put the apostrophe in the search box, it is not finding the results with the apostrophe in them. Any ideas? Code below:

<h2 class="subhomehead">:: Phone Directory</h2>
                              <cfset sortby = form.displayName>
<cfif (IsDefined("form.displayName") AND form.displayName is not "")>

<cfset filter = "(&(objectclass=user)">
<cfparam name="searchvars" default="form.displayName">
<cfif (IsDefined("form.displayName") AND form.displayName is not "")>
    <cfset filter = filter & "(displayName=#form.displayName#*)">
</cfif>
<cfset filter = filter & ")">

      <!--- make the LDAP query --->
      <cfparam name="logindomain" default="amsa">
<cfparam name="password" default="xxx">
<cfparam name="username" default="xxxxxx">
<cfparam name="ldapServer" default="xxx.xxx.com">
<cfparam name="dcStart" default="ou=Users & Groups,dc=xxx,dc=com">
<cfldap action="QUERY" name="results" attributes="displayName,title,department,PhysicalOfficeDeliveryName,telephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">


<cfquery dbtype="query" name="results2">
       SELECT DISTINCT department FROM results WHERE department != ''
</cfquery>

<cfquery dbtype="query" name="results3">
SELECT * FROM results WHERE lower(displayName) like lower('%#searchvars#%')

      
</cfquery>

<!--- Display results --->
      <center>
     
      <table width="82%" cellspacing="0" cellpadding="0" border="0" class="search">
         <tr>
            <th colspan="6">
               <cfoutput>#results3.recordCount# matches found for #searchvars# <!---<br/>Using filter: #filter#--->
                     </cfoutput></TH>
         </tr>
         <tr>
            <th>Name</th>
            <th>Title</th>
            <th>Department</th>
            <th>Ofice</th>
            <th>Phone</th>
            <th>E-mail</th>
         </tr>
         <cfoutput query= "results3">
            <tr>
               <td>#displayName#</td>
               <td>#title#</td>
               <td>#department#</td>
               <td>#PhysicalOfficeDeliveryName#</td>
                     <td>#telephoneNumber#</td>
               <td><A href = "mailto:#mail#">#mail#</A></td>
            </tr>
         </cfoutput>
         </table>
</cfif>
<cfoutput>

<form action="#cgi.script_name#" method="POST">
<p>Enter a name to search in the database.
<p>
Firstname <input type="Text" name="displayName" <cfif (IsDefined("form.displayName") AND form.displayName is not "")>value="#form.displayName#"</cfif>>
<br/><select name="department">
      <option value="">All Departments</option>
      </cfoutput>
      <cfoutput query = "results2">
      <option value="#department#">#department#</option>
      </cfoutput></select>
<input type="Submit" value="Search" name="Search">
</form>
0
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 12

Expert Comment

by:mmc98dl1
ID: 18062919
I think you are referrring to this query:

<cfquery dbtype="query" name="results3">
SELECT * FROM results WHERE lower(displayName) like lower('%#searchvars#%')
</cfquery>

I would suggest that you change the variable to double quotes " 

<cfquery dbtype="query" name="results3">
SELECT * FROM results WHERE lower(displayName) like lower("%#searchvars#%")
</cfquery>

I would also advise you to use <cfquerparam> for all of your queries from a security and performance perspective.
0
 

Author Comment

by:asaworker
ID: 18071996
Still not working. I'm trying to pull a result form the phonebook that has an apostrophe in the last name . An example would be O'Malley. Code Below:

<cfset sortby = form.q>
                              <cfif (IsDefined("form.q") AND form.q is not "")>
                                    <cfset filter = "(&(objectclass=user)">
                                    <cfparam name="searchname" default="form.q">
                                    <cfif (IsDefined("form.departments") AND form.departments is not "")>
                                    <cfparam name="searchdept" default="form.departments">
                                    <cfset searchdept = form.departments>
                                    </cfif>
                                    <cfif (IsDefined("form.q") AND form.q is not "")>
                                        <cfset filter = filter & "(displayName=#form.q#*)">
                                        <cfset searchname = PreserveSingleQuotes(form.q)>
                                    </cfif>
                                    <cfset filter = filter & ")">
                                    <!--- make the LDAP query --->
                                    <cfparam name="logindomain" default="xxxx">
                                    <cfparam name="password" default="xxxxxxxxx">
                                    <cfparam name="username" default="xxxxx">
                                    <cfparam name="ldapServer" default="xxxx.xxxx.com">
                                    <cfparam name="dcStart" default="ou=Users & Groups,dc=xxxx,dc=com">
                                                                        <cfldap action="QUERY" name="results" attributes="displayName,title,department,physicalDeliveryOfficeName,telephoneNumber,otherFacsimileTelephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">
                                    
                                    <cfquery dbtype="query" name="results2">
                                           SELECT DISTINCT department FROM results WHERE department != ''
                                    </cfquery>
                                    <cfquery dbtype="query" name="results3">
                                          SELECT * FROM results WHERE lower(displayName) like lower('%#searchname#%')
                                    </cfquery>
                                    <cfif IsDefined("form.departments")>
                                    <cfquery dbtype="query" name="results4">
                                          SELECT * FROM results WHERE lower(displayName) like lower('%#searchname#%') AND department Like '%#searchdept#%' Order BY department ASC
                                    </cfquery>
                                    </cfif>
                                    <cfoutput>
                              <form action = "#CGI.SCRIPT_NAME#" method="post">
                              <p>Enter a name to search in the database.</p>
                              <p>Name <input type="Text" name="q" <cfif (IsDefined("form.q") AND form.q is not "")>value="#form.q#"</cfif>>
                              <select name="departments" <cfif (IsDefined("form.departments") AND form.departments is not "")>value="#searchdept#"</cfif>>
                                    <option value="">All Departments</option>
                                    </cfoutput>
                                    <cfoutput query = "results2">
                                    <option value="#department#" <cfif (IsDefined("form.departments") AND form.departments eq "#department#")>selected="selected"</cfif>>#department#</option>
                                    </cfoutput>
                              </select>
                              <input type="Submit" value="Search" name="Search">
                              <cfif IsDefined("form.departments")>
                              <cfoutput>
                                          <p><strong>#results4.recordCount#</strong> match<cfif NOT(#results4.recordCount# EQ 1)>es</cfif> found for <strong>#searchname#</strong> in <cfif form.departments eq "">All Departments<cfelse><strong>#searchdept#</strong></cfif>.</p>
                              </cfoutput>
                              <cfelse>
                              <cfoutput>
                                    <p><strong>#results3.recordCount#</strong> match<cfif NOT(#results3.recordCount# EQ 1)>es</cfif> found for <strong>#searchname#</strong>.</p>
                              </cfoutput>
                              </cfif>
                              <table width="100%" id="searchresults">
                                    <!--- Display results --->
                                          <cfif IsDefined("form.departments")>
                                          <cfif NOT(#results4.recordCount# EQ 0)>
                                          <tr>
                                                <th>Name</th>
                                                <th>Title</th>
                                                <th>Department</th>
                                                <th>Office</th>
                                                <th>Phone</th>
                                                <th>Fax</th>
                                                <th>E-mail</th>
                                          </tr>
                                          </cfif>
                                          <cfelse>
                                          <cfif NOT(#results3.recordCount# EQ 0)>
                                          <tr>
                                                <th>Name</th>
                                                <th>Title</th>
                                                <th>Department</th>
                                                <th>Office</th>
                                                <th>Phone</th>
                                                <th>Fax</th>
                                                <th>E-mail</th>
                                          </tr>
                                          </cfif>
                                          </cfif>
                                          <cfif IsDefined("form.departments")>
                                          <cfoutput query="results4">
                                          <tbody>
                                          <tr>
                                                <td>#displayName#</td>
                                                <td>#title#</td>
                                                <td>#department#</td>
                                                <td>#physicalDeliveryOfficeName#</td>
                                                <td>#telephoneNumber#</td>
                                                <td>#otherFacsimileTelephoneNumber#</td>
                                                <td><a href="mailto:#mail#">#mail#</a></td>
                                          </tr>
                                          </tbody>
                                          </cfoutput>
                                          <cfelse>
                                          <cfoutput query="results3">
                                          <tbody>
                                          <tr>
                                                <td>#displayName#</td>
                                                <td>#title#</td>
                                                <td>#department#</td>
                                                <td>#physicalDeliveryOfficeName#</td>
                                                <td>#telephoneNumber#</td>
                                                <td>#otherFacsimileTelephoneNumber#</td>
                                                <td><a href="mailto:#mail#">#mail#</a></td>
                                          </tr>
                                          </tbody>
                                          </cfoutput>
                                          
                                          </cfif>
                                    </table>
                              </cfif>
0
 
LVL 12

Expert Comment

by:mmc98dl1
ID: 18072816
The only other thing I suggest you try is (<cfqueryparam cfsqltype="cf_sql_varchar" value="%#searchname#%">)

You will need to make sure "cf_sql_varchar" is the right value for your database column. You can find a table of the various options on the livedocs page for cfqueryparam.
0
 

Author Comment

by:asaworker
ID: 18122837
My real problem that I'm having is that I can't modify the table that has the entries in it. It's being entered into the Active Directory. I don't have any permissions to modify the data. The table has someones name as o'brien. My query of query won't return o'brien as a result if you type in o'brien in the input field. If I use brien, then o'brien will show up in the results for the phonebook. I need to get this resolved asap.
0
 

Author Comment

by:asaworker
ID: 18293688
I really need this question answered
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18572482

This may not be it, but first observation is that the following statement may not be giving you the value you want in searchvars.

<cfparam name="searchvars" default="form.displayName">
 Above, the value of searchvars will be "form.displayname"

It should be
<cfparam name="searchvars" default="#form.displayName#">

So the value of searchVars gets whatever is in form.displayName.


Also, can you verify that when searching for "O'Brien"  the record is found within the query "results" ?

 You can do this my doing ..

 <cfdump var="#results#">  
 and looking through the output to ensure O'Brien made it that far.

 If you see it there, then, as you said, we focus on the query of queries to get those records (result3)


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18572505

 By the way.  It seems that this ..

<cfparam name="searchvars" default="form.displayName">

 Should really be this..

<cfset searchvars=form.displayName>

 CFPARAM sets the variable to whatever is in 'default' only if it does not exist already.
 If seems you don't really want to test to see if the variable exists or not, basically, you just want to set the value to form.displayName, regardless.   So, use <CFSET instead.

 ... that's just a tangent, it won't solve it :)

0
 

Author Comment

by:asaworker
ID: 18572588
O'brien shows up in the search results, but I can't use o'brien to search for it, I have to use any thing after '. brien will work.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18572703

 Please try this as your results3  query.

<cfquery name="results3" dbtype="query">
  SELECT *
  FROM results WHERE displayName like '%#preservesinglequotes(searchvars)#%'
</cfquery>

If the above does not work, I'd like to see what it looks like when its run.  You can find this if you have debugging turned on and scroll down the page to see all the queries.   Locate this query and cut and paste it in here.

 If you don't have debugging turned on, perhaps you can place just above the Results3  query and grab it from the screen when it displays in the browser...

<cfoutput>  <!--- note its CFOUTPUT instead of cfquery ---->
  SELECT *
  FROM results
  WHERE displayName like '%#preservesinglequotes(searchvars)#%'
</cfoutput>


0
 

Author Comment

by:asaworker
ID: 18572799
Error Executing Database Query.  

Query Of Queries syntax error.
Encountered "brien.  
 
The error occurred in C:\Inetpub\intradev\phone\results.cfm: line 70
 
68 :                                     <cfquery dbtype="query" name="results3">
69 :                                           SELECT * FROM results
70 :                                           WHERE LOWER(displayName) LIKE '%#preservesinglequotes(searchname)#%'
71 :
72 :                                     </cfquery>

 
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18573083

 Well, you're absolutely right.   Query of a query has a problem with single quotes and does NOT act the same as a normal query.

<cfset searchvars = "O'Brien">

A regular query to the database like this works perfectly fine:::

<cfquery name="results3" datasource="myDatabase">
  SELECT *   FROM results
  WHERE displayName like '%#searchvars#%'
</cfoutput>

But doing the same thing with a query of a query fails with the error you showed.

 * THIS DOES NOT WORK
<cfquery name="results3" dbtype="query">
  SELECT *   FROM results
  WHERE displayName like '%#searchvars#%'
</cfoutput>


I found that this UGLY work-around does resolve the problem::
<cfset searchvars= replace(searchvars,"'","''''")>

<cfquery name="results3" dbtype="query">
  SELECT *   FROM results
  WHERE displayName like '%#searchvars#%'
</cfoutput>

This line changes a single quote to FOUR single quotes:
<cfset searchvars= replace(searchvars,"'","''''")>

Here it is spaced out for read-ability (don't use this one):

<cfset searchvars= replace(searchvars, " ' " ,  "   ' ' ' '  " )>

Of course, this makes your searchvars variable Only good for this search.  If you need it for anything else,  create a new variable for the query of query filter.




0
 

Author Comment

by:asaworker
ID: 18573137
I tried it and it's still not finding that result. I'm not getting the error anymore. Below is the revised code:

<cfset sortby = form.q>
                              <cfif (IsDefined("form.q") AND form.q is not "")>
                                    <cfset filter = "(&(objectclass=user)">
                                    <cfparam name="searchname" default="form.q">
                                    <cfset searchname= replace(searchname,"'","''''")>
                                    <cfif (IsDefined("form.departments") AND form.departments is not "")>
                                    <cfparam name="searchdept" default="form.departments">
                                    <cfset searchdept = form.departments>
                                    </cfif>
                                    <cfif (IsDefined("form.q") AND form.q is not "")>
                                        <cfset filter = filter & "(displayName=#form.q#*)">
                                        <cfset searchname = form.q>
                                    </cfif>
                                    <cfset filter = filter & ")">
                                    <!--- make the LDAP query --->
                                    <cfparam name="logindomain" default="amsa">
                                    <cfparam name="password" default="joe@6257$">
                                    <cfparam name="username" default="mquery">
                                    <cfparam name="ldapServer" default="adc2.amsa.com">
                                    <cfparam name="dcStart" default="ou=Users & Groups,dc=amsa,dc=com">
                                    <!---<cfldap action="QUERY" name="results" attributes="displayName,title,department,PhysicalOfficeDeliveryName,office,telephoneNumber,otherFacsimileTelephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">--->
                                    <cfldap action="QUERY" name="results" attributes="displayName,title,department,physicalDeliveryOfficeName,telephoneNumber,otherFacsimileTelephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">
                                    
                                    <cfquery dbtype="query" name="results2">
                                           SELECT DISTINCT department FROM results WHERE department != ''
                                    </cfquery>
                                    <cfquery dbtype="query" name="results3">
                                          SELECT * FROM results
                                          WHERE displayName LIKE '%#searchname#%'

                                    </cfquery>
                                    <cfif IsDefined("form.departments")>
                                    <cfquery dbtype="query" name="results4">
                                          
                                          SELECT * FROM results
                                          WHERE LOWER(displayName) LIKE LOWER('%#searchname#%')
                                          AND department Like '%#searchdept#%' Order BY department ASC
                                    </cfquery>
                                    </cfif>
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18573282
Check out this block of code...

 <cfparam name="searchname" default="form.q">
 <cfset searchname= replace(searchname,"'","''''")>

>>> I feel that at this point the value of searchname = "form.q" litterally
>>> try adding this and see what it is: <cfoutput>#searchname#</cfoutput>

 <cfif (IsDefined("form.departments") AND form.departments is not "")>
     <cfparam name="searchdept" default="form.departments">
     <cfset searchdept = form.departments>
 </cfif>

>>>  Here, you assign searchname to form.q again
>>>  But you didn't do the quote replace after this point

 <cfif (IsDefined("form.q") AND form.q is not "")>
     <cfset filter = filter & "(displayName=#form.q#*)">
     <cfset searchname = form.q>
 </cfif>

>>> try Moving this line to just before you need it in the query of query
>>> That way, you can modify or assign searchName wherever above and it
>>> will be applied right before the query.

 <cfset searchname= replace(searchname,"'","''''")>
 <cfquery dbtype="query" name="results2">
      SELECT * FROM results
      WHERE displayName LIKE '%#searchname#%'
 </cfquery>
0
 

Author Comment

by:asaworker
ID: 18573445
Here's the updated code:

<cfset sortby = form.q>
                              <cfif (IsDefined("form.q") AND form.q is not "")>
                                    <cfset filter = "(&(objectclass=user)">
                                    <cfparam name="searchname" default="#form.q#">
                                    <cfset filter = filter & "(displayName=#searchname#*)">
                                    <cfif (IsDefined("form.departments") AND form.departments is not "")>
                                    <cfparam name="searchdept" default="#form.departments#">
                                    <cfset searchdept = form.departments>
                                    </cfif>
                                    <cfset filter = filter & ")">
                                    <!--- make the LDAP query --->
                                    <cfparam name="logindomain" default="amsa">
                                    <cfparam name="password" default="joe@6257$">
                                    <cfparam name="username" default="mquery">
                                    <cfparam name="ldapServer" default="adc2.amsa.com">
                                    <cfparam name="dcStart" default="ou=Users & Groups,dc=amsa,dc=com">
                                    <!---<cfldap action="QUERY" name="results" attributes="displayName,title,department,PhysicalOfficeDeliveryName,office,telephoneNumber,otherFacsimileTelephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">--->
                                    <cfldap action="QUERY" name="results" attributes="displayName,title,department,physicalDeliveryOfficeName,telephoneNumber,otherFacsimileTelephoneNumber,mail" start="#dcStart#" scope="SUBTREE" sort="displayName ASC" server="#ldapServer#" username="#logindomain#\#username#" password="#password#" rebind="yes">
                                    
                                    <cfquery dbtype="query" name="results2">
                                           SELECT DISTINCT department FROM results WHERE department != ''
                                    </cfquery>
                                    <cfset searchname= replace(searchname,"'","''''")>
                                    <cfquery dbtype="query" name="results3">
                                          SELECT * FROM results
                                          WHERE displayName LIKE '%#searchname#%'

                                    </cfquery>
                                    <cfif IsDefined("form.departments")>
                                    <cfquery dbtype="query" name="results4">
                                          
                                          SELECT * FROM results
                                          WHERE LOWER(displayName) LIKE LOWER('%#searchname#%')
                                          AND department Like '%#searchdept#%' Order BY department ASC
                                    </cfquery>

I'm getting:
:: Phone Directory
0 matches found for o''''brien.


Enter a name to search in the database.

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 1500 total points
ID: 18573956

Hmmm...  i did a test in my database contact table (I actually have an O'Brien there too) and it worked for me.   Perhaps there's a small issue hiding..


Let's try this thing again..

I'd like to see what it looks like when its run.  You can find this if you have debugging turned on and scroll down the page to see all the queries.   Locate this query and cut and paste it in here.

 If you don't have debugging turned on, perhaps you can place just above the Results3  query and grab it from the screen when it displays in the browser...

<cfoutput>  <!--- note its CFOUTPUT instead of cfquery ---->
  SELECT *
  FROM results
  WHERE displayName like '%#preservesinglequotes(searchvars)#%'
</cfoutput>

0
 

Expert Comment

by:lanctr
ID: 23799870
I know this is an old question, but anyone who is searching for an answer to this should also note that using the cfqueryparam tag will escape single quotes for you and not add complexity.
<cfquery dbtype="query" name="result3">
SELECT *
FROM results
WHERE <cfqueryparam value="#searchvars#" cfsqltype="cf_sql_varchar">
</cfquery>

Open in new window

0
 

Expert Comment

by:lanctr
ID: 23799908
Sorry, that should have been:
<cfquery dbtype="query" name="result3">
SELECT *
FROM results
WHERE DisplayName LIKE <cfqueryparam value="%#searchvars#%" cfsqltype="cf_sql_varchar">
</cfquery>

Open in new window

0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question