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 ----
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:
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.