Checks the data type of a query parameter. The cfqueryparam
tag is nested within a cfquery
tag. It is embedded within the query SQL statement. If you specify its optional parameters, cfqueryparam
also performs data validation.
<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" dbName = "database name" 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
Note For data, you must specify the |
The cfqueryparam
tag does the following:
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 performs validation and substitutes the validated parameter value back into the string. If validation fails, an error message is returned. The validation rules follow:
CF_SQL_SMALLINT
, CF_SQL_INTEGER
, CF_SQL_REAL
, CF_SQL_FLOAT
, CF_SQL_DOUBLE
, CF_SQL_TINYINT
, CF_SQL_MONEY
, CF_SQL_MONEY4
, CF_SQL_DECIMAL
, CF_SQL_NUMERIC
, and CF_SQL_BIGINT
, data values can be converted to a numeric value.
CF_SQL_DATE
, CF_SQL_TIME
and CF_SQL_TIMESTAMP
, data values can be converted to a date supported by the target data source.maxLength
attribute is used, data value cannot exceed the maximum length specified.The SQL syntax generated by the ColdFusion server is dependent on the target database. For an ODBC, DB2, or Informix data source, the generated syntax of the SQL statement is as follows:
SELECT *
FROM courses WHERE col1 = ?
For an Oracle 7 or Oracle 8 data source, the syntax of the SQL statement is as follows:
SELECT *
FROM courses WHERE col1 = :1
For a Sybase11 data source, the syntax of the SQL statement is as follows:
SELECT *
FROM courses WHERE col1 = 10
<!-------------------------------------------------------------------- This example shows the use of cfqueryparam when valid input is given in Course_ID. ----------------------------------------------------------------------> <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: #Course_Num#<br> Description: #descript# </P> </cfoutput> </body> </html> <!--------------------------------------------------------------------- This example shows the use of cfqueryparam when invalid numeric data is in Course_ID. ----------------------------------------------------------------------> <html> <head> <title>cfqueryparam Example</title> </head> <body> <h3>cfqueryparam Example With Bad Numeric Data</h3> <cfset Course_ID = "12; DELETE courses WHERE Course_ID = 20"> <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_num#<br> Description: #descript# </P> </cfoutput> </body> </html>
The cfqueryparam
tag returns the following error message when this example is executed.
VALUE
Invalid data '12; DELETE courses WHERE Course_ID = 20' for CFSQLTYPE 'CF_SQL_INTEGER'. <!--------------------------------------------------------------------- This example shows the use of cfqueryparam when invalid string data is in Course_ID. ----------------------------------------------------------------------> <html> <head> <title>cfqueryparam Example</title> </head> <body> <h3>cfqueryparam Example with Bad String Input</h3> <cfset LastName = "Peterson; DELETE employees WHERE LastName = 'Peterson'"> <---------------------------------------------------------------------- For string input, 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> </body> </html>
The cfqueryparam
tag returns the following error message when this example is executed.
VALUE
Invalid data 'Peterson; DELETE employees WHERE LastName = 'Peterson'' value exceeds maxLength setting '17'.