The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages - More
> ColdFusion Development
|
Form update advice
Discuss Form update advice in the ColdFusion Development forum on Dev Shed.
Form update advice ColdFusion Development forum discussing CFML coding practices, tips on CFML, and other CFML related topics. Find out why ColdFusion is the tool of choice for many e-commerce developers.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
March 1st, 2012, 01:46 PM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
|
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.
|
March 1st, 2012, 03:38 PM
|
|
Moderator
|
|
Join Date: Jun 2002
Location: Raleigh, NC
|
|
|
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.
|
March 2nd, 2012, 06:53 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
Quote: | 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.
|
March 2nd, 2012, 08:19 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
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?
|
March 2nd, 2012, 08:42 AM
|
|
Moderator
|
|
Join Date: Jun 2002
Location: Raleigh, NC
|
|
|
What database platform are you using?
|
March 2nd, 2012, 08:43 AM
|
|
Moderator
|
|
Join Date: Jun 2002
Location: Raleigh, NC
|
|
|
Also you should delete that code and write a query that uses cfqueryparam to prevent SQL injection attacks.
|
March 2nd, 2012, 08:50 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
Quote: | Originally Posted by kiteless What database platform are you using? |
SQL Server 2000.
|
March 2nd, 2012, 08:54 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
Quote: | 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.
|
March 2nd, 2012, 09:26 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
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.
|
March 2nd, 2012, 10:54 AM
|
|
Moderator
|
|
Join Date: Jun 2002
Location: Raleigh, NC
|
|
|
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.
|
March 2nd, 2012, 11:52 AM
|
|
Contributing User
|
|
Join Date: Nov 2006
Posts: 33
Time spent in forums: 12 h 58 m 36 sec
Reputation Power: 7
|
|
Quote: | 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.
|
March 8th, 2012, 04:04 PM
|
|
Contributing User
|
|
Join Date: May 2008
Posts: 117
Time spent in forums: 17 h 16 m 2 sec
Reputation Power: 6
|
|
Quote: | 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 04:14 PM.
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
