Using the CFQUERYPARAM Tag


Using the <CFQUERYPARAM> Tag

Some database-management systems allow you to send multiple SQL statements in a single query. Certain security issues arise when you pass parameters in a query string. In many IDEs, including ColdFusion, a dynamic query can append malicious SQL statements to existing queries.

When you allow a query string to pass a parameter, such as the URL, you need to ensure that only the expected information is passed. For example, the following ColdFusion query contains a WHERE clause, which selects those database entries that match the last name specified in the LName field of a form:

 <CFQUERY name="GetDetails" datasource="EmployeeData">     SELECT FName, LName, Salary     FROM Employee     WHERE LName='#Form.LName#' </CFQUERY> 

You can delete all the rows of the Employee table by using the following URL:

 http://friendlyserver/page.cfm?Empl_ID=7%20DELETE%20FROM%20Employee 

As a result, ColdFusion executes the following query:

 <CFQUERY="GetDetails" datasource="EmployeeData">     SELECT * FROM Employee     WHERE Empl_ID = 12 DELETE FROM Employee </CFQUERY> 

Use the <CFQUERYPARAM> tag to evaluate query string parameters and pass a ColdFusion variable within a SQL statement. This tag evaluates variable values before they reach the database and specifies the data type of the corresponding database column in the CFSQLTYPE attribute.

Since the Empl_ID column in the CompanyInfo data source is an integer, specify a CFSQLTYPE attribute as CF_SQL_INTEGER:

 <CFQUERY name="EmplList" datasource="EmployeeData">     SELECT *    FROM Employee     WHERE Empl_ID = <CFQUERYPARAM value = "#Empl_ID#"                                      CFSQLTYPE = "cf_sql_integer"> </CFQUERY> 

The <CFQUERYPARAM> tag checks whether the value of Empl_ID is an integer data type. If the query string has any value that's not an integer, the tag doesn't execute. It returns the following error message:

 Invalid data '12 DELETE FROM Employee' for CFSQLTYPE 'CF_SQL_INTEGER'. 

When passing a variable containing a string to a query, specify CF_SQL_CHAR for the CFSQLTYPE attribute. For example:

 <CFQUERY name = "getData" dataSource = "cfcode">     SELECT * FROM employees     WHERE LName = <CFQUERYPARAM value = "#LName#"                                   CFSQLTYPE = "cf_sql_char" maxLength = "10"> </CFQUERY> 

In this case, <CFQUERYPARAM> ensures that the LName variable contains a string that doesn't have more than 10 characters. It escapes the string with single quotes so that it appears as a single value to the database.




Macromedia ColdFusion MX. Professional Projects
ColdFusion MX Professional Projects
ISBN: 1592000126
EAN: 2147483647
Year: 2002
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net