[PHP] How To Prevent SQL injection attack via secure code

php언어를 통해 SQL injection 공격을 막기 위해 안전한 코드를 짜야한다.

 

우선 mysqli 함수를 통해 DB에 접근하기 위한 코드는 아래와 같다.

<?php
    $mysqli = new mysqli($host, $username, $userpassword, $dbname );
    
    if($mysqli->connect_error){
        exit("DB connect error");
    }
?>

 

위 코드를 db.php 파일이라고 정의하고, 이 파일을 include 하여 사용한다.

mysqli의 PDO는 prepare라는 함수를 가지고 있다.

이것을 이용하여 안전하게 DB에 접근하여 값을 가져오거나 등등의 작업을 할 수 있다.

 

---

INSERT

아래 예제는 user라는 테이블에 id와 pw를 Insert 하는 과정을 나타낸 것이다.

<?php
    include "./db.php";
    
    if($insert = $mysqli->prepare("INSERT INTO user (id,pw) VALUES (?, ?)")){
        $insert->bind_param("ss", $_POST['userid'], $_POST['userpw']);
        $insert->execute();
        $insert->close();
    }
    else{
        exit($mysqli->error);
    }

?>

우선 $mysqli 변수를 쓰기 위해 db.php 파일을 include 하고 prepare 함수를 사용한다.

만약 prepare 함수에서 query에 에러 발생 시 $mysqli->error를 통해 에러를 출력한다. (보안을 위해서라면 이렇게 하지 말자!!)

 

 

prepare 함수를 보면 INSERT INTO user (id, user) VALUES (?, ?) 에서 물음표가 보일 것이다.

물음표는 값이 들어갈 자리이다.

 

물음표 자리는 bind_param() 함수를 통해 사용자가 입력한 값으로 치환이 된다.

bind_param() 함수의 첫번째 인자는 4개의 타입이 정의 되어 있다.

i - Integer
d - Double
s - String
b - Blob

만약 두 인자를 string 타입으로 넣고 싶으면, 아래 처럼 ss 인자를 주면 된다.

bind_param("ss", $str1, $str2)

 

만약 첫번째 값을 string으로 두번째 값은 int 타입으로 query를 구성하고 싶다면, 아래처럼 si 인자를 주면 된다.

bind_param("si", $str1, $int1)

이제 이 query를 실행하기 위해 bind_execute() 함수를 사용한다.

 

메모리 낭비를 줄이기 위해 bind_close()를 꼭 해주자.

 

 

 

---

DELETE and UPDATE

DELETE랑 UPDATE도 INSERT를 했던 방법과 똑같이 하면 된다.

 

DELETE

<?php
    include "./db.php";
    
    if($delete = $mysqli->prepare("DELETE FROM board WHERE idx=?")){
        $delete->bind_param("i", $_POST['idx']);
        $delete->execute();
        $delete->close();
    }
    else{
        exit($mysqli->error);
    }

?>

UPDATE

<?php
    include "./db.php";
    
    if($update = $mysqli->prepare("UPDATE board SET title=?, content=? WHERE idx=?")){
        $update->bind_param("ssi", $_POST['title'], $_POST['content'], $_POST['idx']);
        $update->execute();
        $update->close();
    }
    else{
        exit($mysqli->error);
    }

?>

 

 

 

---

query 질의 중 에러 처리

만약 bind_execute() 를 했을때 에러가 날 경우 아래처럼 에러를 처리할 수 있다.

bind_execute()의 Return 값은, 성공하면 true, 실패하면 false 값을 반환한다.

덕분에, 개발자가 의도치 못한 부분을 캐치 할 수 있게 되었다.

<?php
    include "./db.php";
    
    if($update = $mysqli->prepare("UPDATE board SET title=?, content=? WHERE idx=?")){
        $update->bind_param("ssi", $_POST['title'], $_POST['content'], $_POST['idx']);
        
        $Err = $update->execute();	// Here change
        
        if($Err === false){
            exit($mysqli->error);
        }
        else{
            echo "success";   
        }
        $update->close();
    }
    else{
        exit($mysqli->error);
    }

?>

 

---

SELECT

이제는 안전하게 DB의 결과 값을 가져와 보자.

값을 저장하는 방법은 두 가지가 있다. 

 

get_result()

get_result() 함수는 여러 row 값들을 저장할때 사용된다. 또한 SELECT로 조회 시, 많은 컬럼을 가져와야 결과를 출력할때 유용하다.

예를 들면 게시판의 목록을 가져 올때 사용하는 것이 가장 적절하다.

<?php
    include "./db.php";
    
    if($select = $mysqli->prepare("SELECT * FROM board WHERE category=?")){
        $select->bind_param("s", $_POST['category']);
        $Err = $select->execute();
        
        // Error check
        if($Err === false){
            echo $mysqli->error;
        }
        else{
            // No result
            if($select->num_rows === 0){
                echo "no exist category";
            }
            else{
                // Print result
                $result = $select->get_result();
                while($row = $result->fetch_assoc()){
                    echo $row['idx'];
                    echo $row['title'];
                    echo $row['content'];
                }
            }
        }
        $select->close();
    }
    else{
        exit($mysqli->error);
    }

?>

위 query는 게시판에서 사용자가 특정 category를 보려고 할때 이다.

만약 공격자가 category에 이상한 값을 넣고 조회할 경우

$select->num_rows

를 통해 결과의 row 길이가 0 이면 (결과가 없으면) 에러 페이지를 보여준다.

 

만약 정상적으로 요청을 보낼 경우,

$select->get_result()

를 이용해 결과를 저장하고, 

$row = $result->fetch_assoc()

를 통해 각각의 row 결과를 while 문으로 돌려 결과를 출력한다.

 

 

 

 

bind_result()

bind_result() 함수는 결과 값 중 맨 첫번째 row 만 가져온다. 결과를 변수로 전환하여 쓸 수도 있다. SELECT 조회 시, 적은 컬럼을 조회해 결과를 사용할때 유용하다.

예를 들면 로그인 시, bind_result() 함수를 쓰는 것이 가장 적절하다.

 

<?php
    include "./db.php";
    
    if($select = $mysqli->prepare("SELECT password FROM user WHERE id=?")){
        $select->bind_param("s", $_POST['userid']);
        $Err = $select->execute();
        
        // Error check
        if($Err === false){
            echo $mysqli->error;
        }
        else{
            // No result
            if($select->num_rows === 0){
                echo "no exist user";
            }
            else{
                // Store result into variable
                $select->store_result();
                $select->bind_result($password);
                
                if(password_verify($_POST['userpw']), $password){
                    echo "correct";
                }
                else{
                    echo "wrong password";
                }
            }
        }
        $select->close();
    }
    else{
        exit($mysqli->error);
    }
?>

 

SELECT 결과 중 맨 첫번째의 row만 사용하기 위해 

$select->store_result();
$select->bind_result($password);

를 사용하여 $password 변수에 SELECT 결과 값을 저장한다.

이를 통해, $password 변수를 자유롭게 사용이 가능하다.

 

 

 

 

---

$query->close()

위 코드에서 모든 작업이 끝나면 무조건 close()를 해주어야 불필요한 메모리 낭비를 막을 수 있다.

 

또 다른 이유는 필자가 개발을 하다가 알게 되었는데, close() 하지 않고 중첩으로 prepare를 사용시 두번째 query는 동작을 하지 않고 에러가 발생한다는 것을 알게 되었다.

 

문제의 코드를 간략히 표현하면 아래와 같다.

<?php
    include "./db.php";
    
    if($select = $mysqli->prepare("SELECT nickname FROM user WHERE id=?")){
        $select->bind_param("s", $_POST['userid']);
        $select->execute();
        $select->stroe_result();
        $select->bind_result($nickname);
        
        if($select2 = $mysqli->prepare("SELECT title FROM board WHERE nickname=?")){
            // 유저가 쓴 글을 조회
        }
        else{
            // 에러 발생!!
            echo $mysqli->error;
        }
        
        $select->close();
    }
    else{
        exit($mysqli->error);
    }
?>

 

위 코드를 보면 $select 변수로 user 테이블에 조회를 하고 있다.

그 다음 close를 하지 않고 $select2 변수로 board 테이블을 조회 하려고 한다.

이때 mysqli 에서는 에러가 출력이 된다.

 

이를 막기 위해서는 아래와 같이 close를 한 후 중첩으로 query를 조회 하길 바란다.

 

<?php
    include "./db.php";
    
    if($select = $mysqli->prepare("SELECT nickname FROM user WHERE id=?")){
        $select->bind_param("s", $_POST['userid']);
        $select->execute();
        $select->stroe_result();
        $select->bind_result($nickname);
        
        // 중요!!
        $select->close();
        
        if($select2 = $mysqli->prepare("SELECT title FROM board WHERE nickname=?")){
            // 유저가 쓴 글을 조회
            // ...
            // ...
            // 중요!!
            $select2->close();
        }
        else{
            echo $mysqli->error;
        }
    }
    else{
        exit($mysqli->error);
    }
?>

 

 

 

 

출처:

https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection