Jeff 隨手記

很多人應該都經歷過一段T-SQL的學習成長路程,最多人最常用的就是直接把使用者輸入的值,直接組到查詢字串裡.

例如 :

string strCmd="select * from testdb where testid ='"+TextBox1.Text.Trim()+";";

很多範例及書藉都有這樣的寫法,可是這寫法有很嚴重的SQL Injection的問題,這也不是個新問題,這是很久以前就有發現的,有些人還以為Hot Fix或Service Pack可以修正SQL Injection的問題,但這是程式寫法的問題,跟Hot Fix或Service Pack沒有關係.

記得過去有位同事,有多年的程式撰寫經驗,後來跟我一起Work了一段時間,他的寫法也是如此,當時提出這個安全問題時,還不被重視,但小弟手就是賤,為了讓上頭重視這個問題,只好自己動手去"注射"了,我沒做什麼,就很單純的關掉DB而已,後來上頭才發現這個問題的嚴重性,才要求更改.

 

後來陸續對安全性的重視提高了,開始想著要怎麼去解決這個問題,就延伸出第二種寫法,開始去過濾關鍵字,置換等消極的方式,有時還會發現新的問題,所以就一直修不停.

例如 :

string strCmd="select * from testdb where testid ='"+TextBox1.Text.Trim().Replace("'","%")+";";

這只是示意性的換掉單引號,但還有很多關鍵字或符號.

 

後來決定要想辦法把使用者的查詢值放到Parameters的方式去處理,而做法也沒想像中的難.

以like的問題來說:

SqlCommand cmd=new SqlComand(); //其它怎麼用Connection等,因為不是重點,所以帶過不寫

cmd.Parameters.Add("@testid",TextBox1.Text.Trim());

cmd.CommandText="select * from testdb where testid like @testid+'%'";

這樣就能做到 like TextBox1.Text.Trim()% 的效果,而且不用擔心使用者輸入的字有什麼關鍵字在裡面.

 

那麼in要怎麼做?

會用in的時候,一定是有很多查詢的值要查,所以做法有很多種,但意思差不多.

例如我的查詢條件有"Jeff","Jack","Jay","Jason",將這些條件個個分開放到一個String Arrary裡.

SqlCommand cmd=new SqlComand(); //其它怎麼用Connection等,因為不是重點,所以帶過不寫

cmd.CommandText="select * from testdb where testid in (";

string strName="Jeff,Jack,Jay,Jason";//要用in查詢的資料

string[] strQry=strName.Split(",".ToCharArray());//把它切到String Array裡去

int intCount=0;

foreach (string sq in strQry)

{

cmd.Parameters.Add("@TestID"+intCount, sq) //所以每次的變數都會不同

cmd.CommandText+="@TestID"+intCount+",";

intCount++;//變數加1

}

cmd.CommandText=cmd.CommandText.Remove(cmd.CommandText.Length-1,1)+")"; //把最後一個逗號換為右括號.

最後就會組出select * from testdb where testid in (@TestID0,@TestID1,@TestID2,@TestID3)

 

未來會不會有第四種改進寫法,就不知道了,看各位有沒有什麼好看法,或是未來會不會我又有遇到什麼問題可以動動腦.

 

 

#2008/9/13 補充

在網友提供的資訊,發現用Parameters,在用Like時,使用者輸入的%也是當萬用字元在使用.例如:

cmd.Parameters.Add("@testid","Jeff%");

cmd.CommandText="select * from testdb where testid like @testid";

查詢結果會將所有的Jeff開頭的資料取回,而底線 _ 的單一字的萬用字元也是有同樣的效果,那這樣使用者真的想要找資料內有%或是_時的資料時,要怎麼辨才能達成?目前是想到一個方法,但要用到escape這個方式.

例如 :

cmd.Parameters.Add("@testid","Jeff%".Relpace("!","!!").Relpace("%","!%").Relpace("_","!_")+"%");

cmd.CommandText="select * from testdb where testid like @testid escape '!'";

前三個Relpace的動作,是把使用者輸入的! (因為escape 符號是用!),%及_都在前面加個!,讓它被視為原意做為查詢條件,最後加上的%,因為沒前面先加!,所以最後一個%仍為萬用字元使用,所以舉例資料庫的資料如下:

Jeff1

Jeff%1

Jeff%2

Jeff%_1

Jeff2

查詢結果會是 :

Jeff%1

Jeff%2

Jeff%_1

這是目前想到的做法~