Daryl's ColdFusion Primer

	[<--Prev] [Next-->]
	[Printable Version] [Search: ] [Daryl's TCP/IP Primer] [Daryl's ColdFusion Blog]

ColdFusion Slow? Find out why with SeeFusion

5. Template Style and Structure (Paranoia 101)

Overview

Weinberg's Second Law: If builders built buildings the way programmers wrote programs, then the first woodpecker that came along would destroy civilization.
Daryl's Corollary [*cough*]: If any user anywhere can cause your page to present an error message you didn't write, you did something wrong.

Forms processing is one of the most understudied arts of ColdFusion programming. In the HTML world, you create a form with one or more input elements; the user puts random trash into the input elements (best case), and a second ColdFusion template tries to make sense of the user input and do something useful with it. Most production ColdFusion errors are generated by success-mode processing of form variables. [Success-mode: If all inputs are exactly what the programmer expected, the program works. Otherwise, it crashes. Antonyms: Failure-mode, Microsoft. The key to creating secure, reliable, bullet-proof ColdFusion code is to take a little bit of extra time at the top of any page that accepts user parameters and "scrub" all your parameter variables.

Elements of Style

The following are some procedures I use to keep myself and my programs sane. (Well, it works for my programs, anyway.) They may seem like unnecessary work, until you return to a page weeks or months later to do maintenance. These are rules of thumb, proven by their many exceptions. :-) As usual, your mileage may vary.

Always prefix browser-supplied data with "ff" (for Form Field.) This makes it easy to differentiate user-supplied data from "internal" variables. In particular, you can use "ff" user variables within a query loop without having to explicitly set scope (e.g. FORM.ffUserName), even if the user variable would otherwise have the same name as a table column in the query. (This also prevents certain browsers from misinterpreting variable names; for example, "powerConvert.cfm?voltage=120&amperage=5" might get munged into "powerConvert.cfm?voltage=120&erage=5" by some browsers that will find the "&amp" in the URL, and convert it to an ampersand, even though there is no trailing semicolon.)
Name form variables after the database fields they represent. If you have a database field called "FirstName" that you are allowing a user to edit, call the form field "ffFirstName".
Name queries after the table or specific type of data the query is retrieving, and prefix the query name with a "q". If you're writing a query to retrieve a list of users from a table called Users, name the query "qUsers". If you write a query to retrieve all users that are currently expired, consider calling the query "qExpiredUsers".
Abbreviate consistently, if at all! Don't name a table "Customers" and then name its primary key "CustID". I err on the side of verbosity-- I'll call the primary key "CustomerID", and name the table that joins the Customers table to the Downloads table, "Customers_Downloads". About the only abbreviation I use is "ID" for "Identifier". Oh-- and "ff" for "formfield". And "q" for "query". Well... just be consistent. ;-)
When writing SQL, I like to type SQL keywords in ALL CAPS, and type table and column names in mixed case. For example:
```
SELECT *
FROM Users
WHERE FirstName LIKE 'Dar%'
  AND (MentalStability < 30 OR MentalStability IS NULL)
```

Scrubbing Variables

"Scrubbing Variables" refers to the process of checking every variable that is passed as a parameter to your form, to make sure the data is what you expect. All parameters should be checked; if the value is not an expected value, cannot be "repaired," or should not be ignored (reset to a default value), then you should throw a useful (or at least semi-useful) error message and <CFABORT> processing. I use a short custom tag I call <CF_UserError>. Custom Tags are like procedures in other programming languages; they have their own local variable scope, and they accept parameters, but they don't directly return a value the way functions do. Tag attributes that are passed to a custom tag are defined in the "attributes." variable scope, and the caller's variables are available on a read/write basis through the "caller." variable scope. <CF_UserError> has one parameter, called Message, and is used in the form:

<CF_UserError Message="Oh no! I've fallen and I can't get up!">

Look for the Attributes.Message parameter in the following code snippet:

<CFSETTING EnableCFOutputOnly = "No">
<CFSETTING EnableCFOutputOnly = "No">
<!-- begin cf_usererror.cfm -->
<!--- 
Purpose: Crash and Burn.
Close up any outstanding tables (so Netscape will show the error), display error, then abort.

Required Parameters:
Message:  The message to display to the user.
--->
<CFPARAM Name="Attributes.Message" Default="An unspecified error has occurred.">
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</TD></TR></TABLE>
</CENTER>
<BLOCKQUOTE>
<i>There was a problem processing your request:</i><br>
<br>
<CFOUTPUT><b>#Attributes.Message#</b></CFOUTPUT><br>
<br>
Please press the 
<A HREF="javascript: history.back();">Back</A>
Button on your browser and double-check your input.
</BLOCKQUOTE>
<!-- end cf_usererror.cfm -->
<CFABORT>

[Click Here] to download this tag.

This custom tag is placed in my "CFUSION\Custom Tags" directory, where it is available to all applications on my server.

Use CFPARAM for ALL parameters passed to your page. If you use the Type attribute of CFPARAM, it's also a good idea to create a <CFERROR Type="Validation"> template.

There are four main types of data processed by most applications: Text, Numeric, Date, and Boolean (aka Yes/No). Each requires a different method of scrubbing.

Text Data

There is very little, in most cases, that can or should be done to text input. I usually use trim() to remove any whitespace on either side of the string: <CFPARAM Name="ffSomeText" Default="">

<CFSET ffSomeText = trim(ffSomeText)>

Note that there is a ~~bug~~ feature in some versions of the Microsoft Access ODBC driver that allows commands to be embedded inside string literals (i.e., text data) that run in the context of the current user, which, for most ColdFusion installations, is LocalSystem (See Allaire Security Bulletin ASB99-09 [http://www.allaire.com/security/].) This is a Very Bad Thing. If you MUST use ODBC drivers earlier than the drivers included in Microsoft's Data Access Components 2.1, then use this snippet:

<CFPARAM Name="ffSomeText" Default="">
<CFSET ffSomeText = replace(trim(ffSomeText),"|","","ALL")>

This will remove all vertical bars (aka "pipes") from submitted text.

Numeric Data

Numeric data should always, always, always be converted to a number before being used in a query. The ColdFusion val() function converts anything that looks like a number into a number; everything else is converted to 0 (zero.) If you don't val()idate numbers this way, malicious users can insert unwanted SQL into queries, which can have some catastrophically bad results. For example, if your query was:

SELECT * FROM Widgets
WHERE WidgetID = #ffWidgetID#

and someone passed the value "5 delete from widgets" as ffWidgetID, then database systems that allow multiple statements per query would dutifully delete everything from Widgets, since the statement would be expanded to read:

SELECT * FROM Widgets
WHERE WidgetID = 5 delete from widgets

If you pass ffWidgetID though a val() filter (e.g. <CFSET ffWidgetID = val(ffWidgetID)>) then the statement would be sent to the SQL server as:

SELECT * FROM Widgets
WHERE WidgetID = 0

which is harmless. See Allaire Security Bulletin ASB99-04 [http://www.allaire.com/security/] for more information.

So, we begin by using CFPARAM to ensure the variable is defined:

<CFPARAM Name="ffSomeNumber" Default="">

If your database allows nulls for the field, then use this to convert non-numerics to NULL:

<CFIF isNumeric(ffSomeNumber)>
  <CFSET ffSomeNumber=val(ffSomeNumber)>
<CFELSE>
  <CFSET ffSomeNumber="NULL">
</CFIF>

If your database disallows nulls for the field, then just simply use val():

<CFSET ffSomeNumber=val(ffSomeNumber)>

A database null is often confused with a null string. A null string is a string with a length of zero-- but it is a known value. For example, the second line of my address is a null string; we know what the value is, and the value is blank. A database null, what I like to call a "null value" (to differentiate it from a "null string") represents an unknown value. Databases work with nulls in interesting ways. For starters, nothing is equal to null-- and nothing is not equal to null, either. Any comparison to the null value is false, so SQL has a special set of keywords for dealing with nulls: IS NULL and IS NOT NULL. However, when a database returns a null value to ColdFusion, ColdFusion represents that value as a null string. So, if you want to find all people listed in a "users" table that have empty or missing middle names, the SQL would look like this:

SELECT * FROM Users
WHERE UserMiddleName = ''
   OR UserMiddleName IS NULL

Some DBMSes will convert the expression "WHERE foo = NULL" into "WHERE foo IS NULL", but don't expect that to work. There are many times that I've spent extended periods of time trying to figure out why a query that does "= NULL" fails, before I remember that I need to use "IS NULL" instead.

Another note of caution: I've noticed instances where some DBMSes will automatically convert null strings to null values on insert or update. I think the difference between a null string and a null value is subtle, but very important, and would personally recommend disabling that behavior, as a rule.

Date/Time Data

Some data is date only, some is date/time. I rarely see time-only fields, but I hear they're out there. In any case, first ensure the value is defined:

<CFPARAM Name="ffSomeDate" Default="">

If your database allows nulls for the field:

<CFIF (ffSomeDate IS "") OR (NOT IsDate(ffSomeDate))>
  <CFSET ffSomeDate="NULL">
<CFELSE>
  <CFSET ffSomeDate=CreateODBCDateTime(ffSomeDate)>
</CFIF>

If your database disallows nulls for the field:

<CFIF (ffSomeDate IS "") OR (NOT IsDate(ffSomeDate))>
  <CF_UserError Message="I'm sorry, but I don't recognize '#ffSomeDate#' as a valid date/time entry." &
    "  Try using a format like '1/31/2000 3:45 pm'.">
<CFELSE>
  <CFSET ffSomeDate=CreateODBCDateTime(ffSomeDate)>
</CFIF>

Now, many date fields are used as "Start Date--End Date" pairs. Users will enter an end date as "1/31/2000"; however, if the database fields you're comparing on is actually a date/time field, you will ignore all rows created after midnight of the end date..! If no time is specified for a date, ODBC databases will consider the date value to be date/time value of 12:00 am on that date. For end dates used as report criteria, consider using this snippet:

<CFIF (ffEndDate IS "") OR (NOT IsDate(ffEndDate))>
  <CFSET ffEndDate="NULL">
<CFELSEIF ffEndDate CONTAINS ":">
  <!--- if there's a colon, then the time was specified; use as is --->
  <CFSET ffEndDate=CreateODBCDateTime(ffEndDate)>
<CFELSE>
  <!--- no time specified, so fix datetime value to last second of day --->
  <CFSET ffEndDate=CreateODBCDateTime(DateFormat(ffEndDate) & " 23:59:59")>
</CFIF>

Yes/No or Boolean fields

"Yes/No" [MS Access] or "Bit" [MS SQL Server] fields are often presented to the user as an HTML checkbox. An interesting behavior of checkboxes is that they will POST the value given in the <INPUT Type="Checkbox"> tag when checked, and will leave the variable undefined if the checkbox is not checked. Therefore, the safest and most reliable simple checkbox handling I've found is this:

<CFPARAM Name="ffSomeCheckbox" Default="0">
<CFIF val(ffSomeCheckbox)>
  <CFSET ffSomeCheckbox = 1>
<CFELSE>
  <CFSET ffSomeCheckbox = 0>
</CFIF>

If you're using a text input and expecting "Yes" to indicate true, this will work:

<CFPARAM Name="ffSomeYesNo" Default="">
<CFIF "Yes" CONTAINS ffSomeYesNo>  <!--- accepts "Y" or "Yes" --->
  <CFSET ffSomeYesNo = 1>
<CFELSE>
  <CFSET ffSomeYesNo = 0>
</CFIF>

Aren't you just a bit paranoid?

No, but who told you so--and why??

Seriously, code resilience is the hallmark of the professional programmer. These techniques will help you build secure Web applications that don't fall down, no matter what is thrown at them--a feature your clients (whoever they are) will appreciate (and come back to appreciate again.) It's been said that a good programmer looks both ways before crossing a one-way street.

Template Structure

I find maintenance easiest when I structure templates in (usually) three sections: Variable Initialization, Query Section, and the Display Section:

Variable Initialization: Include a brief comment describing the page's function and indicating which other pages link to it, and scrub all your incoming variables. Commenting the purpose of non-obvious variables is also good style.
Query Section: Try to run all of your queries, and as much business logic as possible, in this section. This makes finding queries and business logic easy. Also, I include the Variable Initialization and Query Sections in <CFSETTING EnableCFOutputOnly="Yes"> to prevent a bunch of whitespace from appearing at the top of the source. If your business logic includes significant looping, this can be an big savings in generated HTML document size.
If I want certain parameter defaults to be defined by a query, I add a second Variable Initialization section here.
Display Section: This is where you display the results of your queries to the user. It is sometimes necessary to place queries in the Display Section, particularly when you need to run a second query for detail on each row a first query returned.

Example Template:

<CFINCLUDE Template="header.cfm">
<!-- begin UserSearchResults.cfm -->
<CFSETTING EnableCFOutputOnly="YES">
<!----------------------------------->
<!---   Variable Initialization   --->
<!----------------------------------->
<!--- This template will search for any users LIKE ffUserName --->
<!--- and list the results to the user, with links to UserDetail.cfm --->
<!--- Called by UserSearch.cfm --->
  
<CFPARAM Name="ffUserName" Default="">
<CFSET ffUserName = trim(ffUserName)>
<CFIF NOT len(ffUserName)>
<!--- ffUserName is a zero
  len()gth string; abort with message --->
  <CF_UserError Message="You must type some part of the name you're looking for.">
</CFIF>

<!-------------------------->
<!---    Query Section   --->
<!-------------------------->

<!--- use SQL "LIKE" operator to find any username that contains ffUserName --->
<!--- the percent sign (%) is the LIKE operator's "wildcard" (it matches any value) --->
<!--- note that this query would be quite slow on a large Users table --->
<CFQUERY Name="qUsers" DataSource="#DSN#">
  SELECT Users.*, Departments.DepartmentName
  FROM Users, Departments
  WHERE Users.DepartmentID = Departments.DepartmentID
    AND UserName LIKE '%#ffUsername#%'
  ORDER BY UserName
</CFQUERY>

<CFSETTING EnableCFOutputOnly="NO">
<!--------------------------->
<!---   Display Section   --->
<!--------------------------->

<H3>Results of User Search</H3>
<TABLE>
<TR>
  <TH bgColor="eeeeee" Colspan=2>
    <CFOUTPUT>#qUsers.RecordCount#</CFOUTPUT> Users Found:
  </TH>
</TR>
<TR>
  <TH bgColor="eeeeee">User</TH>
  <TH bgColor="eeeeee">Department</TH>
</TR>
<CFOUTPUT Query="qUsers">
<TR>
  <TD>
    <A HREF="UserDetail.cfm?ffUserID=#UserID#">#UserName#</A>
  </TD>
  <TD>#DepartmentName#</TD>
</TR>
</CFOUTPUT>
</TABLE>
<!-- end UserSearchResults.cfm -->
<CFINCLUDE Template="footer.cfm">

Next: Forms Processing