Chapter 10. Creating Data-Driven Pages

Accessing Databases

In the past few chapters, you created and executed ColdFusion templates. You worked with different variable types, conditional processing, code reuse, and more.

But this chapter is where it starts to get really interesting. Now it’s time to learn how to connect to databases to create complete dynamic and data-driven pages.

For your first application, you will create a page that lists all movies in the Films table.

Static Web Pages

Before you create your first data-driven ColdFusion template, let’s look at how not to create this page.

Listing 10.1 contains the HTML code for the movie list Web page. The HTML code is relatively simple; it contains header information and then a list of movies, one per line, separated by line breaks (the HTML <br> tag).

Listing 10.1 movies.htm—HTML Code for Movie List

Figure 10.1 shows the output this code listing generates.

Figure 10.1 You can create the movie list page as a static HTML file.

Dynamic Web Pages

Why is a static HTML file not the way to create the Web page? What would you have to do when a new movie is created, or when a movie is dropped? What would you do if a movie title or tag line changed?

You could directly modify the HTML code to reflect these changes, but you already have all this information in a database. Why would you want to have to enter it all again? You’d run the risk of making mistakes—information being misspelled, entries out of order, and possibly missing movies altogether. As the number of movies in the list grows, so will the potential for errors. In addition, visitors will be looking at inaccurate information during the period between updating the table and updating the Web page.

A much easier and more reliable solution is to have the Web page display the contents of your Films table. This way, any table changes are immediately available to all viewers. The Web page would be dynamically built based on the contents of the Films table.

To create your first data-driven ColdFusion template, enter the code as it appears in Listing 10.2 and save it in the 10 directory as movies1.cfm. (Don’t worry if the ColdFusion code doesn’t make much sense yet; I will explain it in detail in just a moment.)

Listing 10.2 movies1.cfm—The Basic Movie List

Now run this page in ColdFusion Builder, or run it in your browser as

http://localhost:8500/ows/10/movies1.cfm

The results will be identical to Figure 10.1.

Understanding Data-Driven Templates

As you can see, there is no difference at all (other than the file extension in the URL) between the static page and the dynamic page. If you look at the HTML source just generated, you’ll see that aside from a lot of extra white space, the dynamically generated code is exactly the same as the static code you entered in Listing 10.1 and nothing like the (much shorter) dynamic code you entered in Listing 10.2.

How did the code in Listing 10.2 become the HTML source code that generated Figure 10.1? Let’s review the code listing carefully.

The <cfquery> Tag

Listing 10.2 starts off with a comment block (as should all the code you write). Then comes a ColdFusion tag called <cfquery>, which submits a SQL statement to a specified data source. The SQL statement is usually a SQL SELECT statement, but it could also be an INSERT, an UPDATE, a DELETE, a stored procedure call, or any other SQL statement.

→ See Chapter 6, “Introducing SQL,” for an overview of data sources, SQL, and SQL statements.

The <cfquery> tag has several attributes, or parameters, that are passed to it when used. The <cfquery> in Listing 10.2 uses only two attributes:

• name: This attribute is used to name the query and any returned data.

• datasource: This attribute contains the name of the data source to be used.

The query name you specified is movies. This name will be used later when you process the results generated by the query.

You specified ows for the datasource attribute, which is the name of the data source created earlier. The datasource attribute is required; without it, ColdFusion would not know which database to execute the SQL statement against.

The SQL statement to be executed is specified between the <cfquery> and </cfquery> tags. The following SQL statement was used, which retrieves all movie titles sorted alphabetically:

SELECT MovieTitle
FROM Films
ORDER BY MovieTitle

ColdFusion pays no attention to the actual text between the <cfquery> and </cfquery> tags (unless you include CFML tags or functions, which we’ll get to later in this chapter). Whatever is between those tags is sent to the data source for processing.

When ColdFusion encounters a <cfquery> tag, it creates a query request and submits it to the specified data source. The results, if any, are stored in a temporary buffer and are identified by the name specified in the name attribute. All this happens before ColdFusion processes the next line in the template.

It’s important to note that, at this point, no data has been displayed. <cfquery> retrieves data from a database table, but it doesn’t display that data. Actually, it does nothing at all with the data—that’s your job. All it does is execute a specified SQL statement when the </cfquery> tag is reached. <cfquery> has no impact on generated content at all, and retrieved data is never sent to the client (unless you send it).

The next lines in the template are standard HTML tags, headers, title, and headings. Because these aren’t ColdFusion tags, they are sent to the Web server and then on to the client browser.

Using <cfoutput> to Display <cfquery> Data

Next, the query results are displayed, one row per line. To loop through the query results, the <cfoutput> tag is used.

<cfoutput> is the same ColdFusion output tag you used earlier (in Chapter 8, “The Basics of CFML”). This time, however, you use it to create a code block that is used to output the results of a <cfquery>. For ColdFusion to know which query results to output, the query name is passed to <cfoutput> in the query attribute. The name provided is the same that was assigned to the <cfquery> tag’s name attribute. In this case, the name is movies.

The code between <cfoutput query="movies"> and </cfoutput> is the output code block. Cold-Fusion uses this code once for every row retrieved. Because 23 rows are currently in the Films table, the <cfoutput> code is looped through 23 times. And any HTML or CFML tags within that block are repeated as well—once for each row.

Using Table Columns

As explained in Chapter 8, ColdFusion uses # to delimit expressions and variables. ColdFusion expressions also can be columns retrieved by a <cfquery>. Whatever column name is specified is used; ColdFusion replaces the column name with the column’s actual value. When ColdFusion processed the output block, it replaced #MovieTitle# with the contents of the MovieTitle column that was retrieved in the movies query. Each time the output code block is used, that row’s MovieTitle value is inserted into the HTML code.

ColdFusion-generated content can be treated as any other content in an HTML document; any of the HTML formatting tags can be applied to them. In this example, the query results must be separated by a line break (the <br> tag).

Look at the following line of code:

#MovieTitle#<br>

That first row retrieved is movie Being Unbearably Light, so when processing the first row the above code will generate the following:

Being Unbearably Light<br>

The output of Listing 10.2 is dynamically generated—each time the page is refreshed, the database query is executed and the output is generated.

The Dynamic Advantage

To see the real power of data-driven pages, take a look at Listing 10.3. This is the same code as in Listing 10.2, but a column has been added to the SQL statement (retrieving PitchText as well now) and the output has been modified so that it displays both the MovieTitle and PitchText columns. Save this file as movies2.cfm (you can edit movies1.cfm and use the Save As option (in the File menu) to save it as movies2.cfm, if you find that easier). Now run the page in ColdFusion Builder or run it in your browser as follows:

http://localhost:8500/ows/10/movies2.cfm

Listing 10.3 movies2.cfm—The Extended Movie List

Figure 10.2 shows the output generated by the revised code.

Figure 10.2 Data-driven pages are easy to modify because only the template needs changing, not every single row.

As you can see, two table columns are now used, each delimited by number signs. The MovieTitle is displayed in bold (using <strong> and </strong> tags) and is followed by a line break; on the next line PitchText is displayed followed by a paragraph break. So for the first row displayed, the previous code becomes

<p><strong>#MovieTitle#</strong><br>
#PitchText#</p>

Compare that to what you’d have had to change in movies.htm to update a static page to look like Figure 10.2, and you’ll start to appreciate the dynamic page advantage.

Excited? You should be. Welcome to ColdFusion and the wonderful world of dynamic data-driven Web pages!

Displaying Database Query Results

Listings 10.2 and 10.3 displayed data in simple line-by-line outputs. But that’s not all you can do with ColdFusion—in fact, there is no type of output that can’t be generated with it. ColdFusion has absolutely nothing to do with formatting and generating output; as long as you can write what you want (in HTML, JavaScript, Flash, DHTML, Ajax, or any other client technology), Cold-Fusion generates the output dynamically.

To better understand this, let’s look at some alternative output options.

Displaying Data Using Lists

HTML features support for two list types—ordered lists (in which each list item is automatically numbered) and unordered lists (in which list items are preceded by bullets). Creating HTML lists is very simple:

1. Start the list with <ul> (for an unordered list) or <ol> (for an ordered list).

2. End the list with a matching end tag (</ul> or </ol>).

3. Between the list’s start and end tags, specify the list members (called list items) between <li> and </li> tags.

For example, the following is a simple bulleted (unordered) list containing three names:

The numbered (ordered) equivalent of this list would be:

So how would you display the movie list in an unordered list? Listing 10.4 contains the code, which you should save as movies3.cfm. Execute the code in your browser (or in ColdFusion Builder, if you prefer); the output should look like Figure 10.3.

Listing 10.4 movies3.cfm—The Movie List in an Unordered List

Figure 10.3 HTML unordered lists provide a simple way to display data-driven output.

Let’s review Listing 10.4 together. It should look familiar because it’s essentially the same code as Listing 10.3 (movies2.cfm), only the actual data output has changed. The new output code is:

As you can see, the list is started before the <cfoutput> tag, and it’s ended after the </cfoutput> tag. This is important—everything within the output block is repeated once for every row retrieved. Therefore, if the list was started inside the output block, 23 lists would be generated, with each containing a single movie, instead of a single list containing 23 movies. Only the data to be repeated should be placed inside the output block.

The output code itself is simple. For the first row, the code

<li><strong>#MovieTitle#</strong> - #PitchText#</li>

becomes

<li><strong>Being Unbearably Light</strong>
 - Love, betrayal, and battling eating disorders</li>

which is a valid list item with the movie title in bold (using <strong> and </strong>) followed by the tag line.

Displaying Data Using Tables

Another important way to display data is using tables. HTML tables enable you to create grids that can contain text, graphics, and more. Tables help facilitate a more controlled page layout, helping you place content side by side, in columns, wrapped around images, and more.

Creating tables involves three sets of tags:

• <table> and </table>: Used to create the table

• <tr> and </tr>: Used to create rows in the table

• <td> and </td>: Used to insert cells within a table row (<th> and </th> also can be used for header cells—essentially data cells formatted a little differently, usually centered and in bold)

So a simple table with a header row, two columns, and three rows of data (as seen in Figure 10.4) might look like this:

Figure 10.4 HTML tables are constructed using tags to create the table, rows, and individual cells.

With that brief intro to HTML tables, let’s modify the movie listing to display the list in an HTML table. Listing 10.5 contains a modified version of the code (again, you can use Save As to create a copy of the previous version for editing). Save the file as movies4.cfm, and then execute it to display an output similar to that shown in Figure 10.5.

Listing 10.5 movies4.cfm—The Movie List in an HTML Table

Figure 10.5 Tables provide a convenient mechanism for displaying data in a grid-like format.

Once again, the code in Listing 10.5 is similar to the previous examples, and once again, it’s only the output block that has changed.

The table is created using the code <table border="1">—a table with a border. The <table> and </table> tags are placed outside the output block (you want a single table, not a table for each row).

The table needs a new table row for each row in the query. So the <tr> and </tr> tags are within the output loop, and within them are two cells (containing MovieTitle and PitchText).

As you can see in Figure 10.5, this code creates a single table with as many rows as there are query rows (23 in this example).

You’ll probably find yourself using tables extensively. To ensure that dynamic HTML table creation is properly understood, another example is in order.

This time the table will contain two rows for each query row. The first will contain two cells—one for the title and tag line and one for the release date. The second row will contain the movie summary (and because the summary can be lengthy, its cell spans both columns). The output generated can be seen in Figure 10.6.

Figure 10.6 For greater control, HTML tables can contain cells that span two or more columns (and rows).

Listing 10.6 contains the revised code; this time save the file as movies5.cfm and execute it in your browser.

Listing 10.6 movies5.cfm—The Movie List in an HTML Table

A few changes have been made in Listing 10.6. First, the <cfquery> SELECT statement has been modified to retrieve two additional columns—Summary contains the movie summary, and DateInTheaters contains the movie’s public release date.

In addition, the following HTML code has been added before the <cfoutput> tag:

This creates a header cell (header contents usually are centered and displayed in bold) containing the text Movie List as a table title. Because the table is two columns wide, the title must span both columns, so the optional attribute colspan="2" is specified.

The output block itself creates two rows (two sets of <tr> and </tr> tags) per movie. The first contains two cells—one with the MovieTitle and PitchText (with a line break between them) and the other with the release date formatted for display using the DateFormat() function. The second row contains a single cell spanning both columns and displaying Summary.

→ The DateFormat() function was introduced in Chapter 8.

As seen in Figure 10.6, the table row containing the title and tag line has a colored background. To set the background color of a table row (or a specific table cell, or even the entire table for that matter) the bgcolor attribute is used, and the color is specified using known named (like red and green) or RGB values in hexadecimal notation as follows:

<tr bgcolor="#cccccc">

Hexadecimal values are preceded by a #, the same character used to delimit ColdFusion expressions. If the above code were used in our <cfoutput> block, ColdFusion would have generated an error message complaining about a missing closing # (it would think that cccccc was an expression needing a closing #). As such, our table code escapes the # as follows:

<tr bgcolor="##cccccc">

→ Escaping # was covered in Chapter 8.

As you can see, as long as you know the basic HTML syntax and know what needs to be repeated for each database row and what doesn’t, creating dynamic data-driven output is quick and painless.

Using Result Variables

So far, you have displayed data retrieved using database queries. But sometimes you’ll need access to data about queries (and not just data within queries). For example, if you wanted to display the number of movies retrieved, where would you get that count from?

To simplify this type of operation, ColdFusion can return special variables with every query using the optional RESULT structure. Table 10.1 lists these variables, and as you can see, RecordCount can provide the number of rows retrieved.

Table 10.1 Query RESULT Variables

To demonstrate using these special variables, create the file movies6.cfm, as shown in Listing 10.7. This code, which is based on movies5.cfm, generates the output seen in Figure 10.7. Save the code, and execute it in your browser.

Listing 10.7 movies6.cfm—Using Query Variables

Figure 10.7 RecordCount can be accessed to obtain the number of rows in a query.

So what changed here? Only three modifications were made to this code. First, the <cfquery> RESULT attribute was specified so that, when processed, the structure named result would be created containing the query execution results. In addition, the title (above the output block) now reads as follows:

Movie List (#result.RecordCount# movies)

#result.RecordCount# returns the number of rows retrieved—in this case, 23. Like any other expression, the text result.RecordCount must be enclosed within number signs and must be between <cfoutput> and </cfoutput> tags. But unlike many other expressions, here the prefix result is required. Why? Because this code isn’t referring to a column in a named query. Rather, RecordCount is a member of a structure named result, and so the fully qualified variable name must be specified.

Here you use RecordCount purely for display purposes. But as you will see later in this chapter, it can be used in other ways, too (for example, checking to see whether a query returned any data at all).

The other line of code that changed is the movie title display, which now has #CurrentRow#: in front of it. CurrentRow is another special variable, but this time it’s in <cfoutput> instead of <cfquery>. Within an output loop, CurrentRow keeps a tally of the iterations—it contains 1 when the first row is processed, 2 when the second row is processed, and so on. In this example, it’s used to number the movies (as seen in Figure 10.7).

CurrentRow can also be used it to implement fancy formatting, for example, alternating the background color for every other row (a green paper effect). Listing 10.8 is movies7.cfm, a modified version of movies4.cfm (I used that older version as it’s simpler and looks better for this example). Background color, as previously seen, is set using the bgcolor attribute, but unlike in the previous example, here the colors are being set dynamically and programmatically.

Listing 10.8 movies7.cfm—Implementing Alternating Colors

The big change in Listing 10.8 is the <cfif> statement right inside the <cfoutput> loop. As you will recall, <cfif> is used to evaluate if statements (conditions), and here the following <cfif> statement is used:

<cfif CurrentRow MOD 2 IS 1>

→ The <cfif> statement was introduced in Chapter 9, “Programming with CFML.”

CurrentRow contains the current loop counter as previously explained. MOD is an arithmetic operator that returns the reminder of an equation, and so testing for MOD 2 is a way to check for odd or even numbers (divide a number by 2, if the remainder is 1 the number is odd otherwise the number is even). So checking MOD 2 IS 1 is effectively checking that the number is odd.

Within the <cfif> statement one of two <cfset> tags will be called; if the CurrentRow is odd then the first is called (setting a variable named bgcolor to MediumSeaGreen), and if even then the second is called (setting bgcolor to white). Once the </cfif> is reached a variable named bgcolor will exist and will contain a color (MediumSeaGreen or white, depending on whether CurrentRow is odd or even). As the <cfif> code is within the <cfoutput> block it’s processed once for every row, and so bgcolor is reset on each row.

→ See Chapter 8 for an introduction to the <cfset> tag.

Then bgcolor is then passed to the <tr> tag’s bgcolor attribute so that on odd rows the <tr> tag becomes:

<tr bgcolor=">

and on even rows it becomes:

<tr bgcolor="White">

Grouping Result Output

Before a new level of complexity is introduced, let’s review how ColdFusion processes queries.

In ColdFusion, data queries are created using the <cfquery> tag. <cfquery> performs a SQL operation and retrieves results if any exist. Results are stored temporarily by ColdFusion and remain only for the duration of the processing of the template that contained the query.

The <cfoutput> tag is used to output query results. <cfoutput> takes a query name as an attribute and then loops through all the rows that were retrieved by the query. The code block between <cfoutput> and </cfoutput> is repeated once for each and every row retrieved.

All the examples created until now displayed results in a single list or single table.

What would you do if you wanted to process the results in subsets? For example, suppose you wanted to list movies by rating. You could change the SQL statement in the <cfquery> to retrieve the rating ID and set the sort order to be RatingID and then by MovieTitle.

This would retrieve the data in the correct order, but how would you display it? If you used <cfoutput> as you have until now, every row created by the <cfoutput> block would have to be the same. If one had the rating displayed, all would have to because every row that is processed is processed with the same block of code.

Look at Figure 10.8. As you can see, the screen contains nested lists. The top-level list contains the rating IDs, and within each rating ID is a second list containing all the movies with that rating. How would you create an output like this?

Figure 10.8 Grouping provides a means with which to display data grouped into logical sets.

Listing 10.9 contains the code for a new page; save this as ratings1.cfm and execute it in your browser.

Listing 10.9 ratings1.cfm—Grouping Query Output

Listing 10.9 starts with the comment block, followed by a <cfquery> that retrieves all the movies (title and rating only) sorted by RatingID and MovieTitle (by RatingID and within each RatingID by MovieTitle).

The display section of the code starts by creating an unordered list—this is the outer list, which contains the ratings.

Then, <cfoutput> is used again to create an output block, but this time the group attribute has been added. group="RatingID" tells the output block to loop through the outer loop only when RatingID changes. In other words, the outer loop is processed once per group value. So in this example, it’s processed once per RatingID value—regardless of the number of movies with that RatingID.

Then the RatingID is displayed, and a second unordered list is started—this is for the inner list within each RatingID.

Next, comes a second <cfoutput> block that displays the MovieTitle. No query is specified here; ColdFusion doesn’t need one. Why? Because group is being used, ColdFusion knows which query is being used and loops through the inner <cfoutput> only as long as RatingID doesn’t change.

As soon as RatingID changes, the inner <cfoutput> loop stops and the inner list is terminated with a </ul>.

This repeats until all rows have been processed, at which time the outer <cfoutput> terminates and the final </ul> is generated.

So how many times is each <cfoutput> processed? The movie list contains 23 rows with a total of 6 ratings. So the outer loop is processed 6 times, and the inner loop is processed 23 times. This outer list contains 6 items (each RatingID value), and each item contains a sub-list containing the movies with that RatingID.

Listing 10.10 contains a modified version of Listing 10.9, this time displaying the results in an HTML table (as seen in Figure 10.9). Save Listing 10.10 as ratings2.cfm, and then execute it in your browser.

Listing 10.10 ratings2.cfm—Grouping Query Output

Figure 10.9 Grouped data can be used in lists, tables, and any other form of data presentation.

The only thing that has changed in Listing 10.10 is the output code. Again, the <cfoutput> tags are nested—the outer loops through RatingID and the inner loops through the movies.

The HTML table is created before any looping occurs (you want only one table). Then, for each RatingID a new table row is created containing two cells. The left cell contains the RatingID, and the right cell contains the movies.

To do this, the inner <cfoutput> loop is used in that right cell (between the <TD> and </td> tags) so that, for each RatingID listed on the left, all the appropriate movies are listed on the right.

Using Data Drill-Down

Now that you’ve learned almost everything you need to know about the <cfoutput> tag, let’s put it all together in a complete application.

Data drill-down is a popular form of user interface within Web applications because it enables the progressive and gradual selection of desired data. Data drill-down applications usually are made up of three levels of interface:

• A search screen

• A results screen (displaying the results of any searches)

• A details screen (displaying the details for any row selected in the results screen)

You won’t create the search screen here (forms are introduced in the next chapter), but you will create the latter two screens. Your application will display a list of movies (similar to the screens created earlier in this chapter) and will allow visitors to click any movie to see detailed information about it.

Introducing Dynamic SQL

You’ve used lots of <cfquery> tags thus far, and each of them has contained hard-coded SQL—SQL that you typed and that stays the same (the results may differ if the data in the database changes, but the SQL itself always stays the same). But SQL passed to ColdFusion need not be static and hard-coded; the real power of <cfquery> is seen when SQL is constructed dynamically.

To demonstrate what we mean, Listing 10.11 contains the code for a new file named dynamicsql.cfm. Save the code and execute it to see a screen like the one shown in Figure 10.10.

Listing 10.11 dynamicsql.cfm—Dynamic SQL Demonstration

Figure 10.10 The <cfquery> result structure contains the final (post–dynamic processing) SQL and additional information.

Listing 10.11 starts by creating a variable as follows:

<cfset FilmID=1>

Next comes a <cfquery> tag containing the following SQL:

The WHERE clause specifies the row to be retrieved, and would usually be an actual value. For example, to retrieve the movie with a FilmID of 1 you would use this SQL:

→ See Chapter 6, “Introducing SQL,” for a detailed explanation of the SELECT statement and its WHERE clause.

And this is exactly what the code in Listing 10.11 does. #FilmID# is a ColdFusion expression, and so ColdFusion will process it, returning the value of FilmID which is 1 (as set in the <cfset> earlier).

In other words, the SQL used here is dynamic in that the actual SQL statement itself can change (in this example based on the value of FilmID). If you wanted to retrieve a different movie you could simply update FilmID so that it contained a different value.

The last block of code contains two <cfdump> tags:

The former simply dumps the returned query (the data contained in the movie variable). The latter dumps the results structure, exposing a field named SQL that contains the SQL used, and additional information (including the same query variables listed in Table 10.1 earlier in this chapter).

As seen previously, the use of result is always optional, but if needed it can expose useful information about tag execution.

Implementing Data Drill-Down Interfaces

Now that you’ve seen how dynamic SQL is used, let’s return to data drill-down pages. The first screen you need to create is the details page—the one that will be displayed when a movie is selected. Figure 10.11 shows the details for one movie.

Figure 10.11 In data drill-down applications, the details page displays all of the details for a specific record.

Listing 10.12 contains the code for the file details1.cfm. You’ll not be able to run this page as is in ColdFusion Builder (you’ll learn why in a moment). So save the code and then execute it in your browser with this URL:

http://localhost:8500/ows/10/details1.cfm?FilmID=2

Listing 10.12 details1.cfm—Data Drill-Down Details

You should see a screen like the one in Figure 10.11.

There are several important things to point out in Listing 10.12. Let’s start with the SQL statement:

The WHERE clause here is used to select a specific movie by its primary key (FilmID). But instead of comparing it to a real number, a ColdFusion variable is used—#URL.FilmID#. This is dynamic SQL, similar to the example in Listing 10.11 above. When ColdFusion encounters #URL.FilmID#, it replaces that expression with whatever the value of the URL parameter FilmID is. So if the URL parameter FilmID had a value of 2, the generated SQL would look like this:

This is why I had you append ?FilmID=2 to the URL when you executed this page. Without a FilmID parameter, this code would have failed, but we’ll get to that in a moment.

The beauty of this technique is that it allows the same details page to be used for an unlimited number of database records—each FilmID specified generates a different page. If FilmID were 10, the SQL statement would have a WHERE clause of FilmID=10, and so on.

→ URL variables were briefly introduced in Chapter 9.

The rest of the code in Listing 10.12 is self-explanatory. The details are displayed in an HTML table with the title spanning two columns. Dates are formatted using the DateFormat() function, and monetary amounts are formatted using the DollarFormat() function (which, as its name suggests, formats numbers as dollar amounts).

One interesting line of code, though, is the <img> tag (used to display the movie poster image):

Binary data, like images, can be stored in databases just like any other data, but accessing these images requires special processing that is beyond the scope of this chapter. And so in this application images are stored in a directory and named using the primary key values. Therefore, in this example, the image for FilmID 2 is f2.gif, and that image is stored in the images directory under the application root. By using #FilmID# in the file name, images can be referred to dynamically. In this example, for FilmID 2 the <img> tag becomes

Try executing Listing 10.12 again, but this time don’t pass the FilmID parameter (or just run the code in ColdFusion Builder). What happens when you execute the code? You probably received an error message similar to the one in Figure 10.12 telling you that you were referring to a variable that doesn’t exist. You can’t use URL.FilmID in your SQL statement if no URL parameter named FilmID exists. This is why you could not run the code as is in ColdFusion Builder, because you would not have had a way to pass URL.FilmID as a parameter.

Figure 10.12 Do not refer to a variable that does not exist; if you do, an error message will be generated.

The solution (which you looked at briefly in Chapter 9) is to check that the variable exists before using it. Listing 10.13 contains an updated version of the code; save it as details2.cfm and execute it. What happens now if no FilmID is specified?

Listing 10.13 details2.cfm—Data Drill-Down Details

The only thing that has changed in Listing 10.13 is the inclusion of the following code before the <CFQUERY> tag:

If FilmID was not passed, users should never have gotten to this page. You could simply display an error message, but instead, why not send them where they need to go? <cflocation> is a Cold-Fusion tag that redirects users to other pages (or even other sites). So the <cfif> statement checks to see whether URL.FilmID exists (using the IsDefined() function). If it does not, the user is sent to the movies6.cfm page automatically. Now the SQL code won’t execute without a FilmID because if no FilmID exists, the <cfquery> tag is never even reached.

→ The IsDefined() function was introduced in Chapter 9.

So far so good, but you’re not there yet. Two other possible trouble spots still exist. Try executing the following URL:

http://localhost:8500/ows/10/details2.cfm?FilmID=1

1 is a valid FilmID, so the movie details are displayed. But FilmID 1 doesn’t have a movie image, which means the <img> tag is pointing to a nonexistent image, causing a browser error.

In addition, try this URL:

http://localhost:8500/ows/10/details2.cfm?FilmID=1000

No movie with a FilmID of 1000 exists, so no movie is displayed, but no error message is displayed either.

Neither of these problems is critical, but they should be addressed anyway. Listing 10.14 contains a final version of the details page; save this file as details3.cfm.

Listing 10.14 details3.cfm—Data Drill-Down Details

A lot has changed here, so let’s walk through the code together.

The first line of code is a <cfset> statement that sets a variable named list_page to movies8.cfm. You’ll see why this was done in a moment.

Next comes the check for the URL parameter FilmID. If it’s not present, <cflocation> is used to redirect the user to the page referred to in variable list_page (the movie list, same as before).

Then comes the query itself—same as before; no changes there.

After the query comes a new <cfif> statement that checks to see whether result.RecordCount IS 0. You will recall that RecordCount lets you know how many rows were retrieved by a query, so if RecordCount IS 0, you know that no rows were retrieved. The only way this could happen is if an invalid FilmID were specified, in which case <cflocation> would be used to send the user back to the movie list page—one problem solved. (Earlier I said that I’d show you an alternative use for RecordCount; well, I just did.)

Next comes a set of two <cfset> statements:

The goal here is to check that the movie image exists before the <img> tag is used to insert it. ColdFusion provides a function named FileExists() that can be used to check for the existence of files, but there is a catch.

Images always have at least two paths by which they are referred—the actual path on disk and the URL (usually a relative URL). So in this example, the image for FilmID 2 would have a path on disk that might look similar to c:coldfusion8wwwrootowsimagesf2.gif and a URL that might look similar to ../images/f2.gif. Usually, you care about only the URL—the actual physical location of a file isn’t important within the browser. But to check for a file’s existence, you do need the actual path (that is what you must pass to FileExists()). And the code you used to build the path (using #FilmID# in the SRC) was a relative path. Enter the two <cfset> statements. The first simply creates a variable named image_src that contains the dynamically generated relative file name (in the case of FilmID 2, it would be ../images/f2.gif), the same technique used in the <img> tag in the previous versions of this code. The second uses a ColdFusion function named ExpandPath() that converts relative paths to complete physical paths (here saving that path to image_path).

At this point, no determination has been made as to whether to display the image. All you have done is created two variables, each containing a path—one physical, suitable for using with FileExists(), and one relative, suitable for use in an <img> tag.

Next comes the details display, which is the same as it was before, except now the <img> tag is enclosed within a <cfif> statement that checks whether FileExists(image_path). If the image exists, FileExists() returns TRUE and the <img> tag is inserted using image_src as the SRC. If FileExists() returns FALSE (meaning the movie had no image), the <img> tag isn’t generated—problem number two solved.

At the very bottom of the page is a new link that enables users to get back to the movie list page. This link also uses the list_page variable. And by now, I hope the reason that a variable for the movie link URL is used is blatantly obvious. The code now has three locations that refer to the movie list file. Had they all been hard-coded, making changes would involve more work and would be more error-prone (the likelihood of you missing one occurrence grows with the number of occurrences). By using a variable, all that needs to change is the variable assignment at the top of the page—the rest all works as is.

The last thing to do is to update the movie-listing page so it contains links to the new details3.cfm page. Listing 10.15 contains the revised movie listing code (based on movies6.cfm). Save it as movies8.cfm and then run it to see the finished page complete with usable links.

Listing 10.15 movies8.cfm—Data Drill-Down Results Page

Just two changes have been made in Listing 10.15. The SELECT statement in the <cfquery> now also retrieves the FilmID column—you need that to pass to the details page. (You will recall that the details page needs the FilmID passed as a URL parameter.)

The display of MovieTitle has been changed to read

<a href="details3.cfm?FilmID=#URLEncodedFormat(Trim(FilmID))#">
#MovieTitle#</a>

The HTML <a href> tag is used to create links to other pages. The text between the <a> and </a> tags is clickable, and when it’s clicked, the user is taken to the URL specified in the href attribute. So the tag <a href="details3.cfm">Click here</a> displays the text Click here, which, if clicked, takes the user to page details3.cfm.

But you need FilmID to be passed to the details page, so for FilmID 1 the href needed would read

<a href="details3.cfm?FilmID=1">Being Unbearably Light</a>

And for FilmID 2 it would have to be

<a href="details3.cfm?FilmID=2">Charlie's Devils</a>

These links are created using the FilmID column so that the URL parameter FilmID is correctly populated with the appropriate value for each movie. As ColdFusion loops through the movies, it creates a link for each one of them. The links all point to the same page—details3.cfm. The only thing that differs is the value passed to the FilmID parameter, and this value is then used in details3.cfm to display the correct movie. So for the movie with FilmID of 1, the URL correctly becomes

<a href="details3.cfm?FilmID=1">Being Unbearably Light</a>

Try it out; you should be able to click any movie to see the details and then click the link at the bottom of the details page to get back.

Pretty impressive for just two files containing fewer than 150 lines of ColdFusion code (including all HTML and comments).

Securing Dynamic SQL Statements

As you’ve just seen, the ability to create SQL statements dynamically using variables and more provides ColdFusion developers with incredible power and flexibility. But this power is not without risk, and before concluding this chapter, we need to look at the very real danger of SQL injection attacks.

Consider the following dynamic ColdFusion query, an example from earlier in this chapter:

Here a WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and you’ll be using this technique continuously as you build Web applications. If the URL were

http://mydomain/path/file.cfm?FilmID=100

the resulting SQL statement would be

But what if someone tampered with that URL so that it read

http://mydomain/path/file.cfm?FilmID=100;DELETE+Films

Now the resulting SQL would be

And depending on the database being used, you could end up executing two statements: first SELECT, and then DELETE Films (which would promptly delete all data from the Films table).

Scared? You should be. SQL statements are not just used for queries. They are also used by most databases to create and drop tables, create user logins, change passwords, set security levels, manage scheduled events, even create and drop entire databases, as well as to implement whatever features your database supports that are be accessible this way.

This type of attack, deliberately manipulating URLs and more to maliciously change generated SQL, is known as a SQL injection attack. And hackers are always looking for signs that dynamic SQL is being generated so as to launch these attacks. This is a very real and dangerous threat. Indeed, SQL injection attacks are among the most common forms of attacks on Web sites.

So how can you protect your application?

Checking All Received Variables

First, you should always be checking parameters before passing them to your database. As a rule, never, ever pass client-supplied data (URL parameters, form fields, or even cookies) to your database unchecked. Attacks aside, it is flat-out unsafe to ever assume that data submitted by a client can be used as is.

Thus, you could be using code like this:

<cfparam name="URL.FilmID" type="integer">

You can add this line before your SQL statements. Actually, you should add a line like this before you even use any passed variables, first thing on your page. And this single line of code will lock out most SQL injection attacks by ensuring that expected values are what they should be.

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

By checking that received values are of the expected type, you’ll ensure the safety of your application.

Using <cfqueryparam>

The <cfqueryparam> tag can optionally be used within <cfquery> tags. It is primarily used for variable binding, which is beyond the scope of this chapter. But it has another important use that is very relevant to this discussion.

As an additional line of defense against SQL injection attacks, you could use <cfqueryparam>, as seen here:

If the previous tampered-with URL were passed to the this query, the value would be rejected, and an error would be thrown. The cfsqltype code performs data-type validation checks, and values that do not match the type are rejected. That’s it—only integers are allowed, and malicious, tampered-with URL parameters are not integers.

Securing Against SQL Injection Attacks

SQL injection attacks have been around for as long as dynamic SQL itself, and unfortunately, many sites get hacked using the methods just described.

ColdFusion has made it incredibly easy to protect yourself against such attacks. Be it by using <cfparam> or <cfqueryparam> or your own conditional processing, it’s simple to protect yourself, and your responsibility to do so.

• Use <cfqueryparam> for every single variable in every single <cfquery> statement.

• Use <cfparam> at the top of every page to initialize and validate each and every variable.

My personal recommendation is that you do both.

Debugging Dynamic Database Queries

Before we finish this chapter, there is something you should be aware of. Look at the following code:

As you now know, this code builds a dynamic SQL statement—the expression #URL.FilmID# is replaced by the contents of that variable to construct a complete SQL SELECT statement at runtime.

This particular example is a simple one; a single expression is used in a simple WHERE clause. But as the complexity of the expressions (or the number of them) increases, so does the chance that you’ll introduce problems in your SQL. And to find these problems, you’ll need to know exactly what SQL was generated by ColdFusion—taking into account all dynamic processing.

I already showed you one way to obtain the dynamically generated SQL (using the optional <cfquery> result attribute). But here is another option.

In Chapter 2, “Accessing the ColdFusion Administrator,” I mentioned the debugging screens (and told you that we’d use them in this chapter). The debugging screens can be used to append debug output to the bottom of generated pages, as seen in Figure 10.13.

Figure 10.13 Dynamic SQL information is displayed along with the standard ColdFusion debugging output.

As you can see, the appended output contains database query information (including the SQL, number of rows retrieved, and execution time), page execution time, passed parameters, CGI variables, and much more.

To try this for yourself, see Chapter 2 for instructions on turning on debug output. Once enabled, execute any page in your browser and the debug output will be appended automatically.