萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

三種資料庫操作路徑比較:字串拼接、參數化查詢與存儲程序的安全性與架構差異

場景:使用者登入驗證

頁面:兩個文字框、一個按鈕。驗證帳號密碼,成功導向新增頁,失敗顯示錯誤。

路徑一:字串拼接動態 SQL(高風險、不推薦)

SqlConnection conn = new SqlConnection("server=.;database=news2;uid=sa;pwd=");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT COUNT(*) FROM users WHERE name='" 
    + this.TextBox1.Text + "' AND pwd='" + this.TextBox2.Text + "'";
cmd.Connection = conn;
int count = (int)cmd.ExecuteScalar();

if (count == 1) Response.Redirect("add.aspx");
else Label1.Text = "error!";

致命缺陷:SQL 注入

輸入:TextBox1 = "admin' --"
產生 SQL:
SELECT COUNT(*) FROM users WHERE name='admin' --' AND pwd='...'
-- 註解掉密碼檢查,直接以 admin 登入!

其他攻擊:'; DROP TABLE users; --、UNION SELECT password FROM admin-- 等。

路徑二:參數化查詢(標準安全做法)

SqlConnection conn = new SqlConnection("server=.;database=news;uid=sa;pwd=");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT COUNT(*) FROM users WHERE name=@name AND pwd=@pwd";
cmd.Connection = conn;

cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = TextBox1.Text;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = TextBox2.Text;

int count = (int)cmd.ExecuteScalar();

為什麼安全?

  • SQL 指令結構與資料分離,資料庫引擎先編譯執行計畫,再套用參數值
  • 參數值永遠不會被解析為 SQL 片段,單引號、註解符號皆視為普通字元
  • 型別強制檢查(SqlDbType.NVarChar),防止型別混淆攻擊

效能優勢

  • 執行計畫快取重用,相同指令模板不重複編譯
  • 減少網路傳輸(參數與指令分離)

路徑三:存儲程序(企業級最佳實踐)

-- SQL Server 建立存儲程序
CREATE PROCEDURE sp_CheckLogin
    @name NVARCHAR(50),
    @pwd  NVARCHAR(50)
AS
BEGIN
    SELECT COUNT(*) FROM users WHERE name = @name AND pwd = @pwd;
END;
// C# 呼叫
SqlCommand cmd = new SqlCommand("sp_CheckLogin", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = TextBox1.Text;
cmd.Parameters.Add("@pwd", SqlDbType.NVarChar, 50).Value = TextBox2.Text;
int count = (int)cmd.ExecuteScalar();

存儲程序額外優勢

  1. 預編譯:建立時即解析、優化、生成執行計畫
  2. 封裝業務邏輯:複雜多步驟操作原子化,減少網路往返
  3. 權限隔離:僅授與 EXECUTE 權限,不需直接表權限
  4. 版本管理:資料庫端版本控制,應用層不需重新部署即可修改邏輯
  5. 跨語言共用:Java/.NET/Python 同樣呼叫

三路徑綜合比較

維度字串拼接參數化查詢存儲程序
SQL 注入防護❌ 無✅ 完整✅ 完整
執行計畫重用❌ 每次重編譯✅ 自動參數化快取✅ 預編譯最優
網路傳輸量大(完整 SQL)中(指令+參數)小(僅呼叫名稱+參數)
業務邏輯位置應用層分散應用層資料庫層集中
除錯/測試容易容易較難(需資料庫工具)
部署依賴僅應用僅應用資料庫同步部署
推薦等級🚫 禁止✅ 標準⭐ 企業級首選

現代架構補充:ORM 與微服務

  • Entity Framework / Dapper / Hibernate 內部皆採用參數化查詢,開發者專注領域模型
  • 微服務:各服務擁有專屬資料庫,存儲程序跨服務調用反模式,改用 API 協定
  • CQRS:讀寫分離,查詢側可直接讀取優化視圖/物化表

結語

從字串拼接 → 參數化查詢 → 存儲程序,代表了安全性、效能、架構分層的演進。現代開發應以參數化查詢為基準,複雜核心業務考慮存儲程序,堅決杜絕字串拼接 SQL。

copyright © 萬盛學電腦網 all rights reserved