ADO.NET SqlParameter 參數化查詢防 SQL 注入完整實戰範例

為什麼要用 SqlParameter？

字串拼接 SQL 是 SQL 注入攻擊的首要原因。SqlParameter 提供：

• 型別安全：強型別參數避免隱性轉換錯誤。
• 自動逸出：單引號、特殊字元自動處理，杜絕注入。
• 計畫快取重用：相同參數化 SQL 可重用執行計畫，提升效能。
• 可讀性/維護性：SQL 與資料分離。

完整實戰代碼

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using System.Web.UI;

namespace ParaMeter {
    public partial class Test : Page {
        private string connectionStr;
        private SqlConnection conn;

        protected void Page_Load(object sender, EventArgs e) {
            // 實務建議：從 web.config  讀取
            // connectionStr = ConfigurationManager.ConnectionStrings["MyDb"].ConnectionString;
            connectionStr = "Server=10.11.43.189\\SQL2008;Database=OA_WEB_DB;User ID=sa;Password=123456;";
            conn = new SqlConnection(connectionStr);
        }

        protected void Button1_Click(object sender, EventArgs e) {
            // 1. 參數化 SQL
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO [OA_WEB_DB].[dbo].[OA_RT_FileType] ([FileTypeName], [Deleted])");
            sql.Append(" VALUES (@fileName, @delete)");

            // 2. 定義參數陣列（指定型別、長度）
            SqlParameter[] parameters = {
                new SqlParameter("@fileName", SqlDbType.NVarChar, 100),
                new SqlParameter("@delete", SqlDbType.Bit)
            };

            // 3. 指派值
            parameters[0].Value = "文件類型";
            parameters[1].Value = false;

            // 4. 執行並處理結果
            bool success = ExecuteNonQuery(sql.ToString(), parameters);
            Label1.Text = success ? "插入成功" : "插入失敗";
        }

        /// 

        /// 執行增刪改（非查詢）參數化命令
        /// 
        private bool ExecuteNonQuery(string sqlText, params SqlParameter[] cmdParms) {
            using (SqlCommand cmd = new SqlCommand()) {
                try {
                    PrepareCommand(cmd, conn, null, sqlText, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    return rows > 0;
                }
                catch (SqlException ex) {
                    // 實務：記錄 ex.Number, ex.Message, ex.StackTrace 至 Log
                    // 常見錯誤碼：2627 (PK違規), 547 (FK違規), 2601 (唯一索引違規)
                    return false;
                }
            }
        }

        /// 

        /// 統一命令準備：開啟連線、指派參數、設定命令型別
        /// 
        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans,
                                    string cmdText, SqlParameter[] cmdParms) {
            if (conn.State != ConnectionState.Open) conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text; // 或 StoredProcedure

            if (trans != null) cmd.Transaction = trans;

            if (cmdParms != null) {
                foreach (SqlParameter p in cmdParms) {
                    // 處理 null 值：輸入/輸出入參數若為 null 轉 DBNull.Value
                    if ((p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput)
                        && p.Value == null) {
                        p.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(p);
                }
            }
        }
    }
}

關鍵最佳實踐總結

進階：預存程序呼叫

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_InsertFileType";

SqlParameter[] spParams = {
    new SqlParameter("@FileTypeName", SqlDbType.NVarChar, 100) { Value = "文件類型" },
    new SqlParameter("@Deleted", SqlDbType.Bit) { Value = false },
    new SqlParameter("@NewId", SqlDbType.Int) { Direction = ParameterDirection.Output } // 輸出參數
};

ExecuteNonQuery(spParams); // 同一個方法支援
int newId = (int)spParams[2].Value; // 取得輸出值

進階：批次作業

// 使用 SqlBulkCopy 大量插入（DataTable 來源）
using (SqlBulkCopy bulk = new SqlBulkCopy(conn)) {
    bulk.DestinationTableName = "OA_RT_FileType";
    bulk.ColumnMappings.Add("FileTypeName", "FileTypeName");
    bulk.ColumnMappings.Add("Deleted", "Deleted");
    bulk.WriteToServer(dataTable); // dataTable 為來源資料
}

常見錯誤與除錯

1. 參數名稱拼寫不一致：SQL 用 @fileName，參數建立用 @filename（大小寫不敏感但建議一致）。
2. Size 未指定導致計畫快取污染：NVarChar 不給 Size 預設 -1（MAX），不同長度產生不同計畫。
3. 重用 SqlCommand 物件未清除 Parameters：每次新建或 cmd.Parameters.Clear()。
4. 交易未 Commit/Rollback 導致鎖死：using (SqlTransaction trans = conn.BeginTransaction()) { ... trans.Commit(); }。

ORM 替代方案提示

新專案建議使用 Entity Framework Core / Dapper：

• EF Core：context.FileTypes.Add(new FileType { Name = "文件類型", Deleted = false }); await context.SaveChangesAsync();（自動參數化、LINQ、遷移）。
• Dapper：conn.Execute("INSERT INTO FileType (Name, Deleted) VALUES (@Name, @Deleted)", new { Name = "文件類型", Deleted = false });（輕量、高效、自動參數化）。

但理解 SqlParameter 底層機制對除錯、效能調校、舊系統維護仍至關重要。