• 資料庫的交易鎖定 Locks

    ID-10013387

    筆者第一年在某知名公司工作時,程式開發人員、資料庫與線上使用者,

    討論到程式與資料庫讀取的設計時,

    有一位資深工程師跳出來並且在黑板畫著說:

    “當有Transaction A 資料寫入 Table A 的時候,其他交易Transaction B就不能讀取Table A ,要一直等到Transaction A交易結束才可以讀取”

    (說著就把 Table A, Table B 畫叉叉,最後所有的 Table 都被劃叉叉 )

    這樣的說法其實是對 database Locks 的機制有些誤解. 讓我們進一步說明

     

    為什麼需要 Locks

    因為多筆交易在資料的讀取與寫入的時候,彼此會相互影響,

    因此為了交易的concurrency與 isolation ,

    資料的讀取或是寫入的時候就會被做一個記號,

    這個記號用來告知該資料正在被讀取或是寫入的狀態,

    其他交易根據這個記號來決定是否要等待到該紀錄狀態結束或是直接讀取該資料

    而該”記號”就是所謂的 Locks

     

    Lock Granularity (Lock 的層級)

    想像一個比較極端的狀況,

    資料庫的資料同時只能夠有一個人User A寫入,

    因此 User 寫入資料的時候就會 Lock 該資料,其他人 User B 就要寫入資料就必須要等待,

    等到User A 資料寫入完畢,Lock 釋放之後,User B 才能進行寫入的動作

    實務上,這樣的資料庫並不實用,因為資料庫通常會有多人”同時”寫入與讀取,

    因為資料庫就必須設定一定的 Lock 與 Isolation 的層級,達到

    • 在多人可以同時存取
    • 交易鎖定的資料完整性

    為了達到可以增加多人同時存取, 當使用者 User A 寫入Table A的時候,

    Lock 就不會針對 整個 Table A or 整個檔案進行鎖定,而是更進一步細分Lock 的最小單位:

    Row (RID)
    • Key (KEY)
    • Page (PAG)
    • Extent (EXT)
    • Heap or B-tree (HoBT)
    • Table (TAB)
    • File (FIL)
    • Application (APP)
    • MetaData (MDT)
    • Allocation Unit (AU)
    • Database (DB)

    ROW-level Lock

    這是資料庫鎖定的最小單位,也就是Table 中每一筆的 Read more…

    Tags: , , ,

  • SQL Injection — Boolean Exploitation Technique

    我們知道大部分的網站存取資料都是透過資料庫,

    不外乎使用 MySQL 因為為 opensource,或是 SQL Server 微軟 IIS 等的 solution

    因此,駭客利用輸入的弱點,輸入特定指令讓該 SQL query 語法執行出非預期的結果

    這次主要要介紹的是 Boolean Exploitation

    Boolean Exploitation 

    主要是利用特定條件式的 query 讓 SQL Server 來告訴駭客 Yes or No

    這種主要用在當相關 SQL Server 回傳的訊息或是錯誤訊息都沒有或是比較少的情況下,

    駭客會使用的一種技巧,

    例如:該 table 名稱第一位字元是不是 A ? 第二位字元是不是 B? 等等

    如此只要時間與嘗試的次數夠多,還是可以知道 Table Name、內容等

     

    舉例來說,正常的 query 如下

    http://www.example.com/index.php?Product_id=1

    通常測試沒有 SQL injection 的可能性,會輸入一個非法字元造成該 SQL Query syntax error

    例如:

    http://www.example.com/index.php?Product_id=1′

    接著,透過一些函數的幫忙,可以得知更進一步的訊息,

    例如:欄位名稱、或是 password 的值

    因此,駭客就會輸入下列字串

    1’ AND ASCII(SubString(password,1,1))=97 AND ‘1’=’1

    該 query 的主要用意就是問 SQL Server

    請問 password 第一個字元ASCII 字碼是否 97

    整個SQL Query 執行指令就變成

     

    SELECT username, password FROM Users WHERE Id=’1’ AND
    ASCII(SUBSTRING(username,1,1))=97 AND ‘1’=’1

    如果不是 的話,就試 ASCII = 98 , 99, 100 ……

     

    如何知道分辨SQL Server 回傳 Yes or No 的結果?

    可以輸入下列兩組指令,一組一定會為 Yes. 另一組一定為 No

    因此,看 SQL Server 回傳到 Web Server , HTTP response 的內容可以判斷

    1’ AND ‘1’ = ‘2

    1′ and ‘1’ =’1

     

    另外一種方法為,利用 Sleep ,刻意延遲 SQL server 回應的時間,

    例如:

    若第一個字碼的ASCII = 97,就延遲 10 秒,不然就回傳 False

        1′ AND If(SUBSTRING(username,1,1))=97, sleep(10), ‘false’))  —

    當然,可以輸入兩組,一組一定為 yes,另一組一定為 no,

    看看網站延遲反應的狀況,

    透過這樣的方式即使沒有任行的 http response message,也能夠進行 SQL injection

    1′ AND If( 1 = 1, sleep(10), ‘false’))  —

    1′ AND If( 2 = 1, sleep(10), ‘false’))  —

     

    安全防護建議

    1. Input validation 不僅在 client 端做檢查,更要在 Web Server 端做檢查

    2. SQL prepared statement 或是用 Store procedure 來執行接收到的參數,並免直接執行該 SQL statement

    3. Firewall / IDS

    4. 輸入字元的合法性與長度檢查

    5. SQL error 訊息的設定隱藏,不回傳於 Web Server

    6. Database Least privileged ,使用者權限的設定僅限於授權的查詢

    Tags: , ,

  • MS SQL Query的內部運作

    SQL Query internals

     

     

     

    要解開SQL 效能的謎題,首先先讓我們看看 SQL Server 內部的運作,

    對一個資料庫而言不管是 SQL Server or MSSQL,都會有類似的架構與運作方式,

    主要的負責人

    先讓我們看看圖中幾個主要的負責人

     

    • SNI (Server Network Interface):  主要負責SQL Server 與網路傳送接收資料
    • Relation Engine:這個部分為資料庫的核心,解析所有SQL語法、運算最佳的查詢效率。
    • Storage Engine: 主要決定資料的存取方式,到 memory 存取 or Disk ?
    • Buffer Pool: SQL Server 記憶體中主要存放的快取內容為 data 與 Query plan

     

    Query 的流程

    了解主要幾個負責人之後,接著我們來看整個 SQL Query 執行的過程

     

    1. Client 端送出SQL Query 透過 TDS (Tabular Data Stream)通訊協定送至 SQL Server

    例如:Select name from tb_Name

    2. SQL Server SNI 收到該指令,轉交由 command parser 處理,解析該指令與語法

    3. Command Parser 轉交由 optimizer 計算怎樣的執行方式會比較有效率,

    要透過 Index Scan or Index Seek,如果沒有 Index 要怎樣存取等。

    根據資料的量、query 語法、Join table 、統計資訊等,相對計算出較便宜的cost 執行

     

    Query Plan

    Query Plan

    4. 選出較便宜cost的 query plan 之後,交由 Query Executor 執行

    5. SQL 執行時,Access Methods 會考量如何存取所要的資料,透過記憶體 or 到 Disk

    6/7. Buffer Manager 會協助管理存取記憶體的資料內容,

    如果記憶體已經有之前的資料或是query plan,

    Buffer Manager會就直接由記憶體直接取用,否則就到磁碟存取

     

    8/9/10. 接著,將取到的資料回傳,query Executor => SNI => 再透過SNI 網路傳輸至 client

     

    這就是整個 Query 的流程. 這樣的流程跟資料庫效能有什麼關係呢?

    舉幾個例子說明:

     

    1. Query 是不是經常需要 re-compile ? 因為 recompile 會耗用 CPU 時間

    2. Index 會間接影響 Query plan  與資料存取的方式,查詢的效率等

    3. Select * …這樣的查詢會增加 SQL server 到磁碟存取的次數與 SNI與網路資料量的傳輸

    4.  Memory 的大小,會影響 data cache 與 plan cahce 是不是有效率.

    對於電腦來說至 memory 存取的單位為 naro sec 而至磁碟的存取卻是 mili sec

     

    或是由資源效能瓶頸來看的話,

    CPU Pressure

    有可能是 CPU privileged time 造成  > 因為 high Disk I/O > 因為 SQL server 找不到 memory cache 資料

    也有可能是因為 query 的語法造成需要常常 re-compile

     

    Memory Pressure

    也有可能因為 page in/out 的次數很多 or 記憶體很小or  因為查詢語法造成 or Index 沒有效率等

    Memory Pressure  可以從 Available Bytes, Page out,  Page Life Expectancy 等

    performance counters 中進一步觀察確定原因

     

    結語

    因此,SQL Server 效能瓶頸,比較難頭痛醫頭,腳痛醫腳

    必須要全面性的評估,相對應的關係等

    這也讓資料庫效能調教成為科學與藝術的結合

    因為要做到恰到好處的平衡狀態,

    並且要定期檢視與微調

    才能讓資料庫運作更加順暢

    Tags: , , , ,