這份是留言板的原程式碼,採三層結構拆分,前端以 AJAX 無刷新更新列表。整個功能只保留留言,程式碼本身就是重點,因此以下依檔案逐一列出,並在每個檔案前簡短說明它的角色。
先設定 UTF-8 相關的 codepage 與字元集,再依 tCMD 決定要新增或刪除,最後一律重新取得分頁資料輸出。表單送出時以 JavaScript 呼叫 $() 觸發非同步請求。
<%@ codepage=65001%>
<% option explicit %>
<% Response.Charset="utf-8"
Session.CodePage=65001 %>
<!-- #include file="AccHelper.asp" -->
<!-- #include file="Common.asp" -->
<!-- #include file="DAL_Guest.asp" -->
<!-- #include file="MOD_Guest.asp" -->
<!-- #include file="BLL_Guest.asp" -->
<%
Dim mybll, myList
Set mybll = new BLL_Guest
Select Case Request("tCMD")
Case "SAVE"
mybll.Insert()
Case "DEL"
mybll.Delete()
End Select
myList = mybll.FindByPage()
%>
<h2>客戶留言</h2>
<p>
<form name="subForm" id="subForm">
<textarea id="con" name="con" cols="56" rows="6" wrap="VIRTUAL"></textarea>
<input type="button" name="submit" value="提交留言"
onclick="$('guest/index.asp?tCMD=SAVE&content='+escape(this.form.con.value))" />
</form>
</p>
<%=myList%>
不直接碰資料庫,負責把請求值放進模型物件、補上時間與 IP,再交給 DAL;分頁 HTML 的組裝也在此完成。
<%
Class BLL_Guest
Private mycom, mymod, mydal
Private LI, UL
Private DEL
Public Sub GetGuest()
mydal.GetGuest(Id)
End Sub
'新增資訊
Public Sub Insert()
mymod.Content = Request("content")
mymod.re = Request("Re")
mymod.Addtime = Now()
mymod.Ip = request.servervariables("HTTP_X_FORWARDED_FOR")
If len(mymod.Ip) <= 0 Then mymod.Ip = request.servervariables("REMOTE_ADDR")
Call mydal.Insert(mymod)
End Sub
Public Sub Update()
Call mydal.Update(mymod)
End Sub
Public Sub Delete()
mydal.Delete(Request("Id"))
End Sub
'分頁查找
Public Function FindByPage()
Dim PageSize, CurrentPage, WhereValue, OrderValue, RecordCount
Dim objRS, tmp1, tmp2, tmp3, parms, i, tCMD
tCMD = Request("tCMD")
PageSize = "8"
CurrentPage = Request("PageNo")
If Len(CurrentPage) <= 0 Then CurrentPage = 1
WhereValue = ""
OrderValue = "Id"
Set objRS = mydal.FindByPage(PageSize, CurrentPage - 1, WhereValue, OrderValue)
RecordCount = mydal.Count(WhereValue)
i = 1
Do While Not objRS.EOF
If tCMD = "DEL" Then
DEL = "<a href=""?tCMD=DEL&Id=" & objRS("Id") & """>刪除</a>"
Else
DEL = ""
End If
parms = Array(i, objRS("content"), objRS("ip"), objRS("addtime"), DEL)
tmp1 = tmp1 & mycom.Format(LI, parms)
objRS.MoveNext
i = i + 1
Loop
tmp3 = mycom.Page(PageSize, CurrentPage, RecordCount, "guest/index.asp?tCMD=LIST")
parms = Array(tmp1, tmp3)
tmp2 = tmp2 & mycom.Format(UL, parms)
FindByPage = tmp2
End Function
Private Sub Class_Initialize()
LI = "<li><span>{0}</span> <span>{1}</span><br /><span>作者:{2}</span> <span>{3}</span> <span>{4}</span></li>" & vbCrLf
UL = "<UL>{0}<li>{1}</li></UL>"
DEL = ""
Set mycom = new Common
Set mymod = new MOD_Guest
Set mydal = new DAL_Guest
End Sub
Private Sub Class_Terminate()
Set mycom = Nothing
Set mymod = Nothing
Set mydal = Nothing
End Sub
End Class
%>
七條 SQL 集中宣告於初始化,透過 Common 的 Format 填入參數。分頁以 Top 搭配 Not In 子查詢實作。
<%
Class DAL_Guest
Private SQL_01, SQL_02, SQL_03, SQL_04, SQL_05, SQL_06, SQL_07
Private mycom, myacc, mymod
Public Function GetGuest(Id)
Dim parms, SQL, objRS
parms = Array(Id)
SQL = mycom.Format(SQL_01, parms)
Set objRS = myacc.ExecuteReader(myacc.CONN_STRING, SQL)
If Not objRS.EOF Then
Call mymod.SetVar(objRS("Id"), objRS("Content"), objRS("Re"), objRS("Addtime"), objRS("Ip"))
Set GetGuest = mymod
Else
GetGuest = Null
End If
End Function
Public Sub Insert(vmod)
Dim parms, SQL
parms = Array(vmod.Content, vmod.Re, vmod.Addtime, vmod.Ip)
SQL = mycom.Format(SQL_02, parms)
Call myacc.ExecuteNonQuery(myacc.CONN_STRING, SQL)
End Sub
Public Sub Update(vmod)
Dim parms, SQL
parms = Array(vmod.Id, vmod.Content, vmod.Re, vmod.Addtime, vmod.Ip)
SQL = mycom.Format(SQL_03, parms)
Call myacc.ExecuteNonQuery(myacc.CONN_STRING, SQL)
End Sub
Public Sub Delete(Id)
Dim parms, SQL
parms = Array(Id)
SQL = mycom.Format(SQL_04, parms)
Call myacc.ExecuteNonQuery(myacc.CONN_STRING, SQL)
End Sub
Public Function Count(WhereValue)
Dim parms, SQL, objRS
parms = Array(WhereValue)
SQL = mycom.Format(SQL_07, parms)
Set objRS = myacc.Recordset(myacc.CONN_STRING, SQL)
Count = objRS.RecordCount
End Function
Public Function FindByPage(PageSize, CurrentPage, WhereValue, OrderValue)
Dim parms, SQL, objRS, s1
If CurrentPage = "0" Then
s1 = PageSize & "|" & OrderValue & "|" & WhereValue
parms = Split(s1, "|")
SQL = mycom.Format(SQL_05, parms)
Else
s1 = PageSize & "|" & OrderValue & "|" & WhereValue & "|" & CurrentPage * PageSize
parms = Split(s1, "|")
SQL = mycom.Format(SQL_06, parms)
End If
Set objRS = myacc.ExecuteReader(myacc.CONN_STRING, SQL)
Set FindByPage = objRS
End Function
Private Sub Class_Initialize()
SQL_01 = "Select * From [Guest] Where Id = {0}"
SQL_02 = "Insert into [Guest] ([Content],[Re],[Addtime],[Ip]) values('{0}','{1}','{2}','{3}')"
SQL_03 = "Update [Guest] Set [Content]='{1}',[Re]='{2}',[Addtime]='{3}',[Ip]='{4}' Where Id = {0}"
SQL_04 = "Delete From [Guest] Where Id In ({0})"
SQL_05 = "Select Top {0} * From [Guest] Order By {1} Desc, Id Desc"
SQL_06 = "Select Top {0} * From [Guest] Where Id Not IN (Select Top {3} Id From [Guest] Order By {1} Desc, Id Desc) Order By {1} Desc, Id Desc"
SQL_07 = "Select * From [Guest]"
Set mycom = new Common
Set myacc = new AccHelper
Set mymod = new MOD_Guest
End Sub
Private Sub Class_Terminate()
Set mycom = Nothing
Set myacc = Nothing
Set mymod = Nothing
End Sub
End Class
%>
<%
Class MOD_Guest
Public Id, Content, Re, Addtime, Ip
Public Sub SetVar(vId, vContent, vRe, vAddtime, vIp)
Id = vId
Content = vContent
Re = vContent
Addtime = vAddtime
Ip = vIp
End Sub
End Class
%>
Format 以 {0}、{1} 佔位符做替換;Page 則把總篇數、首頁、上一頁、下一頁、尾頁、頁次、每頁篇數與跳轉框拼成一列。
<%
Class Common
Public Function Format(str, arr)
Dim r, i
r = str
For i = 0 To UBound(arr)
r = Replace(r, "{" & i & "}", arr(i))
Next
Format = r
End Function
Public Function Page(PageSize, CurrentPage, RecordCount, PageUrl)
const C_RECORDCOUNT = "合計<STRONG><FONT color=""red"">{0}</FONT></STRONG>篇 |"
const C_FIRSTPAGE1 = " <a href=""#"" onclick=""$('{0}&PageNo={1}')"">首頁</a>"
const C_FIRSTPAGE2 = " 首頁"
const C_PREVPAGE1 = " <a href=""#"" onclick=""$('{0}&PageNo={1}')"">上一頁</a>"
const C_PREVPAGE2 = " 上一頁"
const C_NEXTPAGE1 = " <a href=""#"" onclick=""$('{0}&PageNo={1}')"">下一頁</a>"
const C_NEXTPAGE2 = " 下一頁"
const C_LASTPAGE1 = " <a href=""#"" onclick=""$('{0}&PageNo={1}')"">尾頁</a>"
const C_LASTPAGE2 = " 尾頁"
const C_CURRENTPAGE = " 頁次:<STRONG><FONT color=""red"">{0}</FONT>/{1}</STRONG>頁"
const C_PAGESIZE = " <STRONG>{0}</STRONG>篇/頁 "
const C_GOTOPAGE = " 跳轉:<input type=""text"" name=""goto"" value=""{1}""
onchange=""$('{0}&PageNo='+this.value)"" style=""width:20px;border:1px""/> "
Dim PageCount, pages, parms, p
If RecordCount Mod PageSize <> 0 Then pages = 1 Else pages = 0
PageCount = CInt(RecordCount / PageSize + pages)
parms = Array(RecordCount)
lRecordCount = Format(C_RECORDCOUNT, parms)
If CInt(CurrentPage) > 1 Then
parms = Array(Pageurl, 1)
lFirstpage = Format(C_FIRSTPAGE1, parms)
parms = Array(Pageurl, CInt(CurrentPage) - 1)
lPrevpage = Format(C_PREVPAGE1, parms)
Else
lFirstpage = C_FIRSTPAGE2
lPrevpage = C_PREVPAGE2
End If
If CInt(CurrentPage) < PageCount Then
parms = Array(Pageurl, CInt(CurrentPage) + 1)
lNextpage = Format(C_NEXTPAGE1, parms)
parms = Array(Pageurl, CInt(CurrentPage) + 1)
lLastpage = Format(C_LASTPAGE1, parms)
Else
lNextpage = C_NEXTPAGE2
lLastpage = C_LASTPAGE2
End If
parms = Array(CurrentPage, PageCount)
lCurrentpage = Format(C_CURRENTPAGE, parms)
parms = Array(PageSize)
lPagesize = Format(C_PAGESIZE, parms)
parms = Array(Pageurl, CurrentPage)
lGotopage = Format(C_GOTOPAGE, parms)
p = lRecordCount & lFirstpage & lPrevpage & lNextpage & lLastpage & lCurrentpage & lPagesize & lGotopage
Page = p
End Function
End Class
%>
把三種常見的資料庫操作包成函式,連線字串在初始化時指向 db.mdb。
<%
Class AccHelper
Public CONN_STRING
Public Function ExecuteNonQuery(connString, cmdText)
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connString
objConn.Execute cmdText
End Function
Public Function ExecuteReader(connString, cmdText)
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connString
Set objRS = objConn.Execute(cmdText)
Set ExecuteReader = objRS
End Function
Public Function Recordset(connString, cmdText)
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connString
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open cmdText, objConn, 1, 3
Set Recordset = objRS
End Function
Private Sub Class_Initialize()
CONN_STRING = "provider = microsoft.jet.oledb.4.0;data source=" & Server.Mappath("db.mdb")
End Sub
End Class
%>
這份程式作為三層分工的骨架很清晰,但直接上線風險不小。第一,所有 SQL 都以字串拼接方式組出,使用者輸入的內容會原封不動進入語句,SQL 注入與跨站腳本都在射程之內,應改用參數化查詢並對輸出做 HTML 轉義。第二,刪除動作完全沒有身分驗證,任何人只要帶對參數就能刪資料。第三,escape 不支援 UTF-8 中文,請替換為 encodeURIComponent,後端再以對應方式解碼。把這三點補上,這份程式才真正可用。