【教学课件】第十八章使用SQL整合网页与资料库.ppt

《【教学课件】第十八章使用SQL整合网页与资料库.ppt》由会员分享，可在线阅读，更多相关《【教学课件】第十八章使用SQL整合网页与资料库.ppt（71页珍藏版）》请在三一办公上搜索。

1、第十八章使用SQL整合網頁與資料庫,張智星http:/mirlab.org/jang台大資工系 MIR實驗室,本章大綱,大綱本章說明如何使用 SQL 來進行 ASP 網頁與 Access 資料庫的整合，並有大量的實用範例，讓讀者知道如何經由網頁進行對資料庫的檢視、新增、修改、刪除等基本操作。主題18-1：ODBC 與 DSN 簡介 18-2：網頁與資料庫整合的基本範例 18-3：使用 SQL 來檢視資料 18-4：使用 SQL 來新增、修改、刪除資料 18-5：資料隱碼（SQL Injection）,2/69,18-1：ODBC 與 DSN 簡介,本小節介紹ODBC與DSN的設定。,3/69,

2、使用資料庫的好處,資料的維護較為容易，可以使用標準的 SQL（Structure Query Language）指令來進行資料庫的各種資料處理，含查詢、新增、修改、刪除等運算。資料之間的關連也可以使用關連式資料庫（Relational Databases）來保證資料的正確、完整和一致性，並同時減少不必要的資料量。一般應用程式可以使用 ODBC（請見下列說明）來對資料庫進行標準的處理。資料在不同資料庫之間的轉換較為容易，一般資料庫廠商都有提供相關的轉換程式。資料與網頁的呈現是獨立的，可以分開進行，互不干擾。,4/69,ASP與資料庫整合要點,瞭解資料庫的基本概念。瞭解 ASP 如何經由 ODBC

3、 與資料庫溝通。瞭解 如何使用 SQL 來對資料庫進行查詢、新增、修改等動作。,5/69,ODBC,ODBC 是 Open DataBase Connectivity 的簡稱，它是一個工業界的標準。可以看成是各家資料庫廠商所提供的一個應用程式介面（Application Program Interface，簡稱 API）。可讓其他軟體或程式根據這個標準一致的程式介面，來對資料庫進行新增、讀取、修改、刪除等動作。對資料進行的動作，在資料庫的術語來講都是 查詢（Query），而這些查詢動作都是根據 SQL 的標準資料庫語言來完成。,6/69,ASP 經由 ODBC 與資料庫溝通,直接指定資料庫在本

4、機硬碟的路徑此種方法較具彈性，整個應用程式目錄可在不同的伺服器中搬動，但能對資料庫進行的設定有限。指定DSN我們必須在控制台設定資料來源名稱（Data Source Name，簡稱 DSN)，以指定可經由 ODBC 連結的資料庫。此種作法較不具彈性，但卻能經由本機對資料庫進行比較完整的設定。,7/69,設定DSN(1),從微軟視窗系統左下角的開始選單進行選取，順序為開始/控制台/系統管理工具/資料來源(ODBC)，此時所打開的ODBC資料來源管理員，其外觀如下：,8/69,設定DSN(2),點選資料來源(ODBC)，開啟視窗後，再點選系統資料來源名稱。,9/69,設定DSN(3),說明由系統資

5、料來源名稱所設定的 DSN，是屬於系統級的 DSN，因此其他使用者（含網頁瀏覽者）也可以使用此 DSN。若要使用個人級的 DSN，那麼就可以使用使用者資料來源名稱，但此設定並不適用於網頁瀏覽。,10/69,設定DSN(4),點選新增，再選擇Microsoft Access Driver(*.mdb)，請注意：不要誤選另一個很類似的選項Driver do Microsoft Access(*.mdb)！,11/69,設定DSN(5),輸入資料來源名稱，假設我們輸入的字串是 dsn4test。,12/69,設定DSN(6),再按下選取，就可以選取對應的 Access 資料庫，之後再一路點選確定，即

6、可完成 DSN 的設定。,13/69,MS資料庫的選擇,MS Access 並不是企業專用的資料庫引擎，因此效率並不是很好，而且也不支援許多大型的資料庫應有的功能，但是對於小型的網路應用而言（例如同時上線人數少於10人左右），Access 還算堪用。MS SQL Server 是微軟推出的資料庫引擎，專門對付大型網路應用，是一般中小企業較常採用的資料庫。,14/69,18-2：網頁與資料庫整合的基本範例,本小節介紹各種ASP與資料庫整合的方法。,15/69,ADO,在ASP程式設計裡，用來存取資料庫或表格資料的物件統稱 ADO（ActiveX Data Objects）ADO是一個 ASP 內

7、建的資料庫存取元件，可以經由 JavaScript/JScript、VBScript 等語言來控制資料庫的存取，並可連接多種資料庫，包括 SQL Server、Oracle、Access 等支援ODBC的資料庫。ADO 主要包含 Connection、Recordset 及 Command 三種物件。,16/69,Connection 物件,使用 ADO 的 Connection 物件來進行資料庫的檢視查詢，主要有以下四個步驟建立資料庫連結，然後開啟資料庫。執行SQL指令，並將查詢結果儲存於 Recordset 中：若是檢視查詢，我們可將結果存至 RecordSet 物件變數rs中，以便後續取

8、用。取得欄位名稱及內容：若是檢視查詢，我們可以使用下列的的方式來取得欄位名稱及內容等資訊。關閉 RecordSet 及資料庫連結。,17/69,建立並開啟資料庫,使用Server.CreateObject定義一個 ADO 的 Connection 物件，然後使用其Open的方法來開啟資料庫來源。設定 conn 物件的 ConnectionString 性質來指定資料庫。連結到你想要連結的本機或遠端資料庫。最後再用 conn 物件的 Open 方法，來開啟資料庫,18/69,指定資料庫方法,直接指定 Access 資料庫在本機硬碟的路徑指定 DSN（資料來源名稱）直接連結至 SQL Server

9、 資料庫直接連結至 UNIX 的 MySQL 資料庫,19/69,執行SQL指令,若是檢視查詢，我們可將結果存至 RecordSet 物件變數rs中，以便後續取用。說明以上的程式碼將 SQL 指令所查詢到的結果儲存到 Recordset 物件 rs 中。若不是檢視查詢，則不需要將結果存放於變數 rs。,20/69,取得欄位名稱及內容(1),若是檢視查詢，我們可以使用下列的的方式來取得欄位名稱及內容等資訊。,21/69,取得欄位名稱及內容(2),說明要印出每一筆資料的每一個欄位名稱，可用下列典型程式碼：印出每一筆資料的每一個欄位值，可以使用下列典型程式碼：以上的程式碼由 rs(i)讀取資料庫欄位

10、的資料，rs.MoveNext()將 Recordset 的資料指標移到下一筆，經由 rs.EOF 來判斷是否已到了最末筆資料，並配合 while 迴圈即可得到所有查詢結果的資料。,22/69,關閉 RecordSet 及資料庫連結,範例程式碼如下：說明許多有關I/O的指令如果有open()，通常相對就會有close()這個函式，保障對特定I/O的控制權及釋放權。,23/69,範例18-1(1),主題：以 JScript 進行資料庫列表Webpage:remote host,local host,database說明範例中使用直接指定資料庫在本機硬碟的路徑的方式來連結資料庫，其中的 SQL 指

11、令SELECT*FROM testTable代表從資料表 testTable 取出所有資料。最後一筆資料的 RealName 欄位和 Email 欄位都未填入資料，但是 RealName 欄位的並無預設值，因此由資料庫抓回來的資料顯示為 null；另，Email 欄位的預設值是空字串，所以沒有印出任何東西。這些欄位的屬性可由 Access 資料表的設計檢視選單來設定。,24/69,範例18-1(2),如果上述範例發生錯誤，一個可能的原因是：OS 是64-bit，而 Access 資料庫是32-bit，解決方案請見http:/mirlab.org/users/pony.chen/內的Win7 6

12、4-bit上如何使用32-bit的ODBC連結。,25/69,將範例18-1改成DSN連結,若要使用 DSN 連結資料庫，首先我們必須先在伺服器設定 DSN（詳細流程請見上一小節），然後就可以在 ASP 內經由 DSN 來指定資料庫（可以是近端或是遠端）。以範例18-1而言，若要由 DSN 來連結資料庫，而不直接指定資料庫，只要把下一列敘述：改成下一列即可，其中 dsn4test 必須已被設定為指向 test.mdb 的 ODBC 資料來源。Webpage:remote host,local host,26/69,範例18-3,主題：使用 listQueryResult()函數進行查詢Webp

13、age:remote host,local host說明此inc檔分別寫了適用於 JScript 和 VBScript 的函數，因此無論是使用 JScript 或 VBScript 的 ASP 網頁，都可以使用此包含檔來列出資料庫查詢的結果。一般而言，SQL 指令已經具有對資料庫進行檢視、新增、修改、刪除等功能，因此只要使用適當的 SQL 指令，再加上前述的方法，即可對資料庫進行完全的處理。,27/69,18-3：使用 SQL 來檢視資料,本小節介紹查詢資料庫相關的SQL語法。,28/69,SQL簡介,SQL 是結構化查詢語言（Structured Query Language）的簡稱，是由

14、IBM 公司於 1970 年代所發展出來，用於關連式資料庫(Relational Databases)當中的一種資料庫查詢語言，利用 SQL 可以用來進行各種與資料庫相關的處理，例如：產生資料庫內的資料表 定義資料表內的欄位與相關資料型態 建立表格之間的關連性 對資料進行處理：新增、修改、刪除、查詢 對資料進行統計,29/69,SELECT,檢視資料庫的資料，使用的 SQL 主要指令是SELECT。基本結構說明SELECT 的欄位名稱為待查資料庫的欄位名稱。FROM 的資料表名稱為待查資料庫的資料表名稱。WHERE 的條件式為設定查詢的條件式。ORDER BY 的欄位名稱為欲排序的欄位，可將查

15、詢的資料根據這些欄位來排序。指定多個欄位時，則以欄位名稱1排序，若其資料相同則再依欄位名稱2排序，依此類推。中括號表示選擇性條件，也就是說只有SELECT和FROM是必要條件。,30/69,檢示資料範例(1),我們以資料庫 basketball.mdb 為例，這個資料庫包含兩個資料表：Player 包含球員的資料，其中 TeamID 是球員所隸屬的籃球隊代號（載明在 Team 資料表），Percentage 是投籃的命中率。Team 包含籃球隊的資料，其中 WinNo 是本季的贏球次數。,31/69,檢示資料範例(2),SELECT*FROM Team意義：所有球隊資料說明：*代表 Team

16、資料表中所有的欄位查詢結果：,32/69,檢示資料範例(3),SELECT TOP 3*FROM Team意義：所有球隊資料，但只抓前三筆說明：TOP 3代表只抓取前三筆資料。也可以使用TOP 25 percent等，代表抓取所有資料的前百分之二十五。查詢結果：,33/69,檢示資料範例(4),SELECT Name,Percentage FROM Player WHERE NickName=gavins意義：綽號為 gavins 的球員姓名及命中率查詢結果：,34/69,檢示資料範例(5),SELECT*FROM Team WHERE Name like 台%意義：隊名以台開頭的球隊資料說明

17、：%代表任意長度的字串。查詢結果：,35/69,檢示資料範例(6),SELECT Name,Percentage FROM Player WHERE Name like 陳_意義：姓陳且名字有三個字的球員姓名及命中率說明：_代表任意單一字元。查詢結果：,36/69,檢示資料範例(7),SELECT Name,WinNo FROM Team WHERE WinNo10意義：勝場數大於10的球隊名稱及其勝場數查詢結果：,37/69,檢示資料範例(8),SELECT Name,WinNo FROM Team WHERE WinNo10 ORDER BY WinNo DESC意義：勝場數大於10的球隊

18、名稱及其勝場數，並根據勝場數由大到小排列說明：若不加入 DESC，則會進行由小到大的排序。查詢結果：,38/69,檢示資料範例(9),SELECT TeamID,Name,Percentage FROM Player WHERE TeamID=5 ORDER BY Percentage DESC意義：球隊代碼為5的球員命中率排行榜查詢結果：,39/69,檢示資料範例(10),SELECT*FROM Player ORDER BY TeamID,Percentage DESC意義：每一隊的球員命中率排行榜說明：列出結果會先按 TeamID 由小到大排序，再按 Percentage 由大到小排序。

19、查詢結果：,40/69,檢示資料範例(11),SELECT count(*)FROM Team WHERE WinNo10意義：勝場數大於10的球隊總數說明：count()函數會計算資料筆數，資料庫會自動產生暫時的欄位名稱 Expr1000。查詢結果：,41/69,檢示資料範例(12),SELECT max(Percentage)as 最高命中率 FROM Player意義：所有球員的最高命中率說明：max(Percentage)函數會計算命中率最大值。由於使用了as 最高命中率，資料庫會自動產生暫時的欄位名稱最高命中率。查詢結果：,42/69,檢示資料範例(13),SELECT TOP 1

20、Name,Percentage FROM Player ORDER BY Percentage DESC意義：具有最高命中率的球員資料查詢結果：,43/69,檢示資料範例(14),SELECT Name,Percentage FROM Player WHERE Percentage in(SELECT max(Percentage)FROM Player)意義：具有最高命中率的球員資料說明：功能同前一個範例，但是改用兩個 SQL 指令組合來達成同樣的效果。查詢結果：,44/69,GROUP BY與HAVING,基本結構說明GROUP BY 其後所接的欄位名稱為需要聚合的欄位名稱。（所謂聚合，就

21、是將相同欄位值的數筆資料合成一筆新資料。）。HAVING 其後所接的條件式，則會用在聚合後的資料篩選。,45/69,GROUP BY與HAVING範例(1),意義：每個球隊的球員人數及平均命中率說明：avg(Percentage)可以計算命中率平均值，類似的 SQL 聚合函數有 Avg（平均值）、Count（筆數）、Max（最大值）、Min（最小值）、StDev（母群體樣本標準差）、StDevp（母群體標準差）、Sum（總和）、Var（母群體樣本變異數）、VarP（母群體變異數）等。由於這是對於每個球隊的統計數字，所以必須用到群組指令GROUP BY。,46/69,GROUP BY與HAVIN

22、G範例(2),查詢結果：,47/69,GROUP BY與HAVING範例(3),意義：每個球隊的球員人數，但只顯示球員人數大於 2 位的資料說明：由於這是對於每個球隊的統計數字，所以必須用到群組指令GROUP BY，相關的條件則必須使用HAVING來指定。查詢結果：,48/69,根據資料表關聯性檢視查詢(1),意義：台北隊的球員資料說明：由於兩個資料表都有 Name 欄位，所以我們必須使用 Team.Name 及 Player.Name 來區分不同資料表的欄位。另外，這兩個資料表的關聯性是由(Player.TeamID=Team.ID)所建立，所以在後續的範例中，我們會不斷使用這個查詢條件。,

23、49/69,根據資料表關聯性檢視查詢(1),查詢結果：,50/69,根據資料表關聯性檢視查詢(2),意義：高雄隊和台中隊的射手排行榜查詢結果：,51/69,根據資料表關聯性檢視查詢(2),意義：每個球隊的相關統計數字說明：由於這是對於每個球隊的統計數字，所以必須用到群組指令GROUP BY。同時由於被選取的欄位中，Team.Name 和 Team.WinNo 都沒有用到任何聚合函數，所以在 GROUP BY 之後也必須要加上這兩個欄位。,52/69,根據資料表關聯性檢視查詢(3),查詢結果：,53/69,18-4：使用 SQL 來新增、修改、刪除資料,本小節介紹如何用SQL語法管理資料表。,5

24、4/69,CREATE TABLE,新增資料表：使用的 SQL 指令是CREATE TABLE基本結構說明如果新增的資料非一列可以表示的，就需要新增資料表。論壇中新增討論區就會使用這個指令新增討論區資料表。,55/69,INSERT,新增資料：使用的 SQL 指令是INSERT基本結構說明如果欄位名稱沒有指定完全，則資料庫會自動取用此欄位之預設值。我們可由 Access 資料庫的設計檢視來檢視每一個欄位的預設值。,56/69,UPDATE,修改資料：使用的 SQL 指令是UPDATE基本結構說明用來修改資料表欄位中的值。網站上修改會員個人資料時會用到。,57/69,DELETE,刪除資料：使用

25、的 SQL 指令是DELETE基本結構說明如果沒有指定任何條件式，此 SQL 指令會刪除一個資料表內的所有紀錄，所以要特別小心。管理Blog時刪除文章會用到。,58/69,DROP TABLE,刪除資料表：使用的 SQL 指令是DROP TABLE基本結構說明在論壇管理系統中，要刪除某個討論區會用到。,59/69,範例18-5,主題：對資料表進行修改、刪除動作。Webpage:remote host,local host說明範例中所做的事情建立一個資料表 friend。插入兩筆資料。刪除一筆資料。更新一筆資料。刪除資料表 friend。範例中，如果顯示的欄位值是 null，代表我們當初在新增資

26、料時，並沒有設定相關欄位值，資料庫也沒有預設值，所以才會回傳 null。,60/69,範例18-6,主題：可隨時對資料表進行修改刪除的範例Webpage:remote host,local host說明這個範例，可以讓你在網頁上嘗試各種查詢動作，例如新增、修改、刪除等。使用 Access 資料庫的另一個好處是，它提供了一個圖形化的查詢介面，可以使用這個查詢介面產生的要的查詢結果，再將此查詢方法轉成 SQL 的語法，此時就可以將此 SQL 語法直接貼到的 ASP 程式碼，對於產生複雜的 SQL 語法非常好用。,61/69,小秘訣,使用 ASP 整合資料庫時，可參考下列小秘訣資料庫內的資料表名稱及

27、欄位名稱，最好是英文，且中間不可留白。欄位名稱最好複雜一點，以免和資料庫的內建關鍵字相衝。文字欄位的預設值最好是空字串，不要不設定預設值。在 Access 內，除非你的欄位資料量超過255個字元，否則盡量不要用到 memo 欄位，因為 memo 欄位不支援排序，也不支援萬用字元（如*或?等）。,62/69,萬用字元,在 Access 內執行 SQL 指令時，有兩個最重要的萬用字元?：比對一個字元*：比對多個字元 說明若要在 ASP 的程式碼內使用 SQL 的萬用字元，必須將?改為_，*改為%，以符合一般 SQL 語言的標準規範。,63/69,18-5：資料隱碼（SQL Injection）,本

28、小節介紹各種因為ASP與資料庫整合時的疏失，使得他人可以使用非法途徑來取的資料庫內容，或者進行其它侵入。,64/69,SQL Injection簡介,資料隱碼（SQL Injection）臭蟲，簡單地說，就是將帳號和密碼欄位填入具有單引號的特殊字串，造成伺服器端在接合這些欄位資料時，會意外地產生合格的 SQL 指令，造成密碼認證的成功。要特別注意的是，SQL Injection 的問題不限只發生在哪種特定平台或語言，只要是使用 SQL 指令存取資料庫內的資料，都有可能產生這個問題。,65/69,範例18-7(1),主題：以資料庫內之資料進行密碼認證：基本篇Webpage:remote host

29、,local host程式碼重點說明看起來一切沒問題，但是如果你想駭（Hack!）這個網站，事實上只要輸入下列資料就可以了：帳號：*（亂打一通）密碼：or a=a,66/69,範例18-7(2),說明當輸入帳號和密碼分別是林政源和gavins時，可以從資料庫中查到一筆資料，代表帳號和密碼正確，所得到的 SQL 指令是當帳號和密碼分別是xyz和 or a=a時，所產生的 SQL 指令也會執行成功（因為 a=a 是一定成立的）在 SQL 語法的條件式中，會先執行 and，再執行 or。,67/69,避免SQL Injection,最簡單的作法，就是在取用客戶端送進來的資料前，先刪除所有可能造成問題

30、的特殊字元。這些字元包括單引號（）、雙引號（“）、問號（?）、星號（*）、底線（_）、百分比（%）、ampersand（&）等，這些特殊字元都不應該出現在使用者輸入的資料中。刪除特殊字元的動作務必 要在伺服器端進行，因為用戶端的 JavaScript 表單驗證的檢查是只能防君子，不能防小人，別人只要做一個有相同欄位的網頁，就一樣可以呼叫你的 ASP 程式碼來取用資料庫，進而避開原網頁的表單驗證功能。,68/69,範例18-8,主題：使用replace()避免 SQL InjectionWebpage:remote host,local host程式碼重點說明在上述原始碼中，因為 Request

31、(userid)和 Request(passwd)的資料是無法修改的，所以在取代前要先存到另一個個變數。由此範例可以知道，只要刪除使用者輸入字串中的所有單引號，就可以避免 SQL Injection 的問題。,69/69,SQL Injection 的搜索,在Google 打入登入，再對需要登入的網站進行 SQL Injection 的測試，就應該可以找到一些不設防的網站。請通知該網站管理員，並表示自己無惡意：,70/69,我們研習張智星老師的JavaScript程式設計與應用，對網路上的網頁進行 SQL Injection 的測試，發覺您的登入網頁（網址是）並無法對抗 SQL Injection 的入侵，只要帳號任意設定、密碼設定為 or a=a，即可登入。這是一封善意的信，我們僅測試是否可以登入，並未對資料進行任何修改，請查照，謝謝。（請寫出你的全名，以示負責，並將 email 副本給我，以便登記發問一次）,參考資料,可以形成 SQL Injection 的惡意字串還不少，但大部分是針對微軟的 SQL Server 資料庫來進行破壞。以下是參考資料：SQL Injection 的因應與防範之道.mht駭客的 SQL填空遊戲(上).mht駭客的 SQL填空遊戲(下).mht,71/69,