專題 ASP.NET + DB

Transcription

1 ASP.NET + 資料庫 建國科技大學資管系饒瑞佶 2015/9 V1 2016/3 V2

2 幾件一定要會的事 資料庫架構 SQL 指令.NET 資料庫存取架構

3 資料庫種類 檔案式 vs. Server 式 階層式 網狀式 關聯式 NOSQL

4 資料庫系統 資料庫 資料庫管理者 應用程式

5 資料庫 內容 SQL Server (server 式 ) 使用北風 (Northwind) 或 POS 範例資料庫 ASP.NET 資料庫設計模式 資料庫安全 SQL Injection 大小寫驗證 密碼強度等

6 匯入範例資料庫到 SQL Server

7 SQL Server 最新版 SQL Server 2014 有 Express 版本可以使用 管理工具為 SQL Server Management Studio

8 連結 SQL Server 管理方式 透過 SQL Server 管理工具 Management Studio 帳號 + 密碼 (SQL Server 驗證 ) Windows 驗證 透過 VS.Net Client 端連結工具 伺服器總管

9 SQL Server Management Studio

10 2 種驗證模式 SQL Server 驗證 需要帳號密碼 支援遠端連線 Windows 驗證 需要 Windows 管理者權限 忘記帳密時使用 伺服器名稱 : IP 或電腦名稱 \SQLEXPRESS 先試試 Windows 驗證, 確定 SQL Server 活著

11 SQL Server 驗證 IP 驗證方式 帳號 密碼

12 操作技巧答案在左邊, 答案在右鍵

13 SQL Server 附加 / 卸離資料庫 mdf 與 ldf 檔案需要成對

14 附加時若有錯誤 一般是權限問題 請將 mdf 與 ldf 檔案的權限開給 users, 並設定成完全控制即可

15 關閉 SQL Server

16 關閉 SQL Server

17 轉移或備份 SQL Server 資料庫 需要同時搬移或備份 mdf 與 ldf 兩個檔案 可能的位置 ( 要看當初安裝時設定的目錄 ) C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA 搬移或備份時應先關閉 SQL Server

18 加入留言板資料庫與資料表 留言主表 (Guestbook) 與副表 (Reply) 設定關聯

19 設定關聯

20 設定資料庫權限

21 SQL Server 權限 帳號決定可以使用的權限 : 未來就用這組帳號密碼登入與使用資料庫 存取權限 : 只能查詢資料 可以修改資料 可以修改結構 各資料庫可以設定各自的帳號 密碼與權限

22 SQL Server 權限 新增帳號 目前大家使用這組帳號

23 SQL Server 權限 帳號請自行設定 密碼請自行設定 是否可以使用權限 設定帳號原則

24 SQL Server 權限 設定帳號可以使用的資料庫 ( 可複選 ) 存取權限 資料 : db_datareader db_datawriter 結構 : db_owner

25 .NET 連結資料庫基本觀念

26 資料庫程式設計的基本概念 有人幫忙連接資料庫 ( 資料庫連接物件 ) 有人幫忙執行 SQL 指令 有人幫忙顯示 ( 介面設計與顯示物件 ) 但問題是 : 資料庫那麼多, 怎麼處理? 有沒有簡易的方法?

27 ASP.NET 資料庫設計模式 DB ADO.NET CommandBuilder 物件 Connection 物件 Command 物件 DataAdapter 物件 DataReader 物件 ExecteNonQuery 可異動資料 DataReader 物件 ExecuteReader 唯讀 DataSet 物件 On-line 線上作業模式 Off-line 離線作業模式

28 設計方式 建構在 ADO.NET 架構上 使用工具箱中既有物件 ( 拖拉放 + 設定模式 ) 自己寫程式處理所有細節

29 先來個簡單的拖拉放 資料庫 << SQL Server 有人幫忙連接 ( 資料庫連接物件 ) 與執行 SQL 指令 SqlDataSource 物件 有人幫忙顯示 ( 介面設計與顯示物件 ) GridView 物件

30 兩個物件就搞定

31 設定連線資料庫 連線字串

32 資料庫連線設定 這裡選擇剛建立的留言板資料庫

33 這裡最好要勾 Yes, 將資料存在 web.config 內以後統一使用, 維護也比較方便

34 注意 連線字串只要一個, 存放在 web.config 內就好, 以後才好維護 有兩種取用方式 透過物件 透過程式

35 連線字串 連線字串的格式要根據不同資料庫而定 那裡可以找到連線字串的說明?

36 怎麼用 we.config 內的連線呢? 當初存入 we.config 內的名稱 String connstr = System.Web.Configuration.WebConfigurationManager.Connecti onstrings["connectionstringctumenu"].connectionstring.tos tring(); SqlConnection dbconn = new SqlConnection(connstr);

37 SQL 指令 新增刪除修改查詢都在這裡

38 設定顯示物件 GridView

39 先執行看看 沒有刪除與修改?

40 結果

41 加上新 / 刪 / 修 / 查 方法 1: 可以不用會 SQL 指令

42 刪除與修改出現了 勾起來後執行看看

43 結果 刪除加入確認詢問? 欄位轉成 TemplateField 設定 onclientclick 屬性 =return confirm( 確定要刪除? );

44 加上新 / 刪 / 修 / 查 方法 2: 自己寫 SQL 指令

45 一個 SQL 指令範例

46 透過 DetailsView 物件做新增

47 首先 GridView 開啟 Selection

48 設定 SqlDataSource2 直接選剛存的那個連線字串這樣大家就都共用同一個連線方式

49

50 DetailsView 連接到 SqlDataSource2

51 問題 如果到此都設定好了 那新 / 刪 / 修 / 查都有了 透過 DetailsView 新增 / 刪除後, 並沒有重整 GridView 畫面

52 重整畫面 選事件 重新讓 GridView 連結 SqlDataSource 就可以

53 欄位加入檢查條件

54 欄位加入檢查條件

55 DetailsView 加入控制項 以 Calendar 為例 protected void Calendar1_SelectionChanged(object sender, EventArgs e) { TextBox tt = (TextBox)DetailsView1.FindControl("Add_Day"); Calendar cc=(calendar)detailsview1.findcontrol("calendar1"); tt.text = cc.selecteddate.toshortdatestring() + " " + System.DateTime.Now.ToShortTimeString();

56 GridView 加入控制項 以 Calendar 為例 protected void Calendar2_SelectionChanged(object sender, EventArgs e) { TextBox tt = (TextBox)GridView1.Rows[rowIndex].FindControl("TextBox3"); Calendar cc = (Calendar)GridView1.Rows[rowIndex].FindControl("Calendar2"); tt.text = cc.selecteddate.toshortdatestring() + " " + System.DateTime.Now.ToShortTimeString(); static int rowindex; // 取得目前要修改的編號 protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { rowindex = e.neweditindex;

57 自己設計對應的新增版面 放在 Panel 中

58 自己設計對應的新增版面, 再對應到 InsertQuery 指令 Sqldatasource InsertQuery

59 日期另外處理 設定參數對應的物件

60 新增程式 protected void Button3_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); Panel1.Visible = false;

61 處理留言空白與 enter( 新增 ) 首先修改新增參數 原來的參數設定要取消 protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e) 處理新增 { e.command.parameters["@usercontent"].value = TextBox3.Text.Replace(" "," ").Replace("\n","<br>"); e.command.parameters["@contentday"].value = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

62 處理留言空白與 enter( 顯示 ) 將 usercontent 欄位轉成 template

63 處理留言空白與 enter( 顯示 ) protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.row.rowtype == DataControlRowType.DataRow) { var lbldi = e.row.findcontrol("label1") as Label; // 內容 if (lbldi!= null) { lbldi.text = lbldi.text.tostring().replace("\n", "<br />"); 如果存入資料庫時沒有處理 \n

64 結果

65 加入查詢 需要版面 需要動態 SQL 指令 要想辦法把命令轉給 SqlDataSource 物件

66 程式只要一行 改變 sql 指令就可以 protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.SelectCommand = "select * from Guestbook where title like '%" + TextBox1.Text + "%'"; 所以已經很方便了, 但 SQL 指令還是不能省

67 GridView 物件其他操作

68 依條件改變狀態 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { // 設定列的寬度 if (e.row.rowtype == DataControlRowType.Header) { e.row.cells[0].width = 400; // 維護欄位 e.row.cells[0].backcolor = System.Drawing.Color.CornflowerBlue; e.row.cells[1].width = 200; // 編號 e.row.cells[2].width = 300; // 姓名 e.row.cells[3].width = 300; // 帳號 e.row.cells[4].width = 300; // 密碼 e.row.cells[5].width = 300; // 年齡 // 依據狀態設定列的顏色 if (e.row.rowtype!= DataControlRowType.DataRow) return; if (e.row.cells[11].text == " 有效 ") { e.row.backcolor = System.Drawing.Color.GreenYellow; else { e.row.backcolor = System.Drawing.Color.Pink; // 停權

69 圖片 放入其他物件

70 按鈕物件 放入其他物件

71 GridView 加入 Button 取得主鍵 PK protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.commandname == "abc") { int rowindex = int.parse(e.commandargument.tostring()); string val = this.gridview1.datakeys[rowindex].value.tostring(); Response.Write(val);

72 顯示照片 照片放在資料庫外 照片放在資料庫內

73 照片在資料庫外 先加入 ImageField 轉換成 TemplateField 設定連結照片所在外部的位置

74 透過 Generic Handler 照片在資料庫內

75 ImageHandler.ashx 照片在資料庫內 public class ImageHandler : IHttpHandler { public void ProcessRequest (HttpContext context) { string imageid = context.request.querystring["imid"]; SqlConnection con = new SqlConnection("Data Source= ;Initial Catalog=POS;Persist Security Info=True;User ID=sa;Password=1234"); con.open(); SqlCommand cmd = new SqlCommand("select picin from product where p_id=" + imageid, con); SqlDataReader dr = cmd.executereader(); dr.read(); context.response.binarywrite((byte[])dr[0]); con.close(); context.response.end(); public bool IsReusable { get { return false;

76 照片在資料庫內 先加入 TemplateField 加入 Image 物件 設定 DataBindings

77 將圖片存入資料庫 使用 FileUpload 物件

78 將圖片存入資料庫 // Save files to Folder and files path in database protected void btnupload_click(object sender, EventArgs e) { //Condition to check if the file uploaded or not if (fileupload1.hasfile) { int length = fileupload1.postedfile.contentlength; byte[] imgbyte = new byte[length]; HttpPostedFile img = fileupload1.postedfile; //set the binary data img.inputstream.read(imgbyte, 0, length); string filename = Path.GetFileName(fileUpload1.PostedFile.FileName); using (SqlConnection con = new SqlConnection(strCon)) { using (SqlCommand cmd = new SqlCommand()) { cmd.commandtext = "insert into MyImage (imagename,imagedata) values(@name,@data)"; cmd.parameters.addwithvalue("@name", filename); cmd.parameters.addwithvalue("@data", imgbyte); cmd.connection = con; con.open(); cmd.executenonquery(); con.close(); BindGridviewData();

79 檔案上傳 FileUpload 物件 ( 單一檔案 ) // 檔案上傳 Boolean fileok = false; String path = Server.MapPath("~/images/" + Session["modifyStoreID"] + "/"); if (FileUpload1.HasFile) { String fileextension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); String[] allowedextensions = { ".gif", ".png", ".jpeg", ".jpg" ; for (int i = 0; i < allowedextensions.length; i++) { if (fileextension == allowedextensions[i]) { fileok = true; 接下頁

80 檔案上傳 if (fileok) { try { FileUpload1.PostedFile.SaveAs(path + FileUpload1.FileName); Label6.Text = " 檔案上傳完成!"; listimages(); catch (Exception ex) { Label6.Text = " 檔案無法上傳!"; else { Label6.Text = " 檔案格式錯誤!";

81 多檔案一次上傳 一定要.net framework 4.5 以上

82 // 檔案上傳 Boolean fileok = false; String path = Server.MapPath("~/images/" + Session["modifyStoreID"] + "/"); HttpFileCollection uploadedfiles = Request.Files; Label6.Text = string.empty; for (int i = 0; i < uploadedfiles.count; i++) { HttpPostedFile userpostedfile = uploadedfiles[i]; try { if (userpostedfile.contentlength > 0) { // 限制檔案大小, 限制為 2MB int filesize = userpostedfile.contentlength; if (filesize > ) { Label6.Text = " 檔案大小上限為 2MB, 目前上傳的檔案 [" + userpostedfile.filename + "] 超過大小, 無法上傳 "; return; userpostedfile.saveas(path + "\\" + Path.GetFileName(userPostedFile.FileName)); fileok = true; catch (Exception Ex) { Label6.Text += "Error: <br>" + Ex.Message; fileok = false; 接下頁

83 if (fileok) { try { FileUpload1.PostedFile.SaveAs(path + FileUpload1.FileName); Label6.Text = " 檔案上傳完成!"; listimages(); catch (Exception ex) { Label6.Text = " 檔案無法上傳!"; else { Label6.Text = " 檔案格式錯誤!";

84 改用 ListView 顯示

85 ListView 連結 DB 透過 SqlDataSource 物件 ( 直接設定連結就可以 ) 設定後並不會出現資料, 還需要設定 ListView 顯示資料

86 設定 ListView 顯示資料 Configure ListView

87 每列顯示筆數如果改變, 要記得同時設定 ItemTemplate 與 AlternatingItemTemplate 設定 ListView

88 設定 ListView 顯示資料 預覽 顯示樣式 外觀 啟用功能

89 result 自動就會有新增 / 刪除 / 修改功能

90 加入搜尋功能 protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.SelectCommand = "select * from customer where c_name like '%" + TextBox1.Text + "%'";

91 修改介面 例如修改預設按鈕文字 要設定 ItemTemplate 與 AlternatingTemplate

92 Image 可以直接設定 Binding 加入其他物件 Table 1x2 做排版 Button

93 Bind Image 資料庫內儲存照片檔名 ( 位置在 server 內固定位置, 例如 images)

94 透過 ListView 新增圖片 (I) 在 InsertTemplate 中加入 fileupload 控制項

95 透過 ListView 新增圖片 (II) 將要顯示圖片名稱欄位的輸入框 (id=xyz) 之 ClientIDMode 屬性設定為 Static 透過 JavaScript 將 Fileupload 選擇的圖片名稱顯示到輸入框內 <asp:fileupload ID="FileUpload1" runat="server" OnDataBinding="FileUpload1_DataBinding" onchange="getfilename(this.value);" /> <script> function getfilename(file) { // 檔案名稱 var filename = file.replace(/^.*[\\\/]/, '') document.getelementbyid('xyz').value = filename; </script>

96 透過 ListView 新增圖片 (II) Code in Insert button

97 protected void InsertButton_Click(object sender, EventArgs e) { FileUpload fu = (FileUpload)ListView1.InsertItem.FindControl("FileUpload1"); Boolean fileok = false; String path = Server.MapPath("~/images/"); if (fu.hasfile) { String fileextension = System.IO.Path.GetExtension(fu.FileName).ToLower(); String[] allowedextensions = { ".gif", ".png", ".jpeg", ".jpg" ; for (int i = 0; i < allowedextensions.length; i++) { if (fileextension == allowedextensions[i]) { fileok = true; if (fileok) { try { fu.postedfile.saveas(path + fu.filename); catch (Exception ex) { else { Code in Insert button

98 Bind Button 要抓到 ListView 內繫結的物件 ( 如 Button) 事件, 要設定 Button 的 CommandArgument 設定按下後要取得的值

99 撰寫 Button 事件 要取得上一頁的 p_id, 要用的是 Button_Command 事件, 而不是 Button_Click 事件

100 ListView + Session 購物車

101 Session 購物車 確定已經設定按鈕的 CommandArgument DataTable Cart = new DataTable(); // 存放購物車 DataRow objdr; // 每筆購物項目 protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Session["ShoppingCart"] = ""; Response.Write("<script>alert(' 請選擇您要購買的物品 ');</script>");

102 如果要加入數量選擇或輸入到 ListView 將購買按鈕的事件改為 ListView1_ItemCommand 找到 TextBox2 var txt = ListView1.Items[e.Item.DataItemIndex].FindControl("TextBox2") as TextBox; 選擇的項目 index

103 購物按鈕 (I) protected void ListView1_ItemCommand(object sender, ListViewCommandEventArgs e) { var txt = ListView1.Items[e.Item.DataItemIndex].FindControl("TextBox2") as TextBox; if (Session["ShoppingCart"]=="") { // 購物車為空, 需要建立 DataTable 的結構 // 產品編號 Cart.Columns.Add(new DataColumn("ItemID",typeof(int))); // 數量 Cart.Columns.Add(new DataColumn("Quantity", typeof(int))); objdr = Cart.NewRow(); // 產生新列 objdr["itemid"] = e.commandargument; // 產品編號 objdr["quantity"] = txt.text; // 數量 Cart.Rows.Add(objDR); else

104 購物按鈕 (II) else { // 取出目前購物車內容, 判斷是否已經加入? Cart = (DataTable)Session["ShoppingCart"]; Boolean blnmatch = false; // 紀錄是否已經加入購物車 foreach(datarow objdr in Cart.Rows){ if (objdr["itemid"].tostring()==e.commandargument.tostring()) { int newnum = Convert.ToInt16(objDR["Quantity"]) + Convert.ToInt16(txt.Text); objdr["quantity"] = newnum; // 數量 blnmatch = true; break; if (!blnmatch) // 沒有買過 { objdr = Cart.NewRow(); // 產生新列 objdr["itemid"] = e.commandargument; // 產品編號 objdr["quantity"] = txt.text; // 數量 Cart.Rows.Add(objDR); Session["ShoppingCart"] = Cart; Response.Write(e.CommandArgument + " 已經加入購物車 ");

105 檢視購物車 protected void Button4_Click(object sender, EventArgs e) { // 檢視購物車 if (Session["ShoppingCart"]!= "") { Cart = (DataTable)Session["ShoppingCart"]; foreach (DataRow objdr in Cart.Rows) { Response.Write(" 編號 =" + objdr["itemid"] + "/ 數量 =" + objdr["quantity"] + "</br>"); else{ Response.Write(" 目前購物車是空的!"); 可以自己定義版面

106 寫程式連資料庫

107 再看回 ASP.NET 資料庫設計模式 DB ADO.NET CommandBuilder 物件 Connection 物件 Command 物件 DataAdapter 物件 DataReader 物件 ExecteNonQuery 可異動資料 DataReader 物件 ExecuteReader 唯讀 DataSet 物件 On-line 線上作業模式 Off-line 離線作業模式

108 上圖的物件是大架構 表示跟資料庫種類無關 可是現實是, 資料庫種類不完全相同 那要怎麼處理呢? 微軟把個別資料庫連結方式做分類, 放在名稱空間內 (namespace) 需要時再叫進來就可以 依據不同資料庫, 會在物件前面加上適當的冠詞

109 ADO.NET ADO.NET 是用來存取資料庫的物件集合 核心命名空間 : 一般 : System.Data, System.Data.Common SQL Server: System.Data.SqlClient Oracle: System.Data.OracleClient OleDB: System.Data.OleDb (Access) ODBC: System.Data.Odbc 利用 Using 指令參考 ADO.NET 物件

110 以 ON-LINE MODE 讀取資料為例

111 DB On-line mode CommandBuilder 物件 Connection 物件 Command 物件 DataAdapter 物件 DataReader 物件 ExecteNonQuery 可異動資料 DataReader 物件 ExecuteReader 唯讀 DataSet 物件 On-line 線上作業模式 Off-line 離線作業模式

112 DataReader 透過 DataReader 物件可以逐一讀取資料 DataReader = 只能下一筆 唯讀的讀取 根據要求將記錄以串流的方式傳回用戶端 佔用資料庫連線, 使用伺服端資源直到連線關閉 用戶端 DataReader 記錄 record record 資料庫引擎

113 資料庫存取五步驟 ADO.NET 存取資料時, 使用以下五大步驟 : 1. Using ADO.NET 物件 ( 依資料庫種類而定 ) 2. 開啟資料庫連線 DBMS+DB 層 (CONNECTION 物件 ) 3. 進行資料庫操作 ( 讀取 / 寫入 ) TABLE 層 (COMMAND+DATAREADER 物件 ) 4. 顯示資料 5. 關閉資料庫連線

114 DataReader 資料存取示意圖 DBMS DB TABLE CONNECTION 物件負責連結 DBMS 與 DB COMMAND 物件負責 SQL 指令 SQL SERVER 1. SERVER 位置 2. 帳號 3. 密碼 4. DATABASE Access 1. DATABASE DataReader 物件負責取出 DATA 連線字串

115 對應 (I) 1. Using ADO.NET 物件 ( 依資料庫種類而定 ) 2. 開啟資料庫連線 DBMS+DB 層 程式需要的是這個連線字串

116 對應 (II) 1. 進行資料庫操作 ( 讀取 / 寫入 ) TABLE 層 (COMMAND+DATAREADER 物件 ) 程式需要的是 SQL 指令

117 自己透過 Panel 顯示結果 // 連結資料庫 SqlConnection dbconn = new SqlConnection( 連線字串 "); dbconn.open(); // 開啟資料庫 SqlCommand dbcmd = new SqlCommand( SQL 指令 ", dbconn); SqlDataReader dbreader = dbcmd.executereader(); String result = "<table width='100%' border=1><tr>"; // 建立表格 // 顯示表頭 for (int col = 0; col < dbreader.fieldcount; col++) { result += "<td>" + dbreader.getname(col).tostring() + "</td>"; result += "</tr>"; // 顯示資料 while (dbreader.read()) { result += "<tr>"; for (int col = 0; col < dbreader.fieldcount; col++) { result += "<td>" + dbreader[dbreader.getordinal(dbreader.getname(col).tostring())].tostring() + "</td>"; result += "</tr>"; result += "</table>"; dbconn.close(); // 關閉資料庫 Panel1.Controls.Add(new LiteralControl(result));

118 改成用 Class 方式運作?

119 需要繼承 MyPage.cs 使用 DBOperation.cs 與 MyPage.cs // 設定起始事件, 設定 DB 連線字串 protected void Page_Init(object sender, EventArgs e) { // 設定連線字串 constr = 連線字串 "; // 設定要操作的 table name tablename = "product"; // 設定主 key pk = "p_id"; // 設定搜尋欄位 schfield = "p_name";

120 表單中需要加入 利用其與 javascript 溝通 <div> <input type="hidden" name=" EVENTTARGET" id=" EVENTTARGET" value="" /> <input type="hidden" name=" EVENTARGUMENT" id=" EVENTARGUMENT" value="" /> <script type="text/javascript"> //<![CDATA[ var theform = document.forms['form1']; if (!theform) { theform = document.form1; function dopostback(eventtarget, eventargument) { if (!theform.onsubmit (theform.onsubmit()!= false)) { theform. EVENTTARGET.value = eventtarget; theform. EVENTARGUMENT.value = eventargument; theform.submit(); //]]> </script> </div>

121 離線模式 protected void Page_Load(object sender, EventArgs e) { binddata("select * from customer"); private void binddata(string sql) { SqlConnection dbconn = new SqlConnection( 連線字串 "); dbconn.open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(sql, dbconn); da.fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); dbconn.close();

122 ON-LINE MODE 異動資料

123 ExecuteNonQuery 透過以下三種 SQL 語法來修改資料庫資料 新增 :INSERT 修改 :UPDATE 刪除 :DELETE 透過 ExecuteNonQuery() 方法 回傳修改的資料筆數 用戶端 Command 資料庫引擎

124 異動資料程式結構 與查詢的程式結構一樣要使用 ExecuteNonQuery 方法 SqlCommand dbcmd = new SqlCommand( SQL 指令 ", dbconn); dbcmd.executenonquery(); 需要依照新 / 刪 / 修寫的 SQL 指令

125 動態 SQL 指令 1. 用假資料先寫出正確的 SQL 指令 2. 用程式取代假資料 3. 用雙引號將程式與原 SQL 指令切開 4. 用加號將切開位置串起來 把最後完成的結果貼回程式內的 Command 物件

126 動態 SQL 指令 1. 用假資料先寫出正確的 SQL 指令 Select * from customer where c_name like % 小 % 2. 用程式取代假資料 Select * from customer where c_name like %TextBox1.Text% 3. 用雙引號將程式與原 SQL 指令切開 Select * from customer where c_name like % TextBox1.Text % 4. 用加號將切開位置串起來 Select * from customer where c_name like % + TextBox1.Text + %

127 小練習 來將登入登出改成實際可用的 使用線上模式寫程式來達成! 讓欄位區分大小寫檢查 COLLATE Chinese_Taiwan_Stroke_CS_AI Select * from Users where u_account COLLATE Chinese_Taiwan_Stroke_CS_AI=@id and u_pwd COLLATE Chinese_Taiwan_Stroke_CS_AI=@pwd

128 改寫登入 String connstr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["POSConne ctionstring"].connectionstring.tostring(); SqlConnection dbconn = new SqlConnection(connstr); dbconn.open(); SqlCommand dbcmd = new SqlCommand("select * from customer where c_account=@c_account and c_pwd=@c_pwd", dbconn); dbcmd.parameters.addwithvalue("@c_account", TextBox1.Text); dbcmd.parameters.addwithvalue("@c_pwd", TextBox2.Text); SqlDataReader dbreader = dbcmd.executereader(); // 判斷帳號密碼 if(dbreader.read()){ // 改變 / 設定 session Session["iflogin"] = "1"; Session["loginuserid"] = dbreader[dbreader.getordinal("c_id")]; // 跳到設定畫面 Response.Redirect("NewBuycart.aspx"); dbconn.close(); else { Response.Write("<script>alert(' 帳號密碼錯誤 ');</script>"); dbconn.close();

129 SQL Injection 資料隱碼攻擊 SQL 指令的漏洞 or 1=1 or 1=1 or 1= ' or ''=' 取代有問題的字元 - / 等

130 避免 SQL Injection private string fixsql(string sql) { sql = sql.replace("'", ""); sql = sql.replace("\\", "//"); sql = sql.replace("-", "_"); sql = sql.replace("*", "_"); sql = sql.replace("+", "_"); return sql; 制輸入的長度降低 sql server 使用者權限至少不幸被入侵, 只是修改或刪除記錄而不會整個 database 被刪除

131 避免 SQL Injection 改用 ASP.NET 參數方式帶入 String UID = TextBox1.Text; String UPWD = TextBox2.Text; 參數 dbcmd.parameters.add("@id", SqlDbType.NVarChar).Value = fixsql(uid); dbcmd.parameters.add("@pwd", SqlDbType.NVarChar).Value = fixsql(upwd); SqlCommand dbcmd = new SqlCommand("Select * from Users where u_account COLLATE Chinese_Taiwan_Stroke_CS_AI=@id and u_pwd COLLATE Chinese_Taiwan_Stroke_CS_AI=@pwd and u_active=1", dbconn); 表示要區分大小寫

132 購物車結帳 // 產生單號 string news_id = System.DateTime.Now.ToString("yyyyMMddHHmmss"); String connstr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["POSConnectionString "].ConnectionString.ToString(); SqlConnection dbconn = new SqlConnection(connstr); dbconn.open(); // 建立主單 SqlCommand dbcmd = new SqlCommand("insert into sales (s_id,c_id,s_day,s_memo) values (@s_id,@c_id,@s_day,@s_memo)", dbconn); dbcmd.parameters.addwithvalue("@s_id", news_id); dbcmd.parameters.addwithvalue("@c_id", Session["loginuserid"].ToString()); dbcmd.parameters.addwithvalue("@s_day", System.DateTime.Now); dbcmd.parameters.addwithvalue("@s_memo", ""); dbcmd.executenonquery(); // 寫入明細 Cart = (DataTable)Session["ShoppingCart"]; foreach (DataRow objdr in Cart.Rows) { dbcmd = new SqlCommand("insert into sales_detail (s_id,p_id,amount,price) values (@s_id,@p_id,@amount,@price)", dbconn); dbcmd.parameters.addwithvalue("@s_id", news_id); dbcmd.parameters.addwithvalue("@p_id", objdr["itemid"]); dbcmd.parameters.addwithvalue("@amount", objdr["quantity"]); dbcmd.parameters.addwithvalue("@price", "123"); dbcmd.executenonquery(); dbconn.close(); Session["ShoppingCart"] = ""; Cart.Clear();

133 練習 1 製作一個註冊的表單 加入 Validation 檢查 必要輸入 帳號密碼不可以一樣 輸入長度檢查 ^[\s\s]{6,10$ 密碼強度 ( 大小寫至少 1, 混合數字, 長度 6~10) ^(?=.*\d)(?=.*[a-z])(?=.*[a-z]).{6,10$ 格式等