Database Access and Authentication
- 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.
| 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.
| 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>
---- Code Omitted ----
Exercise: Creating a Registration Page
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.
- Open DatabaseBasics/Exercises/Register.cfm in your editor. Much of the file is already complete.
- 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.
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:
Exercise: Using <cfoutput> to Display Query Results
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.
- Open DatabaseBasics/Exercises/cfoutput.cfm in your editor.
- Fix the code so that each record is displayed as a row as shown in the screenshot below:
Database Access and Authentication Conclusion
In this lesson of the ColdFusion tutorial, you have learned how to connect to a database to insert and select records and to use this ability to create registration and login forms. Unfortunately, as it is written currently, only the pages themselves are protected. To protect the whole site in this manner, we would have to force the user to log in to every page. That might frustrate our visitors a bit. You will need to learn about session management to allow the user to stay logged in.