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>

7 comments:

  1. For myself, if I'm allowing a variable to be passed in for the ORDER BY in a SQL Statement, I typically will use a cfswitch and cfcase and just have a cfdefaultcase.

    <cfquery name="Products" datasource="#getDSN()#">

    SELECT ProductID, Title, Price, Rating

    FROM Products

    WHERE

    CategoryID=<cfqueryparam value='#url.categoryID#' cfsqltype="cf_sql_idstamp">

    ORDER BY

    <cfswitch expression="#url.sortBy#">

    <cfcase value="Price">Price</cfcase>

    <cfcase value="Title">Title</cfcase>

    <cfcase value="Rating">Rating</cfcase>

    <cfdefaultcase>Rating</cfdefaultcase>

    </cfswitch>

    </cfquery>

    ReplyDelete
  2. I like the switch approach better.. depending on your database, using a bound variable will affect (or not affect) the query plan, producing a different outcome peformance wise than a static column

    ReplyDelete
  3. Instead of a switch, I prefer:

    order by
    <cfif listFindNoCase("col1,col2,col3", url.sort)>
    #url.sort#
    <cfelse>
    col1
    </cfif>

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Thanks for the alternatives guys. I like the listfindnocase() approach better since it is the shortest. The beauty of the cfif and cfswitch methods is that you can also do the logic outside of the query if you do not like putting logic inside the cfquery block. Like...

    <cfif listFindNoCase("col1,col2,col3", url.sort)>
    <cfset VARIABLES.OrderBy = "ORDER BY #url.sort#">
    <cfelse>
    <cfset VARIABLES.OrderBy = "ORDER BY col1">
    </cfif>

    <cfquery ...>
    SELECT *
    FROM table
    where 0 = 0
    #VARIABLES.OrderBy#
    </cfquery ...>

    ReplyDelete
  6. Good hoot. Keep it up will visit your blog again for more valuable information’s…
    ColdFusion Developer

    ReplyDelete