[C#]SqlParameter參數數量超過2100個

2009-08-09

14790
0
C#

我想為了防範SQL Injection，大家現在已經很習慣透過Parameter的方式來撰寫SqlCommand了，而在多數的應用下，參數的寫法相信不是什麼困難的工作，而有幾個case在我們使用參數處理時需要多費些心思，這部份可以參考Jeff隨手記的：

SQL語法用like %或in時,Parameters要怎麼用,才能避免SQL Injection的問題

而當我們在我們系統中使用Jeff隨手記中的相同方法去實作in的部分時，卻出現了下面的錯誤訊息：

發生這個錯誤的主要原因是我們SQL中的參數數量在2100個以上，誇張嗎？這個問題可能隨時有機會發生在你使用in的程式中，因為當資料量不斷的成長，誰曉得in中的參數數量會不會有一天超過2100個呢？沒有人可以保證。

下方我們簡單寫一個範例來測試這個問題：

SqlConnection tSqlConn = new SqlConnection(@"server=.;database=Northwind;uid=sa;pwd=sa");

try
{
    tSqlConn.Open();
    string tCommand = String.Format("Select * from Customers where CustomerID in (#Customer)");
    SqlCommand tSqlCmd = new SqlCommand(tCommand, tSqlConn);
    string tParameters = "";

    for (int i = 0; i < 3000; i++)
    {
        tParameters += "@CustomerID" + i.ToString() + ",";
        tSqlCmd.Parameters.AddWithValue("@CustomerID" + i.ToString(), i.ToString());
    }
    tParameters=tParameters.Remove(tParameters.Length - 1);
    tCommand = tCommand.Replace("#Customer", tParameters);

    tSqlCmd.CommandText = tCommand;
    tSqlCmd.ExecuteNonQuery();

    string tErroralert = "<script>alert('新增資料成功!!')</script>";
    ClientScript.RegisterStartupScript(typeof(_Default), "formaterror", tErroralert);
}
catch (Exception ex)
{
    string tErroralert = "<script>alert('" + ex .Message+ "')</script>";
    ClientScript.RegisterStartupScript(typeof(_Default), "formaterror", tErroralert);
}
finally
{
    tSqlConn.Close();
}

上面的範例中沒有什麼特別的地方，只是為了呈現這個問題而寫，我們看一下for迴圈中，我無腦的新增了3000個參數，執行這段程式後就會出現這個錯誤：

但是當我把參數數量改到2099個時，它便可以成功被執行了。

看完問題，我們再回來頭看一下這個問題發生的案例：

1.有兩個Table，一個是Customer，記錄每個客戶的相關資訊，Key是CustomerID，每個Customer有一個歸屬的Sales；另一個是Sales，記錄每個Sales的基本資料。

2.使用者會想要撈出某個Sales所歸屬的所有客戶。

3.並非每個使用者都可以撈出所有的資料，因此在撈出Sales所歸屬的Customer清單後，必須依權限的邏輯再做一些過濾。

如果要求只在1.2兩點，當初開發人員可以透過JOIN的方式找出想要的資料就好，不需要用到in來處理，而今天加入3.這一點，讓程式設計師在撰寫時考慮選擇了in的寫法，而整個問題在Sales對應的Customer在2100個以上時開始出現，記得當初處理這個問題時解法是將一句SQL拆成多句，讓參數在2100個以上，再透過程式將資料組合回來。

但其實這只是一個架構設計上的缺陷，因為3.這一點而需要這樣作的原因是記錄權限的Table跟Customer這個Table之間竟然沒有關聯，若兩者之間是直接或間接關聯的，這個問題或許就不會出現了。

游舒帆 (gipi)

探索原力Co-founder，曾任TutorABC協理與鼎新電腦總監，並曾獲選兩屆微軟最有價值專家 ( MVP )，離開職場後創辦探索原力，致力於協助青少年培養面對未來的能力。認為教育與組織育才其實息息相關，都是在為未來儲備能量，2018年起成立為期一年的專題課程《職涯躍升的關鍵24堂課》，為培養台灣未來的領袖而努力。

回首頁