MACROMEDIA COLDFUSION MX 61-GETTING STARTED BUILDING COLDFUSION..., Getting Started

Page: 84 / 134

84 Chapter 7: Lesson 3: Creating a Main Application 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: Writing Your First ColdFusion Application . However,
before you link that search facility, 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:
<cfif IsDefined("URL.ID")>
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.
Protecting your application
To ensure that your application is protected from such an attack, you can exploit the fact that the
ID must be a numeric value. The CFML
Val
function returns the numeric value at the beginning
of a string expression. You can use the
Val
function as follows:
<cfif IsDefined("URL.ID")>
WHERE tripID = #Val(URL.ID)#
</cfif>
Now if non-numeric data is passed within the URL ID field, the
Val
statement returns 0, and the
trip with ID 0 displays (if one exists). If the user enters the previously cited URL
(http://localhost/cfdocs/getting_started/my_app/tripdetail.cfm?ID=24;DROP+trips), the
application ignores the non-numeric values and displays the trip information of trip ID 24.
Warning: The exercises in this tutorial ignore the dynamic SQL risk from attack. To eliminate this risk,
you should use ColdFusion functions (such as Val ) to perform type checking on all URL parameters.
For queries, you can also use the cfqueryparam tag, which is explained in CFML Reference .