웹 시큐어 코딩 : SQL Injection

약 2개월 반에 걸쳐서 로그인, 게시판, 마이페이지 등 기본적인 기능이 들어간 웹 서비스를 구축해보았다.

문제는, 이 웹 사이트는 해킹 취약점에 대한 대응이 전혀 안 되어 있다는 것이다.

그래서 이론으로 공부했던 웹 해킹 취약점들에 대한 방어, 웹 시큐어 코딩을 해보려고 한다.

 

첫 번째 방어할 취약점은 SQL Injection!

SQL injection의 근본적인 대응방안은 바로 prepared statement를 사용하는 것이다.

 

내가 만든 웹 페이지에서 SQL Injection이 일어날 수 있는 곳은 크게는 로그인, 회원가입, 게시판 및 주소 검색 등이지만, 실제로는 대부분의 php 파일에서 SQL로 DB와 연결하여 무언가 작업을 수행하고 있기 때문에, 사실상 파일에 Prepared Statement를 적용해주어야 했다.

 

대표적으로 몇 개의 페이지를 살펴보고, Prepared Statement를 적용하는 방식을 알아보자.

 

① Prepared Statement 사용 방법

[login.php]

<?php
    if(isset($_POST['id']) && isset($_POST['pw'])){
        $conn = mysqli_connect('localhost', 'ID', 'PW', 'DBname');

        $pre_state = $conn->prepare("SELECT * FROM login where login_id = ? and login_pw = ?");
        $pre_state->bind_param("ss", $username, $password);
        
        $username = $_POST['id'];
        $password = $_POST['pw'];

        $result = $pre_state->execute();
        
        if($result){
            session_start();
            $_SESSION['id'] = $username;
            header('Location: index.php');
        } else{
            echo "<script>alert('등록되지 않은 사용자입니다.')</script>";
            echo "<script>window.location.href='login.html';</script>";
        }
        $pre_state->close();
        mysqli_close($conn);
    } else{
        echo "<script>alert('아이디와 비밀번호를 입력해주세요.')</script>";
        echo "<script>window.location.href='login.html';</script>";
    }
?>

prepared statement는 위와 같이 기존에 변수 대신 (?, ?)를 사용하고 사용자의 입력 값을 나중에 바인딩하는 방식을 사용한다.

 

위와 같이 처리해주고, 테스트를 해보았다.

어?? 그런데 SQL Injection이 먹혀서 ID : admin' or 1 = 1 # 로 로그인이 됐다.

뭐가 잘못됐나 해서, 파라미터 바인딩 위치도 바꿔보고, 함수도 확인해봤는데 별다른 에러 문구 없이 SQL Injection이 통과됐다.

 

한참 삽질을 하다가 $result를 출력해보니 아예 DB에 없는 이상한 아이디 넣어도 1이라고 뜨는 것을 확인할 수 있었다.

기존의 논리로 따지면, SQL 구문을 실행했을 때 선택되는 행이 있다면 회원이라는 의미이므로 로그인이 성공하는 것인데, 내가 짠 코드는 실행만 하고 통과시키는 것과 같은 것이었다.

따라서 $result = $pre_state->get_result()->num_rows가 0 이상인지 확인해주어야 한다.

 

[최종 login.php]

<?php
    if(isset($_POST['id']) && isset($_POST['pw'])){
        $conn = new mysqli($Server, $ID, $PW, $DBname);
        if($_POST['id'] != NULL & $_POST['pw'] != NULL){
            $sql = "SELECT * FROM login where login_id = ? and login_pw = ?";

            $stmt = $conn->prepare($sql);
            $stmt->bind_param("ss", $username, $password);

            $username = $_POST['id'];
            $password = $_POST['pw'];
            $stmt->execute();

            $result = $stmt->get_result()->num_rows;

            if($result){
                session_start();
                $_SESSION['id'] = $username;
                header('Location: index.php');
            } else{
                echo "<script>alert('등록되지 않은 사용자입니다.')</script>";
                echo "<script>window.location.href='login.html';</script>";
            }
            $stmt->close();
        } else{
            echo "<script>alert('아이디와 비밀번호를 입력해주세요.')</script>";
            echo "<script>window.location.href='login.html';</script>";
        }
    }
    mysqli_close($conn);
?>

 

 

② Like 절을 포함한 SQL 구문

Prepared Statement를 사용할 때 like 절은 어떻게 표현할까?

[address.php]

<?php
    function print_result(){
        $conn = new mysqli($Server, $ID, $PW, $DBname);
        if(isset($_POST['addr']) && $_POST['addr'] != NULL){
            $sql = "SELECT * FROM address where road_name like ?";

            $pre_state = $conn->prepare($sql);
            $pre_state->bind_param("s", $addrs);

            $addrs = "%".$_POST['addr']."%";
            $pre_state->execute();

            $result = $pre_state->get_result();
            
            if($result){
                while($row = $result->fetch_array()){
                    echo "<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>";
                }
            } else{
                echo "<script>alert('주소 없음.')</script>";
            }
            $pre_state->close();
        } else{
            echo "<tr><td>검색해주세요.</td><td>0</td></tr>";
        }
        mysqli_close($conn);
    }
?>

like 절에는 %가 사용되는데, % 사이에 ?를 넣으니 인식이 안되었다. 그래서 그냥 ?로 넣고 바인딩하는 변수에 %를 문자열로 붙여주었다.

 

 

③ mysqli_real_escape_string과 Prepared Statement의 차이

게시판에서 글을 읽을 때, 해당 글의 id를 넘겨주고 DB에서 넘겨받은 id 값을 가진 데이터를 찾아오는 과정을 거쳤다.

그런데, id는 숫자이기 때문에 SQL 쿼리에서 따옴표로 둘러싸여 있지 않아서 mysqli_real_escape_string을 해도 소용이 없다.

[기존 read.php]

if(isset($_GET['id'])){
    $id = mysqli_real_escape_string($conn, $_GET['id']);
    $sql = "SELECT * FROM board where id = {$id}";
    $result = mysqli_query($conn, $sql);

    $row = mysqli_fetch_array($result);
    $username = $row['username'];
    $title = $row['title'];
    $content = $row['content'];
    $likes_count = $row['likes_count'];
    $file_name = $row['file'];
}

id에 따옴표가 없기 때문에 공격자는 '로 구문을 닫아주지 않아도 된다. 따라서 SQL injection이 가능하다.

 

[최종 read.php]

if(isset($_GET['id'])){
    $sql = "SELECT * FROM board where id = ?";

    $pre_state = $conn->prepare($sql);
    $pre_state->bind_param("s", $id);

    $id = $_GET['id'];
    $pre_state->execute();

    $result = $pre_state->get_result();

    $row = $result->fetch_assoc();
    $username = $row['username'];
    $title = $row['title'];
    $content = $row['content'];
    $likes_count = $row['likes_count'];
    $file_name = $row['file'];
}

위와 같이 숫자 값을 입력받는 경우에는 반드시 Preapared Statement를 사용해야 하고, 데이터를 받아오는 건 위와 같이 $pre_state->get_result()->fetch_assoc()를 사용하면 된다!

 

 

④ 화이트리스트 기반 필터링

Preapared Statement가 적용되지 않는 Column, Table 이름이나 order by 정렬 부분에는 화이트리스트 기반으로 필터링을 해주어야 한다.

[board.php]

<?php
    function board(){
        include 'conn.php';
        $conn = mysqli_connect($Server, $ID, $PW, $DBname);
        if(isset($_POST['board_result'])){
            $find = mysqli_real_escape_string($conn, $_POST['board_result']);
            $column = mysqli_real_escape_string($conn, $_POST['option_val']);
            $start_date = mysqli_real_escape_string($conn, $_POST['date_from']);
            $end_date = mysqli_real_escape_string($conn, $_POST['date_to']);
           
            $option_arr = array("username", "title", "content");
            if(in_array($column, $option_arr)){
                if($start_date && $end_date){
                    $sql = "SELECT * FROM board where $column like '%$find%' and date between '$start_date' and '$end_date';";
                }
                else $sql = "SELECT * FROM board where $column like '%$find%';";
                $result = mysqli_query($conn, $sql);
    
                if(mysqli_num_rows($result) > 0){
                    while($row = mysqli_fetch_array($result)){
                        echo "<tr><td>".$row['username']."</td><td><a href = \"read.php/?id=".$row['id']."\"&view=1>".$row['title']."</a></td><td>".$row['views']."</td><td>".$row['date']."</td></tr>";
                    }
                } 
            }else{
                echo "<script>alert('존재하지 않습니다.')</script>";
            }
        }
        mysqli_close($conn);
    }
?>

 

3번에서 설명한 것처럼, column명에는 Prepared Statement를 사용할 수 없는 데다가, 따옴표를 쓰지 않는 경우에는 mysqli_real_escape_string를 사용해도 소용이 없다. 그래서 화이트리스트 기반으로 column에 들어갈 후보를 필터링해주었다.

사용한 방식은 column으로 가능한 username, title, content를 array로 만들어서, column값이 그중에 하나면 OK, 그 안에 없으면 존재하지 않는다고 처리해주었다. python에서 A in array가 php에서는 in_array이다.

 

오늘은 첫 번째 웹 시큐어 코딩으로 SQL Injection에 대응하여 Prepared Statement를 어떻게 사용하는지, like 절을 사용할 때나 데이터를 가져와야 할 때는 또 어떻게 적용하는지, mysqli_real_escape_string과의 차이점은 무엇인지, 마지막으로 화이트리스트 기반 필터링을 어떻게 하는지에 대해 알아보았다.