Simple CRUD Using CFGRID

I'm currently working on a project where my client needs to be able to create, read, update and delete records in a table displayed on a web page. In ColdFusion CFGRID solves this problem and here's a simple example using the CFARTGALLERY sample database that comes with ColdFusion 9.

Firstly, create an Artist.cfc file containing the following code.

<cfcomponent output="FALSE"> <cffunction name="getArtists" hint="I extract artists from the database" access="remote" output="FALSE" returntype="struct"> <cfargument name="page" required="TRUE" hint="the page the grid is on" /> <cfargument name="pagesize" required="TRUE" hint="records displayed per page" /> <cfargument name="gridsortcolumn" required="TRUE" hint="selected column to sort" /> <cfargument name="gridsortdirection" required="TRUE" hint="the sort direction" /> <cfset var qArtists = "" />  <cfif arguments.gridsortcolumn eq ""> <cfset arguments.gridsortcolumn = "lastname" /> <cfset arguments.gridsortdirection = "asc" /> </cfif>  <cfquery name="qArtists" datasource="cfartgallery"> SELECT * FROM Artists ORDER BY #arguments.gridsortcolumn# #arguments.gridsortdirection# </cfquery>  <cfreturn QueryConvertForGrid( qArtists, arguments.page, arguments.pagesize ) /> </cffunction> <cffunction name="saveArtist" hint="I insert, update or delete an artist" access="remote" output="FALSE" returntype="void"> <cfargument name="gridaction" required="TRUE" hint="I for insert, U for update and D for delete" /> <cfargument name="gridrow" required="TRUE" hint="the rows being inserted or updated" /> <cfargument name="gridchanged" required="TRUE" hint="the changes" /> <cfset var qInsertArtist = "" /> <cfset var qUpdateArtist = "" /> <cfset var qDeleteArtist = "" />  <cfif IsStruct( arguments.gridrow ) and IsStruct( arguments.gridchanged )>  <cfif arguments.gridaction eq "I">  <cfquery name="qInsertArtist" datasource="cfartgallery"> INSERT INTO Artists ( firstname , lastname , address , city , state , postalcode , email , phone , fax , thepassword ) VALUES ( <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.firstname#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.lastname#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.address#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.city#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.state#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.postalcode#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.email#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.phone#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.fax#" /> , <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.gridrow.thepassword#" /> ) </cfquery>  <cfelseif arguments.gridaction eq "U">  <cfset var colname = StructKeyList( arguments.gridchanged ) /> <cfset var value = StructFind( arguments.gridchanged, colname ) /> <cfquery name="qUpdateArtist" datasource="cfartgallery"> UPDATE Artists SET #colname# = <cfqueryparam value="#value#" /> WHERE artistid = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.gridrow.artistid#" /> </cfquery>  <cfelseif arguments.gridaction eq "D">  <cfquery name="qDeleteArtist" datasource="cfartgallery"> DELETE FROM Artists WHERE artistid = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.gridrow.artistid#" /> </cfquery> </cfif> </cfif> </cffunction> </cfcomponent>

Now create an index.cfm file containing the following code.

<cfscript> args = {}; // bind grid to structure returned by getArtists method args.bind = "cfc:artist.getArtists({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"; args.delete = TRUE; args.format = "html"; args.insert = TRUE; args.name = "artistgrid"; // bind onchange event to saveArtist method args.onchange = "cfc:artist.saveArtist({cfgridaction},{cfgridrow},{cfgridchanged})"; args.pagesize = "10"; args.selectmode = "edit"; args.striperows = TRUE; </cfscript> <h1>Simple CRUD Using CFGRID</h1> <cfform name="artistform"> <cfgrid attributecollection="#args#"> <cfgridcolumn name="firstname" header="First Name" /> <cfgridcolumn name="lastname" header="Last Name" /> <cfgridcolumn name="address" header="Address" /> <cfgridcolumn name="city" header="City" /> <cfgridcolumn name="state" header="State" /> <cfgridcolumn name="postalcode" header="Postal Code" /> <cfgridcolumn name="email" header="Email" /> <cfgridcolumn name="phone" header="Phone" /> <cfgridcolumn name="fax" header="Fax" /> <cfgridcolumn name="thepassword" header="Password" /> </cfgrid> </cfform>

Load the index.cfm page in your web browser and the final result looks a bit like this...

Comments

Wednesday, October 5, 2011 Dan Switzer, II

Just a warning that the qArtists query in the getArtists() method is open to SQL injections. A user could use either of the "gridsort" arguments to potentially inject malicious SQL.

To solve this problem, you can use a helper function to make sure that the column and sort direction are valid values.

Wednesday, October 5, 2011 Simon Bingham

Thanks Dan. That's a good tip! :)

Friday, October 7, 2011 Chris Dawes

Dan, people should be using their own in-house cf application firewall instead of relying on human coders ;-)

an application firewall that checks inserted fields against their datatype in the db at the minimum (and key injection keywords), and by using custom datatypes, checking against custom rules. This also allows you to use CFINSERT and CFUPDATE safely also.

I can't believe people are still relying on sql parameter checks in their code for attack protection, it's soo 1990! (but better than nothing) Miss one, and you're application is open for attack. Not worth the risk.

Monday, August 27, 2012 Dan TheMan

Simon,
Thanks for the great example.

Just fyi: CF 8 gave me an error on lines 71 and 72 of Artist.cfc. It was complaining about volname and value. Solution is to remove the var word from each line.

Tuesday, August 28, 2012 Simon Bingham

Yes, in CF8 you have to var scope your variables at the start of your method whereas in CF9 you can do it anyway.

Monday, March 11, 2013 Chuck Duppong

Simon, THANKS for the example. It helped me finally get off the ground with crud in cf.

Xindi CMS

About Me

Wednesday, October 5, 2011 Simple CRUD using CFGRID

Tags

Share