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:

To (yep, append lots of .'s with spaces between each). The spaces are key, the reporting engine tries to fit in as many whole words as it can, and without the spaces it'll try to embed all of the .'s or none at all. As long as the Stretch With Overflow property for the field is set to FALSE, the reporting engine will display the column values followed by as many .'s as can fit. Nice!

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!

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.

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:

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:

Here a WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and is often used in data drill-down interfaces. If the URL was: the resulting SQL statement would be: But what if someone tampered with that URL so that it read: Now the resulting SQL would be: And depending on the DBMS being used, you could end up executing two statements – first the SELECT, and then DELETE Customers (which would promptly delete all data from the Customers table).

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:

This single line of code will lock SQL injection attacks out. How? Think about it, SQL injection (within ColdFusion apps) is really only an issue with non textual fields. If a text value is tampered with you'll end up with tampered text, but that text will all be part of the core string (within quotes) passed as a value, and will therefore not be executed as separate statements. Numbers, on the other hand, are not enclosed within quotes, and so extraneous text can be tampered with to create an additional SQL statement. And <cfparam> can protect you.

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:

If the previous tampered URL was passed to the this query, the value would be rejected and an error would be thrown. The CFSQLTYPE (aside from binding variables) performs data type validation checks, and values that do not match the type are rejected. That's it, only integers allowed, and malicious tampered URL parameters are not integers.

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.

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.

This one has helped me solve all sorts of problems, from pathing issues to case-sensitivity issues to figuring out exactly what data is being passed as and 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"]#.

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.

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

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.

Dean Harmon, the man responsible for reporting in CFMX7, is online and blogging at cfreport.org.

First Mike Nimer and his cfform.com, and now Prayank Swaroop creates Looking for help with CFCHART?, a blog dedicated to ColdFusion's <cfchart> tag. What better way to get the inside scoop than from the engineers who build the features!

A ColdFusion user e-mailed me with a problem; he needed to add an XML DOM into another XML DOM, essentially making the first DOM a child node of the first one. Simple assignments returned the following error:

WRONG_DOCUMENT_ERR: A node is used in a different document than the one that created it.

The problem is that ColdFusion will not allow a DOM to be directly assigned to another DOM. The solution is to simply add the desired nodes and elements one by one using the XMLElemNew() function.

When images are associated with database records, those images are usually stored on disk. The images are related to their respective records by storing their names in a table or perhaps by using the record's identifier as part of the file name. The advantages of storing images as files are performance (no database access needed to retrieve the file, and likely nothing but straight HTTP access), and flexibility.

But sometimes you may indeed need to store images within database tables (perhaps as blobs). Storing the images is easy enough, serving them dynamically is a little more complex. You can't simply use the column name in the IMG SRC attribute as you would static image files, although ColdFusion would indeed embed the image data within the tag, the browser would not display the image as intended.

When browsers render pages containing images they do so by making multiple requests, first for the page itself, and then for each image, making an HTTP request to obtain the URL named in the IMG SRC tag. But that URL need not be an actual file, it can also be a dynamic URL that returns an image.

Therefore, you can create a .cfm file that does nothing more than serve images. The code would work like this:

This file would be saved on the server, and the URL to it (with the image identifier passed as a URL parameter) can now be used as an IMG SRC like this:

And that'll do it.

Several of you wrote to tell me that several Breezos originally created for ColdFusion MX were no longer available, it seems that these got lost during Breeze server migration.

I've reposted them, and here are the new links:

• Java and ColdFusion MX - The Marriage Of Power And Productivity
• When One ColdFusion Is Not Enough - Understanding Multiple Instances
• CFMX6.1 - Introducing A Better CFMAIL

A colleague just forwarded me a link to a page describing WebAssist Database Search, a Dreamweaver extension which generates rather impressive looking search and drill-down interface code against databases of your choice. Code is generated for ColdFusion, ASP, and PHP (as the code supports CF5 or later I am assuming that it does not generate CFCs which is a bit of a bummer). I've not played with this one myself yet, but considering just how many of the apps I run into that are database search front-ends, this one could be well worth $150.

I am working on a series of short Breeze (and Captivate) based tutorials on the new ColdFusion Report Builder. The first on Using Input Parameters is now online. Next one will be on charting. If you have suggestions for future topics, let me know.

A CFC that accepts text input and returns text responses can easily be made accessible via Telnet. This is what you'll need to do:

1) Make a copy of /cfusionmx7/gateway/config/socket.cfg (name it telnet.cfg perhaps), and then edit the copy and change the port: port=23.

2) Create a new gateway instance of type "TCP/IP Socket", give it a unique name, point it at the telnet.cfg config file, and at your CFC file.

3) Start the new gateway instance.

4) Use a Telnet client to connect to your ColdFusion server (specify DNS name or IP address).

That should do it.

Verity Search Packs for ColdFusion are now available for ColdFusion (MX7 and MX) Standard. Verity Search Packs enable ColdFusion MX 7 and ColdFusion MX applications to index and search European and Asian languages.

A developer wrote to ask for my help in using to allow for file selection so that a user could pick a data file on the server. now supports recursion (as of CFMX7), so getting the files is easy. Getting them into is a bit trickier because the returned query cannot be passed to as is ( needs a flat query, not hierarchical data).

The solution is actually quite simple, you need to add all of the relevant directories before you add the files with their paths, and then can do the rest. Here is a full working example of this in case anyone else finds it useful, the bulk of the work is the dirquery2treequery() function which does exactly what its name suggests:

Hot Fix 2 for ColdFusion MX 7 is available for download, details at http://www.macromedia.com/go/fd71533. This Hot Fix is cumulative (it includes Hot Fix 1).

The slides that I used in my "ColdFusion Security Essentials" presentation last month (down in Rolins, GA) are now online at http://www.forta.com/cf/resources/CFMX7_Security_Essentials.swf.

I mentioned this in a comment on a prior post, but it is important enough to warrant its own mention. If you are using Flash Remoting to access ColdFusion code, you may run into an issue in which ColdFusion could construct an invalid URL when appending a jsessionid. If this occurs the web server via which you are accessing your code could throw errors. The solution is simply, just add a ? To the end of the gateway URL, change /flashservices/gateway to /flashservices/gateway?, doing so ensures that the URL will be created correctly. This issue was discovered by our tech support folks, and I believe they'll be posting a TechNote on it shortly.

I just spent a long time troubleshooting a Flex ColdFusion connectivity issue, and now that we've figured out what was going on I am sharing this in case it bites anyone else.

There are two basic ways for Flex to invoke back-end ColdFusion CFCs, SOAP (Web Services) or AMF (Flash Remoting). My app had been using SOAP, but I needed to convert to AMF (the performance difference is significant).

Last year Brandon Purcell wrote an excellent blog entry entitled Switching between webservices and Flash Remoting using Flex, and so I used his steps to make the switch. As he notes in that entry, ColdFusion variables returned via SOAP have their names converted to uppercase, whereas variables returned via AMF retain their original case, and so any binding code (or Flex code referencing returned data) needs to be converted from all caps to actual case.

The converted code worked perfectly for CFC methods returning queries, they ran exactly as they did before, just a whole lot quicker. But my CFC methods returning structures all failed, none of my bindings and client-side code worked.

The problem? Waldo Smeets figured this one out, unlike returned queries, returned structure members are all caps, even when using AMF. This is horribly inconsistent, and hopefully it'll be fixed in the future. For now, keep this in mind.

The Flash Forms calendar controls can restrict selectable date ranges. Fixes dates can be specified using the startRange and endRange attributes, but what if you need to programmatically change the ranges at runtime? The following example does just that, it uses two calendar controls to prompt for start and end dates, and uses a couple of lines of ActionScript to ensure that the end date cannot be before the start date.

A live example can be viewed at http://www.forta.com/misc/flashforms/calendarrange.cfm.