Skip to main content

Using CFQueryParam in Order By Clause

Image
By
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>
Labels:

Comments

  1. UnknownAugust 18, 2008 at 3:08 PM

    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. Zac SpitzerAugust 19, 2008 at 4:03 AM

    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. Dan G. Switzer, IIAugust 19, 2008 at 9:52 AM

    Instead of a switch, I prefer:

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

    ReplyDelete
  4. Joey KrabacherSeptember 3, 2008 at 2:06 PM

    This comment has been removed by the author.

    ReplyDelete
  5. Joey KrabacherSeptember 3, 2008 at 2:12 PM

    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. UnknownSeptember 10, 2010 at 5:15 AM

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

    ReplyDelete
  7. AnonymousNovember 2, 2017 at 2:10 PM

    This comment has been removed by a blog administrator.

    ReplyDelete

Post a Comment

Popular posts from this blog

Dyson AM09 Fan & Heater H2 Error

By KraFusion
No idea what the actual error is and I couldn't find anything useful on the web, so hopefully this will help someone else. I assumed that the H2 error meant that something was dirty, clogged up or that it was overheating because it was dirty or clogged up because the error only showed up when it was in Heater mode. The heater would run for about 30 seconds, then it would show the error and switch over to the high speed fan , I assume to try to blow out the dust. I proceeded to open it up to give it a deep clean because the Dyson instructions for cleaning this thing are ridiculous and don't help at all. Gently wiping down the outside and vacuuming the intake holes....really Dyson, really?! I used cotton swabs and 91% alcohol to clean everything I could get to, starting at the base (in hindsight, this part may not be necessary at all). Then I got to the top of the device where the actual air comes out of and noticed that there was a lot of build up on the heater coils. I could...
26 comments
Read more

ColdFusion to Node.js Conversion : Setup Part 1

By KraFusion
This multi-part post will be community comment driven, which basically means that in an effort to not spend too much time on details that might not matter to the viewers out there, I am only going to share the details which I think are important at the time of publishing the posts. If people (or bots) comment and want more details, I will add them to the original post. As always, constructive criticism is always welcome. Since I am currently using the CFWheels framework (which is inspired by Ruby on Rails or RoR), I chose to try out the Sails.js framework for Node.js (which is also similar to Rails). 1st thing I did was get Node and Apache working together by adding this to my Apache vhost(VirtualHost) block: ProxyRequests on ProxyPass / http://localhost:8124/ And adding this to my hosts file (/etc/hosts): 127.0.0.1 mynewapp.node www.mynewapp.node This allows me to setup an alias in my host file and reference the URL as you normally would without adding the portnumber o...
Post a Comment
Read more

ColdFusion Invalid Image Format Solution

By
For those who have gotten the following error: "java.awt.color.CMMException: Invalid image format" and tried the solutions posted here with no avail. We are going to use the power of Java's JAI (Java Advanced Imaging) library to tackle this one. <cfscript> //path to image imagePath = "pathToImage"; //create java file object, passing in path to image imageFile = createObject("java","java.io.File").init(imagePath); //create a FileSeekableStream, passing in the image file we created fss = createObject("java","com.sun.media.jai.codec.FileSeekableStream").init(imageFile); //create ParameterBlock object and initialize it (call constructor) pb = createObject("java","java.awt.image.renderable.ParameterBlock").init(); //pass in FileSeekableStream pb.add(fss); //create JAI object that will ultimately do the magic we need JAI = createObject("java","javax.media.jai.JAI"); //...
8 comments
Read more