When executing queries, the use of queryparams to bind values to placeholders in SQL statments are crutial, both for performance reasons and for security reasons. See the online documentation for cfquery for more information about security issues relating to not using queryparams in dynamic sql statments. First we will review how to use queryparams, then we will review some examples of how to use them compaired to inserting coldfusion values directly into SQL.
Here is a simple example:
SELECT name FROM users WHERE id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_VARCHAR">
Referring to the online documentation for queryparams, VALUE is a requried attribute, and even though CFSQLTYPE is not a required attribute, I've specified it here. You should always specify the type of value you are referencing and the type should match the column type you have defined in the database. The basic rule of thumb is to use one of the followin 3 types: CF_SQL_VARCHAR for string values, CF_SQL_NUMERIC for numbers and CF_SQL_DATE for date values. We will discuss the numeric and date types later in this document, for now lets stick to the string values. So, what is the big deal here, how is the above cfqueryparm diffent from the following SQL statement:
SELECT name FROM users WHERE id = #id#
Queryparms take advantage of a special way that database can insert values directly into compiled SQL statements. So what is happening here is that the first statement is parsed as SQL and compiled WITHOUT the value specified in the queryparam. Once the SQL is compiled and transformed into a PLAN, the database can then quickly push the value into the compiled plan and execute the statement. The plan will know that the value being considered is a numeric value, and does the appropriate type checking on execution of the query. Non numeric values will throw an error at run time. The best thing about using the cfqueryparam tag is that the tag itsself will check the value to determine if it matches the defined type before the statement is ever passed to the database, so here non numeric values will throw a coldfusion error before anything gets to the database.
The second example, the coldfusion variable is evaluated BEFORE the SQL statement ever gets to the database. This means that there is no type checking done by coldfusion or the database on the value before it is compiled into a plan. This also means that if the coldfusion variable contains a call to a stored procedure, it could be executed in the simple sql statement above. Assuming that the variable ID contains the value "1 DELETE FROM users;", then this statement would be sent do the database and compiled for execution. The resulting SQL would look like this:
SELECT name FROM users WHERE id = 1 DELETE FROM USERS;
When using MSSQL and ODBC drivers, this will execute and delete everything from the users table. Refer to the Macromedia security bulletin for more information. As you can see, this is just one simple example of the types of exploits that can occour using this method for generating SQL. If we had used queryparams, coldfusion would have just thrown an error. The bad statement would have never reached the database to do any harm.