System information

Exercise 2: Avoiding the potential security risk when using dynamic SQL 91
Reviewing the code
The following table describes the ColdFusion code that you use to build the Trip Detail page:
As you did in this exercise, 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 4: Building Dynamic Queries. However, before
you link that search facility, you must understand a potential security risk using dynamic SQL.
The following exercise describes this risk and how to avoid it.
Exercise 2: 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 might 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 that supports this behavior:
<cfif IsDefined("URL.ID")>
WHERE tripID = #URL.ID#
</cfif>
Code Explanation
<cfcomponent>
<cffunction name="getTripDetails"
access="public"
returntype="query">
...
<cfreturn tripDetails>
</cffunction>
</cfcomponent>
The cfcomponent tag creates a CFC. The cffunction tag
creates a method named
getTripDetails in the CFC; the
method returns the results of the query to the calling page.
<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, you want to show only a
single row at a time; therefore, the
maxRows attribute is set
to
1.
<cfif IsDefined("URL.ID")>
WHERE tripID = #URL.ID#
</cfif>
The URL.ID specifies a parameter that you can include in
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
IsDefined function to determine if a parameter is passed
within the URL. You can also use
IsDefined to determine if
the user has entered data in form fields prior to the form
post action.