Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-11-19
13
Medium Priority
?
699 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 75

Accepted Solution

by:
käµfm³d   👽 earned 668 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 53

Assisted Solution

by:_agx_
_agx_ earned 1332 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

Assisted Solution

by:_agx_
_agx_ earned 1332 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 53

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
 

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 75

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 53

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 75

Expert Comment

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

What about hex-encoded single quotes?
0
 
LVL 53

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 75

Expert Comment

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

Expert Comment

by:_agx_
ID: 34190925
Anytime :)

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

581 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