facebook google plus twitter
Webucator's Free ColdFusion MX Tutorial

Lesson: Database Access and Authentication

Welcome to our free ColdFusion MX tutorial. This tutorial is based on Webucator's Comprehensive ColdFusion Training course.

Lesson Goals

  • To create a login form and authenticate users.
  • To use <cfquery> to send queries to a database and store result sets.
  • To use <cfoutput> to output query results.

A Database-less Login Form

Below is a simple login form that hardcodes the username and password.

Code Sample:

DatabaseBasics/Demos/Login-noDB.cfm
               <cfif isDefined("FORM.submitted")>
	<cfif FORM.email EQ "itsme@webucator.com" AND password EQ "password">
		<cflocation url="index.cfm" addtoken="no">
	</cfif>
</cfif>
<cfparam name="FORM.email" default="">
<html>
<head>
<title>Login Page</title>
</head>
<body>

<h2>Log in</h2>
<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 align="right" colspan="2">
		<input type="submit" value="Log in">
		</td>
	</tr>
	<tr>
		<td colspan="2">
			<br><a href="Register.cfm">Register</a>
		</td>
	</tr>
	</table>
</form>

</body>
</html>

As you can see, this page submits to itself. If the user has not yet submitted the form, the form is displayed. If the user submits the form with the correct email and password, the page is redirected to the home page.

<cfquery>

ColdFusion has a special data type called query. Recordsets returned from a database are of this data type and are called "queries".

The <cfquery> tag is used to send queries to a database and to store the results returned in a query variable.

<cfquery> Attributes
Attribute Description
name Name of query.
datasource Name of data source.
dbtype Only possible value is "query". Used with query of queries.
username Overrides username set up in ColdFusion Administrator.
password Overrides password set up in ColdFusion Administrator.
maxrows Maximum number of rows to return in record set.
blockfactor Maximum rows to get at a time from server.
timeout Number of seconds that each action of a query is permitted to execute before returning an error.
cachedafter Date value specifying when to drop query from cache.
cachedwithin Timespan for which to hold query in cache.
debug Turns debugging display on or off.

The query object created by this tag has the following properties.

Query Properties
Property Description
currentRow Current record of the query being processed.
columnList Comma-delimited list of column names.
recordCount Number of records returned by query.
executionTime Time it took to execute query.

You can find out how long a query takes to process by reading the cfquery.executionTime variable.

This following example is very similar to the previous one, except that the valid username and password are not hardcoded in the script but instead are searched for in a database.

Code Sample:

DatabaseBasics/Demos/Login.cfm
               <cfif isDefined("FORM.submitted")>
	<cfquery name="logincheck" datasource="runners">
		SELECT FirstName, LastName, Email FROM Users
		WHERE email='#FORM.email#'
				AND password='#FORM.password#'
	</cfquery>
	<cfif logincheck.RecordCount>
		<cflocation url="index.cfm" addtoken="no">
	</cfif>
</cfif>
---- C O D E   O M I T T E D ----

Creating a Registration Page

Duration: 20 to 30 minutes.

In this exercise, you will create a self-submitting registration page that displays a registration form the first time a user visits the page and processes the form when the user submits it.

  1. Open DatabaseBasics/Exercises/Register.cfm in your editor. Much of the file is already complete.
  2. In the main <cfelse> block follow the directions in the comments. You will write code that:
    • Validates the passwords.
    • Inserts the new record into the database (the fields in the database have the same names as those in the form: firstname, lastname, email, password).

Code Sample:

DatabaseBasics/Exercises/Register.cfm
<html>
<head>
<title>Register</title>
</head>
<body>

<cfif NOT isDefined("FORM.submitted")>

	<h2>Registration Form</h2>
	<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" size="30">
		</td>
	</tr>
	<tr>
		<td>Password:</td>
		<td>
			<input type="password" name="password" size="10">
		</td>
	</tr>
	<tr>
		<td>Repeat Password:</td>
		<td>
			<input type="password" name="password2" size="10">
		</td>
	</tr>
	<tr>
		<td>First name:</td>
		<td>
			<input type="text" name="firstname" size="10">
		</td>
	</tr>
	<tr>
		<td>Last name:</td>
		<td>
			<input type="text" name="lastname" size="10">
		</td>
	</tr>
	<tr>
		<td colspan="2" align="center">
			<input type="submit" value="Register">
		</td>
	</tr>
	</table>
	</form>
<cfelse>
	
<!---
	Write an if statement that checks to make
	sure the passwords are the same.
--->
	<cfif WRITE_CONDITION_HERE>
		<!---
			Write a query that inserts the new record
			into the Users table. The fields in the
			database have the same names as those in
			the form: firstname, lastname, email, password
		--->
		<cfquery datasource="Runners">
		
		</cfquery>
		You have registered successfully.
			<p><a href="index.cfm">Home Page</a></p>
	<cfelse>
		<p class="errors"><b>Your passwords
		do not match. Please <a href=
		"Register.cfm">try again</a>.</p>
	</cfif>
</cfif>

</body>
</html>

Write code on the registration page that first checks to see if that email is already in the Users table. If it is, let the user know she is already registered and do not insert a new record.

Solution:

DatabaseBasics/Solutions/Register.cfm
---- C O D E   O M I T T E D ----
<cfelse>
	
	<cfif FORM.password EQ FORM.password2>
		<cfquery datasource="runners">
			INSERT INTO Users
			(firstname, lastname, email, password)
			VALUES ('#FORM.firstname#', '#FORM.lastname#', '#FORM.email#', '#FORM.password#')
		</cfquery>
		You have registered successfully.
			<p><a href="index.cfm">Home Page</a></p>
	<cfelse>
		<p class="errors"><b>Your passwords
		do not match. Please <a href=
		"Register.cfm">try again</a>.</p>
	</cfif>
</cfif>

</body>
</html>

Challenge Solution:

DatabaseBasics/Solutions/Register-challenge.cfm
---- C O D E   O M I T T E D ----
<cfelse>
	
	<cfif FORM.password EQ FORM.password2>
		<cfquery name="emailcheck" datasource="runners">
			SELECT *
			FROM Users
			WHERE email='#FORM.email#'
		</cfquery>
		<cfif emailcheck.RecordCount EQ 0>
			<cfquery datasource="runners">
				INSERT INTO Users
				(firstname, lastname, email, password)
				VALUES ('#FORM.firstname#', '#FORM.lastname#', '#FORM.email#', '#FORM.password#')
			</cfquery>
			
			<cfquery name="emailcheck2" datasource="runners">
				SELECT *
				FROM Users
				WHERE email='#FORM.email#'
			</cfquery>
			<cfif emailcheck2.RecordCount NEQ 1>
				Registration Failed
			<cfelse>
				You have registered successfully.
				<p><a href="index.cfm">Home Page</a></p>
			</cfif>
		
		<cfelse>
			<p>It appears you have already registered.</p>
		</cfif>
	<cfelse>
		<p class="errors"><b>Your passwords
		do not match. Please <a href=
		"Register.cfm">try again</a>.</p>
	</cfif>
</cfif>

</div>
</body>
</html>

Outputting Database Data

We have used <cfquery> to check for the existence of a record and to insert a new record. It is also often used to select a group of records, known as a recordset, and output them to the web page. The most common way to output the data is to use the <cfoutput> tag with its query attribute set to the relevant query name. The following example illustrates this.

Code Sample:

DatabaseBasics/Demos/cfoutput.cfm
<cfquery name="getUsers" datasource="#APPLICATION.datasource#">
	SELECT firstname, lastname, email
	FROM Users
</cfquery>

<html>
<head>
<title>Using cfoutput</title>
</head>
<body>
<ul>
<cfoutput query="getUsers">
	<li>#firstname#	#lastname# (#email#)</li>
</cfoutput>
</ul>
</body>
</html>

The output of this page is shown below:

Using <cfoutput> to Display Query Results

Duration: 10 to 20 minutes.

In this exercise, you will modify the demo we have just seen so that the records are output as a table rather than as a list.

  1. Open DatabaseBasics/Exercises/cfoutput.cfm in your editor.
  2. Fix the code so that each record is displayed as a row as shown in the screenshot below:

Solution:

DatabaseBasics/Solutions/cfoutput.cfm
<cfquery name="getUsers" datasource="#APPLICATION.datasource#">
	SELECT firstname, lastname, email
	FROM Users
</cfquery>

<html>
<head>
<title>Using cfoutput</title>
</head>
<body>
<table border="1">
<tr>
	<th>FirstName</th>
	<th>LastName</th>
	<th>Email</th>
</tr>
<cfoutput query="getUsers">
	<tr>
		<td>#firstname#</td>
		<td>#lastname#</td>
		<td><a href="mailto:#email#">#email#</a></td>
	</tr>
</cfoutput>
</table>
</body>
</html>