Monday, August 18, 2008

Using CFQueryParam in Order By Clause

With all of the SQL Injection attacks going on in the ColdFusion world I thought that it may be beneficial to show everyone a way that I know of to have cfqueryparam'd 'Order By' clauses. If anyone knows of other ways to accomplish this, please feel free to post a comment on how to do so.



<cfquery name="GetData" datasource="#APPLICATION.DataSource#">
DECLARE @param varchar(25),
   @dataID varchar(25),
   @sql nvarchar(700);

SELECT @param=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.orderby#">,
   @dataID=<cfqueryparam cfsqltype="cf_sql_varchar" value="#VARIABLES.DataID#">,
   @sql = 'SELECT ID,
      Phone_Number,
      First_Name,
      Last_Name,
      Address
   FROM Table WITH (NOLOCK)
   WHERE DataID = ' + @dataID + 'Order by ' + @param + ' asc';

EXEC sp_executesql @sql

</cfquery>