Forta.com - Blog

I just received an e-mail from someone asking me to help him convince his coworkers to never use <CFINSERT> and <CFUPDATE>. Well, he asked the wrong person for help. I have long been a fan of those tags, I encourage their use when appropriate, and would welcome additional related tags. Here is the reply I sent him:

Sorry, I can't help you fight this one because I am siding with your coworkers. Lots of ColdFusion developers (particularly the very experienced long time CFers) have an almost violent reaction to the very mention of <CFINSERT> and <CFUPDATE>. I am not one of them. In fact, I even use these tags myself on occasion, there, I said it.

<CFINSERT> and <CFUPDATE> are designed to do one thing and one thing only, they are designed to make creating and updating database table rows using form field values absolutely brain-dead simple. That's it.

If you need to do all sorts of processing to the data before the database call then you can't use <CFINSERT> and <CFUPDATE>. If you are using CFCs as a database abstraction layer then you can't (and wouldn't want to) use <CFINSERT> and <CFUPDATE>. If you have relational tables and need better control over what rows are inserted where and access to generated primary keys then you may not be able to use <CFINSERT> and <CFUPDATE>. And that's fine. If you need any of those things then don't use <CFINSERT> and <CFUPDATE>.

But there are lots of very simple ColdFusion applications out there. Some are created by absolute beginners who are having to learn CFML, HTML, some JavaScript, and some SQL all at once - and they can (and should) use whatever shortcuts they can use to get the job done. There are also quick and dirty apps thrown together by all sorts of developers, and if <CFINSERT> and <CFUPDATE> can make life simpler, then why not!?

Here are some facts to keep in mind. Fact: Most ColdFusion developers have never used a CFC (even those using CFMX or CFMX7). Fact: Most ColdFusion developers struggle with SQL statements. Fact: Most ColdFusion developers have no idea what a methodology or framework is. Fact: Most ColdFusion developers rely on <CFINPUT> to generate JavaScript form field validation. Fact: Most ColdFusion developers use less than 10 CFML tags on a regular basis. Fact: ColdFusion makes lots of entry-to-mid-level developers productive and successful. ... The fact of the matter is that the very technical crowd that congregates on blogs and discussion lists is not a fair representation of most ColdFusion developers.

And what's more, <CFINSERT> and <CFUPDATE> actually help avoid common pitfalls and problems. Do variables need single quotes around them or not? Not an issue. Dates need to be handled specially? Nope. The dangers of a malformed WHERE clause (too many beginners have mistakenly typed WHERE id=id or WHERE #id#=#id# when they meant WHERE id=#id#) are diminished. SQL injection risks? Not an issue. These are real benefits not to be discounted.

<CFINSERT> and <CFUPDATE> are not suitable for all applications. And many ColdFusion developers start off using <CFINSERT> and <CFUPDATE> and then later learn the benefits of using <CFQUERY>. That's how most of us learned ColdFusion - starting simply and adding language elements and functionality as needed and warranted.

The bottom line is that there is nothing inherently wrong with <CFINSERT> and <CFUPDATE>. These tags have limitations, true, and so when you run into those limitations stop using the tags. ColdFusion is all about productivity. And if <CFINSERT> and <CFUPDATE> make you productive, then use them.

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

Comments

[Add Comment] [Subscribe to Comments]

I for one see your points Ben, and unless I missed this one in your post, I have one other reason that may be relevant - when you have over 300 fields of data (don't ask why so many :-), cfinsert saves quite a bit of typing. That said, the first time I saw this tag in use on some legacy code I inherited (the author had chosen not to use the formfields attribute of the tag), I saw that it was submitting all form fields (including buttons) to the database, and that bothers my sensibility. :-))

# Posted By Mike Givens | 10/3/06 8:13 PM

And that is why I never ask Ben for help... j/k! I guess using cfinsert and cfupdate is as appropriate as having a big switch case for actions on the top of every 1000-lines page. Some people like it, some people don't.

# Posted By Rob Gonda | 10/3/06 8:20 PM

I'm with Ben on this one. I'm somewhere between the "developers who only use 10 tags" and the hard core guru's who have frameworks coming out of their ears. I think you have to use some common sense. I wouldn't build our public facing web sites without a fairly well thought out structure but then again, we bang out small data capture apps using these tags all the time. Not really much point in spending weeks architecting them when they're life span is a couple of weeks. Pick the right tool for the job, or more colourfully, don't use a sledgehammer to crack a nut, just because its the tool you happen to have in your hand.

Don't criticise these small scale simple apps, businesses love them, they're fast, simple and easy. As long as the app is tidy and reasonably efficient given the effort required to get it running, what's the problem?

I'm sure there's a quote about making things as simple as possible but no simpler.

# Posted By Steve Powell | 10/4/06 3:53 AM

I think one of the reasons for the impopularity of cfinsert and cfupdate is that they actually do a lot without giving any insight into what exactly. In CF 5, just create a table with 2 unique indexes in Access and cfupdate would fail. Why? Because behind the screens cfupdate first has to figure out what the primary key is and then do the update. Try with composite primary keys and see what happens. (I would presume / hope this works better with CF MX and later because discovery of the schema can be done through Java methods.)

If you want to take away some of the aversion to cfinsert and update, give us some insight into what they really do behind the screens. For instance tell us if they properly use bind variables in recent versions. Tell us how they discover the primary key of a table.

# Posted By Jochem van Dieten | 10/4/06 4:23 AM

The chief difference between Ben and myself here is I see the dumbing down (or keeping them dumb) of low-end CF developers as a *bad* thing for everyone concerned.

I'd not advocate removing things like <cfinsert> and <cfupdate> from the language for exactly the reasons Ben cites. However that's not to say they should be encouraged.

I think the reason why there is a demographic of CF developer out there that struggles with such simple SQL as INSERT and UPDATE queries is down to <cfinsert> and <cfupdate>. Adobe (and its evangelists) should not encourage people to stay dumb. They should encourage them to improve.

Having - and encouraging - this demographic is counter-productive for CF, as it perpetuates (and provides statisicaly evidence for) the idea that CF is for gibbons, rather than "serious programmers" (whatever that means). There's no problem being a gibbon to start with (everyone needs to start somewhere), but it's bad to positively affirm the notion that it's OK to *stay* being a gibbon, rather than evolving.

--
Adam

# Posted By Adam Cameron | 10/4/06 5:13 AM

Adam ,

I used to share your thoughts as well. People jump on the CF wagon because it's simple and easy to pickup as a first language. But I came to realize while meeting different CFers that not everyone cares to progress any. They need an application that takes this, puts it there, and returns something else, and that's final. They don't have the need, or desire for that reason, to learn select/insert/update/delete, and that may be why they came to CF.

For some, I think if they wanted to spend the time learning more advanced (although sometimes easier) ways of doing things, they may use more easily accessable solutions, such as PHP. While SQL and CFQUERY seem like simple concepts to us, they're probably not for the guy wanting to update a phone book entry on his Corporate Intranet.

Chris.

# Posted By Chris | 10/4/06 10:56 AM

I dunno guys,

Seems to me that if you are going to be an effective devloper you should at least try to have a passing familiarity with the tools that are a requirement of your trade. Regardless if whether you're a CF developer or not, if you're programming for the web, SQL is a must. it doesn't take a rocket scientist to learn to type "insert into table(col1, col2) values (val1, val2)".

I think the value of being good with some flavour of SQL should be stressed a lot more then it is.

# Posted By Joe | 10/4/06 12:27 PM

"Work smarter, not harder"

When all I need to do is move data from a form to a db, I still use <cfinsert> and <cfupdate>. In more complicated applications I use the <cfquery> tag. Simple as that. The existence of (and my use of) <cfinsert> and <cfupdate> didn't "encourage me to stay dumb". They are useful tags that serve a purpose.

# Posted By Jen | 10/4/06 12:53 PM

The biggest problem with CFINSERT is that its simplicity - how do I tell it to use a scope other than Form?

For example, I've got an insertAddress function. Essentially, all it does is validate the arguments and pass them into the database. Rather than being able to use CFINSERT, I'm using the third-party DataMgr component:
<cfset Result.DataMgr = This.DataMgr.insertRecord
(TableName='address', Data=Arguments, OnExists='error')/>

It's a pity I can't just use core functionality to do something similar to:
<cfinsert table="address" data="arguments" onexists="error" result="result.cfinsert"/>

# Posted By Peter Boughton | 10/5/06 7:02 AM

Peter, sounds like a great custom tag ;-)

I agree with Ben and the others who are commenting on the utility of the simple tools when they fit the bill, and I don't see that as *necessarily* keeping CF coders dumb. In addition to my usual programming responsibilities, I also have the pleasure of introducing CF to people who are just getting their feet wet in server side scripting. Especially in a university environment, there is a lot of inertia behind PHP and Python; I can't tell you how eyes light up when I show such people that a simple inquiry form (e.g., send-me-more-info) can be handled with just a few lines of code!

These are smart people; inevitably they start to ask, "But what if...?" And then I show them CFQuery and CFQueryparam, CFStoreproc/param, etc. Sure, there's a built-in calculator in every OS I know of....I'd still recommend the $4.95 handheld from WalMart for balancing a checkbook, though.

/ejt

# Posted By Edward T. | 10/5/06 9:45 AM

Ben,

Thanks as always for an incisive post. Since little of my CF work for the past 8 years has been simple, CFINSERT / CFUPDATE have really never been options for me. Your list of CF developer facts scares me, though.

I guess if it is truly true that "Most ColdFusion developers have never used a CFC (even those using CFMX or CFMX7)." that "Most ColdFusion developers struggle with SQL statements." that "Most ColdFusion developers have no idea what a methodology or framework is." that "Most ColdFusion developers rely on <CFINPUT> to generate JavaScript form field validation." and that "Most ColdFusion developers use less than 10 CFML tags on a regular basis.", then maybe I begin to see why it can be so difficult to convince aherents to other languages that CF can be used as robustly as most.

Makes me sad, I guess, when I know the incredible complexity of development available to us with so much ease in CF.

-J-

# Posted By Jason | 10/5/06 12:39 PM

I agree with Jason. cfinsert and cfupdate are good for simple things. However, past experience teached me that a simple insert or update is never used in our application. But then again, its a developers choice to use or not use these features. And that alone is a plus
Geert

# Posted By Geert | 10/10/06 6:55 AM

You gave a CRUD. The CRUD updates a look up table. For the purpose fo example, let's say you have a table that has category types for products. The number of columns is less than 10. What real business logic exists when modifying a look up table? cfinsert and cfupdate are perfect for this.

Now, let's throw them to the wolves and introduce them to a framework. What is a popular concept about SQL? Let's abstract the hell out of it with an ORM Adapter and not ever write SQL unless I really have to have something custom?

There is a time and place for everything. Ben's facts don't surprise me. I am not saying that we have unintelligent CF'ers, but it takes a certain type of person to want to use different tags, functions and abstractors. These people are not typical nor average and they enjoy being so. These people are also the most celebrated most of the time because of their desire to share their discoveries in ways to make things easier.

Take it easy,
Teddy

# Posted By Teddy R. Payne | 10/11/06 12:13 PM

The last time I checked, CFINSERT and CFUPDATE did not generate injection-proof SQL. If that is still the case, you really shouldn't be recommending them to anyone, for any reason.

Dave Watts, CTO, Fig Leaf Software

# Posted By Dave Watts | 10/12/06 3:33 PM

I use cfupdate and cfinsert very sparingly but I do have one case in particular that they are valuable to me.

We have some software that is versioned and I have one interface to support all versions. to make a long story short, we have a config table that based on the version has different columns. Instead of having to have x number of edit and processing pages, i am able to have one processing page that cfupdates whatever was passed to it and the posting page knows what each version has (as far as columns go).

My point is there are situations where developers like myself (an experienced cf delevelop, including CFCs) can find cases where these tags are helpful.

# Posted By Jim Schwiegeraht | 12/15/06 11:50 AM

- "SQL injection risks? Not an issue."
- "CFINSERT and CFUPDATE did not generate injection-proof SQL"

Hi Ben,

Is there more info on this cfinsert/cfupdate sql-injection protection?

Thanks!,
-Aaron

# Posted By Aaron Neff | 7/31/08 8:20 PM

I'm having trouble with hackers using SQL injections on my site. I'm using coldfusion and I've used CFPARAM's on all variables. Also,r anytime data is fed from a form into the database, the processing page uses statements like <cfqueryparam value="#GetItem.pubid#" cfsqltype="CF_SQL_INTEGER"> in my CFQUERYs. I've swept the whole site and used these types of statements, yet they're still getting through. There are a few times I use CFINSERT which of course doesn't have a spot to process a cfqueryparam. I thought perhaps they were getting in through there, but your post says they can't do sql injections when you're using CFINSERT so that rules that out. I'm stumped. Where else could they be getting through? What am I missing?

They hack my site like clockwork every Friday night between 6pm - 11pm and they always insert the same malicious javascript into the same fields of the same 4 tables. Any ideas? Thanks!

# Posted By Marnie Pehrson | 8/16/08 10:56 AM

When this post was made, close to two years ago, the primary concern with SQL injections involved numeric fields which were most vulnerable to this type of attack. And CFINSERT and CFUPDATE, which perform validation on the passed values, do indeed help, as they'd not allow text to be passed through if the table wanted a numeric value. But, more recent SQL injection attacks have proven that even string data type fields are vulnerable, and as CFINSERT and CFUPDATE do not parametrize queries the way CFQUERY/CFQUERYPARAM do, CFINSERT and CFUPDATE are not going to protect adequately. I have added an update note to the post.

--- Ben

# Posted By Ben Forta | 8/19/08 10:27 AM

Can someone please provide example for CFUPDATE and CFINSERT?

# Posted By Rohit Lokmanwar | 8/25/09 6:27 AM

Hey Ben, sorry for digging up an old post, but I am wondering if you could shed more light on this subject? I found a blog that insisted that if you used the "formfields" CF insert/update was not vulnerable. I can't find any info or confirmation on this issue from Adobe.

ALSO, does CF9 have this same issue with this CF insert/update vulnerability? If so, then WHY?

Thanks!

# Posted By Mark W. Breneman | 10/12/09 5:26 PM

[Add Comment]

Use <CFINSERT> And <CFUPDATE>