字符串拼接 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值列表,避免手動拼接逗號邏輯。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
ADODB.Command 真正參數化。AddRawField(不加引號)、AddNull、Join 方法演進為輕量 ORM。此類適合中小型 ASP 專案快速構建 CRUD SQL,大型系統建議遷移至成熟 ORM 或參數化查詢層。