Bonnie wrote:
>
>you need to escape the apostrophes with
>
><cfset vexperience=Replace(#strEXPERIENCE#, "'", "''", "ALL")>
>
>then insert the new field into the query string. I've been fighting this
>problem for 6 months and this is the solution that seems to work best.
>Preservesinglequotes doesn't work in this instance.
>
>Hope this solves your problem
>
>Bonnie
>AGS
Bonnie,
This depends on your database. This technique doesn't work with SQL server
7. My suggestion is to
use a stored procedure with cfstoredproc. It takes care of the problem.
There is a definate bug in the way that CF handles apostrophes.
For example, in VBScript, if you want to insert data into SQL 7 that has an
apostrophe
in it, you simply change the variable to:
varName = Replace(varname, "'", "''")
The first apostrophe escapes the second one and it is entered correctly into
the database.
Try it in CF and it blows up.
<cfset varUnSafeAtAnySpeed = Replace("O'Reilly", "'", "''")>
<cfoutput><br>varUnSafeAtAnySpeed=#varUnSafeAtAnySpeed#</cfoutput>
<cfquery name="test" datasource="#application.datasource#" dbtype="ODBC">
<!--- this blows up SQL --->
INSERT INTO TEST (Name, Id) VALUES ('#varUnSafeAtAnySpeed#', 123)
</cfquery>
Ok...so let's try another tact. We'll look to see if there is an apostrophe,
then insert one after it.
<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, Id) VALUES ('#varSQLSafeName#', 123)
</cfquery>
This doesn't blow it up, but it prints out wrong.
Ok, let's hard code it into the query and see what happens.
<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, Id) VALUES ('O''Reilly Hard-Code', 123)
</cfquery>
<cfquery name="test_results" datasource="#application.datasource#"
dbtype="ODBC">
SELECT * FROM TEST Id WHERE Id = 123 ORDER BY CreateDate
</cfquery>
This finally enters the correct data into the database and it displays
correctly.
But is it a bug in CF?
Cut and paste the following into a new template and test it out for
yourself:
<cfsetting enablecfoutputonly="Yes">
<!---
***************************************************
'Brian W. Zaleski - 415.265-2717 [EMAIL PROTECTED]
'Created: 7/31/00
'Purpose: Test out the bug in ColdFusion
'Required attributes:
'Optional attributes:
'Related files:
'
'Modified:
'
'
***************************************************
--->
<cfparam name="attributes.SQL_type" default="">
<cfparam name="attributes.table_name" default="Test">
<cfparam name="attributes.update_string" default="insert">
<!--- build the SQL statment --->
<cfif isdefined('form.fieldnames')>
<!--- Start the statements --->
<cfswitch expression="#attributes.SQL_type#">
<cfcase value="space">
<cfset attributes.update_string = """ VALUES (""">
<CFSET attributes.note = " Note: Leading space
exists">
<CFSET ValuesFormSQL = " VALUES (">
</cfcase>
<cfcase value="spice">
<cfset attributes.update_string = """VALUES (""">
<CFSET attributes.note = " Note: No leading space">
<CFSET ValuesFormSQL = "VALUES (">
</cfcase>
</cfswitch>
<!--- Loop throught the form elements --->
<cfloop index="form_element" list="#form.fieldnames#">
<CFIF ((Right(form.fieldnames, 2) NEQ ".x") AND
(Right(form.fieldnames, 2) NEQ ".y"))>
<!--- Eliminate input type=image tags from the SQL --->
<CFIF IsNumeric(form.fieldnames)>
<!--- Build the query --->
<CFSET ValuesFormSQL = ValuesFormSQL &
evaluate("form." & form_element) & ", ">
<CFELSE>
<CFSET ValuesFormSQL = ValuesFormSQL & "'" &
evaluate("form." & form_element) & "', ">
</cfif>
</cfif>
</cfloop>
<!--- figger out how long the statements are --->
<cfset iValuesFormSQLLength = (Len(Trim(ValuesFormSQL)) - 1)>
<cfset BeforeMidValuesFormSQL = ValuesFormSQL>
<!--- remove the trailing commas --->
<CFSET ValuesFormSQL = Mid(ValuesFormSQL, 1, iValuesFormSQLLength)>
</cfif>
<cfsetting enablecfoutputonly="NO">
<BODY>
<br>Use this form to demonstrate the bug with the apostrophe's in CF
<br>I found it while building a custom tag that makes insert queries.
<br>I made a similar function in ASP this weekend and copied it to CF.
<br>That's why I identified the bug. Thank Microshaft.....
<br>
<br>1) Start by building the two strings that you need for an update
statement
<br>2) Grab the values from the form and get the form elements
<br>3) Set the update statement
<br>4) Update sting:
<b><CFOUTPUT>#attributes.update_string#</b> #attributes.note#</cf
output>
<br>5) Normally what we would do is to loop through the elements and add
them to a list.
<br>However, since I first wrote this in ASP I didn't have that luxury. I
did it the ASP way.
<br>6) You figger out how long the string is, then cut off the last comma.
<br>7) Length of the trimmed string:
<b><CFOUTPUT>#iValuesFormSQLLength#</cfoutput></b>
<br>8) String before cutting off the last comma:
<b><CFOUTPUT>#BeforeMidValuesFormSQL#</cfoutput></b>
<br>9) String after cutting off the last comma:
<b><CFOUTPUT>#ValuesFormSQL#</cfoutput></b>
<form action="test_form_input.cfm" method="post" name="form1" id="form1">
<input type="text" name="test1">
<input type="text" name="test2">
<SELECT name="SQL_type">
<option value="space">Insert - With space</option>
<option value="spice">Insert - No Space</option>
</select>
<input type="submit" value="Test">
</form>
</body>
Brian W. Zaleski, DC, MS
Media Application Developer
roundpeg
www.roundpeg.com
1700 California Suite 260
San Francisco, CA. 94109
Ph. 415.437.3900
Fax 415.437.3904
Cell 415.265.2717
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
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.
