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

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

Fixing DNS issue on MacBook Pro

By
This may be an issue with 10.6(Snow Leopard) altogether or just with the MacBook Pros, but every once in a while the internal DNS settings get hung up or crap out or just stop working! You're connected to the internet, you've checked the connections, you've got on other devices , you've asked co-workers if they are having issues with the internet, you've pulled your hair out, banged your head against the wall, you get my drift. Whatever you do or try your $2000 MacBook Pro will not do one simple thing CONNECT TO THE INTERNET!! Hopefully this solution will work for some of you and save the hair on your head. Run these commands from terminal(copy command from sudo to plist): sudo launchctl unload /System/Library/LaunchDaemons/com.apple.mDNSResponder.plist THEN sudo launchctl load /System/Library/LaunchDaemons/com.apple.mDNSResponder.plist and bingo bango everything is just peachy again!
8 comments
Read more