COAL example
37 views
Skip to first unread message
Ryan Guill
Sep 1, 2005, 6:20:24 PM9/1/05
to CF_...@googlegroups.com
Hey guys,
I have had a lot of people that are still unsure of the
concept of COAL and so I would like to develop a useful service for
COAL and then I can do a tutorial or article explaining how this will
help developers.
So my question is, what is something that many of us developers do
every day or every project. The possibilities are endless really, I
can think of a many, but I want to see what you guys come up with to
see if there are any trends. Something that can be useful immediately
would be a better benefit to the community.
--
Ryan Guill
BlueEyesDevelopment
ryan...@gmail.com
www.ryanguill.com
(270) 217.2399
The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com
www.ryanguill.com/
The Roman Empire: www.ryanguill.com/blog/
I have had a lot of people that are still unsure of the
concept of COAL and so I would like to develop a useful service for
COAL and then I can do a tutorial or article explaining how this will
help developers.
So my question is, what is something that many of us developers do
every day or every project. The possibilities are endless really, I
can think of a many, but I want to see what you guys come up with to
see if there are any trends. Something that can be useful immediately
would be a better benefit to the community.
--
Ryan Guill
BlueEyesDevelopment
ryan...@gmail.com
www.ryanguill.com
(270) 217.2399
The Coldfusion Open Application Library - COAL - http://coal.ryanguill.com
www.ryanguill.com/
The Roman Empire: www.ryanguill.com/blog/
Phillip Senn
Sep 2, 2005, 5:14:17 PM9/2/05
to CF_...@googlegroups.com
CRUD stands for Create, Retrieve, Update and Delete.
I've talked with other developers where I work about this....
"Script injection attack" is mentioned in ColdFusion Developer's Journal:
http://coldfusion.sys-con.com/read/45571.htm
Basically it's this:
A web page's address could be something like this:
http://www.myDomain.com/Question.cfm?ID=3
Which would then be used in ColdFusion like this:
SELECT * FROM Question WHERE QuestionID = #URL.ID#
This is a normal thing to do, and a lot of Applications use this type of
addressing.
The problem is that a malicious hacker could append a valid SQL command onto
the end of the web address.
http://www.myDomain.com/Question.cfm?ID=3;DELETE * FROM CUST
This would have the effect of issuing the following two (2) SQL commands:
SELECT * FROM Question WHERE QuestionID = 3
DELETE * FROM CUST
'nough said.
To combat this, there are several things that can be done
A) Use method="post" instead of method="get".
B) Use the cfqueryparam tag (check to make sure the URL parameter is
numeric).
C) Allow only stored procedures.
The problem with (A) is it's not fool-proof against a determined attacker.
The problem with (B) is that SQL Server is still relying upon someone else
to ensure its own security. If I were SQL Server,
I would want to provide my own security, and not have to rely upon the
application to validate parameters. And if SQL Server
is to validate parameters, it has to use a stored procedure.
The CFDJ article says to turn everything off except stored procedures.
So instead of:
<cfquery name="Question" datasource="#Application.DSN#">
SELECT * FROM Question
</cfquery>
we now have to say:
<cfstoredproc procedure="QuestionSelect" datasource="#Application.DSN#"
debug="yes">
<cfprocresult name="Question">
</cfstoredproc>
So how to create a generic CRUD form that doesn't use cfquery?
Here are 6 SQL Server stored procedures A) Select, B) Update, C) Insert, D)
Delete.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.SQLServerSelect(
@TableName VARCHAR(255),
@GUID UniqueIdentifier,
@SQLSelect VARCHAR(255) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLSelect = 'SELECT * FROM ' + @TableName
PRINT @GUID
IF @GUID IS NOT NULL
SET @SQLSelect = @SQLSelect + ' WHERE ' + @TableName + 'ID = ' +
QUOTENAME(@GUID,CHAR(39))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Select',@SQLSelect)
EXEC(@SQLSelect)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to SELECT', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerUpdate(
@TableName VARCHAR(128),
@GUID UniqueIdentifier,
@Log_TimeStmp Integer,
@SQLstr VARCHAR(8000) OUTPUT,
@RowCount Integer OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@SQLSelect VARCHAR(320),
@Error Integer
SET @SQLstr = 'UPDATE ' + @TableName + ' SET ' + @SQLstr
+ ', Log_TimeStmp=Log_TimeStmp+1'
+ ' WHERE ' + @TableName + 'ID=' + QUOTENAME(@GUID,CHAR(39))
+ ' AND Log_TimeStmp=' + CAST(@Log_TimeStmp AS VARCHAR(10))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Update',@SQLStr)
BEGIN TRANSACTION
EXEC(@SQLstr)
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF @Error <> 0 GOTO ProcErr
COMMIT TRANSACTION
SET @SQLSelect = 'SELECT * FROM ' + @TableName
+ ' WHERE ' + @TableName + 'ID = ' + QUOTENAME(@GUID,CHAR(39))
-- SELECT @Log_TimeStmp=Log_TimeStmp doesn't seem to work
EXEC(@SQLSelect)
RETURN(0)
ProcErr:
BEGIN
ROLLBACK TRANSACTION
SET @RowCount = 0
RAISERROR('There was an error trying to UPDATE', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerInsert(
@TableName VARCHAR(255),
@FieldsInserted VARCHAR(8000),
@ValuesInserted VARCHAR(8000),
@ID UniqueIdentifier OUTPUT,
@RowCount Integer OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@SQLInsert VARCHAR(8000),
@SQLSelect VARCHAR(320),
@GUID CHAR(36),
@Error INT
SET @GUID = NEWID()
SET @SQLInsert = 'INSERT INTO ' + @TableName + ' (' + @TableName + 'ID, ' +
@FieldsInserted + ') VALUES (' +
QUOTENAME(@GUID,CHAR(39)) + ', ' + @ValuesInserted + ')'
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Insert',@SQLInsert)
BEGIN TRANSACTION
EXEC(@SQLInsert)
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF @Error <> 0 GOTO ProcErr
SET @SQLSelect = 'SELECT * FROM ' + @TableName + ' WHERE ' + @TableName +
'ID = ' + QUOTENAME(@GUID,CHAR(39))
EXEC(@SQLSelect)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
COMMIT TRANSACTION
SET @ID = @GUID
RETURN 0
ProcErr:
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error trying to INSERT', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerDelete(
@TableName VARCHAR(255),
@GUID UniqueIdentifier,
@SQLDelete VARCHAR(255) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLDelete = 'DELETE FROM ' + @TableName + ' WHERE ' + @TableName + 'ID
= ' + QUOTENAME(@GUID,CHAR(39))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'SQLServerDelete',@SQLDelete)
EXEC(@SQLDelete)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to DELETE', 16, 1)
RETURN -100
END
-- I think the following one could be done directly in ColdFusion, but
haven't taken the time to do it.
CREATE PROC dbo.getPrimaryKey(
@TableName VARCHAR(255)
)
AS
SET NOCOUNT ON
EXEC sp_pkeys @TableName
RETURN 0
-- And this one could probably be refactored into dbo.SQLServerSELECT:
CREATE PROC dbo.get(
@FieldName VARCHAR(255),
@TableName VARCHAR(255),
@SortOrder VARCHAR(255),
@Condition VARCHAR(255),
@SQLstr VARCHAR(8000) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLStr = 'SELECT ' + @FieldName + ' FROM ' + @TableName
IF @Condition <> ''
SET @SQLStr = @SQLStr + ' WHERE ' + @Condition
IF @SortOrder <> ''
SET @SQLStr = @SQLStr + ' ORDER BY ' + @SortOrder
-- INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Get',@SQLStr)
EXEC(@SQLStr)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to GET data', 16, 1)
RETURN -100
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
These procedures are not ready for prime time. They make several
assumptions because of the way I program.
The major assumption is that every table has a GUID primary key whose
fieldname is the tablename followed by "ID".
OK, so how are they used?
Here are the ColdFusion CUSTOM TAGS that go with the SQL Server stored
procedures:
retrieve.cfm:
<cfset myName = "cf_retrieve">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<!--- <cfstoredproc procedure="dbo.sp_pkeys" datasource="Master">
<cfprocparam type="IN" dbvarname="@table_name"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocresult name="RS2">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS2.Column_Name#")> --->
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo1 = StructNew()>
<cfset DebugInfo1.PrimaryKey = RS1.Column_Name>
<cfset DebugInfo1.GUID = GUID>
<cfdump var="#DebugInfo1#" label="#myName# before the stored
procedure">
</cfif>
</cfif>
<!--- This SELECT procedure will work with any table --->
<cftry>
<cfstoredproc procedure="SQLServerSelect"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@GUID" value="#GUID#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLSelect" variable=SQLSelect>
<cfprocresult name="SQLServerSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
</cftry>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.SQLSelect = SQLSelect>
<cfset DebugInfo2.RecordsSelected =
SQLServerSelect.RecordCount>
<cfset DebugInfo2.ColumnsSelected =
SQLServerSelect.ColumnList>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif>
<!--- Now populate the FORM fields from the database --->
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<cfloop index="FieldName" list=#SQLServerSelect.ColumnList#>
<cfset X = Evaluate("SQLServerSelect.#FieldName#")>
<cfif LISTFINDNOCASE(BitColumns,FieldName)>
<cfif X EQ 0>
<cfset X = false>
<cfelse>
<cfset X = true>
</cfif>
</cfif>
<cfset "caller.FORM.#FieldName#" = X>
<cfset "caller.SAVE.#FieldName#" = X>
</cfloop>
update.cfm:
<cfset myName = "cf_update">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key Value --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<!--- <cfstoredproc procedure="dbo.sp_pkeys" datasource="Master">
<cfprocparam type="IN" dbvarname="@table_name"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocresult name="RS2">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS2.Column_Name#")> --->
<cfobject name="ListofColumnsObj" component="Components.Gendbs">
<cfset ListofColumnsQry = ListofColumnsObj.Select(FieldName="TOP 1
*",TableName="#Attributes.tableName#",Condition="",SortOrder="",DataSource="
#Attributes.DataSource#")>
<cfset ListofColumns = ListofColumnsQry.ColumnList>
<cfset X = LISTFINDNOCASE(ListofColumns,Attributes.tableName & "ID")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
</cfif>
<cfset X = LISTFINDNOCASE(ListofColumns,"Log_TimeStmp")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
<cfelse>
<cfset CALLER.Variables.HeaderMessage = "No Log_TimeStmp found in "
& Attributes.tableName>
<cfexit>
</cfif>
<cfobject name="StringColumnsObj" component="Components.Gendbs">
<cfset StringColumnsQry = StringColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (35,36,99,167,175,231,239)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset StringColumns = VALUELIST(StringColumnsQry.Name)>
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<!--- Build a list such as: A=1, Description='Chopped Liver', C=3 --->
<cfset FieldList = "">
<cfloop index="FieldName" list="#CALLER.FORM.FieldNames#">
<cfif LISTFINDNOCASE(ListofColumns,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfif LISTFINDNOCASE(StringColumns,FieldName)>
<cfset X = "'" & X & "'">
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND (X EQ false)>
<cfset X = 0>
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND (X EQ true)>
<cfset X = 1>
</cfif>
<cfif Len(X) GT 0>
<!--- What if they're nulling a field? --->
<cfset FieldList = FieldList & FieldName & "=" & X & ', '>
</cfif>
</cfif>
</cfloop>
<cfset FieldList = Left(FieldList,Len(FieldList)-2)>
<cfset RowCount = 0>
<cfset Log_TimeStmp = CALLER.FORM.Log_TimeStmp>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfoutput>Update #Attributes.tableName# SET
Log_TimeStmp=Log_TimeStmp+1,<br>
#FieldList#<br>
WHERE #Attributes.tableName#ID='#GUID#'
AND Log_TimeStmp = #Log_TimeStmp#
</cfoutput>
</cfif>
</cfif>
<!--- This UDPATE procedure will now work with any table, any list of fields
--->
<cfset caller.FORM.RowCount = 0>
<cftry>
<cfparam name="TableName" default="">
<cfparam name="GUID" default="">
<cfparam name="Log_TimeStmp" default="">
<cfstoredproc procedure="dbo.SQLServerUpdate"
datasource="#Attributes.DataSource#" debug="yes" returncode="YES">
<cfprocparam type="IN" dbvarname="@TableName"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="IN" dbvarname="@GUID" value="#GUID#"
cfsqltype="CF_SQL_CHAR" maxlength="36">
<cfprocparam type="IN" dbvarname="@Log_TimeStmp"
value="#Log_TimeStmp#" cfsqltype="CF_SQL_INTEGER">
<cfprocparam type="INOUT" dbvarname="@SQLstr" value="#FieldList#"
variable="SQLstr" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="OUT" dbvarname="@RowCount" variable="RowCount"
cfsqltype="CF_SQL_INTEGER">
<cfprocresult name="SQLSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<cfif RowCount EQ 0>
<cfset CALLER.Variables.HeaderMessage = "Could not update.">
<cfloop index="I" from="1" to="#ListLen(ListofColumns)#">
<cfset FieldName = ListGetAt(ListofColumns,I)>
<cfif ListFind(CALLER.FORM.FieldNames,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfset Y = Evaluate("SQLSelect.#FieldName#")>
<cfif X NEQ Y>
<cfset CALLER.Variables.HeaderMessage =
"Could not update - " & FieldName & " was changed to '" & Y & "' by someone
else.">
<cfbreak>
</cfif>
</cfif>
</cfloop>
</cfif>
<cfset caller.FORM.RowCount = RowCount>
<cfset caller.FORM.Log_TimeStmp = SQLSelect.Log_TimeStmp>
<!--- <cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.StatusCode = cfstoredproc.statusCode>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif> --->
addnew.cfm:
<cfset myName = "cf_addnew">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfobject name="ListofColumnsObj" component="Components.Gendbs">
<cfset ListofColumnsQry = ListofColumnsObj.Select(FieldName="TOP 1
*",TableName="#Attributes.tableName#",Condition="",SortOrder="",DataSource="
#Attributes.DataSource#")>
<cfset ListofColumns = ListofColumnsQry.ColumnList>
<cfset X = LISTFINDNOCASE(ListofColumns,Attributes.tableName & "ID")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
</cfif>
<cfobject name="StringColumnsObj" component="Components.Gendbs">
<cfset StringColumnsQry = StringColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (35,36,99,167,175,231,239)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset StringColumns = VALUELIST(StringColumnsQry.Name)>
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<cfset FieldsInserted = "">
<cfset ValuesInserted = "">
<cfloop index="FieldName" list="#CALLER.FORM.FieldNames#">
<cfif LISTFINDNOCASE(ListofColumns,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfif Len(X) GT 0>
<cfif LISTFINDNOCASE(StringColumns,FieldName)>
<cfset X = "'" & X & "'">
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND
(X EQ false)>
<cfset X = 0>
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND
(X EQ true)>
<cfset X = 1>
</cfif>
<cfset FieldsInserted = FieldsInserted & FieldName & ", ">
<cfset ValuesInserted = ValuesInserted & X & ", ">
</cfif>
</cfif>
</cfloop>
<cfset FieldsInserted = Left(FieldsInserted,Len(FieldsInserted)-2)>
<cfset ValuesInserted = Left(ValuesInserted,Len(ValuesInserted)-2)>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfoutput>INSERT INTO #Attributes.tableName#
(#Attributes.tableName#ID,<br>
#FieldsInserted#<br>
) VALUES (NEWID(),<br>
#ValuesInserted#)
</cfoutput>
</cfif>
</cfif>
<!--- This INSERT procedure will now work with any table, any list of fields
--->
<cftry>
<cfstoredproc procedure="SQLServerInsert"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="8000"
dbvarname="@FieldsInserted" value="#FieldsInserted#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="8000"
dbvarname="@ValuesInserted" value="#ValuesInserted#">
<cfprocparam type="out" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@ID" variable="ID">
<cfprocparam type="out" cfsqltype="cf_sql_integer"
dbvarname="@RowCount" variable="RowCount">
<!--- <cfprocparam type="out" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@SQLInsert" variable=SQLInsert>
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLSelect" variable=SQLSelect> --->
<cfprocresult name="SQLServerSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<!--- <cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<!--- <cfset DebugInfo2.SQLInsert = SQLInsert> --->
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.RowCount = RowCount>
<!--- <cfset DebugInfo2.SQLSelect = SQLSelect> --->
<cfset DebugInfo2.RecordsSelected =
SQLServerSelect.RecordCount>
<cfset DebugInfo2.ColumnsSelected =
SQLServerSelect.ColumnList>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif> --->
<!--- Now populate the FORM fields with any defaults from the database --->
<cfset "caller.FORM.#Attributes.tableName#ID" = ID>
<cfloop index="FieldName" list=#SQLServerSelect.ColumnList#>
<cfset "caller.FORM.#FieldName#" =
Evaluate("SQLServerSelect.#FieldName#")>
</cfloop>
delete.cfm:
<cfset myName = "cf_delete">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo1 = StructNew()>
<cfset DebugInfo1.PrimaryKey = RS1.Column_Name>
<cfset DebugInfo1.GUID = GUID>
<cfdump var="#DebugInfo1#" label="#myName# before the stored
procedure">
</cfif>
</cfif>
<!--- This SELECT procedure will work with any table --->
<cftry>
<cfstoredproc procedure="SQLServerDelete"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@GUID" value="#GUID#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLDelete" variable=SQLDelete>
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
</cftry>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.SQLDelete = SQLDelete>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif>
<cfset caller.sHeaderMessage = "Record Deleted">
<cfset "caller.FORM.#RS1.Column_Name#" = "">
And finally, a component!
Gendbs.cfc:
<cfcomponent>
<!---
gendbs - Generic database commands
gendbs - Generate database commands
You decide --->
<cffunction name="TrimErrorMessage" access="public" returntype="string">
<cfargument name="errorMessage" type="string" required="yes">
<cfset var result = arguments.errorMessage>
<cfset result = REPLACE(result,'[Macromedia]','','ALL')>
<cfset result = REPLACE(result,'[SQLServer JDBC Driver]','','ALL')>
<cfset result = REPLACE(result,'[SQLServer]','','ALL')>
<!---
<cfset result = REPLACE(result,'UPDATE statement conflicted with
COLUMN CHECK constraint','Invalid','ALL')>
<cfset result = REPLACE(result,'. The conflict occurred in
database','','ALL')> --->
<cfreturn result>
</cffunction>
<cffunction name="Select" output="true">
<cfargument name="FieldName" type="string" default="*">
<cfargument name="TableName" type="string" required="yes">
<cfargument name="Condition" type="string" default="">
<cfargument name="SortOrder" type="string" default="">
<cfargument name="DataSource" type="string" required="yes"> <!---
This routine is too generic to allow a default DataSource --->
<cftry>
<cfstoredproc procedure="dbo.get" datasource="#DataSource#"
debug="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@FieldName" value="#FieldName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@TableName" value="#TableName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@SortOrder" value="#SortOrder#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@Condition" value="#Condition#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar"
maxlength="8000" dbvarname="@SQLStr" variable=SQLStr>
<cfprocresult name="getRecordSet">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs"
method="TrimErrorMessage" errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<!--- <cfoutput>Database: #SQLStr#</cfoutput> --->
<cfreturn getRecordset>
</cffunction>
</cfcomponent>
That's a lot of code and I still haven't wrapped it all up yet.
I'll wrap it up in the next post.
I've talked with other developers where I work about this....
"Script injection attack" is mentioned in ColdFusion Developer's Journal:
http://coldfusion.sys-con.com/read/45571.htm
Basically it's this:
A web page's address could be something like this:
http://www.myDomain.com/Question.cfm?ID=3
Which would then be used in ColdFusion like this:
SELECT * FROM Question WHERE QuestionID = #URL.ID#
This is a normal thing to do, and a lot of Applications use this type of
addressing.
The problem is that a malicious hacker could append a valid SQL command onto
the end of the web address.
http://www.myDomain.com/Question.cfm?ID=3;DELETE * FROM CUST
This would have the effect of issuing the following two (2) SQL commands:
SELECT * FROM Question WHERE QuestionID = 3
DELETE * FROM CUST
'nough said.
To combat this, there are several things that can be done
A) Use method="post" instead of method="get".
B) Use the cfqueryparam tag (check to make sure the URL parameter is
numeric).
C) Allow only stored procedures.
The problem with (A) is it's not fool-proof against a determined attacker.
The problem with (B) is that SQL Server is still relying upon someone else
to ensure its own security. If I were SQL Server,
I would want to provide my own security, and not have to rely upon the
application to validate parameters. And if SQL Server
is to validate parameters, it has to use a stored procedure.
The CFDJ article says to turn everything off except stored procedures.
So instead of:
<cfquery name="Question" datasource="#Application.DSN#">
SELECT * FROM Question
</cfquery>
we now have to say:
<cfstoredproc procedure="QuestionSelect" datasource="#Application.DSN#"
debug="yes">
<cfprocresult name="Question">
</cfstoredproc>
So how to create a generic CRUD form that doesn't use cfquery?
Here are 6 SQL Server stored procedures A) Select, B) Update, C) Insert, D)
Delete.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.SQLServerSelect(
@TableName VARCHAR(255),
@GUID UniqueIdentifier,
@SQLSelect VARCHAR(255) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLSelect = 'SELECT * FROM ' + @TableName
PRINT @GUID
IF @GUID IS NOT NULL
SET @SQLSelect = @SQLSelect + ' WHERE ' + @TableName + 'ID = ' +
QUOTENAME(@GUID,CHAR(39))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Select',@SQLSelect)
EXEC(@SQLSelect)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to SELECT', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerUpdate(
@TableName VARCHAR(128),
@GUID UniqueIdentifier,
@Log_TimeStmp Integer,
@SQLstr VARCHAR(8000) OUTPUT,
@RowCount Integer OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@SQLSelect VARCHAR(320),
@Error Integer
SET @SQLstr = 'UPDATE ' + @TableName + ' SET ' + @SQLstr
+ ', Log_TimeStmp=Log_TimeStmp+1'
+ ' WHERE ' + @TableName + 'ID=' + QUOTENAME(@GUID,CHAR(39))
+ ' AND Log_TimeStmp=' + CAST(@Log_TimeStmp AS VARCHAR(10))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Update',@SQLStr)
BEGIN TRANSACTION
EXEC(@SQLstr)
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF @Error <> 0 GOTO ProcErr
COMMIT TRANSACTION
SET @SQLSelect = 'SELECT * FROM ' + @TableName
+ ' WHERE ' + @TableName + 'ID = ' + QUOTENAME(@GUID,CHAR(39))
-- SELECT @Log_TimeStmp=Log_TimeStmp doesn't seem to work
EXEC(@SQLSelect)
RETURN(0)
ProcErr:
BEGIN
ROLLBACK TRANSACTION
SET @RowCount = 0
RAISERROR('There was an error trying to UPDATE', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerInsert(
@TableName VARCHAR(255),
@FieldsInserted VARCHAR(8000),
@ValuesInserted VARCHAR(8000),
@ID UniqueIdentifier OUTPUT,
@RowCount Integer OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@SQLInsert VARCHAR(8000),
@SQLSelect VARCHAR(320),
@GUID CHAR(36),
@Error INT
SET @GUID = NEWID()
SET @SQLInsert = 'INSERT INTO ' + @TableName + ' (' + @TableName + 'ID, ' +
@FieldsInserted + ') VALUES (' +
QUOTENAME(@GUID,CHAR(39)) + ', ' + @ValuesInserted + ')'
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Insert',@SQLInsert)
BEGIN TRANSACTION
EXEC(@SQLInsert)
SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT
IF @Error <> 0 GOTO ProcErr
SET @SQLSelect = 'SELECT * FROM ' + @TableName + ' WHERE ' + @TableName +
'ID = ' + QUOTENAME(@GUID,CHAR(39))
EXEC(@SQLSelect)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
COMMIT TRANSACTION
SET @ID = @GUID
RETURN 0
ProcErr:
BEGIN
ROLLBACK TRANSACTION
RAISERROR('There was an error trying to INSERT', 16, 1)
RETURN -100
END
CREATE PROC dbo.SQLServerDelete(
@TableName VARCHAR(255),
@GUID UniqueIdentifier,
@SQLDelete VARCHAR(255) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLDelete = 'DELETE FROM ' + @TableName + ' WHERE ' + @TableName + 'ID
= ' + QUOTENAME(@GUID,CHAR(39))
INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'SQLServerDelete',@SQLDelete)
EXEC(@SQLDelete)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to DELETE', 16, 1)
RETURN -100
END
-- I think the following one could be done directly in ColdFusion, but
haven't taken the time to do it.
CREATE PROC dbo.getPrimaryKey(
@TableName VARCHAR(255)
)
AS
SET NOCOUNT ON
EXEC sp_pkeys @TableName
RETURN 0
-- And this one could probably be refactored into dbo.SQLServerSELECT:
CREATE PROC dbo.get(
@FieldName VARCHAR(255),
@TableName VARCHAR(255),
@SortOrder VARCHAR(255),
@Condition VARCHAR(255),
@SQLstr VARCHAR(8000) OUTPUT
) AS
SET NOCOUNT ON
DECLARE
@Error INT
SET @SQLStr = 'SELECT ' + @FieldName + ' FROM ' + @TableName
IF @Condition <> ''
SET @SQLStr = @SQLStr + ' WHERE ' + @Condition
IF @SortOrder <> ''
SET @SQLStr = @SQLStr + ' ORDER BY ' + @SortOrder
-- INSERT INTO dbo.Log (LogID, SPROC, LogDescription)
VALUES(NEWID(),'Get',@SQLStr)
EXEC(@SQLStr)
SET @Error = @@Error
IF @Error <> 0 GOTO ProcErr
RETURN 0
ProcErr:
BEGIN
RAISERROR('There was an error trying to GET data', 16, 1)
RETURN -100
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
These procedures are not ready for prime time. They make several
assumptions because of the way I program.
The major assumption is that every table has a GUID primary key whose
fieldname is the tablename followed by "ID".
OK, so how are they used?
Here are the ColdFusion CUSTOM TAGS that go with the SQL Server stored
procedures:
retrieve.cfm:
<cfset myName = "cf_retrieve">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<!--- <cfstoredproc procedure="dbo.sp_pkeys" datasource="Master">
<cfprocparam type="IN" dbvarname="@table_name"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocresult name="RS2">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS2.Column_Name#")> --->
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo1 = StructNew()>
<cfset DebugInfo1.PrimaryKey = RS1.Column_Name>
<cfset DebugInfo1.GUID = GUID>
<cfdump var="#DebugInfo1#" label="#myName# before the stored
procedure">
</cfif>
</cfif>
<!--- This SELECT procedure will work with any table --->
<cftry>
<cfstoredproc procedure="SQLServerSelect"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@GUID" value="#GUID#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLSelect" variable=SQLSelect>
<cfprocresult name="SQLServerSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
</cftry>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.SQLSelect = SQLSelect>
<cfset DebugInfo2.RecordsSelected =
SQLServerSelect.RecordCount>
<cfset DebugInfo2.ColumnsSelected =
SQLServerSelect.ColumnList>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif>
<!--- Now populate the FORM fields from the database --->
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<cfloop index="FieldName" list=#SQLServerSelect.ColumnList#>
<cfset X = Evaluate("SQLServerSelect.#FieldName#")>
<cfif LISTFINDNOCASE(BitColumns,FieldName)>
<cfif X EQ 0>
<cfset X = false>
<cfelse>
<cfset X = true>
</cfif>
</cfif>
<cfset "caller.FORM.#FieldName#" = X>
<cfset "caller.SAVE.#FieldName#" = X>
</cfloop>
update.cfm:
<cfset myName = "cf_update">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key Value --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<!--- <cfstoredproc procedure="dbo.sp_pkeys" datasource="Master">
<cfprocparam type="IN" dbvarname="@table_name"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocresult name="RS2">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS2.Column_Name#")> --->
<cfobject name="ListofColumnsObj" component="Components.Gendbs">
<cfset ListofColumnsQry = ListofColumnsObj.Select(FieldName="TOP 1
*",TableName="#Attributes.tableName#",Condition="",SortOrder="",DataSource="
#Attributes.DataSource#")>
<cfset ListofColumns = ListofColumnsQry.ColumnList>
<cfset X = LISTFINDNOCASE(ListofColumns,Attributes.tableName & "ID")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
</cfif>
<cfset X = LISTFINDNOCASE(ListofColumns,"Log_TimeStmp")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
<cfelse>
<cfset CALLER.Variables.HeaderMessage = "No Log_TimeStmp found in "
& Attributes.tableName>
<cfexit>
</cfif>
<cfobject name="StringColumnsObj" component="Components.Gendbs">
<cfset StringColumnsQry = StringColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (35,36,99,167,175,231,239)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset StringColumns = VALUELIST(StringColumnsQry.Name)>
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<!--- Build a list such as: A=1, Description='Chopped Liver', C=3 --->
<cfset FieldList = "">
<cfloop index="FieldName" list="#CALLER.FORM.FieldNames#">
<cfif LISTFINDNOCASE(ListofColumns,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfif LISTFINDNOCASE(StringColumns,FieldName)>
<cfset X = "'" & X & "'">
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND (X EQ false)>
<cfset X = 0>
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND (X EQ true)>
<cfset X = 1>
</cfif>
<cfif Len(X) GT 0>
<!--- What if they're nulling a field? --->
<cfset FieldList = FieldList & FieldName & "=" & X & ', '>
</cfif>
</cfif>
</cfloop>
<cfset FieldList = Left(FieldList,Len(FieldList)-2)>
<cfset RowCount = 0>
<cfset Log_TimeStmp = CALLER.FORM.Log_TimeStmp>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfoutput>Update #Attributes.tableName# SET
Log_TimeStmp=Log_TimeStmp+1,<br>
#FieldList#<br>
WHERE #Attributes.tableName#ID='#GUID#'
AND Log_TimeStmp = #Log_TimeStmp#
</cfoutput>
</cfif>
</cfif>
<!--- This UDPATE procedure will now work with any table, any list of fields
--->
<cfset caller.FORM.RowCount = 0>
<cftry>
<cfparam name="TableName" default="">
<cfparam name="GUID" default="">
<cfparam name="Log_TimeStmp" default="">
<cfstoredproc procedure="dbo.SQLServerUpdate"
datasource="#Attributes.DataSource#" debug="yes" returncode="YES">
<cfprocparam type="IN" dbvarname="@TableName"
value="#Attributes.tableName#" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="IN" dbvarname="@GUID" value="#GUID#"
cfsqltype="CF_SQL_CHAR" maxlength="36">
<cfprocparam type="IN" dbvarname="@Log_TimeStmp"
value="#Log_TimeStmp#" cfsqltype="CF_SQL_INTEGER">
<cfprocparam type="INOUT" dbvarname="@SQLstr" value="#FieldList#"
variable="SQLstr" cfsqltype="CF_SQL_VARCHAR">
<cfprocparam type="OUT" dbvarname="@RowCount" variable="RowCount"
cfsqltype="CF_SQL_INTEGER">
<cfprocresult name="SQLSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<cfif RowCount EQ 0>
<cfset CALLER.Variables.HeaderMessage = "Could not update.">
<cfloop index="I" from="1" to="#ListLen(ListofColumns)#">
<cfset FieldName = ListGetAt(ListofColumns,I)>
<cfif ListFind(CALLER.FORM.FieldNames,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfset Y = Evaluate("SQLSelect.#FieldName#")>
<cfif X NEQ Y>
<cfset CALLER.Variables.HeaderMessage =
"Could not update - " & FieldName & " was changed to '" & Y & "' by someone
else.">
<cfbreak>
</cfif>
</cfif>
</cfloop>
</cfif>
<cfset caller.FORM.RowCount = RowCount>
<cfset caller.FORM.Log_TimeStmp = SQLSelect.Log_TimeStmp>
<!--- <cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.StatusCode = cfstoredproc.statusCode>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif> --->
addnew.cfm:
<cfset myName = "cf_addnew">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfobject name="ListofColumnsObj" component="Components.Gendbs">
<cfset ListofColumnsQry = ListofColumnsObj.Select(FieldName="TOP 1
*",TableName="#Attributes.tableName#",Condition="",SortOrder="",DataSource="
#Attributes.DataSource#")>
<cfset ListofColumns = ListofColumnsQry.ColumnList>
<cfset X = LISTFINDNOCASE(ListofColumns,Attributes.tableName & "ID")>
<cfif X>
<cfset ListofColumns = LISTDELETEAT(ListofColumns,X)>
</cfif>
<cfobject name="StringColumnsObj" component="Components.Gendbs">
<cfset StringColumnsQry = StringColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (35,36,99,167,175,231,239)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset StringColumns = VALUELIST(StringColumnsQry.Name)>
<cfobject name="BitColumnsObj" component="Components.Gendbs">
<cfset BitColumnsQry = BitColumnsObj.Select(
FieldName="SYSCOLUMNS.Name",
TableName="SYSCOLUMNS JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID",
Condition="SYSOBJECTS.NAME = '#Attributes.tableName#' AND SYSCOLUMNS.xtype
IN (104)",
SortOrder="ColID",
DataSource="#Attributes.DataSource#")>
<cfset BitColumns = VALUELIST(BitColumnsQry.Name)>
<cfset FieldsInserted = "">
<cfset ValuesInserted = "">
<cfloop index="FieldName" list="#CALLER.FORM.FieldNames#">
<cfif LISTFINDNOCASE(ListofColumns,FieldName)>
<cfset X = Evaluate("Caller.FORM.#FieldName#")>
<cfif Len(X) GT 0>
<cfif LISTFINDNOCASE(StringColumns,FieldName)>
<cfset X = "'" & X & "'">
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND
(X EQ false)>
<cfset X = 0>
<cfelseif LISTFINDNOCASE(BitColumns,FieldName) AND
(X EQ true)>
<cfset X = 1>
</cfif>
<cfset FieldsInserted = FieldsInserted & FieldName & ", ">
<cfset ValuesInserted = ValuesInserted & X & ", ">
</cfif>
</cfif>
</cfloop>
<cfset FieldsInserted = Left(FieldsInserted,Len(FieldsInserted)-2)>
<cfset ValuesInserted = Left(ValuesInserted,Len(ValuesInserted)-2)>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfoutput>INSERT INTO #Attributes.tableName#
(#Attributes.tableName#ID,<br>
#FieldsInserted#<br>
) VALUES (NEWID(),<br>
#ValuesInserted#)
</cfoutput>
</cfif>
</cfif>
<!--- This INSERT procedure will now work with any table, any list of fields
--->
<cftry>
<cfstoredproc procedure="SQLServerInsert"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="8000"
dbvarname="@FieldsInserted" value="#FieldsInserted#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="8000"
dbvarname="@ValuesInserted" value="#ValuesInserted#">
<cfprocparam type="out" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@ID" variable="ID">
<cfprocparam type="out" cfsqltype="cf_sql_integer"
dbvarname="@RowCount" variable="RowCount">
<!--- <cfprocparam type="out" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@SQLInsert" variable=SQLInsert>
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLSelect" variable=SQLSelect> --->
<cfprocresult name="SQLServerSelect">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<!--- <cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<!--- <cfset DebugInfo2.SQLInsert = SQLInsert> --->
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.RowCount = RowCount>
<!--- <cfset DebugInfo2.SQLSelect = SQLSelect> --->
<cfset DebugInfo2.RecordsSelected =
SQLServerSelect.RecordCount>
<cfset DebugInfo2.ColumnsSelected =
SQLServerSelect.ColumnList>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif> --->
<!--- Now populate the FORM fields with any defaults from the database --->
<cfset "caller.FORM.#Attributes.tableName#ID" = ID>
<cfloop index="FieldName" list=#SQLServerSelect.ColumnList#>
<cfset "caller.FORM.#FieldName#" =
Evaluate("SQLServerSelect.#FieldName#")>
</cfloop>
delete.cfm:
<cfset myName = "cf_delete">
<cfif NOT IsDefined("Attributes.DataSource")>
Usage: <#myName# <b>DataSource="myDataSource"</b>,
tableName="myTable"[, debug="yes"]><br />
<cfabort>
</cfif>
<cfif NOT IsDefined("Attributes.tableName")>
Usage: <#myName# DataSource="myDataSource",
tableName="<b>myTable</b>"[, debug="yes"]><br />
<cfabort>
</cfif>
<!--- Get the Primary Key --->
<cfstoredproc procedure="getPrimaryKey" datasource="#Attributes.DataSource#"
debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocresult name="RS1">
</cfstoredproc>
<cfset GUID = Evaluate("caller.FORM.#RS1.Column_Name#")>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo1 = StructNew()>
<cfset DebugInfo1.PrimaryKey = RS1.Column_Name>
<cfset DebugInfo1.GUID = GUID>
<cfdump var="#DebugInfo1#" label="#myName# before the stored
procedure">
</cfif>
</cfif>
<!--- This SELECT procedure will work with any table --->
<cftry>
<cfstoredproc procedure="SQLServerDelete"
datasource="#Attributes.DataSource#" debug="yes" returncode="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@TableName" value="#Attributes.tableName#">
<cfprocparam type="in" cfsqltype="cf_sql_char" maxlength="36"
dbvarname="@GUID" value="#GUID#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar" maxlength="255"
dbvarname="@SQLDelete" variable=SQLDelete>
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs" method="TrimErrorMessage"
errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
</cftry>
<cfif IsDefined("Attributes.Debug")>
<cfif Attributes.Debug EQ "yes">
<cfset DebugInfo2 = StructNew()>
<cfset DebugInfo2.statusCode = cfstoredproc.statusCode>
<cfset DebugInfo2.SQLDelete = SQLDelete>
<cfdump var="#DebugInfo2#" label="#myName# after the stored
procedure">
</cfif>
</cfif>
<cfset caller.sHeaderMessage = "Record Deleted">
<cfset "caller.FORM.#RS1.Column_Name#" = "">
And finally, a component!
Gendbs.cfc:
<cfcomponent>
<!---
gendbs - Generic database commands
gendbs - Generate database commands
You decide --->
<cffunction name="TrimErrorMessage" access="public" returntype="string">
<cfargument name="errorMessage" type="string" required="yes">
<cfset var result = arguments.errorMessage>
<cfset result = REPLACE(result,'[Macromedia]','','ALL')>
<cfset result = REPLACE(result,'[SQLServer JDBC Driver]','','ALL')>
<cfset result = REPLACE(result,'[SQLServer]','','ALL')>
<!---
<cfset result = REPLACE(result,'UPDATE statement conflicted with
COLUMN CHECK constraint','Invalid','ALL')>
<cfset result = REPLACE(result,'. The conflict occurred in
database','','ALL')> --->
<cfreturn result>
</cffunction>
<cffunction name="Select" output="true">
<cfargument name="FieldName" type="string" default="*">
<cfargument name="TableName" type="string" required="yes">
<cfargument name="Condition" type="string" default="">
<cfargument name="SortOrder" type="string" default="">
<cfargument name="DataSource" type="string" required="yes"> <!---
This routine is too generic to allow a default DataSource --->
<cftry>
<cfstoredproc procedure="dbo.get" datasource="#DataSource#"
debug="yes">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@FieldName" value="#FieldName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@TableName" value="#TableName#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@SortOrder" value="#SortOrder#">
<cfprocparam type="in" cfsqltype="cf_sql_varchar"
maxlength="255" dbvarname="@Condition" value="#Condition#">
<cfprocparam type="out" cfsqltype="cf_sql_varchar"
maxlength="8000" dbvarname="@SQLStr" variable=SQLStr>
<cfprocresult name="getRecordSet">
</cfstoredproc>
<cfcatch type="database">
<cfinvoke component="Components.Gendbs"
method="TrimErrorMessage" errormessage="#cfcatch.detail#"
returnvariable="CALLER.Variables.HeaderMessage" />
<cfexit>
</cfcatch>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
<!--- <cfoutput>Database: #SQLStr#</cfoutput> --->
<cfreturn getRecordset>
</cffunction>
</cfcomponent>
That's a lot of code and I still haven't wrapped it all up yet.
I'll wrap it up in the next post.
Phillip Senn
Sep 2, 2005, 5:32:18 PM9/2/05
to CF_...@googlegroups.com
Let's pretend you have a table called CRUD (for lack of a better word):
CREATE TABLE [CRUD] (
[CRUDID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CRUD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRUDDescription] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Seq] [int] NOT NULL CONSTRAINT [DF__CRUD__Seq__16451E08] DEFAULT
(0),
[Log_Timestmp] [int] NOT NULL CONSTRAINT
[DF__CRUD__Log_Times__17394241] DEFAULT (1),
CONSTRAINT [PK__CRUD__1550F9CF] PRIMARY KEY NONCLUSTERED
(
[CRUDID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
And another one (used for logging transactions) called LOG:
CREATE TABLE [Log] (
[LogID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Sproc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[LogDescription] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[DateUpdated] [datetime] NULL CONSTRAINT
[DF__Log__Date_Update__3A186A2A] DEFAULT (getutcdate()),
PRIMARY KEY NONCLUSTERED
(
[LogID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is the maintenance screen for the CRUD table:
<cfset myTable = "CRUD">
<cfset Debugging = "No">
<cfinclude template="../../includes/MaintenanceHeader.cfm">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Create, Retrieve, Update and Delete example</title>
</head>
<body>
<cfform format="flash" skin="haloBlue" preloader="false" timeout="5000"
preservedata="yes" height="700">
<cfformitem type="html"><font size="+10">Create, Retrieve, Update and Delete
example</font></cfformitem>
<cfif (NOT isDefined("FORM.FIELDNAMES")) OR IsDefined("FORM.Back")>
<cfobject name="CRUDObj" component="Components.CRUD">
<cfset CRUDQry = CRUDObj.View0()>
<cfselect query="CRUDqry" name="CRUDID" display="CRUDDescription"
value="CRUDID" width="100" size="10"
onChange="submitForm()"></cfselect>
<cfinput type="submit" name="Create" value="Create">
<cfelse>
<cfformitem
type="html"><cfoutput>#HeaderMessage#</cfoutput> </cfformitem>
<cfformitem type="hrule"></cfformitem>
<cfinput name="CRUD" label="CRUD Code:" type="text" size="3"
maxlength="2" onchange="#EnableUpdate#">
<cfinput name="CRUDDescription" label="Description:" type="text"
maxlength="255" size="50" onchange="#EnableUpdate#">
<cfinput name="Seq" label="Seq:" type="text" validate="integer"
size="3" onchange="#EnableUpdate#">
<cfinclude template="../../includes/MaintenanceNavigation.cfm">
<cfinput type="hidden" name="CRUDID" label="CRUDID" size="50">
</cfif>
</cfform>
</body>
</html>
Here is the component crud.cfc:
<cfcomponent>
<cfobject name="DatabaseObj" component="Components.Gendbs">
<cffunction name="View0" output="true" returntype="query">
<cfset qry =
DatabaseObj.Select(FieldName="*",TableName="CRUD",Condition="",SortOrder="Se
q",DataSource="#DataSource#")>
<cfreturn qry>
</cffunction>
</cfcomponent>
And here are the includes:
MaintenanceHeader.cfm:
<cfset HeaderMessage = "">
<cfimport prefix="Database" taglib="/DatabaseTags">
<cfif isDefined("FORM.AddLike")>
<cfset "FORM.#myTable#ID" = "">
<cfelseif isDefined("FORM.AddNew")>
<Database:AddNew datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.Update")>
<Database:Update datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.btnDelete")>
<Database:Delete datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.Create")>
<cfset "FORM.#MyTable#ID" = "">
<cfelseif isDefined("FORM.Back")>
<cfelseif isDefined("FORM.#MyTable#ID")>
<Database:Retrieve datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelse>
</cfif>
<cfsavecontent variable="EnableUpdate">
<cfif isDefined("FORM.FIELDNAMES")>
<cfset ID = Evaluate("FORM.#MyTable#ID")>
<cfif ID EQ "">
AddNew.enabled=true
<cfelse>
Update.enabled=true
</cfif>
</cfif>
</cfsavecontent>
<!--- Constructing an Application with Flash Forms from the Ground Up
http://coldfusion.sys-con.com/read/101342.htm --->
MaintenanceNavigation.cfm:
<cfformgroup type="horizontal">
<cfinput type="submit" name="Back" value="Return Search">
<cfinput type="button" name="Print" value="Print">
<cfset FORM.ID = Evaluate("FORM.#MyTable#ID")>
<cfif FORM.ID EQ "">
<cfinput type="submit" name="AddNew" value="Add New"
enabled="false">
<cfinput type="submit" name="btnDelete" value="Delete"
enabled="false">
<cfinput type="submit" name="AddLike" value="Add Like"
enabled="false">
<cfelse>
<cfinput type="submit" name="Update" value="Update"
enabled="false">
<cfinput type="submit" name="btnDelete" value="Delete">
<cfinput type="submit" name="AddLike" value="Add Like">
</cfif>
</cfformgroup>
<cfif Debugging EQ "yes">
<cfformitem type="hrule"></cfformitem>
<cfinput type="text" name="ID" label="#myTable#ID:" size="36">
<cfinput type="text" name="Log_TimeStmp" label="Log_TimeStmp"
validate="integer" size="10">
<cfinput type="text" name="RowCount" label="RowCount"
validate="integer" size="10">
<cfelse>
<cfinput type="hidden" name="ID">
<cfinput type="hidden" name="Log_TimeStmp">
<cfinput type="hidden" name="RowCount">
</cfif>
MaintenanceFooter.cfm:
<cfif Debugging EQ "yes">
<pre>
<cfdump var="#myForm#">
</pre>
</cfif>
So that's it (FINALLY!) We have a generic CRUD screen that only uses stored
procedures and logs the actions.
The next step is to add security to the stored procedures, so that only
users who are logged in can use them.
It's also possible to determine which fields are the ones that have been
changed and need to be included the SQL UPDATE stmt
(only the ones where the FIELD value is different from the SAVE value, which
was create in retrieve.cfm). Right now, all fields are updated, whether
they've changed or not.
There's lot of room for standardization and improvements.
CREATE TABLE [CRUD] (
[CRUDID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CRUD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CRUDDescription] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Seq] [int] NOT NULL CONSTRAINT [DF__CRUD__Seq__16451E08] DEFAULT
(0),
[Log_Timestmp] [int] NOT NULL CONSTRAINT
[DF__CRUD__Log_Times__17394241] DEFAULT (1),
CONSTRAINT [PK__CRUD__1550F9CF] PRIMARY KEY NONCLUSTERED
(
[CRUDID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
And another one (used for logging transactions) called LOG:
CREATE TABLE [Log] (
[LogID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Sproc] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[LogDescription] [varchar] (8000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[DateUpdated] [datetime] NULL CONSTRAINT
[DF__Log__Date_Update__3A186A2A] DEFAULT (getutcdate()),
PRIMARY KEY NONCLUSTERED
(
[LogID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here is the maintenance screen for the CRUD table:
<cfset myTable = "CRUD">
<cfset Debugging = "No">
<cfinclude template="../../includes/MaintenanceHeader.cfm">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Create, Retrieve, Update and Delete example</title>
</head>
<body>
<cfform format="flash" skin="haloBlue" preloader="false" timeout="5000"
preservedata="yes" height="700">
<cfformitem type="html"><font size="+10">Create, Retrieve, Update and Delete
example</font></cfformitem>
<cfif (NOT isDefined("FORM.FIELDNAMES")) OR IsDefined("FORM.Back")>
<cfobject name="CRUDObj" component="Components.CRUD">
<cfset CRUDQry = CRUDObj.View0()>
<cfselect query="CRUDqry" name="CRUDID" display="CRUDDescription"
value="CRUDID" width="100" size="10"
onChange="submitForm()"></cfselect>
<cfinput type="submit" name="Create" value="Create">
<cfelse>
<cfformitem
type="html"><cfoutput>#HeaderMessage#</cfoutput> </cfformitem>
<cfformitem type="hrule"></cfformitem>
<cfinput name="CRUD" label="CRUD Code:" type="text" size="3"
maxlength="2" onchange="#EnableUpdate#">
<cfinput name="CRUDDescription" label="Description:" type="text"
maxlength="255" size="50" onchange="#EnableUpdate#">
<cfinput name="Seq" label="Seq:" type="text" validate="integer"
size="3" onchange="#EnableUpdate#">
<cfinclude template="../../includes/MaintenanceNavigation.cfm">
<cfinput type="hidden" name="CRUDID" label="CRUDID" size="50">
</cfif>
</cfform>
</body>
</html>
Here is the component crud.cfc:
<cfcomponent>
<cfobject name="DatabaseObj" component="Components.Gendbs">
<cffunction name="View0" output="true" returntype="query">
<cfargument name="DataSource" type="string"
default="#Application.DSN#">
<cfset qry =
DatabaseObj.Select(FieldName="*",TableName="CRUD",Condition="",SortOrder="Se
q",DataSource="#DataSource#")>
<cfreturn qry>
</cffunction>
</cfcomponent>
And here are the includes:
MaintenanceHeader.cfm:
<cfset HeaderMessage = "">
<cfimport prefix="Database" taglib="/DatabaseTags">
<cfif isDefined("FORM.AddLike")>
<cfset "FORM.#myTable#ID" = "">
<cfelseif isDefined("FORM.AddNew")>
<Database:AddNew datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.Update")>
<Database:Update datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.btnDelete")>
<Database:Delete datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelseif isDefined("FORM.Create")>
<cfset "FORM.#MyTable#ID" = "">
<cfelseif isDefined("FORM.Back")>
<cfelseif isDefined("FORM.#MyTable#ID")>
<Database:Retrieve datasource="#Application.DSN#"
tablename="#myTable#" debug="#Debugging#">
<cfelse>
</cfif>
<cfsavecontent variable="EnableUpdate">
<cfif isDefined("FORM.FIELDNAMES")>
<cfset ID = Evaluate("FORM.#MyTable#ID")>
<cfif ID EQ "">
AddNew.enabled=true
<cfelse>
Update.enabled=true
</cfif>
</cfif>
</cfsavecontent>
<!--- Constructing an Application with Flash Forms from the Ground Up
http://coldfusion.sys-con.com/read/101342.htm --->
MaintenanceNavigation.cfm:
<cfformgroup type="horizontal">
<cfinput type="submit" name="Back" value="Return Search">
<cfinput type="button" name="Print" value="Print">
<cfset FORM.ID = Evaluate("FORM.#MyTable#ID")>
<cfif FORM.ID EQ "">
<cfinput type="submit" name="AddNew" value="Add New"
enabled="false">
<cfinput type="submit" name="btnDelete" value="Delete"
enabled="false">
<cfinput type="submit" name="AddLike" value="Add Like"
enabled="false">
<cfelse>
<cfinput type="submit" name="Update" value="Update"
enabled="false">
<cfinput type="submit" name="btnDelete" value="Delete">
<cfinput type="submit" name="AddLike" value="Add Like">
</cfif>
</cfformgroup>
<cfif Debugging EQ "yes">
<cfformitem type="hrule"></cfformitem>
<cfinput type="text" name="ID" label="#myTable#ID:" size="36">
<cfinput type="text" name="Log_TimeStmp" label="Log_TimeStmp"
validate="integer" size="10">
<cfinput type="text" name="RowCount" label="RowCount"
validate="integer" size="10">
<cfelse>
<cfinput type="hidden" name="ID">
<cfinput type="hidden" name="Log_TimeStmp">
<cfinput type="hidden" name="RowCount">
</cfif>
MaintenanceFooter.cfm:
<cfif Debugging EQ "yes">
<pre>
<cfdump var="#myForm#">
</pre>
</cfif>
So that's it (FINALLY!) We have a generic CRUD screen that only uses stored
procedures and logs the actions.
The next step is to add security to the stored procedures, so that only
users who are logged in can use them.
It's also possible to determine which fields are the ones that have been
changed and need to be included the SQL UPDATE stmt
(only the ones where the FIELD value is different from the SAVE value, which
was create in retrieve.cfm). Right now, all fields are updated, whether
they've changed or not.
There's lot of room for standardization and improvements.
ps...@alexlee.com
Sep 4, 2005, 4:55:31 PM9/4/05
to CF_COAL
I sent the same 2 mails to Raymond Camden [r...@camdenfamily.com], with
the explanation:
"I'm starting a discussion on the COAL user group.
I thought you might be interested as well".
the explanation:
"I'm starting a discussion on the COAL user group.
I thought you might be interested as well".
He replied:
"Interesting stuff. How can I subscribe?"
Ryan Guill
Sep 6, 2005, 2:27:56 PM9/6/05
to CF_...@googlegroups.com
Phillip Senn
Sep 6, 2005, 2:45:28 PM9/6/05
to CF_...@googlegroups.com
Ryan,
This wasn't the response I was expecting.
At first I thought maybe you hadn't seen my post because it was the weekend.
Then I thought maybe it was because it was Labor Day.
I thought "This would be a really cool example because it's generic and
needs to be done with practically every table of every project".
Then I thought, "With Ray Camden on our side, this would be a kick ass
development team".
I mean - if Ray Camden expresses any interest at all, we (everyone in the
group) should make a point to personally invite him.
What I'm trying to say is: I'm excited about the possibilities!
This wasn't the response I was expecting.
At first I thought maybe you hadn't seen my post because it was the weekend.
Then I thought maybe it was because it was Labor Day.
I thought "This would be a really cool example because it's generic and
needs to be done with practically every table of every project".
Then I thought, "With Ray Camden on our side, this would be a kick ass
development team".
I mean - if Ray Camden expresses any interest at all, we (everyone in the
group) should make a point to personally invite him.
What I'm trying to say is: I'm excited about the possibilities!
Ryan Guill
Sep 6, 2005, 2:44:59 PM9/6/05
to CF_...@googlegroups.com
Phillip,
Im sorry man, I must be missing something...
What is it were talking about again? I thought you were just asking
how Ray could sign up for the coal list... I must have lost something
in the shuffle. And the lack of response was because of the
weekend/holiday and now a crazy tuesday that thinks its a monday!
Sorry for the confusion... although im still confused, LOL!
Im sorry man, I must be missing something...
What is it were talking about again? I thought you were just asking
how Ray could sign up for the coal list... I must have lost something
in the shuffle. And the lack of response was because of the
weekend/holiday and now a crazy tuesday that thinks its a monday!
Sorry for the confusion... although im still confused, LOL!
Phillip Senn
Sep 6, 2005, 2:54:33 PM9/6/05
to CF_...@googlegroups.com
Maybe it's the way google handled my post from Friday afternoon.
Go to the web site http://groups.google.com/group/CF_COAL
to see that I posted some largish code there.
Here it is again, just to cover all the bases:
SQL Server stored procedures A) Select, B) Update, C) Insert, D) Delete E)
getPrimaryKey F) get.
Part Two (2):
Go to the web site http://groups.google.com/group/CF_COAL
to see that I posted some largish code there.
Here it is again, just to cover all the bases:
getPrimaryKey F) get.
Ryan Guill
Sep 6, 2005, 3:02:48 PM9/6/05
to CF_...@googlegroups.com
Ah ha. I remember seeing this now, but I assumed it was on one of my
other mailing lists. Didn't think to look to see where it came from.
Everything looks good, and sounds like an excellent idea. Only
problem is I wont be able to help much except maybe with helping plug
it into a component and offering suggestions. Mainly because I am not
much of a DBA (I get by for sure, but I prefer doing things with cf
rather than the db in most cases, mostly because I dont know how to do
the things I need to in SQL) and also because I don't have access to
sql server. But please don't let that discourage you at all. Please
keep talking about it, maybe make a new thread in the CF_COAL group to
talk about it and develop it on out! It sounds like something that
would be quite useful and maybe bring us a little closer to a *rails*
type situation...
Thanks for the effort man, keep it up!
other mailing lists. Didn't think to look to see where it came from.
Everything looks good, and sounds like an excellent idea. Only
problem is I wont be able to help much except maybe with helping plug
it into a component and offering suggestions. Mainly because I am not
much of a DBA (I get by for sure, but I prefer doing things with cf
rather than the db in most cases, mostly because I dont know how to do
the things I need to in SQL) and also because I don't have access to
sql server. But please don't let that discourage you at all. Please
keep talking about it, maybe make a new thread in the CF_COAL group to
talk about it and develop it on out! It sounds like something that
would be quite useful and maybe bring us a little closer to a *rails*
type situation...
Thanks for the effort man, keep it up!
Reply all
Reply to author
Forward
0 new messages