Dynamic SQL : 쿼리 문자열과 application 변수를 엮어서 만드는 쿼리를 말한다.
<?php
$sql = "SELECT * FROM Bugs WHERE bug_id = $bug_id";
$stmt = $pdo->query($sql);
application 안에서 이와 같은 작업을 하게 구성하는 것은 어렵지 않은 일이나, SQL 인젝션의 결과로 인한 소프트웨어 결함은 이를 만족시키는데 실패해 생기는 것이다.
SQL 인젝션은 어떤 내용을 SQL 쿼리 문자열에 삽입해 쿼리의 동작을 원래의 의도와 다르게 수정하는 것이다.
고전 적인 방법은 다음과 같이 처리하는 것이다.
SELECT * FROM Bugs WHERE bug_id = 1234; DELETE FROM Bugs
버그 데이터베잉스를 보는 웹 인터페이스를 작성하고 있고, 프로젝트 이름으로 프로젝트 정보를 볼 수 있는 페이지를 만들고 있다고 생각해 보자
<?php
$project_name = $_REQUEST["name"];
$sql = "SELECT * FROM Projects WHERE project_name = '$project_name'";
만약 project_name으로 값이 "O'Hare" 인 경우 쿼리에러가 발생하게 된다.
SELECT * FROM Projects WHERE project_name = 'O'Hare'
사실 문법 에러가 포함된 문장은 실행될 수 없으므로 더 이상의 나쁜 일이 발생할 위험은 낮다.
공격자가 SQL인젝션을 이용해 SQL문을 조작할 수 있게 되면 심각한 위협이 될 수 있다.
<?php
$password = $_REQUEST["password"];
$userid = $_REQUEST["userid"];
$sql = "UPDATE Accounts SET password_hash = SHA2('$password')
WHERE account_id = $userid";
위의 패스워드를 설정하는 구문에 다음과같이 userid가 넘어간다고 생각해 보자.
http://bugs.example.com/setpass?password=xyzzy@userid=123 OR TRUE
다음과 같이 쿼리가 변경됨을 확인할 수 있다.
UPDATE Accounts SET password_hash = SHA2('xyzzy')
WHERE account_id = 123 OR TRUE;
SQL 인젝션은 파싱되기 전의 SQL문을 조작하는 방법으로 동작한다. SQL문이 파싱되기 전에 동적인 부분을 삽입하는 한, SQL 인젝션 위험이 있는 것이다.
SQL 인젝션을 막기 위한 기법들을 알아보도록 하자.
매칭되지 않는 따옴표 문자가 생기는 것을 막는 방법으로 따옴표 문자가 문자열의 마지막이 되지 않도록 모든 따옴표 문자를 escape하는 것이다.
다음과 같이 처리되게 한다.
-- START:standard
SELECT * FROM Projects WHERE project_name = 'O''Hare'
-- END:standard
-- START:backslash
SELECT * FROM Projects WHERE project_name = 'O\'Hare'
-- END:backslash
php 같은 경우 다음과 같이 처리한다.
<?php
$project_name = $pdo->quote($_REQUEST["name"]);
$sql = "SELECT * FROM Projects WHERE project_name = $project_name";
이 기법은 따옴표를 통한 위험은 줄어주지만, 문자열이 아닌 경우에는 잘 동작하지 않는다.
<?php
$password = $pdo->quote($_REQUEST["password"]);
$userid = $pdo->quote($_REQUEST["userid"]);
$sql = "UPDATE Accounts SET password_hash = SHA2($password)
WHERE account_id = $userid";
UPDATE Accounts SET password_hash = SHA2('xyzzy')
WHERE account_id = '123 OR TRUE'
가장 자주 사용하는 방법으로 SQL 문자열에 동적 값을 삽입하는 대신, 쿼리를 만들 때 파라미터가 들어갈 자리를 미리 정의하는 것이다.
// START:correct
<?php
$stmt = $pdo->prepare("SELECT * FROM Projects WHERE project_name = ?");
$params = array($_REQUEST["name"]);
$stmt->execute($params);
// END:correct
?>
이 방법은 매우 강력한 방어 방법이지만, 다음과 같은 문제로 보편적 해결 방법은 될 수 없다.
값의 목록을 하나의 파라미터로 전달 할 수 없다.
// START:list
<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs WHERE bug_id IN ( ? )");
$stmt->execute(array("1234,3456,5678"));
// END:list
?>
테이블 이름은 파라미터로 전달 할 수 없다.
// START:table
<?php
$stmt = $pdo->prepare("SELECT * FROM ? WHERE bug_id = 1234");
$stmt->execute(array("Bugs"));
// END:table
?>
컬럼 이름을 파라미터로 전달 할 수 없다.
// START:column
<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs ORDER BY ?");
$stmt->execute(array("date_reported"));
// END:column
?>
SQL 키워드를 파라미터로 전달 할 수 없다.
// START:keyword
<?php
$stmt = $pdo->prepare("SELECT * FROM Bugs ORDER BY date_reported ?");
$stmt->execute(array("DESC"));
// END:keyword
SQL 인젝션 취약성에 대응하는 매우 강력한 방법으로 알려져 있다. 보통 저장 프로시져는 고정된 SQL문을 포함하며, 프로시져를 정의할 때 파싱된다.
하지만 다음과 같이 사용하면 여전히 문제가 된다.
CREATE PROCEDURE UpdatePassword(input_password VARCHAR(20),
input_userid VARCHAR(20))
BEGIN
SET @sql = CONCAT('UPDATE Accounts
SET password_hash = SHA2(', QUOTE(input_password), ')
WHERE account_id = ', input_userid);
PREPARE stmt FROM @sql;
EXECUTE stmt;
END
이 방법을 사용하면 안전하다고 하는 사람들이 있으나, SQL문을 문자열로 작성하는 것을 허용한다면 어떤 프레임워크이든 그 주장은 잘못된 것이다.
실질적으로, 모든 데이터베이스 어플리케이션은 SQL문을 동적으로 생성한다. 만약, SQL문의 어느 부분이든 문자열을 연결하거나 값을 문자열에 삽입해 만든다면, 항상 잠재적인 문제가 있는 것이다.
SQL 인젝션은 보안 취약점이 있을 수 없다는 것 때문에 다른 안티패턴과는 다르다.
하나의 기술로 SQL 코드를 안전하게 만드는 방법은 없으므로 다음의 방법들을 적절히 사용하도록 한다.
해당 입력에 대해 유효하지 않은 문자는 모두 제거한다.
<?php
$bugid = filter_input(INPUT_GET, "bugid", FILTER_SANITIZE_NUMBER_INT);
$sql = "SELECT * FROM Bugs WHERE bug_id = {$bugid}";
$stmt = $pdo->query($sql);
<?php
$bugid = intval($_GET["bugid"]);
$sql = "SELECT * FROM Bugs WHERE bug_id = {$bugid}";
$stmt = $pdo->query($sql);
다음과 같이 정규 표현식을 사용해, 규칙을 벗어난 내용을 제거하게 할 수도 있다.
<?php
$sortorder = "date_reported"; // default
if (preg_match("/[_[:alnum:]]+/", $_GET["order"], $matches)) {
$sortorder = $matches[1];
}
$sql = "SELECT * FROM Bugs ORDER BY {$sortorder}";
$stmt = $pdo->query($sql);
쿼리의 동작이 간단한 값이면, 쿼리 파라미터를 사용해 다른 SQL 표현과 분리한다.
<?php
$sql = "UPDATE Accounts SET password_hash = SHA2(?) WHERE account_id = ?";
$stmt = $pdo->prepare($sql);
$params = array($_REQUEST["password"], $_REQUEST["userid"]);
$stmt->execute($params);
실행계획등의 문제로 인해 직접 값을 입력해야 하는 경우도 있다. 그와 같은 경우에는 다음과 같이 문자열을 주의깊게 사용해야 한다.
<?php
$quoted_active = $pdo->quote($_REQUEST["active"]);
$sql = "SELECT * FROM Accounts WHERE is_active = {$quoted_active}";
$stmt = $pdo->query($sql);
테이블 이름 및 컬럼 이름을 따로 받아서 쿼리에 적용하게 한다.
// START:init
<?php
$sortorder = $_REQUEST["order"];
$direction = $_REQUEST["dir"];
$sql = "SELECT * FROM Bugs ORDER BY $sortorder $direction";
$stmt = $pdo->query($sql);
// END:init
// START:definemap
$sortorders = array( "status" => "status", "date" => "date_reported" );
$directions = array( "up" => "ASC", "down" => "DESC" );
// END:definemap
// START:defaults
$sortorder = "bug_id";
$direction = "ASC";
// END:defaults
// START:mapinput
if (array_key_exists($_REQUEST["order"], $sortorders)) {
$sortorder = $sortorders[ $_REQUEST["order"] ];
}
if (array_key_exists($_REQUEST["dir"], $directions)) {
$direction = $directions[ $_REQUEST["dir"] ];
}
// END:mapinput
// START:interpolate
$sql = "SELECT * FROM Bugs ORDER BY {$sortorder} {$direction}";
$stmt = $pdo->query($sql);
// END:interpolate
위와 같이 사용하는 경우 다음과 같은 장점이 있다.
결함을 찾아내는 가장 좋은 방법은 다른 사람과 함께 코드를 검토하는 것이다.
검사할 때에는 다음의 가이드 라인을 따르기 바란다.