Eric, You might try using <cfqueryparam> inside your SQL statement. According to the Help Doc for it, one of the benefits is that it "Escapes string variables in single quotation marks." Macromedia suggests that you use cfqueryparam for a couple reasons. It should make your queries faster (by allowing SQL to do data binding, which allows SQL to cache the query), and will allow for data typing as well (which will keep malicious users from possibly executing SQL statements through form and url variables).
rob -----Original Message----- From: Davis, Eric [mailto:[EMAIL PROTECTED] Sent: Monday, December 29, 2003 11:37 AM To: [EMAIL PROTECTED] Subject: [CFCDev] Accessors, SQL, and quotes Surprisingly, I haven't yet had to deal with apostrophes in the string-typed members of my components; I'll need a pointer or two. Normally, as we all know, when CF encounters a string variable output between <cfquery> tags, it automagically escapes any single quotes inside the value. When accessing the value via a method, this is not the case: <cfcomponent> <cfset variables.noquote = "no quotes in here" /> <cfset variables.quoted = "check out 'these' quotes" /> <!--- imagine getters and setters for both of the above ---> <cffunction name="testQuotes"> <cfquery name="sqTest" datasource="myDSN"> INSERT INTO myTable (noquotes, quotes) VALUES ('#getNoQuotes()#', '#getQuotes()#') </cfquery> </cffunction> </cfcomponent> Yields, in the SQL (according to the debug output and the error received): INSERT INTO myTable(noquotes, quotes) VALUES ('no quotes in here', 'check out 'these' quotes') In cases of nested collections -- getDiners()[i].getName() , where name yields "Joe's Restaurant" -- I can't just change the access to my internal variable (not that I want to). Will I have to edit all the SQL output of every accessor to escape the quotes or is there a better way you folks can think of to prevent these errors? TIA, ecd. -- Eric C. Davis Programmer/Analyst I Georgia Department of Transportation Office of I.T. Applications Web Applications Group 404.463.2860.199 [EMAIL PROTECTED] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
