Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-19
13
Medium Priority
?
690 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 52

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 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
 

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 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 75

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 75

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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