Sql Injection을 막아보자

햐얀하늘·2024년 5월 2일

Sql Injection에 대해 쓴 계기

회사에서 sql injection 때문에 보안 위험이 있다고 일이 내려왔다. sql injection에 대해 들어는 보았지만 실제로 어떻게 동작하는지는 잘 알지 못했다. 그래서 sql injection에 대해 공부하고 이를 해결하기 위해 다양한 방법을 찾아보고 어째서 회사코드가 보안에 취약했는지 분석했다. 분석했더니 거의 수십페이지가 sql injection에 위험했고 모든 페이지를 결국 sql injection 방어를 위해 코드를 다 뜯어 고쳐야했다. 그러면서 안되는 것들도 많았고 어려웠다. 그래서 한번 정리해보고자 쓴다.

php를 기준으로 작성하였습니다.

SQL Injection이란???

입력 데이터를 통해 악의적인 SQL 쿼리를 실행하는 공격이라고 보면된다. sql 쿼리문을 통해 데이터를 자기가 원하는 대로 커스터마이징해서 원하는 데이터를 출력하기도 하고 데이터를 변형시킬 수 있다.

예시 코드를 한번 보자. 아래의 코드는 필터링을 통해 name에 asdf가 들어가는 값들을 출력하기 위한 input창에 넣은 코드다.

'asdf' or 1=1 --

위의 코드를 분석하면 asdf가 있거나 또는 1=1인 것을 가져오고 뒤에 다른 쿼리는 '--'를 통해 주석처리한다. 이러면 모든 데이터를 가져올 수 있게 된다. 위의 코드는 where절을 무력화하는 가장 일반적인 sql injection 코드로 보면된다.
이것 말고도 sql version을 출력한다거나 하는 등 다양한 쿼리를 날릴 수 있다.

왜 SQL injection에 취약했는가??

php orm을 사용하지 않고 직접 쿼리문을 날려서 db를 조회하다보니 sql문을 sprintf를 이용해서 사용자 입력값을 그대로 이용해 쿼리를 날렸다. where절을 만들 때 모든 input값을 생으로 때려박아 쿼리문을 날렸던 것이다.

예를 들어

$where = (1)
if ($where) {
	$where .= sprintf(and users = %s, $input);
}
$query = sprintf(select * from member where (1) and %s, $where);

이런 방식으로 $input 입력값을 그대로 바인딩하다보니 sql injection에 취약했다.

where절에 (1)은 왜 있을까? 처음에 진짜 뭔가 했는데 where절을 추가로 계속 달기 위해서 무조건 참인 값을 넣은 것이다.
즉 쿼리를 일일히 제어하기 귀찮아서 달아 놓은 것이다.

SQL Injection 막는 방법

대표적인 2가지 방법이 존재한다.

입력값 유효성검사 즉 특수문자검사하기 => '' , "", -- 같은 것이 나오면 4#( 등 특수하게 변형시켜서 해당 특수문자를 변형하는 방법이다.

이 방법은 가장 단순하지만 몇가지 단점이 있었다.

예를 들어 보안 패턴을 저장하는 코드가 있는데 이때 validation 체크를 통해 특수문자를 변형시켰더니 db에 들어갈 때도 특수문자가 변형되어 쿼리가 날라가다 보니 제대로 저장되지 않아 검색 시에도 문제가 많았다.

디코딩과 인코딩을 계속 해야되다 보니 쓸데없는 동작이 많이 생겨 효율성이 많이 떨어졌다.

또한 모든 특수문자를 다 검열하려다 보니 너무 종류가 많기도 많아서 최후의 수단으로 남겨두기로 했다.

동적 바인딩으로 입력값을 그대로 사용하는 것이 아니라 동적으로 매핑 시켜주는 것이다. 현재 대부분의 프레임 워크에서는 orm을 쓰기 때문에 동적 바인딩을 기본적으로 해줘 sql injection을 예방할 수 있다.

예를 들어

// MySQLi 객체 생성 및 연결
$conn = new mysqli($servername, $username, $password, $dbname);


// 사용자 입력
$desired_username = 'myUsername';

// SQL 쿼리 준비
$stmt = $conn->prepare("SELECT * FROM users WHERE username = :username");

// 매개 변수에 값 바인딩
$stmt->bind_value(:username, $desired_username);

// 쿼리 실행
$stmt->execute();

// 결과 가져오기
$result = $stmt->get_result();

이런식으로 bindValue와 bindParam을 이용해서 동적 바인딩을 통해 sql injection을 예방할 수 있다.

Warning!!!!

위의 $$conn->prepare($ $query) 에서 $query는 반드시 sql문에 올바른 것이어야한다!!!
=> 만약 sql문 형식이 아니라면 binding 되지 않을 것이다.

prepare한 문장은 로그를 찍을 수 없다. 이전에 bindValue는 제대로 다 되었는데 쿼리가 제대로 안나가서 무엇이 문제인지 쿼리가 문제인건지 stmt를 찍어보려햇는데 찍을 수 없엇다. 그 이유는 따로 객체를 만들어서 보관해 볼 수 없다.

Hint 및 고생한 것

sql문을 만들고 execute를 실행했으나 db조회가 되지 않았다. $stmt 값을 찍어서 쿼리문이 제대로 만들어지는지 보려고 했으나 불가능했다. 그래서 일일히 bindValue 시의 key값과 value값을 찍고 그 이후에도 바인딩이 안되는 건지 계속 알아보려했으나 binding에는 문제가 없었다. 그래서 어디서 문제가 생겼는지 찾기 힘들었다. $stmt->execute() 시에도 문제가 없는데 $result값을 보면 텅텅 비어있다.

무엇이 문제일까??

sql query 문제
db 문제
binding문제

4가지로 나누었고 각각을 다 고려해서 테스트해보았다.

sql문의 문제 => 기존의 하드코딩된 쿼리를 날려보았을 때 제대로 db조회가 되었다. 그리고 sql문과 where문을 각각 출력해서 합쳐서 db쿼리를 생으로 날렸을 때도 제대로 값이 나왔다. 쿼리문에 문제가 없다고 판단
db 문제 => db에 값이 없거나 sqlcipher 등으로 db조회가 제한되어있다? => 1번과 마찬가지로 수행했을 때 db조회가 잘됨. db문제 없다고 판단.
binding문제 => 바인딩시의 key,value값을 일일히 찍어보고 제대로 execute까지는 문제가 없는지 확인했다.
쿼리문은 나가고 특정 상황에서는 db가 조회되고 특정 상황에서는 db조회가 안되는 경우가 발생했다.
즉 binding시 문제가 발생했다.

그 원인을 찾아보니

동적 바인딩 시킬 때 어떤 input값이 단순 나열로 들어왔을 때 문제가 발생했다.

원래 코드에서는 1,2,3,4,5로 들어오면

$input = array(1, 2, 3, 4, 5); // 예시 입력 값

// 입력이 존재하는 경우 memberId를 포함하는 WHERE 절 생성
$where = '';
if ($input) {
    $where = ' WHERE memberId IN (' . implode(',', $input) . ')';
}

// 동적으로 생성된 WHERE 절을 포함하여 SQL 쿼리 실행
$sql = 'SELECT * FROM member' . $where;

=> $sql = select * from member where memberId in (1,2,3,4,5);

이런식으로 DB쿼리를 만들었다. 당연히 db쿼리는 예쁘게 잘 나오니 전혀 문제가 없다고 판단한 것이 문제였다. 각각의 1,2,3,4,5의 값이 제대로 동적 바인딩 하기 위해서는 배열 각각을 동적 바인딩 시켜야한다.

$input = array(1, 2, 3, 4, 5); // 예시 입력 값

$params = [];
$where = '';

if ($input) {
    $where = ' WHERE memberId IN (';
    foreach ($input as $index => $value) {
        $paramName = sprintf(":memberId_%d", $index); // 매개변수 이름 생성
        $params[] = $paramName;
        $args[$paramName] = $value; // 매개변수 이름과 값을 $args에 추가
    }
    $where .= implode(', ', $params);
    $where .= ')';
}

$sql = 'SELECT * FROM member' . $where;

위의 방식과 똑같이 sql문을 동적바인딩 시켜서 만드는 것이다.
$args와 $sql을 한번 살펴보면

Array
(
    [:memberId_0] => 1
    [:memberId_1] => 2
    [:memberId_2] => 3
    [:memberId_3] => 4
    [:memberId_4] => 5
)

SELECT * FROM member WHERE memberId IN (:memberId_0, :memberId_1, :memberId_2, :memberId_3, :memberId_4)

이렇게 나오는데 이렇게 $input의 모든 값을 각각 동적바인딩 시켜서 값을 출력해야한다. 뭉텅이로 binding시키는 것이 아니다!!!!

이렇게 하면 제대로 동적바인딩 되어서 값이 나온다.

현재 모든 페이지를 bindValue를 이용해 동적바인딩 시키고 있고 where절을 만드는 구문을 다 수정하고 있다.
양이 많다보니 cypress를 이용해 테스트 코드를 작성하고 있다. cypress를 선택한 이유는 현재 폐쇄망에서 일을 하고 있기 때문에 단순히 zip파일을 이용해 환경에 설치해서 사용할 수 있기 때문에 선택했다. javascript만을 이용하기 때문에 php 백엔드 테스트는 어렵지만 현재 binding이 제대로 되는지 단순히 확인하고 빠르게 테스트 코드를 작성할 수 있기 때문에 cypress를 이용했다.