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.
