Most databases provide a mechanism to bind SQL parameters to specific datatypes. Doing so prevents the database (and database drivers) from having to perform implicit type conversions. The major benefits of parameter binding are:
Parameter binding requires that a parameter be specified along with a datatype with which it is to be bound. The following are the names of the ColdFusion-supported types:
ColdFusion parameter binding is implemented via the <cfqueryparam> tag, which must be used in between <cfquery> and </cfquery> tags. The following query uses <cfqueryparam> to bind a URL parameter to a specific datatype (an integer): <cfquery datasource="DSN" name="Product"> SELECT product_id, product_name FROM products WHERE product_id=<cfqueryparam value="#URL.product_id#" cfsqltype="CF_SQL_INTEGER"> </cfquery> The <cfqueryparam> cfsqltype attribute defaults to CF_SQL_CHAR, if not specified. Additional attributes may be used optionally to specify value lengths and ranges. CAUTION SQL query parameter binding is not supported by all databases and database drivers. Refer to the ColdFusion documentation to determine whether your database is supported. NOTE The exact syntax used by the database for query parameter binding varies dramatically from one database to the next. Fortunately, this is handled internally by the <cfqueryparam> tag so that you need not worry about it. |