Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

Coldfusion Search forms: how to disable case sensitivity

I'm using coldfusion to search and display data from an Oracle DB.

I've included the code snippet for my search form, its a basic text input search facility and it works pretty well for what I need.

However.....

Some of the fields in my Tables are upper case, and some are lower case and my search form is using a case sensitive approach to finding the data.

Is there a way to turn off case sensitivity?

I also need to display an error if the search returns no results.

thanks in advance.
<cfparam name="FORM.search_field" default="">
 
 
 
 
<cfquery name="Qsearch" datasource="wprop">
SELECT *
FROM contacts
WHERE FIRST_NAME LIKE '%#FORM.search_field#%' OR LAST_NAME LIKE '%#FORM.search_field#%' OR JOB_TITLE LIKE '%#FORM.search_field#%' OR DEPARTMENT LIKE '%#FORM.search_field#%'
ORDER BY LAST_NAME
</cfquery>
 
 
 
<!--  db search form -->
<cfform method="post" action="index.cfm">
<cfinput type="text" name="search_field" required="yes" message="Please enter a search" size="40">
<input name="action" id="action" type="submit">
</cfform>
 
.....
 
 
 
 
<cfif isDefined("action") is "true">
 
	<cfif trim(FORM.search_field) is "">
		<cfoutput>Please enter a search query</cfoutput>
        
        <cfelse>
        
        <cfloop query="Qsearch">
        <cfoutput>
        	<div><p>#Qsearch.LAST_NAME#, #Qsearch.FIRST_NAME#<br />
            #Qsearch.DEPARTMENT# - 
            #Qsearch.JOB_TITLE#</p></div>
         </cfoutput>
        </cfloop>
    </cfif>
 
</cfif>
 
.....

Open in new window

0
CurtinProp
Asked:
CurtinProp
  • 3
  • 2
2 Solutions
 
azadisaryevCommented:
one approach will be to convert all to one case. most DBs, and i am sure Oracle as well, have string manipulation functions like LOWER() and UPPER(), and CF has LCase() and UCase() functions. So you can do something like:

<cfquery name="Qsearch" datasource="wprop">
SELECT *
FROM contacts
WHERE LOWER(FIRST_NAME) LIKE '%#LCASE(trim(FORM.search_field))#%' OR ...
</cfquery>

check your db manual for proper function to use.
and please please please use <cfqueryparam> in your queries!!!

hth
0
 
azadisaryevCommented:
on the error subject, and a few other things:
a) scope your variables: <cfif isDefined("action") is "true"> should really be <cfif isDefined("FORM.action")>
b) i would move your query inside this cfif block - no need to run it if the form was not submitted
c) no need for <cfloop>+<cfoutput> - just use <cfoutput query="Qsearch">
d) enclose your query output into another cfif block checking if your search query returnd any records and show error if not:
<cfif Qsearch.recordcount><cfoutput query="Qsearch">...</cfoutput><cfelse>Sorry, no results were found to match your search criteria... Please try again.</cfif>
d) did i mention you should use <cfqueryparam> in your queries???

hth
0
 
CurtinPropAuthor Commented:
hi, thanks for the quick response,

Im just going to test out your solutions now, why and where shoudl I use cfquery param?
0
 
azadisaryevCommented:
if you want to protect yourself from SQL injection attacks and make your queries perform faster overall, use <cfqueryparam> on all values, user-submitted values especially. make it a rule that any value in your queries must be <cfqueryparam>'ed and you will not regret it.

usage is described in the CMFL reference manual. download free PDF from abobe.com if you do not have one.
0
 
CurtinPropAuthor Commented:
thanks for your help on this one, I've got a few more questions regarding cfquery param and the SQL for my search facility but I'll make them into a new question so you dont end up giving 12 solutions for nothing.

thanks again
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now