Programming instructions

88 Lesson 3 Creating a Main Application Page

Reviewing the code

The following table describes the ColdFusion code used to build the Trip Detail page:

As you can see, you can build comprehensive database query applications using CFML

and dynamic SQL. To further test the new Trip Detail page that you created, you will

link it to the search facility that you built in Lesson 2. However, before you link the

search facility you built in Lesson 2, you need to understand a potential security risk

using dynamic SQL. The following section describes this risk and how to code around it.

Avoiding the potential security risk when using dynamic SQL

To reduce round trips between the client and the database server, many SQL database

servers permit the client to submit multiple SQL statements in a single request, separated

by a semicolon (;). For these database managements systems, the following SQL request

is valid:

DELETE from trips where tripLocation = 'China'; SELECT tripName from trips

This request may be an efficient way to list the trips that remain after the database

management system removes the China trip. Problems arise when the SQL statement is

built dynamically.

In the Trip Maintenance application, when the client program or user passes an ID in the

URL that calls the Trip Detail page, the page displays the relevant trip information. The

following code builds the correct WHERE clause supporting this behavior:

WHERE tripID = #URL.ID#

</cfif>

If a user called the Trip Detail page using the following statement:

http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips

the SQL database management system executes the proper SQL SELECT statement,

then immediately erases the Trips table from the database.

Code Explanation

<cfquery name="TripQuery"

dataSource="CompassTravel" maxRows=1>

The cfquery tag includes a maxRows attribute.

This attribute limits the number of result rows

brought back from the database. In the Trip Detail

page, we want to only show a single row at a time,

therefore, maxRows is set to 1.

WHERE tripID = #URL.ID#

</cfif>

The URL.ID specifies a parameter that can be

contained within the URL that requests this page.

If the ID parameter is passed within the URL, it is

used in the SQL query to identify the tripID to

SELECT. You can use the CFML function

IsDefined to determine if a parameter is passed

within the URL. It can also be used to determine if

the user has entered data in form fields prior to the

form post action.