User Guide

94 Chapter 6 Making Variables Dynamic
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.
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