asked on

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

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

egoselfaxis

ASKER

Could you please clarify what solution you're referring to?
Specifically how would I revise my UPDATE query?

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

macrem

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)#")>

_agx_

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

egoselfaxis

ASKER

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

kaufmed

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

_agx_

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.

kaufmed

@_agx_

What about hex-encoded single quotes?

_agx_

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

kaufmed

Cool. Thx for the info :)

_agx_

Anytime :)