Checks the data type of a query parameter. This tag is nested within a cfquery
tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation.
Macromedia recommends that you use the cfqueryparam
tag within every cfquery
tag, to help secure your databases from unauthorized users. For more information, see:
<cfquery name = "query_name" dataSource = "ds_name" ...other attributes... SELECT STATEMENT WHERE column_name = <cfqueryparam value = "parameter value" CFSQLType = "parameter type" maxLength = "maximum parameter length" scale = "number of decimal places" null = "Yes" or "No" list = "Yes" or "No" separator = "separator character"> AND/OR ...additional criteria of the WHERE clause... </cfquery>
cfinsert,
cfprocparam,
cfprocresult,
cfquery,
cfstoredproc,
cftransaction,
cfupdate
For data, to ensure that validation is enforced, you must specify the maxlength
attribute.
The ColdFusion ODBC, DB2, Informix, Oracle 7 and Oracle 8 drivers support SQL bind parameters. The ColdFusion Sybase 11 driver and Sybase native driver do not support SQL bind parameters.
If a database does not support bind parameters, ColdFusion validates and substitutes the validated parameter value back into the string. If validation fails, it returns an error message.
The validation rules are as follows:
maxLength
attribute is used, a data value cannot exceed the maximum length specified.The SQL syntax that the ColdFusion server generates depends on the target database. For an ODBC, DB2, or Informix data source, the syntax is as follows:
SELECT *
FROM courses WHERE col1 = ?
For an Oracle 7 or Oracle 8 data source, the syntax is as follows:
SELECT *
FROM courses WHERE col1 = :1
For a Sybase11 data source, the syntax is as follows:
SELECT *
FROM courses WHERE col1 = 10
<!--- This example shows cfqueryparam with VALID input in Course_ID. ---> <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: #Course_ID#<br> Description: #descript#</p> </cfoutput> <!--- This example shows the use of CFQUERYPARAM when INVALID string data is in Course_ID. ----> <p>This example throws an error because the value passed in the CFQUERYPARAM tag exceeds the MAXLENGTH attribute</p> <cfset LastName="Peterson; DELETE employees WHERE LastName='Peterson'"> <!------- Note that for string input you must specify the MAXLENGTH attribute for validation. --------------------------------------------------> <cfquery name="getFirst" datasource="cfsnippets"> SELECT * FROM employees WHERE LastName=<cfqueryparam value="#LastName#" cfsqltype="CF_SQL_VARCHAR" maxlength="17"> </cfquery> <cfoutput query="getFirst"> <p> Course Number: #FirstName# #LastName# Description: #Department# </p> </cfoutput>