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

Replacing your HDD with a SSD in a Mid-2009 Mac

By KraFusion
What you'll need: T6 screwdriver PH000 philips screwdriver SATA to USB connector ( or SATA to Thunderbolt if you feel like spending the cash) Apples DiskUtility Application 2.5 inch SATA SSD (e.g. Samsung 850 EVO), size is up to you Before we swap the drives out we want to test our speeds so that we can compare with what we get afterwards. You can run the below commands a few times, then take an average if you want a better representation. Write Speed (16GB file, lower the values of the bs and count params for a smaller file) 52.872 MB/sec write speed Read Speed 50.6726 MB/sec read speed Also, in the Disk tab of the ActivityMonitor you can see a real-time measurement of reads/writes per second, which where an approximate match to the values I got above. Hook up the new drive The next thing to do is clone the current disk over to the SDD. I used a SATA to USB connector I found online to connect it to the Mac and I used the DiskUtility app to clone the disk. Onc
Post a Comment
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