#PreserveSingleQuotes(string)#
----- Original Message -----
From: "Gil Barden" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, April 17, 2001 12:09 PM
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
