Welcome to our free ColdFusion MX tutorial. This tutorial is based on Webucator's Comprehensive ColdFusion Training course.
Lesson Goals
In this section, you will learn how to create a user administration page that will display all
of the users' information within a table structure. The page will consist of multiple forms
that allow you to edit user information, delete users and add new users. The page will look
like this:
The page is divided into two parts:
This first demo shows how to perform the query and display the results as form fields. Note the use of the query attribute in the <cfoutput> tag. This instructs ColdFusion to treat the <cfoutput> like a <cfloop>, looping through each record returned by the query.
<cfquery name="getUsers" datasource="#APPLICATION.datasource#"> SELECT userid, firstname, lastname, email, role FROM Users </cfquery> <html> <head> <title>Admin Page</title> <style> .admin {color:#990000} .user {color:#000000} </style> </head> <body> <cfoutput> <form method="post" action="#CGI.SCRIPT_NAME#"> </cfoutput> <table cellpadding="3" cellspacing="0" id="maintable" width="700"> <tr> <th>First Name</th> <th>Last Name</th> <th>Email</th> <th>Role</th> <th colspan="2">Action</th> </tr> <tr> <td><input type="text" name="firstname" size="15"></td> <td><input type="text" name="lastname" size="15"></td> <td><input type="text" name="email" size="30"></td> <td> <select name="role"> <option value="admin" class="admin">Admin</option> <option value="user" selected class="user">User</option> </select> </td> <td colspan="2" align="center" width="120"> <input name="add" type="submit" value="Add User" style="font-size:xx-small"> </td> </tr> <tr><td colspan="6"><hr></td></tr> </table> </form> <cfoutput query="getUsers"> <form method="post" style="margin:0px" action="#CGI.SCRIPT_NAME#"> <table width="700"> <tr> <td> <input type="text" name="firstname" value="#firstname#" size="15" class="#role#"> </td> <td> <input type="text" name="lastname" value="#lastname#" size="15" class="#role#"> </td> <td> <input type="text" name="email" value="#email#" size="30" class="#role#"> </td> <td> <cfif role EQ "admin"> <cfset adminselected = "selected"> <cfset userselected = ""> <cfelse> <cfset userselected = "selected"> <cfset adminselected = ""> </cfif> <select name="role"> <option value="admin" #adminselected# class="admin">Admin</option> <option value="user" #userselected# class="user">User</option> </select> </td> <td width="120"> <input type="submit" value="EDIT" style="font-size:xx-small" class="#role#"> <input type="submit" value="DELETE" style="font-size:xx-small" class="#role#"> </td> </tr> </table> </form> </cfoutput> <cfinclude template="Includes/Footer.cfm"> </body> </html>
We will now add code to try to insert users when the Add User button is pressed. As we'll be processing edits and deletes with this same page, we must detect not only that a form has been submitted, but which form it was.
We will want to email the new user an auto-generated password, so we will include Includes/Functions.cfm, which contains generatePassword() and sendPassword() functions.
<cfinclude template="Includes/Functions.cfm"> <cfif isDefined("FORM.inserting")> <cfset password=GeneratePassword(10)> <cfset sendPassword(FORM.email,password)> <cfquery datasource="#APPLICATION.datasource#"> INSERT INTO Users (email, password, firstname, lastname, role) VALUES ('#FORM.email#', '#password#','#FORM.firstname#','#FORM.lastname#','#FORM.role#') </cfquery> </cfif> ---- C O D E O M I T T E D ----
Now we will add the code to edit and delete users. Note that we need to be able to determine which submit button was pushed. We do this by giving the submit buttons names. The name-value pair of the button that is pressed will be sent to the server.
<cfinclude template="Includes/Functions.cfm"> <cfif isDefined("FORM.inserting")> <cfset password=GeneratePassword(10)> <cfset sendPassword(FORM.email,password)> <cfquery datasource="#APPLICATION.datasource#"> INSERT INTO Users (email, password, firstname, lastname, role) VALUES ('#FORM.email#', '#password#','#FORM.firstname#','#FORM.lastname#','#FORM.role#') </cfquery> <cfelseif isDefined("FORM.editing")> <cfquery datasource="#APPLICATION.datasource#"> UPDATE Users SET firstname='#FORM.firstname#', lastname='#FORM.lastname#', email='#FORM.email#', role='#FORM.role#' WHERE userid=#FORM.userid# </cfquery> <cfelseif isDefined("FORM.deleting")> <cfquery datasource="#APPLICATION.datasource#"> DELETE FROM Users WHERE userid=#FORM.userid# </cfquery> </cfif> ---- C O D E O M I T T E D ----
As we only want users who are admins to be able to view this page, we need to check the user's role before displaying the page. We do this by checking SESSION.role.
Note that we also had to add code to Login.cfm to set the SESSION.role variable as shown in the files below.
<cfif isDefined("FORM.submitted")> <cfquery name="logincheck" datasource="#APPLICATION.datasource#"> SELECT firstname, lastname, userid, role FROM Users WHERE email='#FORM.email#' AND password='#FORM.password#' </cfquery> <cfif logincheck.RecordCount EQ 1> <cfset SESSION.firstname = logincheck.firstname> <cfset SESSION.lastname = logincheck.lastname> <cfset SESSION.userid = logincheck.userid> <cfset SESSION.role = logincheck.role> <cfif isDefined("FORM.rememberme")> <cfcookie name="loggedin" value="#logincheck.userid#" expires="7"> </cfif> <cflocation url="index.cfm" addtoken="no"> <cfelse> <cfset badlogin=true> </cfif> </cfif> <cfparam name="FORM.email" default=""> <html> <head> <title>Login Page</title> </head> <body> <h2>Log in</h2> <cfif isDefined("badlogin")> <p class="errors"><b>That is not the correct email and password. Please <a href="Login.cfm">try again</a>.</p> </cfif> <cfoutput> <form method="post" action="#CGI.SCRIPT_NAME#"> </cfoutput> <input type="hidden" name="submitted" value="true"> <table> <tr> <td>Email:</td> <td><input type="text" name="email" value="<cfoutput>#FORM.email#</cfoutput>" size="40"></td> </tr> <tr> <td>Password:</td> <td> <input type="password" name="password" size="14"> </td> </tr> <tr> <td colspan="2"> <input type="checkbox" name="rememberme"> Remember Me </td> </tr> <tr> <td align="right" colspan="2"> <input type="submit" value="Log in"> </td> </tr> </table> </form> <cfinclude template="Includes/Footer.cfm"> </body> </html>
We can abort the execution of a page when users who are not permitted try to access it with the <cfabort> tag.
<cfif NOT isDefined("SESSION.role") or SESSION.role NEQ "admin"> You do not have permissions to view this page. <cfabort> </cfif> ---- C O D E O M I T T E D ----
To make it convenient to access the admin page, we added a link to the footer that only appears when an "admin" user is logged in.
<br><hr width="350" align="right"> <div align="right" id="copyright">© 2007 Runners Home. All rights reserved. <a href="index.cfm">Home</a> | <a href="Races.cfm">Races</a> | <a href="Resources.cfm">Resources</a> | <cfif isDefined("SESSION.userid")> <a href="Logout.cfm">Log out</a> <cfelse> <a href="Login.cfm">Log in</a> </cfif> <cfif isDefined("SESSION.role") AND SESSION.role EQ "admin"> | <a href="Admin4.cfm">Admin</a> </cfif> </div>
To see how the applicatoin now works:
The admin page has a very serious problem. It allows an admin to delete himself or to change his status from admin to user. This could create a situation in which there were no admins left to administer the users. In production code, you would want to take measures to prevent this from happening.
In this section, you will learn how to display records from a database and control read/write access to individual records based on a user's role and his relationship to the record. The following rules will apply:
The interface will look like this when a "User" user logs in:
This first demo displays all the races, but they are not yet sortable. It also allows all users to edit all data.
<cfquery name="getraces" datasource="#APPLICATION.datasource#"> SELECT raceid, racename, racetime, distance, city, state, active, u.userid, firstname, lastname, email FROM Users u, Races r WHERE u.userid = r.userid AND active=1 </cfquery> <html> <head> <title>Running Races</title> </head> <body> <table border="1" cellpadding="3" cellspacing="0" id="maintable"> <tr> <th>Race</th> <th>Location</th> <th>Distance</th> <th>Date & Time</th> <th>Added by</th> <th>Edit/Delete</th> </tr> <cfoutput query="getraces"> <tr> <td>#racename#</td> <td>#city#, #state#</td> <td>#distance#</td> <td>#DateFormat(racetime, "ddd, mmm d, yyyy")# at #TimeFormat(racetime,"h:mm tt")#</td> <td><a href="mailto:#email#">#firstname# #lastname#</a></td> <td> <form method="post" action="RaceEdit.cfm" style="margin-bottom:0px"> <input type="hidden" name="raceid" value="#raceid#"> <input type="submit" value="EDIT" style="font-size:xx-small"> <input type="submit" name="deleting" value="DELETE" style="font-size:xx-small"> </form> </td> </tr> </cfoutput> </table> <cfinclude template="Includes/Footer.cfm"> </body> </html>
We will add code that prevents users from editing other people's records.
We will also add code to allow any user who is logged in to add a race.
<cfif isDefined("SESSION.userid")> <cfset userid = SESSION.userid> <cfelse> <cfset userid =0> </cfif> <cfquery name="getraces" datasource="#APPLICATION.datasource#"> SELECT raceid, racename, racetime, distance, city, state, active, u.userid, firstname, lastname, email FROM Users u, Races r WHERE u.userid = r.userid AND active=1 </cfquery> <html> <head> <title>Running Races</title> </head> <body> <cfif userid NEQ 0> <a href="RaceAdd.cfm">Add a race</a> </cfif> <table border="1" cellpadding="3" cellspacing="0" id="maintable"> <tr> <th>Race</th> <th>Location</th> <th>Distance</th> <th>Date & Time</th> <th>Added by</th> <th>Edit/Delete</th> </tr> <cfoutput query="getraces"> <tr> <td>#racename#</td> <td>#city#, #state#</td> <td>#distance#</td> <td>#DateFormat(racetime, "ddd, mmm d, yyyy")# at #TimeFormat(racetime,"h:mm tt")#</td> <td><a href="mailto:#email#">#firstname# #lastname#</a></td> <cfif VARIABLES.userid EQ getraces.userid OR (isDefined("SESSION.role") AND SESSION.role EQ "admin")> <td> <form method="post" action="RaceEdit.cfm" style="margin-bottom:0px"> <input type="hidden" name="raceid" value="#raceid#"> <input type="submit" value="EDIT" style="font-size:xx-small"> <input type="submit" name="deleting" value="DELETE" style="font-size:xx-small"> </form> </td> <cfelse> <td>PROTECTED</td> </cfif> </tr> </cfoutput> </table> <cfinclude template="Includes/Footer.cfm"> </body> </html>
"User" users now get a "PROTECTED" cell for records that they don't own. Admins are able to edit all records.
We will now make the table headings into links that, when clicked on, will change the order in which the records appear.
---- C O D E O M I T T E D ---- <table border="1" cellpadding="3" cellspacing="0" id="maintable"> <cfoutput> <tr> <th> <a href="#CGI.SCRIPT_NAME#?orderfield=racename">Race</a> </th> <th> <a href="#CGI.SCRIPT_NAME#?orderfield=city">Location</a> </th> <th> <a href="#CGI.SCRIPT_NAME#?orderfield=distance">Distance</a> </th> <th> <a href="#CGI.SCRIPT_NAME#?orderfield=racetime">Date & Time</a> </th> <th> <a href="#CGI.SCRIPT_NAME#?orderfield=firstname">Added by</a> </th> <th>Edit/Delete</th> </tr> </cfoutput> ---- C O D E O M I T T E D ----
That completes the page for displaying data. In the next sections, we will learn how to add, edit and delete races.
A stored procedure is a database object that holds a callable query, allowing parameters to be passed in.
The major benefits of stored procedures.
In ColdFusion, stored procedures can be called directly within a <cfquery> tag using the database-specific syntax for executing them. They can also be called using the <cfstoredproc> tag.
The <cfstoredproc> tag works much like the <cfquery> tag, except that, rather than a straight query, it contains (or can contain) one or more <cfprocparam> tags for passing parameters to the stored procedure.
Attribute | Description |
---|---|
procedure | Required. The name of the stored procedure. |
dataSource | Required. The name of the data source. |
username | Overrides username set up in ColdFusion Administrator. |
password | Overrides password set up in ColdFusion Administrator. |
blockfactor | Maximum rows to get at a time from server. |
debug | Turns debugging display on or off. |
returnCode | Sets cfstoredproc.statusCode to the status code returned by the stored procedure. |
result | The name of the structure to hold the variables returned by the stored procedure. By default, the structure is stored in cfstoredproc. |
Using <cfprocparam>, parameters are passed into a stored procedure in the same order as they are set in the stored procedure itself.
Attribute | Description |
---|---|
type | Possible values are in, out, or inout depending on whether a value is being sent to and/or received from the stored procedure. |
variable | Required if type is in or inout. The variable name that will hold the data returned by the stored procedure. |
value | Required if type is in and optional if type is inout. Value passed to stored procedure. |
CFSQLType | Required. The SQL data type (e.g, cf_sql_integer, cf_sql_varchar, etc.) |
maxLength | The maximum number of characters of an in or inout value. "0" means no limit. |
scale | The number of digits after the decimal of a numeric parameter. "0" means no limit. |
null | The in or inout parameter passes a null value. The value attribute is ignored. |
The following three examples show different methods for inserting the race:
<cfif NOT isDefined("SESSION.userid")> <cflocation url="index.cfm" addtoken="no"> </cfif> <cfparam name="FORM.racehour" default=""> <cfparam name="FORM.raceminute" default=""> <cfparam name="FORM.raceday" default=""> <cfparam name="FORM.racemonth" default=""> <cfparam name="FORM.raceyear" default=""> <cfparam name="FORM.raceampm" default="am"> <cfparam name="FORM.racename" default=""> <cfparam name="FORM.distance" default=""> <cfparam name="FORM.city" default=""> <cfparam name="FORM.state" default=""> <cfparam name="FORM.description" default=""> <html> <head> <title>Add a Race</title> </head> <body> <cfif isDefined("FORM.submitted")> <cfif FORM.raceampm EQ "pm"> <cfset racehour = FORM.racehour+12> <cfelse> <cfset racehour = FORM.racehour> </cfif> <cfset racetime = CreateDateTime(FORM.raceyear, FORM.racemonth, FORM.raceday, racehour, FORM.raceminute, 0)> <cfset racetime = CreateODBCDateTime(racetime)> <!---Use <cfquery> with a standard insert statement---> <cfquery datasource="#APPLICATION.datasource#"> INSERT INTO Races (racename, racetime, distance, city, state, active, userid, description) VALUES('#FORM.racename#',#racetime#,'#FORM.distance#','#FORM.city#', '#FORM.state#',1,#SESSION.userid#,'#FORM.description#') </cfquery> <p class="success">Race Added.<br> <a href="Races.cfm">Return to Race List</a>.</p> <h1>Add Another Race</h1> <!---Clean up variables---> <cfset FORM.racehour = ""> <cfset FORM.raceminute = ""> <cfset FORM.raceday = ""> <cfset FORM.racemonth = ""> <cfset FORM.raceyear = ""> <cfset FORM.raceampm = ""> <cfset FORM.racename = ""> <cfset FORM.distance = ""> <cfset FORM.city = ""> <cfset FORM.state = ""> <cfset FORM.description = ""> <cfelse> <h1>Add Race</h1> </cfif> <cfoutput> <form method="post" action="#CGI.SCRIPT_NAME#"> <input type="hidden" name="submitted" value="true"> <table> <tr> <td>Race Name:</td> <td><input name="racename" value="#FORM.racename#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>City:</td> <td><input name="city" value="#FORM.city#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>State:</td> <td><input name="state" value="#FORM.state#" type="text" size="3" maxlength="2"></td> </tr> <tr valign="top"> <td>Distance (in miles):</td> <td><input name="distance" value="#FORM.distance#" type="text" size="10" maxlength="20"></td> </tr> <tr valign="top"> <td>Time:</td> <td> <select name="racehour"> <cfloop index="hour" from="1" to="12" step="1"> <option value="#hour#"<cfif hour EQ FORM.racehour> selected</cfif>>#hour#</option> </cfloop> </select> : <select name="raceminute"> <cfloop index="minute" from="0" to="59" step="15"> <option value="#minute#"<cfif minute EQ FORM.raceminute> selected</cfif>>#NumberFormat(minute,"00")#</option> </cfloop> </select> <input name="raceampm" type="radio" value="am"<cfif FORM.raceampm EQ "am"> checked</cfif>> AM <input name="raceampm" type="radio" value="pm"<cfif FORM.raceampm EQ "pm"> checked</cfif>> PM</td> </tr> <tr valign="top"> <td>Date:</td> <td> <select name="racemonth"> <cfloop index="month" from="1" to="12"> <option value="#month#"<cfif month EQ FORM.racemonth> selected</cfif>>#MonthAsString(month)#</option> </cfloop> </select> <select name="raceday"> <cfloop index="day" from="1" to="31"> <option value="#day#"<cfif day EQ FORM.raceday> selected</cfif>>#day#</option> </cfloop> </select> <select name="raceyear"> <cfloop index="year" from="#Year(Now())#" to="#Year(Now())+3#"> <option value="#year#"<cfif year EQ FORM.raceyear> selected</cfif>>#year#</option> </cfloop> </select> </td> </tr> <tr valign="top"> <td>Description:</td> <td> <textarea name="description" rows="5" cols="40" wrap="soft">#FORM.description#</textarea> </td> </tr> <tr> <td colspan="2" align="right"><input type="submit" value="Add Race"></td> </tr> </table> </form> </cfoutput> <cfinclude template="Includes/Footer.cfm"> </body> </html>
---- C O D E O M I T T E D ---- <!---Use <cfquery> with a call to the spInsertRace stored procedure---> <cfquery datasource="#APPLICATION.datasource#"> exec spInsertRace '#FORM.racename#','#FORM.description#','#FORM.distance#',#racetime#,'#FORM.city#', '#FORM.state#',1,#SESSION.userid# </cfquery> ---- C O D E O M I T T E D ----
---- C O D E O M I T T E D ---- <!---Use <cfstoredproc> with nested <cfparam>s---> <cfstoredproc procedure="spInsertRace" datasource="#APPLICATION.datasource#"> <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.racename#"> <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.description#"> <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.distance#"> <cfprocparam type="In" cfsqltype="cf_sql_date" value="#racetime#"> <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.city#"> <cfprocparam type="In" cfsqltype="cf_sql_varchar" value="#FORM.state#"> <cfprocparam type="In" cfsqltype="cf_sql_bit" value="1"> <cfprocparam type="In" cfsqltype="cf_sql_tinyint" value="1"> </cfstoredproc> ---- C O D E O M I T T E D ----
Stored procedures can also return one or more recordsets. The <cfprocresult> tag is used to store these recordsets as ColdFusion queries. It works much like the name attribute of the <cfquery> tag.
Attribute | Description |
---|---|
name | Required. The name of the recordset. |
resultSet | A number indicating which recordset to store (only used if multiple recordsets are returned by the stored procedure). |
maxRows | The maximum number of records to retrieve for that recordset. |
The page for editing and deleting data is shown below.
<cfif NOT isDefined("SESSION.userid") OR NOT isDefined("FORM.raceid")> <cflocation url="index.cfm" addtoken="no"> </cfif> <html> <head> <title>Edit Race</title> </head> <body> <cfif isDefined("FORM.deleting")> <cfquery datasource="#APPLICATION.datasource#"> DELETE FROM Races WHERE raceid=<cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer"> </cfquery> <cflocation url="Races3.cfm" addtoken="no"> <cfelseif isDefined("FORM.editing")> <cfif FORM.raceampm EQ "pm"> <cfset racehour = FORM.racehour+12> <cfelse> <cfset racehour = FORM.racehour> </cfif> <cfset racetime = CreateDateTime(FORM.raceyear, FORM.racemonth, FORM.raceday, FORM.racehour, FORM.raceminute, 0)> <cfset racetime = CreateODBCDateTime(racetime)> <cfquery datasource="#APPLICATION.datasource#"> UPDATE Races SET racename = '#FORM.racename#', racetime = #racetime#, distance = '#FORM.distance#', city = '#FORM.city#', state = '#FORM.state#', description = '#FORM.description#', active = #FORM.active# WHERE raceid = <cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer"> </cfquery> <p class="success">Race Updated.<br> <a href="Races3.cfm">Return to Race List</a>.</p> <h1>Edit Race</h1> <cfelse> <h1>Edit Race</h1> </cfif> <cfquery name="getraceinfo" datasource="#APPLICATION.datasource#"> SELECT racename, racetime, distance, city, state, description, active FROM Races WHERE raceid=<cfqueryparam value="#FORM.raceid#" cfsqltype="cf_sql_integer"> </cfquery> <cfoutput query="getraceinfo"> <form method="post" action="#CGI.SCRIPT_NAME#"> <input type="hidden" name="editing" value="true"> <input type="hidden" name="raceid" value="#FORM.raceid#"> <table> <tr> <td>Race Name:</td> <td><input name="racename" value="#racename#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>City:</td> <td><input name="city" value="#city#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>State:</td> <td><input name="state" value="#state#" type="text" size="3" maxlength="2"></td> </tr> <tr valign="top"> <td>Distance (in miles):</td> <td><input name="distance" value="#distance#" type="text" size="10" maxlength="20"></td> </tr> <tr valign="top"> <td>Time:</td> <td> <select name="racehour"> <cfloop index="hour" from="1" to="12"> <option value="#hour#"<cfif hour EQ TimeFormat(racetime,"h")> selected</cfif>>#hour#</option> </cfloop> </select> : <select name="raceminute"> <cfloop index="minute" from="0" to="59" step="15"> <option value="#minute#"<cfif minute EQ TimeFormat(racetime,"mm")> selected</cfif>>#NumberFormat(minute,"00")#</option> </cfloop> </select> <input name="raceampm" type="radio" value="am"<cfif TimeFormat(racetime,"tt") EQ "am"> checked</cfif>> AM <input name="raceampm" type="radio" value="pm"<cfif TimeFormat(racetime,"tt") EQ "pm"> checked</cfif>> PM</td> </tr> <tr valign="top"> <td>Date:</td> <td> <select name="racemonth"> <cfloop index="month" from="1" to="12"> <option value="#month#"<cfif month EQ DateFormat(racetime,"m")> selected</cfif>>#MonthAsString(month)#</option> </cfloop> </select> <select name="raceday"> <cfloop index="day" from="1" to="31"> <option value="#day#"<cfif day EQ DateFormat(racetime,"d")> selected</cfif>>#day#</option> </cfloop> </select> <select name="raceyear"> <cfloop index="year" from="#Year(Now())#" to="#Year(Now())+3#"> <option value="#year#"<cfif year EQ DateFormat(racetime,"yyyy")> selected</cfif>>#year#</option> </cfloop> </select> </td> </tr> <tr valign="top"> <td>Active:</td> <td> <input name="active" type="radio" value="1"<cfif active EQ 1> checked</cfif>> Yes <input name="active" type="radio" value="0"<cfif active EQ 0> checked</cfif>> No</td> </tr> <tr valign="top"> <td>Description:</td> <td> <textarea name="description" rows="5" cols="40" wrap="soft">#description#</textarea> </td> </tr> <tr> <td colspan="2" align="right"><input type="submit" value="Edit Race"></td> </tr> </table> </form> </cfoutput> </body> </html>
This is the processing page that is executed when the Edit or Delete button is pressed in the Races table.
The code at the top of the page prevents access to this page for people who are not logged in. It also prevents direct access to this page by checking to make sure that raceid
exists in the FORM
scope.
The code then checks to see if the Delete button was clicked. If it was, then it deletes that record from the table and returns to the Races page:
The code then checks if the Edit button on this page was clicked.
Note that the Edit button on the Races page is not named, so it will not trigger the code above to run. It is triggered by the hidden field in the form on this same page (shown below).
This example uses the <cfqueryparam>
tag when deleting and editing to verify the data type
of FORM.raceid
. Using <cfqueryparam>
in this way is highly
recommended as it helps to protect your database from malicious
attacks.
This exercise involves making changes to three pages: ManagingData/Exercises/Resources.cfm, ManagingData/Exercises/ResourceAdd.cfm, and ManagingData/Exercises/ResourceEdit.cfm. The files have comments showing where to edit or add code.
<!--- If the user is not logged in, set userid to 0. Otherwise set userid to the user's userid as contained in the SESSION. ---> <cfparam name="URL.orderfield" default="resourceid"> <cfset orderfields = "resourcename, description, firstname"> <cfif NOT ListContains(orderfields,URL.orderfield)> <cfset URL.orderfield = "resourceid"> </cfif> <cfquery name="getresources" datasource="#APPLICATION.datasource#"> SELECT resourceid, resourcename, link, active, description, u.userid, firstname, lastname, email FROM Resources r, Users u WHERE u.userid = r.userid ORDER BY #URL.orderfield# </cfquery> <html> <head> <title>Running Resources</title> </head> <body> <h1>Running Resources</h1> <cfif userid NEQ 0> <a href="ResourceAdd.cfm">Add a resource</a> </cfif> <table border="1" cellpadding="3" cellspacing="0" id="maintable"> <!--- Make the table headers into links that, when clicked, sort the table on that field ---> <tr> <th>Resource</th> <th>Description</th> <th>Added by</th> <th>Edit/Delete</th> </tr> <cfoutput query="getresources"> <tr valign="top"> <td> <a href="#link#">#resourcename#</a> </td> <td>#description#</td> <td> <a href="mailto:#email#">#firstname# #lastname#</a> </td> <cfif Variables.userid EQ getresources.userid OR (isDefined("SESSION.role") AND SESSION.role EQ "admin")> <td> <form method="post" action="ResourceEdit.cfm" style="margin-bottom:0px"> <input type="hidden" name="resourceid" value="#resourceid#"> <input type="submit" value="EDIT" style="font-size:xx-small"> <input type="submit" name="deleting" value="DELETE" style="font-size:xx-small"> </form> </td> <cfelse> <td>PROTECTED</td> </cfif> </tr> </cfoutput> </table> <cfinclude template="Includes/Footer.cfm"> </body> </html>
<cfif NOT isDefined("SESSION.userid")> <cflocation url="index.cfm" addtoken="no"> </cfif> <cfparam name="FORM.resourcename" default=""> <cfparam name="FORM.link" default=""> <cfparam name="FORM.description" default=""> <html> <head> <title>Add a Resource</title> </head> <body> <cfif isDefined("FORM.submitted")> <!--- Write a <cfquery> to insert the resource entry into the database. The database fields are resourcename, link, active, userid, and description. ---> <p class="success">Resource Added.<br> <a href="resources.cfm">Return to Resource List</a>.</p> <h1>Add Another Resource</h1> <cfelse> <h1>Add Resource</h1> </cfif> <cfoutput> <form method="post" action="#CGI.SCRIPT_NAME#"> </cfoutput> <input type="hidden" name="submitted" value="true"> <cfoutput><input type="hidden" name="userid" value="#SESSION.userID#"></cfoutput> <table> <tr> <td>Resource Name:</td> <td><input name="resourcename" type="text" size="30"maxlength="50"></td> </tr> <tr> <td>URL:</td> <td><input name="link" type="text" size="30" maxlength="50"></td> </tr> <tr valign="top"> <td>Description:</td> <td> <textarea name="description" rows="5" cols="40" wrap="soft"></textarea> </td> </tr> <tr> <td colspan="2" align="right"><input type="submit" value="Add Resource"></td> </tr> </table> </form> <cfinclude template="Includes/Footer.cfm"> </body> </html>
<cfif NOT isDefined("SESSION.userid") OR NOT isDefined("FORM.resourceid")> <cflocation url="index.cfm" addtoken="no"> </cfif> <html> <head> <title>Edit Resource</title> </head> <body> <cfif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE DELETED> <!--- Write a <cfquery> to update the resource entry in the database. The database fields are resourcename, link, active, description, and resourceid. ---> <cflocation url="Resources.cfm" addtoken="no"> <cfelseif WRITE CONDITION TO CHECK IF RESOURCE SHOULD BE EDITED> <!--- Write a <cfquery> to update the resource entry in the database. The database fields are resourcename, link, active, description, and resourceid. ---> <p class="success">Resource Updated.<br> <a href="resources.cfm">Return to Resource List</a>.</p> <cfelse> <h1>Update Resource</h1> </cfif> <cfquery name="getresourceinfo" datasource="#APPLICATION.datasource#"> SELECT userid, resourcename, description, link, active FROM Resources WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer"> </cfquery> <cfoutput> <form method="post" action="#CGI.SCRIPT_NAME#"> <input type="hidden" name="editing" value="true"> <input type="hidden" name="resourceid" value="#FORM.resourceid#"> <table> <tr> <td>Resource Name:</td> <td><input name="resourcename" value="#getresourceinfo.resourcename#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>URL:</td> <td><input name="link" value="#getresourceinfo.link#" type="text" size="30" maxlength="50"></td> </tr> <tr> <td>Active:</td> <td> <input name="active" value="1" type="radio"<cfif getresourceinfo.active EQ 1> checked</cfif>>Yes <input name="active" value="0" type="radio"<cfif getresourceinfo.active EQ 0> checked</cfif>>No </td> </tr> <tr valign="top"> <td>Description:</td> <td> <textarea name="description" rows="5" cols="40" wrap="soft">#getResourceInfo.description#</textarea> </td> </tr> <tr> <td colspan="2" align="right"><input type="submit" value="Update Resource"></td> </tr> </table> </form> </cfoutput> <cfinclude template="Includes/Footer.cfm"> </body> </html>
<cfif isDefined("SESSION.userid")> <cfset userid = SESSION.userid> <cfelse> <cfset userid = 0> </cfif> ---- C O D E O M I T T E D ---- <tr> <th><a href="resources.cfm?orderfield=resourcename">Resource</a></th> <th><a href="resources.cfm?orderfield=description">Description</a></th> <th><a href="resources.cfm?orderfield=firstname">Added by</a></th> <th>Edit/Delete</th> </tr> ---- C O D E O M I T T E D ----
---- C O D E O M I T T E D ---- <cfif isDefined("FORM.submitted")> <cfquery datasource="#APPLICATION.datasource#"> INSERT INTO Resources (resourcename, link, description, active, userid) VALUES('#FORM.resourcename#','#FORM.link#','#FORM.description#',1,#SESSION.userid#) </cfquery> <p class="success">Resource Added.<br> <a href="resources.cfm">Return to Resource List</a>.</p> <h1>Add Another Resource</h1> <cfelse> ---- C O D E O M I T T E D ----
---- C O D E O M I T T E D ---- <cfif isDefined("FORM.deleting")> <cfquery datasource="#APPLICATION.datasource#"> DELETE FROM Resources WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer"> </cfquery> <cflocation url="Resources.cfm" addtoken="no"> <cfelseif isDefined("FORM.editing")> <cfquery datasource="#APPLICATION.datasource#"> UPDATE Resources SET resourcename = '#FORM.resourcename#', description = '#FORM.description#', link = '#FORM.link#', active = #FORM.active# WHERE resourceid = <cfqueryparam value="#FORM.resourceid#" cfsqltype="cf_sql_integer"> </cfquery> <p class="success">Resource Updated.<br> <a href="resources.cfm">Return to Resource List</a>.</p> <cfelse> ---- C O D E O M I T T E D ----