#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9

    Form update advice


    I have a form that has a large number of editable fields. I am collecting the edited fields into an 2D array, and passing the array to an update page. This array looks like this:
    theArray[[1][1,2,3]]. The second element will always contain 3 values, and the length can be from 1 to x elements.

    On the update page, I am looping through the array, and as of now, I am building SQL update statements based on the values contained with the array. I am only updating the values that were updated on the form.

    So, I'm at an impasse with how I want to execute the update statements. I could just run them as they are built using cfquery, or I was thinking about shipping them off to the DB in a long string, parsing through it, and executing them there.

    So, does either of these options seem more practical, or has anyone done it another way, which they would like to share?

    Thanks in advance.
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,281
    Rep Power
    968
    I tend to follow Donald Knuth's famous quote: Premature optimization is the root of all evil.

    I would just get it working with separate update statements. If it ever becomes a performance problem, only then should you try to optimize it.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9
    Originally Posted by kiteless
    I tend to follow Donald Knuth's famous quote: Premature optimization is the root of all evil.
    I'm printing that quote out. thanks.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9
    I'm running into a weird problem executing the query.

    Here is the query string

    Code:
    <cfset attribution_query &= ",updated_by ='" & #uCase(cookie.user_id)# & "', update_date = '" & #dateFormat(now(),"mm/dd/yyyy")# & "' where asset_profile_id =" & #profile_id# & " and attribution_id = '" & #udc_code# & "'">
    the output is

    Code:
    update tbl_asset_profile_detail_attributions set quarter=-.18,updated_by =''ADCX6KV'', update_date = ''03/02/2012'' where asset_profile_id =2 and attribution_id = ''PRDIFF''
    For some reason it is adding a space and an additional single quote around anything that I had to single quote.

    When I remove the single quotes from the string, it doesn't add a space or the other single quote, which will not execute correctly either.

    Any ideas?
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,281
    Rep Power
    968
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,281
    Rep Power
    968
    Also you should delete that code and write a query that uses cfqueryparam to prevent SQL injection attacks.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9
    Originally Posted by kiteless
    What database platform are you using?
    SQL Server 2000.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9

    Red face


    Originally Posted by kiteless
    Also you should delete that code and write a query that uses cfqueryparam to prevent SQL injection attacks.
    I have a feeling that using the cfqueryparam might solve my issue, with binding the data to a sql data type.

    I'm going to give it a shot.

    EDIT: Can't use them as my code is written, because I am building the query string outside of the cfquery tag.

    Code:
    <cfswitch expression="#table#">
      <cfcase value="a">
        <cfif value_type eq"quarter">
          <cfset attribution_query &= "quarter=" & #theArray[i][2]#">
        <cfelseif value_type eq"ytd">
          <cfset attribution_query &= "ytd=" & #theArray[i][2]#">
        </cfif>
        <cfset attribution_query &= ",updated_by ='" & #uCase(cookie.user_id)# & "', update_date = '" & #dateFormat(now(),"mm/dd/yyyy")# & "' where asset_profile_id =" & #profile_id# & " and attribution_id = '" & #udc_code# & "'">
        <cfquery name="attribution_update" datasource="#dsn#">
          #attribution_query#
        </cfquery>
        <cfset attribution_query = "update tbl_asset_profile_detail_attributions set ">
      </cfcase>
    ...
    </cfswitch>

    EDIT 2: Thinking about it. I probably could move the if statement into the cfquery tag and use the cfqueryparams that way.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9
    using the cfqueryparams seems to have fixed the problem.

    Code:
    <cfquery name="attribution_update" datasource="#dsn#">
      update tbl_asset_profile_detail_attributions set
      <cfif value_type eq "quarter">
        quarter = <cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#theArray[i][2]#">,
      <cfelseif value_type eq "ytd">
        ytd = <cfqueryparam cfsqltype="CF_SQL_FLOAT" value="#theArray[i][2]#">,
      </cfif>
      updated_by = <cfqueryParam cfsqltype="CF_SQL_VARCHAR" value="#uCASE(cookie.user_id)#">,
      update_date = <cfqueryParam cfsqltype="cf_sql_date" value="#dateformat(now(),'mm/dd/yyyy')#">
      where asset_profile_id  = <cfqueryParam cfsqltype="cf_sql_integer" value="#profile_id#">
      and attribution_id = <cfqueryParam cfsqltype="cf_sql_varchar" value="#udc_code#">
    </cfquery>
    I'm still unclear why it added that extra stuff into the string, but is working now.
  18. #10
  19. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,281
    Rep Power
    968
    You should ALWAYS use cfqueryparam for ALL SQL parameters. The fact that it deals with escaping the values, etc., is just gravy. For security reasons it is absolutely mandatory anyway.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    33
    Rep Power
    9
    Originally Posted by kiteless
    You should ALWAYS use cfqueryparam for ALL SQL parameters. The fact that it deals with escaping the values, etc., is just gravy. For security reasons it is absolutely mandatory anyway.
    Makes sense, but I guess I get lazy with security, especially when the app is inward facing.

    Thanks for the tip.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    I'm still unclear why it added that extra stuff into the string, but is working now.
    It is a protection feature. CF automatically escapes (ie doubles) single quotes within dynamic sql strings to protect queries from sql injection attacks.
    Last edited by cfSearching; March 9th, 2012 at 05:14 PM.

IMN logo majestic logo threadwatch logo seochat tools logo