Solved

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

Posted on 2010-11-19
13
670 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:egoselfaxis
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 167 total points
ID: 34174929
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
 

Author Comment

by:egoselfaxis
ID: 34175256
Could you please clarify what solution you're referring to?  
Specifically how would I revise my UPDATE query?
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 333 total points
ID: 34176061
(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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 333 total points
ID: 34176221
>> <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
 

Expert Comment

by:macrem
ID: 34179439
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34180826
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:egoselfaxis
ID: 34188581
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 34188667
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34189487
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 34189533
@_agx_

What about hex-encoded single quotes?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34189578
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
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 34189611
Cool. Thx for the info  :)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34190925
Anytime :)

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now