Blog

I was working on a report today, and needed horizontal dotted lines between columns in each row, essentially joining the columns to make it easier to read across. There is no automated way to do this in the ColdFusion Report Builder, but Mr. CF Reports (aka Dean Harmon) sent me this brilliant hack ...

Change the field contents from:

Read More ›

I just spent some time helping a very irate customer. You know, the "ColdFusion sucks" "we're going to dump CF" "how can you sell this ****" brand of irate. The cause of all of this anger and frustration was very serious ColdFusion performance issues. And I am sure the individual has been under a lot of pressure and so the ramblings are somewhat understandable.

But, at the end of the day, as is so often the case, the bottleneck was in database access and query processing. No, there was nothing wrong with the DBMS. And no, there was nothing wrong with the SQL used. And no, the connection and data source settings were not the problem.

So what was the problem? Simply, ColdFusion was being made to do the DBMS' job! I found dozens of queries that were returning data that was being used solely to feed other queries. I saw queries being returned and then filtered client-side, within ColdFusion. I even saw queries (some that were quite long running) that were never being used once executed (leftover code from previous edits, I assume). And more.

I've been saying this for years, but, let's go through this again. ColdFusion is NOT a DBMS. Whatever database back-end you are using, chances are that it is a big powerful application that is designed to do one thing and do it well, manage and manipulate data. It makes absolutely no sense to give your DBMS less work to do while making ColdFusion work hard doing what the DBMS is supposed to do, what it is designed to do, what it is optimized to do.

So, a few rules:

• Don't ever retrieve data you don't need.
• Don't ever retrieve too much data, only to filter the results within ColdFusion (via querying a query, or excluding data while looping).
• Don't ever retrieve data if that data is only going to be used to drive additional queries. If you have a query, and then are using the results (perhaps in a <cfloop>, or via a ValueList()) in subsequent queries, then you need to find a way to consolidate those queries and do the work on the DBMS. And yes, that may mean writing JOINs or using stored procedures.
• Don't ever perform calculations and aggregations within ColdFusion, unless the data being used is already retrieved for some other purpose. Yes, the SQL aggregate functions (and having to use GROUP BY and figuring out how to make that work while using WHERE or JOINs) can be a little tricky. But do it anyway.
• Don't ever underestimate the important of caching query results. If you use the data frequently, and it is not changing as frequently as it is used, then cache it!
• There's more, but you get the idea.

Keep the following in mind:

• ColdFusion is never going to be able to query/sort/filter/count/sum/average/etc. as fast as your DBMS will. That is not what ColdFusion is optimized to do, and it is exactly what your DBMS is optimized to do.
• Sending unnecessary data from your DBMS to ColdFusion creates unnecessary network traffic, puts unnecessary load on the ColdFusion server hardware, unnecessarily increases server memory load, and unnecessarily slows down your and other requests.
• More often that not, your ColdFusion box is going to be under heavy load while your DBMS box sits with cycles to spare. It makes no sense to not leverage that power and potential.
• And as a side benefit, by moving data processing and manipulation back where it belongs, on the DBMS, you significantly increase the likelihood that you'll be able to reuse that work and effort.

And a couple of side notes:

• None of these comments are ColdFusion specific, and are just as applicable to applications developed in PHP, ASP, JSP, etc.
• You MUST learn how to use your DBMS. In the situation that triggered this post, one particularly problematic query was taking over 10 seconds to execute. Adding an index to the table used dropped that down to a couple of milliseconds!
• "My DBA does not let me write stored procedures" is not a valid argument, and is all the justification you need to get a new DBMS or a new DBA!

Considering how data centric ColdFusion applications tend to be, all of this is critical!

Bottom line, you have a DBMS for a reason, use it!

Read More ›

Mark Kruger posted comments about the performance of <CFDIRECTORY ACTION="list"> (see post 1 and post 2). He noted that returning full directory information with all file details (especially over network connections) can be painfully slow. As per his post, using Java APIs to return directory lists is far quicker, which is odd as <CFDIRECTORY ACTION="list"> actually uses the same Java APIs internally.

In a comment on Mark's blog, Tom Jordahl pointed out that the reason for this is that for <CFDIRECTORY> to return all file details it needs to first retrieve the file list and then perform a Java stat() for each file, and that stat() call is very time consuming (especially over network connections). The solution, if you just need file names and not other details, is to use the undocumented LISTINFO attribute. LISTINFO defaults to "all", but specify LISTINFO="name" and <CFDIRECTORY> will only return file names and will run just as quickly as the Java APIs it relies on.

Read More ›

DateDiff() is usually used to perform date difference calculations. But for simple difference calculations you can subtract dates from each other, like this:

The returned number will likely not be an integer, and will contain date fractions too, so you may want to use Int() to round the number to just the integer portion:

Read More ›

I was just on a web site (no, not a ColdFusion powered site, and no I will not name names) browsing for specific content. The URLs used typical name=value query string conventions, and so I changed the value to jump to the page I wanted. And I made a typo and added a character to the numeric value. The result? An invalid SQL error message.

That's bad. Very very bad. It means that I was able to create a SQL statement that was submitted to the database for processing, a SQL statement that was passed to the database as is, unchecked.

You'd think that by now we'd have learned to lock down our code so as to prevent SQL injection attacks, but apparently this is not the case. You do not know what a SQL injection attack is? Well, read on.

Consider the following simple dynamic ColdFusion query:

Scared? You should be. SQL statements are not just used for queries. They are also used by most DBMSs to create and drop tables, create user logins, change passwords, set security levels, manage scheduled events, even creating and dropping entire databases. And whatever features are supported by your DBMS may be accessible this way.

Before I go further I must point out that this is not a ColdFusion vulnerability at all. In fact, it is not even a bug or a hole. This is truly a feature – many DBMS do indeed allow queries to contain more than a single operation, this is legal and by design.

Of course, you should always be checking parameters anyway before passing them to your DBMS. Passing client supplied data (URL parameters, FORM fields, and even cookies) through unchecked is programmatic suicide. Attacks aside, it is flat out unsafe to ever assume that data submitted by a client can be used as is.

As such, you should already be using code like this:

Of course, you may want more control, in which case you could use code like this:

And as an additional line of defense you can use <cfqueryparam>, as seen here:

The bottom line is that SQL injection attacks have been around for as long as dynamic SQL itself. ColdFusion has made it incredibly easy to protect yourself against such attacks. Be it <cfparam> or <cfqueryparam> or your own conditional processing, it's simple to protect yourself, and your responsibility to do so.

If you have not been paying attention to this risk, stop whatever you are doing, fire up your IDE, and do a search for every single <cfquery> in your code. Then quickly scan to find any that contain #'s in them (that are not enclosed in quotes or passed to <cfqueryparam>), and make a list of the variables used. If any of them are URL parameters or FORM fields, create a <cfparam> for each (at the top of the page, or before the <cfquery>). It's that simple. Really. There is no legitimate reason not to protect yourself, so just do it. Now! And I mean right now, before you leave for the day or take off for the holidays, and despite whatever project you are working on or deadline you are up against. No excuses (and if your boss complains about you switching gears to take care of this one, send him my way!).

Enough said! (I hope).

(UPDATED 07/24/2008)

Since this post was made, SQL injection attacks have evolved, and it is now know that even strings are vulnerable. See the more current related posts linked below.

Read More ›

If you are trying to debug Flex ColdFusion connectivity issues, then be sure to keep in mind that if you start ColdFusion at the command prompt you'll see a detailed dump of the messages, both in and out. To do this (on Windows):

1. Make sure that ColdFusion is not running.
2. Open a command prompt window.
3. Go to c:\cfusionmx7\bin (or the appropriate folder in your own installation).
4. Run cfstart.

Read More ›

A user just e-mailed me with a problem. He had to process the results of an existing form which was made up of fields named with invalid characters (one was "first name"). Trying to access the field as #FORM.first name# won't work and will throw an error. So how to access these fields?

The answer is to remember that FORM is not just a prefix, it is also a structure. Structure members can be accessed as #structure.member# and as #structure["member"]#. And that latter format will allow access to badly named form fields (as the name is enclosed in quotes). So, the solution is to use #FORM["first name"]#.

Read More ›

Someone just e-mailed me to ask if there was a way to see a dump of all query contents when viewing ColdFusion debug output. And, actually, there is. Here's the scoop.

The ColdFusion debug output that we are all so familiar with is actually generated by a .cfm page. If you look in coldfusionroot/wwwroot/WEB-INF/debug you'll see several .cfm files. classic.cfm is the debug output that you usually see, dockable.cfm is the docked view debug output, and dreamweaver.cfm is used to generate debug output that Dreamweaver displays. And these files may be modified (well, don't mess with the Dreamweaver one), and you can create your own ones, too.

Actually, if you are going to tinker with these, then please make a copy and edit it. Then save it in the same folder, and you'll then be able to select it as the active debug template in CF Admin.

If you look at these files (and yes you can, they are not encoded) you will see that a big query named qEvents is retrieved from ColdFusion, this is all of the debugging data retrieved from the debugging service's event table. The rest of the templates simply process this data to display the debug output.

To add the query contents dump, find the section that begins with the comment <--- SQL Queries ---> (it'll be at about line 550 or so in CFMX7). That block of code starts with a <cfif> statement that checks to see if any queries were processed. If yes, it loops through each query displaying the SQL, number of rows returned, and more. There are two nested loops in this section, the outer loop loops through queries, and the inner loop loops query attributes for each query. Locate the </cfloop> for the outer loop, and add this code before it (after the </cfif> and before the </cfloop>:

Then save your new debug template, and activate it in CF Admin. Now the complete result set for any executed query will be included in the debug output.

Read More ›

CFEclipse 1.2 has been released. Details on the CFEclipse site.

Read More ›

ColdFusion MX 7 cumulative hot fix 3 has been released (for all editions and platforms), fixing about 20 issues and including one recent security update. ColdFusion MX 7 cumulative hot fixes are installed in the ColdFusion Administrator. Details are on the hot fix page.

Read More ›