|
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.
|