2

Possible Duplicate:
Coldfusion adding extra quotes when constructing database queries in strings

All,

I am trying to use a getter to reference a bean during an insert. CF is not escaping the single quote properly in the value in 'form.title' and therefore I am receiving a malformed sql error.

Any ideas?

Here's the code.

<cfscript>
form.title = "page's are awesome";

page = new model.page.page(argumentCollection = form);


<cfquery name="test" datasource="ksurvey">
insert into page(title)
values('#page.getTitle()#')
</cfquery>
Community
  • 1
  • 1
jason
  • 23
  • 1
  • 3

2 Answers2

15

If you're going to do it that way, you need preserveSingleQuotes()

INSERT INTO page( title ) VALUES ( '#preserveSingleQuotes( page.getTitle() )#' )

Of course, insert the standard caveat about how you should be using cfqueryparam to avoid SQL injection attacks.

INSERT INTO page( title ) VALUES ( <cfqueryparam value="#page.getTitle()#" cfsqltype="cf_sql_varchar" /> )

For reference:

charliegriefer
  • 3,342
  • 1
  • 18
  • 20
  • Hey Charlie, thanks much for the quick response. As it always seems to be, I figured this out shortly after posting. I was trying queryparam and preservesinglequotes and was refreshing my app, but no luck. Figured I was passing refresh instead of reload in the URL, doh! Marking your answer right though because it is in fact the answer to my question :) Need more coffee! - Thanks – jason Jul 20 '11 at 04:34
  • The site `cfquickdocks dot com` seems gone - look at https://cfdocs.org/preservesinglequotes – Bernhard Döbler Jul 19 '19 at 14:11
3

I wouldn't insert any value into a database without using cfqueryparam, its not safe! Not only that but cfqueryparam will handle all the escaping for you.

<cfquery name="test" datasource="ksurvey">
   insert into 
       page(title)
   values(<cfqueryparam value="#page.getTitle()#" cfsqltype="cf_sql_varchar">);
</cfquery>
bittersweetryan
  • 3,383
  • 5
  • 28
  • 42