Need to update coldfusion UPDATE query to not choke on apostrophe characters

I need to update a coldfusion UPDATE query to so that it's forgiving of apostrophe characters:

I suspect that it's the culprit because I'm getting the following error message:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 's'.

My code is below.  Please advise:

Thanks!
- Yvan


<cfif isDefined('FORM.PROCESS')>

	<cfloop index="ID" list="#FORM.listofids#" delimiters=",">
	
	      <cfquery datasource="#application.datasource#">
	            UPDATE Books 
				SET customizing_options = '#Evaluate("FORM.customizing_options_#ID#")#'
	            WHERE ID = '#ID#'
	      </cfquery>
		  
	</cfloop>

</cfif> 
    
    
    
    <cfquery datasource="#application.datasource#" name="get_books">
	  SELECT * FROM Books WHERE customizing_options LIKE '%11.5%' ORDER BY ID DESC
	</cfquery>

    
    <form name="editForm" id="editForm" method="post" action="update.cfm">
    
    <input type="hidden" name="PROCESS" value="TRUE" />
    
     <div style="position:fixed;text-align:right;margin-left:900px;background-color:white;padding:20px;border:1px double black;">  
    
        <cfoutput>	
        
        <span style="padding:2px;background-color:yellow;font-weight:bold;">#get_books.recordcount#</span> books left<br />
        
        </cfoutput>
      
        <input type="submit" value="UPDATE" style="background-color:green;border:1px solid black;padding:5px;color:white;font-weight:bold;" /> 
        
    </div>      
    
    <cfoutput query="get_books">
	
		<p><strong>###ID# - #title#</strong></p>
        
        <cfset customizing_options2 = Replace(customizing_options, "11.5", "10.5", "ALL")>
    
    	<textarea name="customizing_options_#ID#" cols="75" rows="10">#customizing_options2#</textarea><br /><br />
	
	</cfoutput>
    
    
     <cfoutput>
     <input type="hidden" name="listofids" value="#ValueList(get_books.ID)#">
	 </cfoutput> 
    
    </form>

Open in new window

egoselfaxisAsked:
Who is Participating?
 
käµfm³d 👽Commented:
You should have a look at the following with regards to sanitizing data. String concatenation of SQL queries is a prime attack point for your site. The following should also rectify your apostrophe issue.

    http://kb2.adobe.com/cps/300/300b670e.html
0
 
egoselfaxisAuthor Commented:
Could you please clarify what solution you're referring to?  
Specifically how would I revise my UPDATE query?
0
 
_agx_Commented:
(no points ...)

If you read the article kaufmed mentioned it explains you should always use cfqueryparam in your queries.  So instead of using the raw FORM values in your SQL

UPDATE Books 
SET customizing_options = '#Evaluate("FORM.customizing_options_#ID#")#'
WHERE ID = '#ID#'

Open in new window


... you should wrap both variables in cfqueryparam. This prevents them from being executed as a command which could possibly damage your database.   In addition to the "value" you also need to supply the correct cfsqltype.  They generally match the data type of your table column, but see the documentation for a complete matrix

<!---- Note, I'm assuming #ID# is a form variable --->
UPDATE Books 
SET customizing_options = 
<cfqueryparam value="#FORM['customizing_options_'& FORM.ID]#" cfsqltype="cf_sql_varchar">
WHERE ID = <cfqueryparam value="#FORM.ID#" cfsqltype="cf_sql_varchar">

Open in new window


Notice, you don't need to use Evaluate().  It's simpler, and slightly more efficient, to use associative array notation for dynamic field names. And always scope your variables.

  ie   #FORM["fieldName"& dynamicID]#

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
_agx_Commented:
>> <cfloop index="ID" list="#FORM.listofids#" delimiters=",">

Just noticed #ID# is not a FORM variable, it's in the VARIABLES scope.  So any references to #FORM.ID# should be #VARIABLES.ID# instead.

UPDATE Books 
SET customizing_options = <cfqueryparam value="#FORM['customizing_options_'& VARIABLES.ID]#" cfsqltype="cf_sql_varchar">
WHERE ID = <cfqueryparam value="#VARIABLES.ID#" cfsqltype="cf_sql_varchar">

Open in new window


>> I suspect that it's the culprit because I'm getting the following error message

BTW: Quotes aren't normally a problem. It could be the use of Evaluate().  If so, that's another reason to avoid it and always use associative array notation instead ;-)

0
 
macremCommented:
Sanitize all strings going to your query. look to replace key SQL directives and special characters.
A good character to replace single quote is ascii 96
<cfset string=replace(string,"'","#chr(96)#")>
0
 
_agx_Commented:
Yeah, having two layers are safer. You should *always* use cfqueryparam for basic protection. But you should still sanitize strings to guard against other attacks like xss, etc...
0
 
egoselfaxisAuthor Commented:
Thanks guys!  Turns out that it was the Evaluate function that was tripping things up with regards to the apostrophes.  I switched to using the cfqueryparam parameters -- and ditched the evaluate function in favor of the associate arrays and all worked as expected.

Thanks!
- Yvan
0
 
käµfm³d 👽Commented:
Here's a crude example of what we're talking about.

Let's say this is the query you have for a "forgot my password" link on your site (pseudocode):

    "SELECT password FROM tblUsers WHERE emailAddr = '" + formValue + "' AND isAdmin = 'false'"

Now, let's say you have an input field that takes in the email address to send the login info to. Let's also say you reuse that same value (i.e. you don't use what's stored in the DB) as the address to send to and that you receive the following as input:

    malicious@example.com' OR 1=1 --

What query would you expect the DB to execute???

Here is a breakdown of what would happen:

The malicious@example.com' closes off the opening quote in your query which is intended to surround a string value. The OR 1=1 will evaluate true FOR EVERY ROW in your DB, so all rows will be returned. The -- makes everything after it a comment, so your "isAdmin = 'false'" never even gets seen by the DB. Effectively, what your DB sees is:

    SELECT password FROM tblUsers WHERE emailAddr = 'malicious@example.com' OR 1=1

and what effectively happens is that you email every password in your DB to malicious@example.com.

This is an intentionally simple example, but hopefully you see why the need for data sanitizing is so important  :)
0
 
_agx_Commented:
With modern versions of CF, most string fields are safe because it automatically escapes any single quotes it finds.  So something like this

<cfset FORM.email = "  malicious@example.com' OR 1=1 --  ">
<cfquery ...>
SELECT password FROM tblUsers WHERE emailAddr = '#FORM.email#'
</cfquery>

Is converted to a safe string by CF:
SELECT password FROM tblUsers WHERE emailAddr = 'malicious@example.com'' OR 1=1'

My single quotes may be slightly off, but the idea is CF forces the malicious value to be treated as a plain string.  So string fields are safe unless you use PreserveSingleQuotes(). In which case all bets are off.  I rarely use Evaluate(), so I'm not sure how it's handled.

Numeric fields do still have the problem mentioned. So you have to use val() or cfqueryparam to make them safe.
0
 
käµfm³d 👽Commented:
@_agx_

What about hex-encoded single quotes?
0
 
_agx_Commented:
I'd have to check, but I doubt it touches those.  IIRC, it shouldn't be a problem unless it's executed like a dynamic sql string.  In that case, nothing will save you in any language ;-)
0
 
käµfm³d 👽Commented:
Cool. Thx for the info  :)
0
 
_agx_Commented:
Anytime :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.