• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Using PreserveSingleQuotes in a dynamic WHERE Clause

I have a dynamic query that works just fine if the user doesn't put any apostrophes in the input form fields.

I need to be able to allow apostrophe's in the form fields and have SQL Server process them as text instead of identifiying them as a delimiter that separates strings.

Can someone tell me how I can accomplish that?

The query is:
--------------------------------------
 <cfset WhereClause = " 1=1 "> 
   <cfif isDefined("FORM.txtUIDs") AND FORM.txtUIDs NEQ "">
       <cfset WhereClause = WhereClause & " AND a.UID IN ("&FORM.txtUIDs&")" >
   </cfif>
   <cfif isDefined("FORM.txtCenterName") AND FORM.txtCenterName NEQ "">
        <cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&FORM.txtCenterName&"%'" >
   </cfif>
   <cfif isDefined("FORM.txtStreetNum") AND FORM.txtStreetNum NEQ "">
       <cfset WhereClause = WhereClause & " AND a.BldgStNum = '"&FORM.txtStreetNum&"'" >
   </cfif>
  <cfif isDefined("FORM.txtStreetName") AND FORM.txtStreetName NEQ "">
      (((  a bunch more of these if statements...   you get the idea.  ( i hope) )))
   </cfif>

<CFQUERY NAME="GetRecords" DATASOURCE="#dsn#">
SELECT a.UID, a.SN, a.Sec, a.[ID], a.ID_numeric, a.Status, a.KM, a.BldgName,
     a.BldgStNum, a.BldgStName, a.BldgCity, a.BldgState, a.BldgZip,
     a.Contiguous, a.YrRenov, a.Census, a.Anchor, a.[Type], a.preferedListing,
     a.rate1, a.rate2, a.avgRent, a.parkingSpaces, a.commission, a.commissionPercent       
FROM RET_tblData a
LEFT JOIN RET_tblSector h ON a.Sec = h.Sector         
WHERE #PreserveSingleQuotes(WhereClause)#
</CFQUERY>

I cut a bunch of stuff out of the query for brevity but the concept is there.
The problem is with the field for "FORM.txtCenterName".  This is just a regular textbox and I must allow the use of single quotes. The sql statement is chocking on it when a single quote is encountered.

Any ideas on how I can fix this?

-Eddie


0
FastEddie___
Asked:
FastEddie___
  • 3
  • 2
1 Solution
 
js_vaughanCommented:
It seems like the problem here is that you are using PreserveSingleQuotes() in your WHERE clause and not around the FORM variables themselves.  The difference is that while you do what to preserve the single quotes that are coming from the textfield, SQL still needs to properly interpret the single quotes you are putting around the string itself.

So for example, change your code to look more like this:

<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&PreserveSingleQuotes(FORM.txtCenterName)&"%'" >

See if that helps.
0
 
FastEddie___Author Commented:
Thanks for the suggestion. I tried it but it did not work. I'm getting this DB error:

 Incorrect syntax near 's'.

The SQL Where cause that is being returned is this:

WHERE 1=1 AND a.BldgName LIKE 'Kathryn's%' order by a.preferedListing desc, a.[ID]

It's the single quote in the string "Kathryn's" that is causing the problem.

Got any other suggestions?
0
 
FastEddie___Author Commented:
JS, I don't know why preserveSingleQuotes didn't work in tat instance but your suggestion allowed me to come up with a solution.

I just replaced the single quote with two single quotes in the form field prior to setting the where clause.

<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '"&ReplaceNoCase(FORM.txtCenterName,"'","''","All")&"%'" >

That did the trick.

0
 
js_vaughanCommented:
Ok, I did some playing around and here is what I came up with

To keep you current code intact, the easiest way seems to escape the single quotes manually using a user-defined function like so:

<cffunction name="sqlEscape" returntype="string">
    <cfargument name="sql" required="yes">
    <cfset sql = Replace(sql,"'","''","ALL")> <!--- Double-Single-Double / Double-Single-Single-Double --->
    <cfreturn sql>
</cffunction>

<cfset WhereClause = WhereClause & " AND a.BldgName LIKE '%"&sqlEscape(FORM.txtCenterName)&"%'">

<cfquery ...>
    #preserveSingleQuotes(WhereClause)#
</cfquery>

That should get the job done, but what I normally do in my own code to solve this problem is to use <cfqueryparam> and the <cfif> tags inside the <cfquery> itself - like so:

<CFQUERY NAME="GetRecords" DATASOURCE="#dsn#">
SELECT a.UID, a.SN, a.Sec, a.[ID], a.ID_numeric, a.Status, a.KM, a.BldgName,
     a.BldgStNum, a.BldgStName, a.BldgCity, a.BldgState, a.BldgZip,
     a.Contiguous, a.YrRenov, a.Census, a.Anchor, a.[Type], a.preferedListing,
     a.rate1, a.rate2, a.avgRent, a.parkingSpaces, a.commission, a.commissionPercent        
FROM RET_tblData a
LEFT JOIN RET_tblSector h ON a.Sec = h.Sector        
WHERE 1=1
<cfif isDefined("FORM.txtCenterName") AND FORM.txtCenterName NEQ "">
    AND a.BldgName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(preserveSingleQuotes(FORM.txtCenterName))#%">
</CFQUERY>
</cfif>
0
 
FastEddie___Author Commented:
I tried using queryparam on the form field and the where clause, both tests failed.

Your function solution is great though. It'll let me do it with alot less typing.

Thank you so much for your help.

-Eddie
 
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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