利用 ASP 無元件上傳技術,配合資料庫儲存二進位檔案內容,可在自有網站建立「虛擬 FTP 伺服器」:多用戶隔離空間、支援大檔案分塊上傳、突破 IIS 上傳大小限制。本文提供完整資料庫結構與核心流程設計。
CREATE TABLE Users (
ID AUTOINCREMENT PRIMARY KEY,
Username VARCHAR(50) UNIQUE NOT NULL,
PasswordHash VARCHAR(255) NOT NULL, -- 存雜湊值,非明文
Role VARCHAR(20) DEFAULT 'User', -- Admin, User
CreatedAt DATETIME DEFAULT NOW()
);
CREATE TABLE VFiles (
ID AUTOINCREMENT PRIMARY KEY,
ParentID LONG DEFAULT 0, -- 0 表根目錄
FileName VARCHAR(255) NOT NULL,
FileLength LONG DEFAULT 0, -- 0 表資料夾
FileType VARCHAR(100), -- MIME 類型
FileData IMAGE / VARBINARY(MAX), -- 二進位內容 (資料夾為 NULL)
UploadDate DATETIME DEFAULT NOW(),
UserID LONG NOT NULL, -- 關聯 Users.ID
FOREIGN KEY (UserID) REFERENCES Users(ID)
);
CREATE INDEX IX_VFiles_User_Parent ON VFiles(UserID, ParentID);
設計要點:
FileLength = 0 表示資料夾節點,FileData 為 NULL。ParentID=0, FileLength=0 的根資料夾。ParentID 即可重建樹狀結構。<!-- Login.asp -->
<%
If ValidateUser(Request.Form("username"), Request.Form("password")) Then
Session("UserID") = userID
Session("Username") = username
Response.Redirect "MyFiles.asp"
Else
Response.Write "帳號或密碼錯誤"
End If
%>
<!-- MyFiles.asp -->
<%
If Session("UserID") = "" Then Response.Redirect "Login.asp"
Dim currentFolderID, sql, rs
currentFolderID = CLng(Request.QueryString("folder")) ' 0 表根目錄
' 安全性:驗證資料夾屬於當前用戶
sql = "SELECT ID FROM VFiles WHERE ID=" & currentFolderID & " AND UserID=" & Session("UserID") & " AND FileLength=0"
Set rs = conn.Execute(sql)
If rs.EOF Then currentFolderID = 0 ' 非法 folder 參數,回根目錄
rs.Close
' 取得麵包屑導航路徑
Function GetPath(folderID)
Dim path, rs2
path = ""
Do While folderID <> 0
Set rs2 = conn.Execute("SELECT FileName, ParentID FROM VFiles WHERE ID=" & folderID)
If rs2.EOF Then Exit Do
path = " / " & rs2("FileName") & path
folderID = rs2("ParentID")
rs2.Close
Loop
GetPath = "根目錄" & path
End Function
%>
<h2><%= GetPath(currentFolderID) %></h2>
<table border="1" cellpadding="5">
<tr><th>名稱</th><th>大小</th><th>類型</th><th>上傳時間</th><th>操作</th></tr>
<%
sql = "SELECT * FROM VFiles WHERE ParentID=" & currentFolderID & " AND UserID=" & Session("UserID") & " ORDER BY FileLength, FileName"
Set rs = conn.Execute(sql)
While Not rs.EOF
If rs("FileLength") = 0 Then
' 資料夾
Response.Write "📁 " & rs("FileName") & " - 資料夾 " & rs("UploadDate") & " 刪除 "
Else
' 檔案
Response.Write "📄 " & rs("FileName") & " " & FormatFileSize(rs("FileLength")) & " " & rs("FileType") & " " & rs("UploadDate") & " 刪除 "
End If
rs.MoveNext
Wend
rs.Close
%>
前端(HTML5/Flash/Silverlight)將大檔案切片(如 100KB/片),每片 POST 到 UploadChunk.asp:
<!-- UploadChunk.asp -->
<%
' 參數:fileId (新檔案首片時為 0), chunkIndex, chunkData (二進位), fileName, fileType, totalChunks
Dim fileId, chunkIndex, chunkData, fileName, fileType, totalChunks, parentID
fileId = CLng(Request.Form("fileId"))
chunkIndex = CLng(Request.Form("chunkIndex"))
chunkData = Request.BinaryRead(Request.TotalBytes) ' 需無元件上傳解析器提取
fileName = Request.Form("fileName")
fileType = Request.Form("fileType")
totalChunks = CLng(Request.Form("totalChunks"))
parentID = CLng(Request.Form("folder"))
If fileId = 0 Then
' 首片:建立檔案記錄
Set rs = conn.Execute("INSERT INTO VFiles (ParentID, FileName, FileLength, FileType, FileData, UserID) VALUES (" & _
parentID & ", '" & EscapeSQL(fileName) & "', 0, '" & EscapeSQL(fileType) & "', NULL, " & Session("UserID") & ")")
' 取得新 ID (Access: SELECT @@IDENTITY; SQL Server: SCOPE_IDENTITY())
fileId = GetLastInsertID()
End If
' 追加二進位資料
' Access: rs("FileData").AppendChunk chunkData
' SQL Server: UPDATE VFiles SET FileData = FileData + @chunk WHERE ID = @id (需參數化)
' 這裡略過具體 AppendChunk 代碼,視資料庫而異
' 更新總長度
conn.Execute "UPDATE VFiles SET FileLength = FileLength + " & LenB(chunkData) & " WHERE ID=" & fileId
If chunkIndex = totalChunks - 1 Then
Response.Write "COMPLETE|" & fileId
Else
Response.Write "CONTINUE|" & fileId
End If
%>
<!-- Download.asp -->
<%
fileId = CLng(Request.QueryString("id"))
Set rs = conn.Execute("SELECT FileName, FileType, FileData, FileLength FROM VFiles WHERE ID=" & fileId & " AND UserID=" & Session("UserID"))
If rs.EOF Then Response.End
Response.ContentType = rs("FileType")
Response.AddHeader "Content-Disposition", "attachment; filename=" & Server.URLEncode(rs("FileName"))
Response.AddHeader "Content-Length", rs("FileLength")
Response.BinaryWrite rs("FileData") ' 大檔案建議分塊 Response.BinaryWrite 避免記憶體暴漲
rs.Close
%>
<!-- Delete.asp -->
<%
itemId = CLng(Request.QueryString("id"))
' 遞迴刪除資料夾及其所有子項
Sub DeleteTree(folderId)
conn.Execute "DELETE FROM VFiles WHERE ID=" & folderId & " AND UserID=" & Session("UserID")
Set rs = conn.Execute("SELECT ID FROM VFiles WHERE ParentID=" & folderId & " AND UserID=" & Session("UserID"))
While Not rs.EOF
DeleteTree rs("ID")
rs.MoveNext
Wend
rs.Close
End Sub
Set rs = conn.Execute("SELECT FileLength FROM VFiles WHERE ID=" & itemId & " AND UserID=" & Session("UserID"))
If Not rs.EOF Then
If rs("FileLength") = 0 Then DeleteTree(itemId) Else conn.Execute "DELETE FROM VFiles WHERE ID=" & itemId
End If
rs.Close
Response.Redirect Request.ServerVariables("HTTP_REFERER")
%>
| 挑戰 | 解決方案 |
|---|---|
| IIS 上傳大小限制 (預設 200KB) | 前端分塊上傳 + 後端 AppendChunk 重組 |
| Access 二進位欄位 64KB 限制 | 改用 SQL Server VARBINARY(MAX) 或檔案系統儲存,資料庫只存路徑 |
| 大檔案下載記憶體爆炸 | 分塊 Response.BinaryWrite + Response.Flush,或使用 ADODB.Stream |
| 並發上傳衝突 | 資料庫交易 + 唯一索引保證原子性 |
| 權限橫向越權 | 每個 SQL 皆加 AND UserID=Session("UserID") 強制隔離 |
虛擬 FTP 核心在於:資料庫統一儲存元資料與內容、分塊上傳突破體積限制、嚴格的 UserID 隔離查詢。雖然經典 ASP 架構在現代雲原生環境下已少見,但此設計模式(元資料與 Blob 分離、分塊傳輸、租戶隔離)在任何技術棧中皆具參考價值。