Skip to main content
N
neve_m
Known Participant
October 1, 2009
Answered

Passing dates to cfc?

  • October 1, 2009
  • 4 replies
  • 4532 views

I'm just starting to write cfcs and I am a bit puzzled by why my dates don't work if I pass them through an argument, but they work fine if I put the date in directly as a variable (todayDate).

The date is set at the top of the page<cfset todayDate = CreateODBCDate(now())>. The intention is to use a form to pass the dates, however it didn't work so I am simplifying with a default date to try and find the problem.

This works:

<cffunction name="getTimeReport" returntype="query">
  <cfargument name="UserRole" default="2" required="no">
  <cfargument name="UserID" default="#session.Username#" required="yes" type="string">
  <cfargument name="StartDate" default="#todayDate#" required="yes" type="date">
  <cfargument name="EndDate" default="#todayDate#" required="yes" type="date">
  <cfargument name="GroupBy" default="activityID" required="no">
  <cfquery datasource="#application.ds#" name="getSumTime">
   SELECT #Arguments.GroupBy#, SUM(time) AS TotalTime
   FROM mydb
   WHERE userID = '#Arguments.UserID#'
     AND entrydate >= #todayDate# AND entrydate <= #todayDate#
    GROUP BY #Arguments.GroupBy#
   </cfquery>
  <cfreturn getSumTime>
</cffunction>

This doesn't:

<cffunction name="getTimeReport" returntype="query">
  <cfargument name="UserRole" default="2" required="no">
  <cfargument name="UserID" default="#session.Username#" required="yes" type="string">
  <cfargument name="StartDate" default="#todayDate#" required="yes" type="date">
  <cfargument name="EndDate" default="#todayDate#" required="yes" type="date">
  <cfargument name="GroupBy" default="activityID" required="no">
  <cfquery datasource="#application.ds#" name="getSumTime">
   SELECT #Arguments.GroupBy#, SUM(time) AS TotalTime
   FROM mydb
   WHERE userID = '#Arguments.UserID#'
     AND entrydate >= #Arguments.StartDate# AND entrydate <= #Arguments.EndDate#
    GROUP BY #Arguments.GroupBy#
   </cfquery>
  <cfreturn getSumTime>
</cffunction>

Any ideas? I am pretty bad at spotting obvious errors so I'd appreciate any pointers. I tried different date formats but I don't think that's the problem, I just can't seem to pass it through the arguments. The other arguments are passing through ok.

Neve

    This topic has been closed for replies.
    Correct answer ilssac

    Now I am testing with the actual form - now the arguments are working.

    Are they date-time Objects like you get from the now() function?

    Yes, for the default parameter FORM.startdate = #todayDate# for example it is using:

    todayDate is <cfset todayDate = CreateODBCDate(now())>

    This works fine.

    Passing values from the actual form's datefields aren't working yet, I've tried a few things and am getting no results. The form fields are:

    <cfinput type="dateField" name="startdate" label="Start Date"
          width="100" value="#Form.initstartdate#">
        <cfinput type="dateField" name="enddate" label="End Date" width="100"
          value="#Form.initenddate#">

    This isn't working no matter what date format I try, or leaving it alone. I just get no results again... no errors. Now I have a date problem (and not for the first time!) I think.

    Actually- now it sometimes works depending on what dates I choose but the default dates give no response. The popup calendar seems a little flaky, I click on a date and the date field empties half the time. Hmmm... Mabye it's just me now.

    Neve

    Message was edited by: neve_m


    It is probably a good thing to note that a form element is never going to produce a date-time object no matter how slick the DHTML controls are to allow the user the select a date.  Once the date is passed through the HTTP request, all it will ever be on the other end is a date-time string.

    It is your responsibility to take that date-time string and turn it into a date-time object.  ColdFusion provides serveral functions to allow for this.  createDate(), parseDate(), createODBCdate(), createODBCtime(), createODBCdatetime(), etc.  As well as the ever popular <cfqueryparam value="..." cfsqltype="CF_SQL_DATE|CF_SQL_TIME|CF_SQL_TIMESTAMP">  Which will both convert a date-time string into a date-time object and  protect ones database from SQL injection.

    4 replies

    A
    Anonymous
    October 1, 2009

    The difference is that in the working query, his where clause is:

    AND entrydate >= #todayDate# AND entrydate <= #todayDate#

    And in the non-working query, the where clause is:

    AND entrydate >= #Arguments.StartDate# AND entrydate <= #Arguments.EndDate#

    I'd do a cfdump of todayDate, and then one of arguments.StartDate / EndDate and compare.

    Depending on your db, a cfqueryparam with a cf_sql_type of date could be the key when referencing the arguments.startdate and arguments.enddate.

    If you still have trouble, do the two cfdumps and post the results.

    N
    neve_mAuthor
    Known Participant
    October 1, 2009

    The difference is that in the working query, his where clause is:

    AND entrydate >= #todayDate# AND entrydate <= #todayDate#

    And in the non-working query, the where clause is:

    AND entrydate >= #Arguments.StartDate# AND entrydate <= #Arguments.EndDate#

    I'd do a cfdump of todayDate, and then one of arguments.StartDate / EndDate and compare.

    Umm... but that's the whole point of my question. the todayDate was put in only to test why the arguments StartDate and EndDate weren't working and rule out any other parts of the query and/or other arguments. I have done CFDUMP of both- as I said using the arguments just gives no results compared to putting todayDate in the query. I am trying to get those arguments to work and I am just scratching my head as to why. I meant I didn't see why there would be any difference in the generated code or the way CF would interpret it. Shouldn't it be exactly the same? The reason why I tried using todayDate in the arguments was to rule out issues with dates being passed from the form. Sorry if I wasn't clear- I just tried everything I could think of before posting and tried to simplify it down.

    So to clarify it doesn't seem to matter what I put in StartDate or EndDate, now(), form data, date formatted data, todayDate (which works if used directly in the query)- the argument doesn't seem to work no  matter what. All the others arguments are working fine.

    -
    -__cfSearching__-
    Inspiring
    October 1, 2009

    neve_m wrote:

      <cfquery datasource="#application.ds#" name="getSumTime">
      SELECT #Arguments.GroupBy#, SUM(time) AS TotalTime
       FROM mydb
       WHERE userID = '#Arguments.UserID#'
         AND entrydate >= #todayDate# AND entrydate <= #todayDate#
        GROUP BY #Arguments.GroupBy#
       </cfquery>

    Though not directly related to your error, that is a potentially dangerous query.  Direct usage of "user supplied" values in SQL poses a sql injection risk for most databases.

    N
    neve_mAuthor
    Known Participant
    October 1, 2009

    Though not directly related to your error, that is a potentially dangerous query.  Direct usage of "user supplied" values in SQL poses a sql injection risk for most databases.

    How do you mean? How would be a safer way to do it? It is an internal application behind a firewall with a login. I would imagine if an internal person in the organisation hacked in they would be in serious trouble... but I am curious if there is a 'safer' way because the info is confidential. How should the userID be supplied?

    All advice appreciated...

    Neve

    -
    -__cfSearching__-
    Inspiring
    October 1, 2009
    It is an internal application behind a firewall with a login. I would imagine if an internal person in the organisation hacked in they would be in serious trouble...

    That will not help much after the damage has been done ;-) Always design applications with basic security in mind. _Especially_ when it contains confidential data,  be it your own or someone else's.

    How would be a safer way to do it?

    For starters, always use cfqueryparam on all user supplied values.  Cfqueryparam's primary purpose is for performance, but it also has a beneficial side effect of reducing sql injection risks. So instead of using the user values directly:

            WHERE userID = '#Arguments.UserID#'
            AND entrydate >= #Arguments.StartDate# AND entrydate <= #Arguments.EndDate#

    You would wrap them all in cfqueryparam.  The cf_sql_types will vary depending on your column data types. But this is the basic structure.

            WHERE userID =  <cfqueryparam value="#Arguments.UserID#" cfsqltype="cf_sql_varchar">
             AND entrydate >= <cfqueryparam value="#Arguments.StartDate#" cfsqltype="cf_sql_date">

            AND entrydate <= <cfqueryparam value="#Arguments.EndDate#" cfsqltype="cf_sql_date">

    The problem with your query is that it also uses user values to define the SELECT and ORDER BY column lists. Unfortunately, cfqueryparam cannot be used to represent column or table names.  Only simple values.  So your query is still vulnerable.

    http://www.adobe.com/devnet/coldfusion/articles/sql_injection_02.html

    If you really need to generate that type of dynamic SELECT statment. You must scrub the data thoroughly first, before passing it into the cfquery. That way the CF code maintains absolute control of the generated sql, and a bad user value cannot slip in.

    D
    Dan_Bracuk
    Inspiring
    October 1, 2009

    You might be mixing up strings, dates, and timestamps (aka date-times).

    Your form will pass a string.  You will have to use a cf function, maybe createdate(), to create your date variable.

    For troubleshooting, <cfdump> is your freind.  Start with the arguments scope to see what is being received by your function.

    ilssac
    ilssac
    Inspiring
    October 1, 2009

    I'm sorry, but I do not see the difference between the one that "worked" and the one the "doesn't"

    Also how does it not work?  Does it throw an error, create the wrong value, what does "doesn't work" mean in this particular case.

    While waiting for information on those quesitons I'll point out a couple of bad practicies I did spot on those CFC.

    Using a "default" parameter and a "required" parameter in the same <cfargument....> tag is rather mutually exlusive.  While it does not actually generate an error, if there is a default, the argument is allways going to exist so the required paramter does not mean anything,.

    Secondly, having a cfc rely on an exteriour scope, such as "session" breaks some of the encapsulation idea of an object.  It is usually considered a better idea to pass in the session value when invoking the component function rather then have the function read the session variable directly.  Doing it your way can lead to some hard to understand errors if one ever starts moving ones componets outside of the web root.  It makes your component much less portable.

    N
    neve_mAuthor
    Known Participant
    October 1, 2009

    I'm sorry, but I do not see the difference between the one that "worked" and the one the "doesn't"

    No... me neither... that's why I don't get it. One generates results, one gives no results. I don't get why they aren't identical. No errors. I am using CFDUMP to test the cfc and view results. I have tried a few date formats as well but I don't see why that would be the issue if passing it through the argument should be the same.


    Using a "default" parameter and a "required" parameter in the same <cfargument....> tag is rather mutually exlusive.

    I did wonder about that...

    It is usually considered a better idea to pass in the session value when invoking the component function rather then have the function read the session variable directly

    Yes, I am planning on passing the sesssion ID through the form (the idea is that someone in one user role will only see their own, a user in a manager role will be able to choose which userID they want to see), I just wanted a default value for the time being...  but good point I will try not to do this. I can't really see this cfc ever used outside of this app but I guess you never know.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices