CFF Coding Source - SELECT Statement, INSERT Statement, UPDATE Statement, DELETE Statement, in ASP using Parameter's, in the combat against SQL & XSS INJECTION

Live Editing Disabled for Server-Side Example

HTML

<%' The above file is our Connection to our database.
' Below is all the code that is needed in order to retrieve our data from our Database.
getType = protectSQL(request.QueryString("Type"))
Set getAllSQL = CreateObject("ADODB.Command")
getAllSQL.ActiveConnection=objConn
getAllSQL.Prepared = true
getAllSQL.Commandtext = "SELECT ID, MyFirst, MySecond, MyInfo FROM Teaching" ' We only want to get the list of records, so we do not need anything else added to our SELECT STATEMENT.
set objRs = getAllSQL.execute
set objRs1 = getAllSQL.execute ' So we can reuse
getS = ProtectSQL(Request.QueryString("Update")) ' Looks at the QueryString, if present, then we will use it against our database, wrapped in our Global Injection Protector

if getS<>"" then ' Check to see if there is a value given for the Update query. If a value exists, then proceed.
'response.Write getS
'response.End()
Set getSQL = CreateObject("ADODB.Command") ' Creates our Command Object
getSQL.ActiveConnection=objConn ' Gets our Connection
getSQL.Prepared = true ' Stores the CommandText for future use (To retrieve the records faster)
getSQL.Commandtext = "SELECT ID, MyFirst, MySecond, MyInfo FROM Teaching where ID=?" ' Always list these in order as they appear.
' Parameters will help with the prevention of SQL INJECTION. Get used to using this, as it will save your site and maybe even your job one day.
getSQL.Parameters.Append getSQL.CreateParameter("@ID", adInteger, adParamInput, , getS)
set UpdateRs = getSQL.execute ' We set our RecordSet here, so there i no need to use the CreateObject.

if not UpdateRs.eof then ' If there are any records, we will continue on to the next line. If the DB is empty, then we stop.
MyID = int(UpdateRs("ID")) ' Gets the ID, and the int, make sure that it remains a Number.
MyFirst = ReverseSQL(UpdateRs("MyFirst")) ' Gets the MyFirst Record
MySecond = ReverseSQL(UpdateRs("MySecond")) ' Gets the MySecond Record
MyInfo = ReverseSQL(UpdateRs("MyInfo"))
' To read the information back into the Memofield (Textarea), we have to format it and strip the tags from it.
end if ' end it here
' close our RecordSet
UpdateRs.close
set UpdateRs = nothing
end if ' end it here
%>
<!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=utf-8" />
<title>ASP Classic - SELECT Statement, INSERT Statement, UPDATE Statement, DELETE Statement, in ASP using Parameter's, in the combat against SQL & XSS INJECTION</title>
<%' Always put your Attributes up here; never keep them in your HTML.
' The WIDTH tag is no longer supported in valid XHTML, so always do your WIDTH and HEIGHT tags here.
' Capital Letters in your CSS Values is not supported. Your Class and ID Names can be CaPiTaL but not the attributes.%>
<style type="text/css">
.MyTdTop{
border:1px #000000 dotted;
}
.MyTd{
border:1px #000000 double;
}
</style>

</head>

<body>
<a href="Load.asp?Type=Insert">Insert Records</a> | <a href="Load.asp?Type=Delete">Delete Records</a> | <a href="Load.asp?Type=All">View All Records</a>
<%if getType="All" or getS<>"" then%>
<form name="UpdateForm">
<table>
<tr><td colspan="2"  class="MyTdTop">Update an existing Record in the Database</td></tr>
<input name="MyID" type="hidden" value="<%=MyID%>" />
<tr><td class="MyTd">My First</td><td><input name="MyFirst" type="text" value="<%=MyFirst%>" /></td></tr>
<tr><td class="MyTd">My Second</td><td><input name="MySecond" type="text" value="<%=MySecond%>" /></td></tr>
<tr><td class="MyTd">Information</td><td><textarea name="MyInfo" cols="25" rows="5"><%=MyInfo%></textarea></td></tr>
<tr><td colspan="2" class="MyTd">
<input type="submit" name="Submit" tabindex="3" style="width:150px;" value="Update" id="Update" onClick="document.getElementById('SubmitForm').value='Update';" />
<input type="hidden" id="SubmitForm" name="SubmitForm" value="" />
</td></tr>
<tr><td colspan="2" class="MyTd"><div class="postData">Update</div></td></tr>
</table>
</form>
<%elseif getType="Insert" then%>

<form name="InsertForm">
<table>
<tr><td colspan="2"  class="MyTdTop">Insert a New Record into the Database</td></tr>
<tr><td class="MyTd">My First</td><td><input name="MyFirst" type="text" /></td></tr>
<tr><td class="MyTd">My Second</td><td><input name="MySecond" type="text" /></td></tr>
<tr><td class="MyTd">Information</td><td><textarea name="MyInfo" cols="25" rows="5"></textarea></td></tr>
<tr><td colspan="2" class="MyTd"><input type="submit" name="Insert" tabindex="3" style="width:150px;" value="Update" id="Update" onClick="document.getElementById('SubmitForm').value='Insert';" />
<input type="hidden" id="SubmitForm" name="SubmitForm" value="" /></td></tr>
<tr><td colspan="2" class="MyTd"><div class="postData">Insert</div></td></tr>
</table>
</form>
<%elseif getType="Delete" then%>

<form name="DeleteForm">
<table>
<tr><td colspan="2"  class="MyTdTop">Delete a Record in a Database</td></tr>
<tr><td class="MyTd">Choose Record</td><td><%'We are going to get the Values from the database and put them in a Dropdown (ListMenu) and so we can choose the item that we want to Delete%>
<select name="DelCom" style="font: 8pt verdana;">
<option value="">Choose a Record</option><%
While (NOT objRs.EOF)
if not objRs.eof then	
strSec = objRs("MySecond")
strFirst = objRs("MyFirst")
strID = objRs("ID")						  
end if
%>
<option value="<%=strID%>"><%=strFirst&" - "&strSec%></option><%
  objRs.MoveNext()
Wend
%>
</select>
</td></tr>
<tr><td colspan="2" class="MyTd"><input type="submit" name="Delete" tabindex="3" style="width:150px;" value="Update" id="Update" onClick="document.getElementById('SubmitForm').value='Delete';" />
<input type="hidden" id="SubmitForm" name="SubmitForm" value="" /></td></tr>
<tr><td colspan="2" class="MyTd"><div class="postData">Delete</div></td></tr>
</table>
</form>

<%End if%>

<%' Here, we are going to list all the Records in the Database, so we can then choose the one that we want to edit (UPDATE)
' This will also show you how to loop through the records and display them on the page.%>
<table>
<tr><td><caption>List of all the records to update</caption></td></tr>
  <tr>
    <td class="MyTdTop">ID</td>
    <td class="MyTdTop">Names</td>
  </tr><%While Not objRs1.EOF%><tr>
    <td class="MyTd"><%=objRS1("ID")%></td>
    <td class="MyTd"><a href="https://www.cffcs.com/Lessons/8/Load.asp?Update=<%=objRS1("ID")%>"><%=objRS1("MyFirst")&" "&objRs1("MySecond")%></a></td>
  </tr><%' Always close out of your RecordSets and your DB Connection after you are finished with them and no longer are in need of them.
    objRs1.MoveNext
    Wend
    objRs1.Close
    Set objRs=Nothing
	objConn.close
	set objConn = nothing
%></table>
<script src="https://www.cffcs.com/Assets/jquery-3.6.0.js"></script>
<script type="text/javascript" src="https://www.cffcs.com/Lessons/8/Newajaxsbmt.js"></script>

</body>
</html>

JavaScript

$(document).ready(function(){
    $('form').submit(function(e){
        e.preventDefault();
        $.ajax({
            url: "setRecords.asp",
            type: "POST",
            data: $(this).serialize(),
            success: function(data){
                $(".postData").html(data);
            },
            error: function(){
                $(".postData").html("Form submission failed!");
            }
        });

    });
});

SQL

-- Create a new database called [Virtual-Class-01] in SQL Server.
-- Right-click and choose [New Query]
-- Copy and paste the code below and hit [Execute]

USE [Virtual-Class-01]
GO
/****** Object:  Table [dbo].[Teaching]    Script Date: 5/12/2022 2:43:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teaching](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MyFirst] [nvarchar](50) NOT NULL,
    [MySecond] [nvarchar](50) NOT NULL,
    [MyInfo] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Teaching] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Teaching] ON 
GO
INSERT [dbo].[Teaching] ([ID], [MyFirst], [MySecond], [MyInfo]) VALUES (4, N'Second', N'Second Line in Second Column', N'This is another bit of information that is not really worth anything, just something to write.')
GO
INSERT [dbo].[Teaching] ([ID], [MyFirst], [MySecond], [MyInfo]) VALUES (5, N'Carrz-Fox-Fire Promotions', N'Company Name', N'This is our company name.')
GO
SET IDENTITY_INSERT [dbo].[Teaching] OFF
GO

Classic ASP

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%' The above line only goes in this file, and this file MUST BE the 1 INCLUDE FILE of every page that needs to have a database connection.

' This page contains the link to the ADOVBS.inc file, which contains all your Parameters.
' This is very important in the battle to combat SQL Injection.
%>
<!--#include file="ADOVBS.inc"-->
<%
siteurl = "192.168.2.12/cs"
getServer = "sqlcorecs-01"
getInstance = "sql2019"
getID = "testuser"
getPW = "testuser"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open("Provider=SQLOLEDB; Data Source="&getServer&"\"&getInstance&"; Initial Catalog=Virtual-Class-01;User ID="&getID&";Password="&getPW&";")
' Remember, when you open a connection to your database, you will need to close it.
' You will find the closers in the setRecords.asp and Load.asp
' At the bottom of each page.


' Added 12/11/2009
' We are going to protect our database and our site globally. Tested and true.

' Now we are going to protect all of our database entries against possible intrusions and attacks.
' Which are most commonly referred to as SQL & XSS Injections.
Function ProtectSQL(SQLString)
SQLString = Replace(SQLString, "'", "''") ' replace single Quotes with Double Quotes
SQLString = Replace(SQLString, ">", "&gt;") ' replace > with &gt;
SQLString = Replace(SQLString, "<", "&lt;") ' replace < with &lt;
SQLString = Replace(SQLString, "(","&#40;") ' replace ( with &#40;
SQLString = Replace(SQLString, ")","&#41;") ' replace ) with &#41;
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function

' Just added 5/12/2022
' Here we are going to reverse our protection; this is used to display our records back to an input or textarea for editing.
Function ReverseSQL(SQLReverse)
SQLReverse = Replace(SQLReverse, "''", "'") ' replace single Quotes with Double Quotes
SQLReverse = Replace(SQLReverse, "(","&#40;") ' replace ( with &#40;
SQLReverse = Replace(SQLReverse, ")","&#41;") ' replace ) with &#41;
SQLReverse = Replace(SQLReverse, "<br />", vblf) ' replace vblf with <br /> (This is mainly used for Memo fields.
SQLReverse = Trim(SQLReverse)
ReverseSQL = SQLReverse
End Function

<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996-1998 Microsoft Corporation.  All Rights Reserved.
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- ExecuteOptionEnum Values ----
Const adRunAsync = &H00000010
Const adAsyncExecute = &H00000010
Const adAsyncFetch = &H00000020
Const adAsyncFetchNonBlocking = &H00000040
Const adExecuteNoRecords = &H00000080

'---- ConnectOptionEnum Values ----
Const adAsyncConnect = &H00000010

'---- ObjectStateEnum Values ----
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001
Const adStateConnecting = &H00000002
Const adStateExecuting = &H00000004
Const adStateFetching = &H00000008

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
'Const adDBTimeStamp = 135
Const adDateTime = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205
Const adChapter = 136
Const adFileTime = 64
Const adDBFileTime = 137
Const adPropVariant = 138
Const adVarNumeric = 139

'---- FieldAttributeEnum Values ----
Const adFldMayDefer = &H00000002
Const adFldUpdatable = &H00000004
Const adFldUnknownUpdatable = &H00000008
Const adFldFixed = &H00000010
Const adFldIsNullable = &H00000020
Const adFldMayBeNull = &H00000040
Const adFldLong = &H00000080
Const adFldRowID = &H00000100
Const adFldRowVersion = &H00000200
Const adFldCacheDeferred = &H00001000
Const adFldKeyColumn = &H00008000

'---- EditModeEnum Values ----
Const adEditNone = &H0000
Const adEditInProgress = &H0001
Const adEditAdd = &H0002
Const adEditDelete = &H0004

'---- RecordStatusEnum Values ----
Const adRecOK = &H0000000
Const adRecNew = &H0000001
Const adRecModified = &H0000002
Const adRecDeleted = &H0000004
Const adRecUnmodified = &H0000008
Const adRecInvalid = &H0000010
Const adRecMultipleChanges = &H0000040
Const adRecPendingChanges = &H0000080
Const adRecCanceled = &H0000100
Const adRecCantRelease = &H0000400
Const adRecConcurrencyViolation = &H0000800
Const adRecIntegrityViolation = &H0001000
Const adRecMaxChangesExceeded = &H0002000
Const adRecObjectOpen = &H0004000
Const adRecOutOfMemory = &H0008000
Const adRecPermissionDenied = &H0010000
Const adRecSchemaViolation = &H0020000
Const adRecDBDeleted = &H0040000

'---- GetRowsOptionEnum Values ----
Const adGetRowsRest = -1

'---- PositionEnum Values ----
Const adPosUnknown = -1
Const adPosBOF = -2
Const adPosEOF = -3

'---- enum Values ----
Const adBookmarkCurrent = 0
Const adBookmarkFirst = 1
Const adBookmarkLast = 2

'---- MarshalOptionsEnum Values ----
Const adMarshalAll = 0
Const adMarshalModifiedOnly = 1

'---- AffectEnum Values ----
Const adAffectCurrent = 1
Const adAffectGroup = 2
Const adAffectAll = 3
Const adAffectAllChapters = 4

'---- ResyncEnum Values ----
Const adResyncUnderlyingValues = 1
Const adResyncAllValues = 2

'---- CompareEnum Values ----
Const adCompareLessThan = 0
Const adCompareEqual = 1
Const adCompareGreaterThan = 2
Const adCompareNotEqual = 3
Const adCompareNotComparable = 4

'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterPredicate = 4
Const adFilterConflictingRecords = 5

'---- SearchDirectionEnum Values ----
Const adSearchForward = 1
Const adSearchBackward = -1

'---- PersistFormatEnum Values ----
Const adPersistADTG = 0
Const adPersistXML = 1

'---- StringFormatEnum Values ----
Const adStringXML = 0
Const adStringHTML = 1
Const adClipString = 2

'---- ConnectPromptEnum Values ----
Const adPromptAlways = 1
Const adPromptComplete = 2
Const adPromptCompleteRequired = 3
Const adPromptNever = 4

'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &Hc
Const adModeShareDenyNone = &H10

'---- IsolationLevelEnum Values ----
Const adXactUnspecified = &Hffffffff
Const adXactChaos = &H00000010
Const adXactReadUncommitted = &H00000100
Const adXactBrowse = &H00000100
Const adXactCursorStability = &H00001000
Const adXactReadCommitted = &H00001000
Const adXactRepeatableRead = &H00010000
Const adXactSerializable = &H00100000
Const adXactIsolated = &H00100000

'---- XactAttributeEnum Values ----
Const adXactCommitRetaining = &H00020000
Const adXactAbortRetaining = &H00040000

'---- PropertyAttributesEnum Values ----
Const adPropNotSupported = &H0000
Const adPropRequired = &H0001
Const adPropOptional = &H0002
Const adPropRead = &H0200
Const adPropWrite = &H0400

'---- ErrorValueEnum Values ----
Const adErrInvalidArgument = &Hbb9
Const adErrNoCurrentRecord = &Hbcd
Const adErrIllegalOperation = &Hc93
Const adErrInTransaction = &Hcae
Const adErrFeatureNotAvailable = &Hcb3
Const adErrItemNotFound = &Hcc1
Const adErrObjectInCollection = &Hd27
Const adErrObjectNotSet = &Hd5c
Const adErrDataConversion = &Hd5d
Const adErrObjectClosed = &He78
Const adErrObjectOpen = &He79
Const adErrProviderNotFound = &He7a
Const adErrBoundToCommand = &He7b
Const adErrInvalidParamInfo = &He7c
Const adErrInvalidConnection = &He7d
Const adErrNotReentrant = &He7e
Const adErrStillExecuting = &He7f
Const adErrOperationCancelled = &He80
Const adErrStillConnecting = &He81
Const adErrNotExecuting = &He83
Const adErrUnsafeOperation = &He84

'---- ParameterAttributesEnum Values ----
Const adParamSigned = &H0010
Const adParamNullable = &H0040
Const adParamLong = &H0080

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

'---- EventStatusEnum Values ----
Const adStatusOK = &H0000001
Const adStatusErrorsOccurred = &H0000002
Const adStatusCantDeny = &H0000003
Const adStatusCancel = &H0000004
Const adStatusUnwantedEvent = &H0000005

'---- EventReasonEnum Values ----
Const adRsnAddNew = 1
Const adRsnDelete = 2
Const adRsnUpdate = 3
Const adRsnUndoUpdate = 4
Const adRsnUndoAddNew = 5
Const adRsnUndoDelete = 6
Const adRsnRequery = 7
Const adRsnResynch = 8
Const adRsnClose = 9
Const adRsnMove = 10
Const adRsnFirstChange = 11
Const adRsnMoveFirst = 12
Const adRsnMoveNext = 13
Const adRsnMovePrevious = 14
Const adRsnMoveLast = 15

'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialConstraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29
Const adSchemaDBInfoKeywords = 30
Const adSchemaDBInfoLiterals = 31
Const adSchemaCubes = 32
Const adSchemaDimensions = 33
Const adSchemaHierarchies = 34
Const adSchemaLevels = 35
Const adSchemaMeasures = 36
Const adSchemaProperties = 37
Const adSchemaMembers = 38

'---- SeekEnum Values ----
Const adSeekFirstEQ = &H1
Const adSeekLastEQ = &H2
Const adSeekAfterEQ = &H4
Const adSeekAfter = &H8
Const adSeekBeforeEQ = &H10
Const adSeekBefore = &H20

'---- ADCPROP_UPDATECRITERIA_ENUM Values ----
Const adCriteriaKey = 0
Const adCriteriaAllCols = 1
Const adCriteriaUpdCols = 2
Const adCriteriaTimeStamp = 3

'---- ADCPROP_ASYNCTHREADPRIORITY_ENUM Values ----
Const adPriorityLowest = 1
Const adPriorityBelowNormal = 2
Const adPriorityNormal = 3
Const adPriorityAboveNormal = 4
Const adPriorityHighest = 5

'---- CEResyncEnum Values ----
Const adResyncNone = 0
Const adResyncAutoIncrement = 1
Const adResyncConflicts = 2
Const adResyncUpdates = 4
Const adResyncInserts = 8
Const adResyncAll = 15

'---- ADCPROP_AUTORECALC_ENUM Values ----
Const adRecalcUpFront = 0
Const adRecalcAlways = 1
%>

<!--#include file="ACN.asp"-->
<%' Update 1/11/2016 (Added Global Protection line: 36)
'Here we have all of our database statements. We keep them on a single page and choose which one we wish to use by using a simple IF STATEMENT. Using the IF statement, we can have multiple statements on the same page. This cuts down on the paper trail and makes it easier to work with. 

'Always keep everything together, and put other items in other places. For example, our database connection is in a file called cons.asp, which stands for Connections.

'The Parameters that we are using here will help in the combat against SQL INJECTION and also makes for a better code presentation for yourself and future developers that will come in, later on, to work with your code.

'Using Parameters makes sure that you set them in order. For Example
'UpCom.CommandText = "Update Teaching set [MyFirst]=?, [MySecond]=? WHERE ID=?"
'UpCom.Parameters.Append UpCom.CreateParameter("@MyFirst", adChar, adParamInput, 255, UpF)
'UpCom.Parameters.Append UpCom.CreateParameter("@MySecond", adChar, adParamInput, 255, UpS
'UpCom.Parameters.Append UpCom.CreateParameter("@ID", adInteger, adParamInput, , UpID)

'In the above, each line corresponds with the way it is presented in the statement, make sure that you do not get them mixed up, as you can mess up your records (Or) it will generate an error.

'I am pretty sure that you will grasp the concept here. It is easy to use and understand.
'The file ADOVBS.inc holds the values for all the Parameters that are located in your statements CreateParameter.

'Good Luck
'Carrzkiss (Wayne Barron – Carrz-Fox-Fire Promotions)
'response.Write "Hello World"

'====================================================================================================
' Now that we have it wrapped in a Function, we can now apply it to our field in the code below.
' We will add it to every field that requires protection to keep ourselves out of harm's way.
' Example of usuage....
' fieldVale = ProtectSQL(trim(request.Form("MyFieldValue")))
' fieldNum  = ProtectSQL(int(request.Form("MyFieldNumber")))
' You will notice that we have   ProtectSQL(trim(
' we always want to add in what the variable is, rather it is
' text   = TRIM
' number = INT 
' Please look through the code and keep up good practice.
'gettheSub = Request.Form("SubmitForm")
getSub = protectSQL(request.form("SubmitForm"))
'strGet = request.Form("Submit")
'response.Write "Hello"
if getSub="Delete" then
Del = ProtectSQL(request.form("DelCom"))
' We need to make sure that there is a record selected, so that we do not get an error on the form.
if Del="Choose a Record" then
response.write"Please choose a record to delete"
else
Set DelCom=Server.CreateObject("ADODB.Command")
DelCom.ActiveConnection=objConn
DelCom.CommandText = "DELETE FROM Teaching WHERE ID=?"
DelCom.Parameters.Append DelCom.CreateParameter("@ID", adInteger, adParamInput, , Del)
DelCom.Execute
response.Write"Record Deleted Successfully!"

Set getSQLCt = CreateObject("ADODB.Command") ' Creates our Command Object
getSQLCt.ActiveConnection=objConn ' Gets our Connection
getSQLCt.Prepared = true ' Stores the CommandText for future use (To retrieve the records faster)
getSQLCt.Commandtext = "SELECT count(ID) as ctItems FROM Teaching" ' Always list these in order as they appear.
set rsCt = getSQLCt.execute ' We set our RecordSet here, so there i no need to use the CreateObject.
if rsCt("ctItems")= 0 then
MyFirst = Split("First,Second,Carrz-Fox-Fire Promotions,CFF Coding Source", ",")
MySecond = Split("First line in Second Column,Second Line in Second Column,Company Name,Website Design", ",")
MyInfo = Split("This information in about something else all together.,This is another bit of information that is not really worth anything just something to write.,This is our company name!,A site for developing your skills as a web developer.", ",")
'arrMN = Split(SplitIt, ",")
for i=0 to uBound(MyFirst)
Set genSQL = CreateObject("ADODB.Command")
genSQL.ActiveConnection=objConn
genSQL.Prepared = true
genSQL.commandtext = "insert into Teaching (MyFirst, MySecond, MyInfo) values (?,?,?)"
genSQL.Parameters.Append genSQL.CreateParameter("@ListName", adVarChar, adParamInput, 255, MyFirst(i))
genSQL.Parameters.Append genSQL.CreateParameter("@ListName", adVarChar, adParamInput, 255, MySecond(i))
genSQL.Parameters.Append genSQL.CreateParameter("@ListName", adVarChar, adParamInput, 500, MyInfo(i))
genSQL.execute
next
response.Write "Inserted Another Set."
end if
'end if
end if
end if

' To update an item we do this
if getSub="Update" then
' get our fields
UpF = ProtectSQL(Trim(request.Form("MyFirst")))
UpS = ProtectSQL(Trim(request.Form("MySecond")))
UpID = ProtectSQL(Int(request.Form("MyID")))
' now lets make sure they have information in them
if UpF="" then
response.Write "Field #1 is not allow blank"
elseif UpS="" then
response.Write "Field #2 is not allow blank"
else
Set UpCom=Server.CreateObject("ADODB.Command")
UpCom.ActiveConnection=objConn
UpInfo = ProtectSQL(trim(request.Form("MyInfo")))
UpCom.CommandText = "Update Teaching set [MyFirst]=?, [MySecond]=?, [MyInfo]=? WHERE ID=?"
UpCom.Parameters.Append UpCom.CreateParameter("@MyFirst", adVarChar, adParamInput, 255, UpF)
UpCom.Parameters.Append UpCom.CreateParameter("@MySecond", adVarChar, adParamInput, 255, UpS)
UpCom.Parameters.Append UpCom.CreateParameter("@MyInfo", adVarChar, adParamInput, 4000, UpInfo)
UpCom.Parameters.Append UpCom.CreateParameter("@ID", adInteger, adParamInput, , UpID)
UpCom.Execute
response.Write"Record Updated Successfully!"
end if
end if

' To insert an item we do this
if getSub="Insert" then
InsF = ProtectSQL(Trim(request.Form("MyFirst")))
InsS = ProtectSQL(Trim(request.Form("MySecond")))
InsInfo = ProtectSQL(trim(request.Form("MyInfo")))
if InsF="" then
response.Write "Field #1 is not allow blank"
elseif InsS="" then
response.Write "Field #2 is not allow blank"
elseif InsInfo="" then
response.Write "Field #3 is not allow blank"
else
Set InsCom=Server.CreateObject("ADODB.Command")
InsCom.ActiveConnection=objConn
InsCom.CommandText = "Insert into Teaching(MyFirst, MySecond, MyInfo)Values(?,?,?) "
InsCom.Parameters.Append InsCom.CreateParameter("@MyFirst", adVarChar, adParamInput, 255, InsF)
InsCom.Parameters.Append InsCom.CreateParameter("@MySecond", adVarChar, adParamInput, 255, InsS)
InsCom.Parameters.Append InsCom.CreateParameter("@MyInfo", adVarChar, adParamInput, 4000, InsInfo)
InsCom.Execute
response.Write"Record Inserted Successfully!"
end if
end if
    ' Close out connections
    objConn.close
    set objConn = nothing
%>

Preview

About Company

Top Viewed Articles