Try using PreserveSingleQuotes() Jeff Garza Web Developer/Webmaster Spectrum Astro, Inc. 480.892.8200 [EMAIL PROTECTED] http://www.spectrumastro.com -----Original Message----- From: Gil Barden [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 17, 2001 10:10 AM To: CF-Talk Subject: escaping ' in SQL Hi everyone, I have a question about how to pass an SQL statement through a text area box in a form while protecting the SQL statement. Below I have a self-submitting form, that passes the dynamic text through the text-area box and then passes to a query that is to be then displayed. Problem is, that the SQL gets re-parsed when I use the " ' " single tick for a string to a " " " double tick. I have tried htmlcodeFormat() and htmleditformat() with no success. The error I recieve is: ++++++++++++++++++++++++++++++++++++++++ Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'n'. SQL = "select * from d_school_names where charter_school = ''n''" ++++++++++++++++++++++++++++++++++++++++ Here is my code: ++++++++++++++++++++++++++++++++++++++++ <html> <head> </head> <body bgcolor="#C0C0C0"> <cfif isdefined("form.submit")> <!--- Dynamice Query ---> <!--- <cftry> ---> <cfquery name="dynQuery" datasource="xxx" username="xxxx" password="xxxx"> #form.sql# </cfquery> <!--- Outputting query ---> <cfset columns = ListtoArray(#dynQuery.ColumnList#)> <cfset numColumns = arraylen(#columns#)> <table border="1" cellpadding="1" cellspacing="1"> <th>Current Row</th> <cfloop index="i" from="1" to="#numColumns#"> <cfoutput><th>#columns[i]#</th></cfoutput> </cfloop> <cfloop query="dynQuery"> <tr> <td> <cfoutput>#dynQuery.currentRow#</cfoutput> </td> <cfloop index="i" from="1" to="#numColumns#"> <td> <cfoutput> <cfif evaluate(columns[i]) EQ ""> <cfelse> #evaluate(columns[i])# </cfif> </cfoutput> </td> </cfloop> </tr> </cfloop> </table> <!--- <cfcatch type="Database"> You have caused a database error! Please hit your back button and try again. </cfcatch> <!--- Catching any type of error. ---> <cfcatch type="Any"> You have caused a database error! Please hit your back button and try again. </cfcatch> </cftry> ---> <!--- First time through form ---> <cfelse> <h3> Please Enter a SQL Statement below</h3> <form action="#cgi.script_name#?#cgi.Query_String#" method="post"> <textarea cols="50" rows="5" name="SQL"> </textarea> <br> <input type="Submit" name="submit"> </form> </cfif> </body> </html> ++++++++++++++++++++++++++++++++++++++++ Gil Barden ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
