Here...
<CFQUERY NAME="GetCenters" DATASOURCE="cfsnippets">
SELECT Name, Address1, Address2, City, Phone
FROM Centers
WHERE City IN (#PreserveSingleQuotes(List)#)
</CFQUERY>
You have to use PreserveSingleQuotes. Let me know PLEASE if this helped
anyone!
thanks,
Jake
----- Original Message -----
From: "Brian W. Zaleski, DC, MS" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, July 29, 2000 1:50 PM
Subject: Re: SQL and apostrophes
> Jake,
>
> I may be totally off base, but in ASP you do something like this:
>
> Function RequestDQ (VarName)
> RequestDQ = Replace (Request(VarName), "'", "''")
> End Function
>
> The SQL output that is returned to the page is normal (i.e. even though it
> puts in O''Rielly to the SQL Server when it is displayed to the page it is
> O'Rielly). It appears that for once MicroShaft got something right.
>
> Try the same thing in CF:
>
> <cfparam name="Form.search" default="">
> <cfparam name="varSQLSafeName" default="">
>
> <form action="test3.cfm" method="POST">
>
> <input type="Text" name="search">
> <input type="Submit" name="test">
>
> </form>
>
> <cfif Form.search NEQ "">
>
> <cfquery name="test_delete" datasource="#application.datasource#"
> dbtype="ODBC">
> DELETE FROM TEST WHERE JobID = 123
> </cfquery>
>
> <cfset varUnSafeAtAnySpeed = Replace("O'Reilly", "'", "''")>
>
> <!--- <cfquery name="test" datasource="#application.datasource#"
> dbtype="ODBC">
> <!--- This blows up SQL --->
> INSERT INTO TEST (Name, JobId) VALUES ('#varUnSafeAtAnySpeed#', 123)
> </cfquery> --->
>
> <cfoutput><br>varUnSafeAtAnySpeed=#varUnSafeAtAnySpeed#</cfoutput>
>
> <cfset iAphostropheLocation = FindOneOf("'", Form.search)>
> <cfif iAphostropheLocation GT 0>
> <cfset varSQLSafeName = Insert("'", Form.search, iAphostropheLocation)>
> </cfif>
>
> <cfquery name="test" datasource="#application.datasource#" dbtype="ODBC">
> <!--- This doesn't blow it up, but it prints out the wrong thing
> (O''Reilly) --->
> INSERT INTO TEST (Name, JobId) VALUES ('#varSQLSafeName#', 123)
> </cfquery>
>
> <cfoutput><br>varSQLSafeName=#varSQLSafeName#</cfoutput>
>
> <cfquery name="test" datasource="#application.datasource#" dbtype="ODBC">
> <!--- This query doesn't blow it up and prints out the right thing
> (O'Reilly Hard-Code) --->
> INSERT INTO TEST (Name, JobId) VALUES ('O''Reilly Hard-Code', 123)
> </cfquery>
>
> <cfquery name="test_results" datasource="#application.datasource#"
> dbtype="ODBC">
> SELECT * FROM TEST JobId WHERE JobID = 123 ORDER BY CreateDate
> </cfquery>
>
> <br>
> <cfoutput query="test_results"><br>Results= #Name#</cfoutput>
> </cfif>
>
> These are the results on the page:
>
> varUnSafeAtAnySpeed=O''Reilly
> varSQLSafeName=O''Reilly from form
>
> Results= O'Reilly Hard-Code
> Results= O''Reilly from form
>
> You don't run into these problems when you use cfstoredproc on SQL Server.
> Any other suggestions would be welcomed.
>
> Brian Zaleski
> [EMAIL PROTECTED]
>
>
> >I did just that. Now if the field has a double quote in it, it gets cut
off
> at that point (it looks like the end of the string). :-(
>
> >At 04:18 PM 7/28/00 -0400, Jake Hileman wrote:
> >Just put it between double quotes. Most datatypes use doublequotes
anyhow..
> >so it would be
> >last_name="O'Brien" and you should be okay.
> >
> >let me know if i'm retarded or not.. i probably am :-)
> >
> >jake
> >----- Original Message -----
> >From: "Peter Theobald" <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> >Sent: Friday, July 28, 2000 1:23 PM
> >Subject: Re: SQL and apostrophes
> >
> >> I'm having the same problem.
> >>
> >> At 04:03 PM 7/27/00 -0600, Nick Call wrote:
> >> >Any SQL Server 7 gurus out there that know how to allow apostrophe's
in
> >SQL
> >> >statements? For example, last_name = 'O'Brien' chokes the query.
> >> >
> >> >TIA.
> >> >Nick
>
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
