錯誤寫法（字串串接，有 SQL Injection 風險）
"select * from biao1 where id >= " & i & " and id <= " & j & " and name = '" & lk & "'"
正確寫法：參數化查詢
Imports MySql.Data.MySqlClient
Dim sql As String = "SELECT * FROM biao1 WHERE id >= @minId AND id <= @maxId AND name = @name"
Using conn As New MySqlConnection(connStr),
cmd As New MySqlCommand(sql, conn)
cmd.Parameters.AddWithValue("@minId", i)
cmd.Parameters.AddWithValue("@maxId", j)
cmd.Parameters.AddWithValue("@name", lk)
conn.Open()
Using reader = cmd.ExecuteReader()
While reader.Read()
' 處理資料
End While
End Using
End Using
關鍵優勢
| 項目 | 字串串接 | 參數化查詢 |
|---|
| 安全性 | ❌ SQL Injection | ✅ 自動轉義 |
| 效能 | 每次重新編譯 | ✅ 計畫快取重用 |
| 可讀性 | 雜亂 | ✅ SQL/參數分離 |
| 型別處理 | 手動轉字串 | ✅ 自動對應 |
進階：動態 WHERE 條件
Dim conditions As New List(Of String)
Dim params As New List(Of MySqlParameter)
If minId.HasValue Then
conditions.Add("id >= @minId")
params.Add(New MySqlParameter("@minId", minId.Value))
End If
If maxId.HasValue Then
conditions.Add("id <= @maxId")
params.Add(New MySqlParameter("@maxId", maxId.Value))
End If
If Not String.IsNullOrEmpty(name) Then
conditions.Add("name = @name")
params.Add(New MySqlParameter("@name", name))
End If
Dim sql = "SELECT * FROM biao1"
If conditions.Count > 0 Then sql &= " WHERE " & String.Join(" AND ", conditions)
' 後續建立 Command 並加入 params
