Validating Data Types

It is often not sufficient that input data merely exists; it must also have the right format. For example, a date field must have data in a date format. A salary field must have data in a numeric or currency format. There are many ways to ensure the validity of data, including the following methods:

Using the cfparam tag with the type attribute to validate any variable.
Using a form input tag with a hidden attribute to validate the contents of a form input field.
Using cfform controls that have validation attributes. (For information on using cfform tags, see Chapter 9, "Building Dynamic Forms".)
Using the cfqueryparam tag in a SQL WHERE clause to validate query parameters.

Note

The data validation discussed in this chapter is done by the ColdFusion Server. Validation using cfform tags is done using JavaScript in the user's browser, before any data is sent to the server.

Using cfparam to validate the data type

The cfparam type attribute lets you validate the type of a parameter. You can specify that the parameter type must be any of the following values:

Type value

Meaning

any
any value

array
any array value

binary
any binary value

boolean
true, false, yes, or no

date
any value in a valid date, time, or date-time format

numeric
any number

query
a query object

string
a text string or single character

struct
a structure

UUID
a Universally Unique Identifier (UUID) formatted as XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXX where X stands for a hexadecimal digit (0-9 or A-F).

variableName
a valid variable name

Type value	Meaning
any	any value
array	any array value
binary	any binary value
boolean	true, false, yes, or no
date	any value in a valid date, time, or date-time format
numeric	any number
query	a query object
string	a text string or single character
struct	a structure
UUID	a Universally Unique Identifier (UUID) formatted as XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXX where X stands for a hexadecimal digit (0-9 or A-F).
variableName	a valid variable name

For example, you can use the following code to validate the variable BirthDate:

<cfparam name="BirthDate" type="date">

If the variable is not in a valid date format, an error occurs and the page stops processing.

Validating form field data types

One limitation of standard HTML forms is that you cannot validate that users input the type or range of data you expect. ColdFusion enables you to do several types of data validation by adding hidden fields to forms.

The following table describes the hidden field suffixes that you can use to do validation:

Field Suffix

Value Attribute

Description

_integer
Custom error message
Verifies that the user entered a number. If the user enters a floating point value, it is rounded to an integer.

_float
Custom error message
Verifies that the user entered a number. Does not do any rounding of floating point values.

_range
MIN=MinValue
MAX=MaxValue
Verifies that the numeric value entered is within the specified boundaries. You can specify one or both of the boundaries separated by a space.

_date
Custom error message
Verifies that the user entered a date and converts the date into the proper ODBC date format. Will accept most common date forms; for example,
9/1/98; Sept. 9, 1998.

_time
Custom error message
Verifies that the user correctly entered a time and converts the time to the proper ODBC time format.

_eurodate
Custom error message
Verifies that the user entered a date in a standard European date format and converts into the proper ODBC date format.

Field Suffix	Value Attribute	Description
_integer	Custom error message	Verifies that the user entered a number. If the user enters a floating point value, it is rounded to an integer.
_float	Custom error message	Verifies that the user entered a number. Does not do any rounding of floating point values.
_range	MIN=MinValue MAX=MaxValue	Verifies that the numeric value entered is within the specified boundaries. You can specify one or both of the boundaries separated by a space.
_date	Custom error message	Verifies that the user entered a date and converts the date into the proper ODBC date format. Will accept most common date forms; for example, 9/1/98; Sept. 9, 1998.
_time	Custom error message	Verifies that the user correctly entered a time and converts the time to the proper ODBC time format.
_eurodate	Custom error message	Verifies that the user entered a date in a standard European date format and converts into the proper ODBC date format.

Note

Adding a validation rule to a field does not make it a required field. You need to add a separate _required hidden field if you want to ensure user entry.

The following procedure creates a simple form for entering a start date and a salary. It uses hidden fields to ensure that you enter data and that the data is in the right format.

This example illustrates another concept that might seem surprising. You can use the same CFML page as both a form page and its action page. Because the only action is to display the values of the two variables that you enter, the action is on the same page as the form.

Using a single page for both the form and action provides the opportunity to illustrate the use of the IsDefined function to check that data exists. This way, the form does not show any results until you submit the input.

To validate the data that users enter in the insert form:

Create a new page in ColdFusion Studio.

Enter the following text:

<html>

<head>

  <title>Simple Data Form</title>

</head>

<body>

<h2>Simple Data Form</h2>



<!--- Form part --->

<form action="datatest.cfm" method="Post">

  <input type="hidden" 

    name="StartDate_required" 

    value="You must enter a start date.">

  <input type="hidden" 

    name="StartDate_date" 

    value="Enter a valid date as the start date.">

  <input type="hidden" 

    name="Salary_required" 

    value="You must enter a salary.">

  <input type="hidden" 

    name="Salary_float" 

    value="The salary must be a number.">

  Start Date: 

  <input type="text" 

    name="StartDate" size="16" 

    maxlength="16"><br>

  Salary: 

  <input type="text" 

    name="Salary" 

    size="10" 

    maxlength="10"><br>

  <input type="reset" 

    name="ResetForm" 

    value="Clear Form">

  <input type="submit" 

    name="SubmitForm" 

    value="Insert Data">

</form>

<br>



<!--- Action part --->

<cfif isdefined("Form.StartDate")>

  <cfoutput>

    Start Date is: #DateFormat(Form.StartDate)#<br>

    Salary is: #DollarFormat(Form.Salary)#

  </cfoutput>

</cfif>

</html>

Save the file as datatest.cfm.
View the file in your browser, omit a field or enter invalid data, and click the Submit button.

When the user submits the form, ColdFusion scans the form fields to find any validation rules you specified. The rules are then used to analyze the user's input. If any of the input rules are violated, ColdFusion sends an error message to the user that explains the problem. The user then must go back to the form, correct the problem. and resubmit the form. ColdFusion does not accept form submission until the user enters the entire form correctly.

Because numeric values often contain commas and dollar signs, these characters are automatically deleted from fields with _integer, _float, or _range rules before they are validated and saved to a database.

Reviewing the code

The following table describes the code and its function:

Code

Description

<form action="actionpage.cfm"   method="post">
Gather the information from this form using the Post method, and do something with it on the page dataform.cfm, which is this page.

<input type="hidden"   name="StartDate_required"   value="You must enter a start date."> <input type="hidden"   name="StartDate_date"   value="Enter a valid date as the   start date.">
Require input into the StartDate input field. If there is no input, display the error information "You must enter a start date." Require the input to be in a valid date format. If the input is not valid, display the error information "Enter a valid date as the start date."

<input type="hidden"   name="Salary_required"   value="You must enter a salary."> <input type="hidden"   name="Salary_float"   value="The salary must be a number.">
Require input into the Salary input field. If there is no input, display the error information "You must enter a salary." Require the input to be in a valid number. If it is not valid, display the error information "The salary must be a number."

Start Date: <input type="text"   name="StartDate" size="16"   maxlength="16"><br>
Create a text box called StartDate in which users can enter their starting date. Make it exactly 16 characters wide.

Salary: <input type="text"   name="Salary"   size="10"   maxlength="10"><br>
Create a text box called Salary in which users can enter their salary. Make it exactly ten characters wide.

<cfif isdefined("Form.StartDate")>   <cfoutput>      Start Date is:       #DateFormat(Form.StartDate)#<br>     Salary is:       #DollarFormat(Form.Salary)#   </cfoutput> </cfif>
Output the values of the StartDate and Salary form fields only if they are defined. They are not defined until you submit the form, so they do not appear on the initial form. Use the DateFormat function to display the start date in the default date format. Use the DollarFormat function to display the salary with a dollar sign and commas.

Code	Description
<form action="actionpage.cfm" method="post">	Gather the information from this form using the Post method, and do something with it on the page dataform.cfm, which is this page.
<input type="hidden" name="StartDate_required" value="You must enter a start date."> <input type="hidden" name="StartDate_date" value="Enter a valid date as the start date.">	Require input into the StartDate input field. If there is no input, display the error information "You must enter a start date." Require the input to be in a valid date format. If the input is not valid, display the error information "Enter a valid date as the start date."
<input type="hidden" name="Salary_required" value="You must enter a salary."> <input type="hidden" name="Salary_float" value="The salary must be a number.">	Require input into the Salary input field. If there is no input, display the error information "You must enter a salary." Require the input to be in a valid number. If it is not valid, display the error information "The salary must be a number."
Start Date: <input type="text" name="StartDate" size="16" maxlength="16"><br>	Create a text box called StartDate in which users can enter their starting date. Make it exactly 16 characters wide.
Salary: <input type="text" name="Salary" size="10" maxlength="10"><br>	Create a text box called Salary in which users can enter their salary. Make it exactly ten characters wide.
<cfif isdefined("Form.StartDate")> <cfoutput> Start Date is: #DateFormat(Form.StartDate)#<br> Salary is: #DollarFormat(Form.Salary)# </cfoutput> </cfif>	Output the values of the StartDate and Salary form fields only if they are defined. They are not defined until you submit the form, so they do not appear on the initial form. Use the `DateFormat` function to display the start date in the default date format. Use the `DollarFormat` function to display the salary with a dollar sign and commas.

Checking query parameters with cfqueryparam

You can use the cfqueryparam tag to validate SQL query parameters. This tag can validate the value of the SQL query parameter against a SQL data type such as REAL, TIME, or DATE. The cfqueryparam tag validates the data as follows:

If the value does not match the data type, the tag returns an error message.
If the value matches the data type and the database driver supports data bind parameters, the tag generates a SQL BIND PARAMETER statement to bind the parameter.
If the database driver does not support bind parameters, the tag just uses the parameter value in the query string.

The cfqueryparam tag can also validate parameter value length and its number of decimal places.

Note

The cfqueryparam tag allows you to specify SQL parameters in queries. It improves performance, maintenance, and security of data queries by improving server-side caching for Oracle databases, supporting updating of long text fields from a SQL statement, and preventing a malicious user from attaching multiple SQL statements to a SQL statement substitution variable. For more information on cfqueryparam and its use, see the CFML Reference.

The cfqueryparam tag can have any of several additional advantages, depending on the database system and Web server software that you are using:

Some Web servers have security issues in which SQL appended to URL strings can evade system security. cfqueryparam can help prevent this problem.
Some database management systems, including some Oracle releases, limit the size of query text fields to 4K bytes. By using cfqueryparam you can overcome this limitation.
Using cfqueryparam can speed database processing by using bind parameters.

The following example shows the use of cfqueryparam when valid input is given in the Course_ID variable used as a query parameter. To see what happens when you use invalid data, substitute a text string such as "test" for the integer 12 in the cfset statement.

Note that this example uses the cfsnippets database that is provided with ColdFusion, not the CompanyInfo database used in most of this book.

<html>

<head>

<title>cfqueryparam Example</title>

</head>



<body>

<h3>cfqueryparam Example</h3>

<cfset course_id=12>

<cfquery name="getFirst" datasource="cfsnippets">

    SELECT * 

    FROM courses

    WHERE Course_ID=<cfqueryparam value="#Course_ID#"

    cfsqltype="CF_SQL_INTEGER"> 

</cfquery>

<cfoutput query="getFirst">

<p>

Course Number: #number#<br>

 Description: #descript#

</p>

</cfoutput>

</body>

</html>

Reviewing the code

The following table describes the code and its function:

Code

Description

<cfset Course_ID=12>>
Set the course_ID variable to 12.

<cfquery name="getFirst" DataSource="cfsnippets">
Query the cfsnippets data source and return the results in the getFirst query object.

SELECT * FROM courses
Select all columns from the courses table.

WHERE Course_ID=<cfqueryparam value="#Course_ID#" cfsqltype="CF_SQL_INTEGER"> </cfquery>
Only select rows where the Course_ID column equals the value of Course_ID CFML local variable. Validate that the variable value is an integer and, if appropriate for the database driver, use a bind parameter to associate the value with the SQL statement.

<cfoutput query="getFirst"> <p> Department Number: #number#<br> Description: #descript# </p> </cfoutput>
For each row that matches the query, output the contents of the number and descript columns.

Developing ColdFusion Applications
Making Variables Dynamic

Code	Description
<cfset Course_ID=12>>	Set the course_ID variable to 12.
<cfquery name="getFirst" DataSource="cfsnippets">	Query the cfsnippets data source and return the results in the getFirst query object.
SELECT * FROM courses	Select all columns from the courses table.
WHERE Course_ID=<cfqueryparam value="#Course_ID#" cfsqltype="CF_SQL_INTEGER"> </cfquery>	Only select rows where the Course_ID column equals the value of Course_ID CFML local variable. Validate that the variable value is an integer and, if appropriate for the database driver, use a bind parameter to associate the value with the SQL statement.
<cfoutput query="getFirst"> <p> Department Number: #number#<br> Description: #descript# </p> </cfoutput>	For each row that matches the query, output the contents of the number and descript columns.