CFQUERY Inconsistancies?

[fredp60157821]

In the example below, assume FORM.NAME has an embedded single quote.

Does someone have a moment to explain why

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
INSERT INTO TEST (NAME) VALUES ('#FORM.NAME#')
</CFQUERY>

works perfectly, but if I put the SQL in a variable it fails.  For example,

<CFSET lsSql = "INSERT INTO TEST (NAME) VALUES ('#FORM.NAME#')">

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
#lsSql#
</CFQUERY>

fails.  Any idea why?  Or, alternatively, how could I hcvae constructed lsSql so that it would work?

Thanks.

ps: And using #PreserveSingleQuotes(lsSql)# didn't help.

[George____]

First a warning,  you should never do this with FORM elements, and in general should never do it.   In your example you've made it super easy for someone to perform a SQL Injection attack using FORM.NAME.    It should be within a cfqueryparam and then the single quote won't matter.

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
     INSERT INTO TEST (NAME) VALUES ( <cfqueryparam values='#FORM.NAME#'> )
</CFQUERY>

PreserveSingleQuotes is how you do this, but you have to handle the single quote in the name yourself.   So if you output #PreserveSingleQuotes(lsSql)#  you'll probably see something like this:

INSERT INTO TEST (NAME) VALUES ('O'Niel')

That single quote in between the O and the N needs to be doubled to be proper SQL syntax.    Couple ways you could do it, but this should work:

<CFSET lsSql = "INSERT INTO TEST (NAME) VALUES ('" & Replace(FORM.NAME, "'", "''") & "')">
<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
#lsSql#
</CFQUERY>

[Dave Watts]

Amen to @George____ for giving this warning!

You might be able to avoid the quotes issue by using the CFSAVECONTENT tag.

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-r-s/cfsavecontent.html

Dave Watts, Eidolon LLC

[fredp60157821]

George, that was so nice to get your response.  In an effort to be succinct I left out a critical bit of information.  In my code I am trying to have just one CFQUERY and that CFQUERY is in a template file.  So I pass into the template file a variable, namely lsSql, which I want the template file to process.  [I think it would take us astray to get into why I want all database access to be performed inside one file, so for the moment let's leave that aside.]

So the template file has no concept of the contents of lsSql.  I do realize that as I create lsSql outside the template file I could use Coldfusion's Replace function to double up on every parameter that might have a double quote, but I have hundreds of SQL statements and it would be huge task to go back and redo all that code.  And, as I want to have all database activity take place in one template file, the use of CFQUERYPARM won't work.

I don't know why I didn't discover this problem earlier.  It looks like I am in a big mess now.  I suppose I could try to parse lsSql in my template file to see if single quotes need to be doubled up - but that's a non-trivial task.  Anyway it's my problem, but I still don't understand why if I have:

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
INSERT INTO TEST (NAME) VALUES ('#FORM.NAME#')
</CFQUERY>

it works fine, but if I put the INSERT INTO . . . into a variable and then call:

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
#lsSql#
</CFQUERY>

it fails.  It must be that Coldfusion is processing raw text like INSERT INTO TEST (NAME) VALUES ('#FORM.NAME#') differently than if the same text were put into a variable.  But that seems odd.

[Dave Watts]

I'm going to double down on my suggestion that this would be a really bad idea. I literally just went through someone else's site that did this same thing, fixing the results of an SQL injection attack. Your site will definitely be vulnerable to SQL injection if you do this. SQL injection is very easy to do and to automate. Preventing it is also very easy if you use CFQUERYPARAM. I really can't stress this enough.

http://web.archive.org/web/20061104102240/http://www.adobe.com/devnet/coldfusion/articles/cfquerypar...

That said, if you use CFSAVECONTENT instead of CFSET to create your variable, you will be able to do this.

Dave Watts, Eidolon LLC

[George____]

It must be that Coldfusion is processing raw text like INSERT INTO TEST (NAME) VALUES ('#FORM.NAME#') differently than if the same text were put into a variable.  But that seems odd.

It does.   When CF sends that query to to the server it will automatically double all the single quotes within the single quoted '#FORM.NAME#' to try and prevent SQL injection.   So O'Niel becomes O''Niel.  That's why if you're going to create the lsSQL variable you have to double them up yourself.

I don't see how you can handle the single quotes on your template page.   It'll receive ('O'Niel').  How is it going to know to only do the one between O and N.  I guess you could do some fancy regex replace where you don't double up (' and '), but I suspect you'll find all kind of cases where that doesn't work.   Plus, what happens if someone types something like ''); DELETE FROM TEST;

The only time you should use FORM elements in a dynamic SQL is when you've verified they match a predefined list of safe values.  There's not really a way to do that with names.

[fredp60157821 · Accepted solution]

I found a non-eloquent work around for my problem.  I wrote some regular expressions to search for occurences of

'#some variable#'

and to replace all such occurences with

'#DoubleUp(some variable)#'

So code like

<cfset p_lsSql = "INSERT INTO TEST (NAME,CITY,STREET) VALUES ('#FORM.NAME#','#FORM.CITY#','#FORM.STREET#')">

<cfinclude template="process_sql.cfm">

gets changed to

<cfset p_lsSql = "INSERT INTO TEST (NAME,CITY,STREET) VALUES ('#DoubleUp(FORM.NAME)#','#DoubleUp(FORM.CITY)#','#DoubleUp(FORM.STREET)#')">

<cfinclude template="process_sql.cfm">

I had hundreds and hundreds of places to do this and without the use of regular expressions I would have had a nightmare on my hand.  With regular expressions it was trivial. For those interested my regular expressions were:

Find: '#([A-Z._a-z0-9]+[A-Z._a-z0-9]*)#'

Replace with:'#DoubleUp(\1)#'

and inside template="process_sql.cfm I have

<CFQUERY . . . >

#PreserveSingleQuotes(p_lsSql )#

</CFQUERY>

I then just wrote the cutom function DoubleUp:

<cffunction name="DoubleUp" output="no" access="public" returnType="string">
<cfargument name="lsVarIn" type="string" required="true"/>
<cfreturn Replace(lsVarIn,"'","''","ALL") />
</cffunction>

So far I haven't come across any problems with this approach and I have been able to have all my SQL pass through template. (I wanted to do this for my own reasons.)

[George____]

Thanks for posting the solution you decided to go with.   The paranoid in me would be concerned that someone could do a SQL injection attack by double encoding, or some other creative method, but hopefully that's not the case.

[BKBK]

@fredp60157821 , to specifically answer the 2 questions you raised:

1. No, there is no inconsistency.
2. PreserveSingleQuotes helps.

You could fix your code as follows:

<!--- Convert every ' within form.name into '' --->
<cfset nameEscaped=replace(form.name,"'","''","all")>
<cfset lsSql="INSERT INTO TEST (NAME) VALUES ('" & nameEscaped & "')">

<CFQUERY NAME="A" DATASOURCE="MY_LOCALHOST">
   #preserveSingleQuotes(lsSql)#
</CFQUERY>