Using form data to generate SQL statements

As described in previous chapters, you can retrieve a record for every employee in a database table by composing a query like the following:

<cfquery name="GetEmployees" datasource="cfdocexamples">
   SELECT  FirstName, LastName, Contract
   FROM   Employee
</cfquery>

When you want to return information about employees that matches user search criteria, you use the SQL WHERE clause with a SQL SELECT statement. When the WHERE clause is processed, it filters the query data based on the results of the comparison.

For example, to return employee data for only employees with the last name of Smith, you build a query that looks like the following:

<cfquery name="GetEmployees" datasource="cfdocexamples"> 
   SELECT FirstName, LastName, Contract
   FROM    Employee
   WHERE  LastName = 'Smith'
</cfquery>

However, instead of putting the LastName directly in the SQL WHERE clause, you can use the text that the user entered in the form for comparison:

<cfquery name="GetEmployees" datasource="cfdocexamples">
   SELECT FirstName, LastName, Salary
   FROM Employee
   WHERE LastName=<cfqueryparam value="#Form.LastName#" 
CFSQLType="CF_SQL_VARCHAR"> 
</cfquery>

For security, this example encapsulates the form variable within the cfqueryparam tag to ensure that the user passed a valid string value for the LastName. For more information on using the cfqueryparam tag with queries and on dynamic SQL, see Accessing and Retrieving Data.

View comments in LiveDocs