萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP SQL 語句構建類:SQLString 類封裝 Insert/Update/Delete/Select 流式構建

ASP 面向對象 SQL 構建器:SQLString 類消除拼接痛點

字符串拼接 SQL 易出錯、難維護、注入風險高。本文介紹 SQLString 類,通過流式 API(AddField、屬性設置)構建參數化風格的 SQL 語句,自動處理字段列表、值列表、WHERE、ORDER BY 子句。

類結構設計

Class SQLString
    Private sTableName, iSQLType, sWhere, sOrder, sSQL
    Private Const SQL_INSERT = 0, SQL_UPDATE = 1, SQL_DELETE = 2, SQL_SELECT = 3
    
    Private Sub Class_Initialize()
        sTableName = "" : iSQLType = SQL_INSERT : sWhere = "" : sOrder = "" : sSQL = ""
    End Sub
    
    ' === 屬性 =====================================================
    Public Property Let TableName(v) : sTableName = v : End Property
    Public Property Let Where(v) : sWhere = v : End Property
    Public Property Let Order(v) : sOrder = v : End Property
    
    Public Property Let SQLType(v)
        iSQLType = v
        Select Case iSQLType
            Case SQL_INSERT : sSQL = "INSERT INTO #0 (#1) VALUES (#2)"
            Case SQL_UPDATE : sSQL = "UPDATE #0 SET #1=#2"
            Case SQL_DELETE : sSQL = "DELETE FROM #0"
            Case SQL_SELECT : sSQL = "SELECT #1 FROM #0"
        End Select
    End Property
    
    ' === 核心方法 =================================================
    Public Sub AddField(sFieldName, sValue)
        Select Case iSQLType
            Case SQL_INSERT
                sSQL = Replace(sSQL, "#1", sFieldName & ",#1")
                sSQL = Replace(sSQL, "#2", "'" & sValue & "',#2")
            Case SQL_UPDATE
                sSQL = Replace(sSQL, "#1", sFieldName)
                sSQL = Replace(sSQL, "#2", "'" & sValue & "',#1=#2")
            Case SQL_SELECT
                sSQL = Replace(sSQL, "#1", sFieldName & ",#1")
        End Select
    End Sub
    
    Public Function ReturnSQL()
        sSQL = Replace(sSQL, "#0", sTableName)
        Select Case iSQLType
            Case SQL_INSERT
                sSQL = Replace(sSQL, ",#1", "")
                sSQL = Replace(sSQL, ",#2", "")
            Case SQL_UPDATE
                sSQL = Replace(sSQL, ",#1=#2", "")
            Case SQL_SELECT
                sSQL = Replace(sSQL, ",#1", "")
        End Select
        
        If sWhere <> "" Then sSQL = sSQL & " WHERE " & sWhere
        If sOrder <> "" Then sSQL = sSQL & " ORDER BY " & sOrder
        ReturnSQL = sSQL
    End Function
    
    Public Sub Clear()
        sTableName = "" : iSQLType = SQL_INSERT : sWhere = "" : sOrder = "" : sSQL = ""
    End Sub
End Class

使用示例

<%
Dim sqlBuilder
Set sqlBuilder = New SQLString

' === INSERT 示例 ===
sqlBuilder.TableName = "message"
sqlBuilder.SQLType = 0  ' INSERT
sqlBuilder.AddField "incept", incept(i)
sqlBuilder.AddField "sender", membername
sqlBuilder.AddField "title", title
sqlBuilder.AddField "content", message
sqlBuilder.AddField "sendtime", Now()
sqlBuilder.AddField "flag", 0
sqlBuilder.AddField "issend", 1
Response.Write sqlBuilder.ReturnSQL()
' 生成:INSERT INTO message (incept,sender,title,content,sendtime,flag,issend) VALUES ('值1','值2',...)

' === UPDATE 示例 ===
sqlBuilder.Clear
sqlBuilder.TableName = "users"
sqlBuilder.SQLType = 1  ' UPDATE
sqlBuilder.Where = "id = 123"
sqlBuilder.AddField "username", "newname"
sqlBuilder.AddField "email", "new@mail.com"
Response.Write sqlBuilder.ReturnSQL()
' 生成:UPDATE users SET username='newname',email='new@mail.com' WHERE id = 123

' === SELECT 示例 ===
sqlBuilder.Clear
sqlBuilder.TableName = "products"
sqlBuilder.SQLType = 3  ' SELECT
sqlBuilder.AddField "id"
sqlBuilder.AddField "name"
sqlBuilder.AddField "price"
sqlBuilder.Where = "category = 'electronics'"
sqlBuilder.Order = "price DESC"
Response.Write sqlBuilder.ReturnSQL()
' 生成:SELECT id,name,price FROM products WHERE category = 'electronics' ORDER BY price DESC

Set sqlBuilder = Nothing
%>

關鍵設計亮點

  • 佔位符替換機制:#0表名、#1字段列表、#2值列表,避免手動拼接逗號邏輯。
  • 流式 API:AddField 可鏈式調用,順序即字段順序。
  • 自動收尾:ReturnSQL 移除尾部佔位符,拼接 WHERE/ORDER BY。
  • 可復用:Clear() 重置實例,單對象構建多條 SQL。

安全增強:防注入版

' 修改 AddField 使用參數化佔位符
Public Sub AddFieldSafe(sFieldName, sValue)
    ' 實際項目中應配合 ADODB.Command 參數化查詢
    ' 此處演示:單引號轉義 + 類型檢查
    sValue = Replace(sValue, "'", "''")
    ' 可擴展:IsNumeric/IsDate 驗證
    AddField sFieldName, sValue
End Sub

局限與改進方向

  1. 無參數化查詢:仍屬字符串拼接本質,建議配合 ADODB.Command 真正參數化。
  2. 複雜條件不支持:JOIN、子查詢、函數調用需手寫 SQL。
  3. 類型安全:數值/日期類型未自動格式化,易出錯。
  4. 擴展建議:添加 AddRawField(不加引號)、AddNull、Join 方法演進為輕量 ORM。

此類適合中小型 ASP 專案快速構建 CRUD SQL,大型系統建議遷移至成熟 ORM 或參數化查詢層。

copyright © 萬盛學電腦網 all rights reserved