간혹 SQLalchemy와 같은 ORM 활용 없이 네이티브 쿼리를 작성하기 위해 sqlite3 모듈을 사용할 때가 있다.
이런 결정에 가장 크게 영향을 받는 커넥션 관리, object mapping에 대한 고려는 충분히 했겠지만, SQL Injection은 트래픽과 상관없이 문제가 되므로 이 모듈에서 어떤 기능이 지원되는지 알아야할 필요가 있다.
따라서 sqlite3 모듈에서 제공하는 플레이스 홀더 기능과 해당 기능이 SQL Injection을 어떻게 방어하는지에 대해 알아보자.
1. SQL Injection 공격법
사용자에게서 input을 받아서 검색 결과를 반환하는 아래와 같은 함수를 예시로 들어보자.
def find_by_id(input) -> dict:
conn = sqlite3.connect('my_sqlite_path')
cur = conn.cursor()
cur.executescript(
f'SELECT * FROM "my_table" WHERE "id" = {input}'
)
# ...
이때 input으로 '2 OR TRUE' 이 들어오면 아래와 같은 쿼리가 전송된다. 그럼 이 악성 사용자는 모든 사용자에 대한 정보를 얻을 수 있게된다.
SELECT * FROM "my_table" WHERE "id" = 2 OR TRUE
만약 유저 권한관리가 제대로 안된 상황이라면 더욱 심각한 공격도 성공할 수 있다. '2; DELETE FROM "my_table"' 이라는 값이 들어온 경우 쿼리는 아래와 같아진다.
SELECT * FROM "my_table" WHERE "id" = 2; DELETE FROM "my_table"그럼 my_table의 데이터는 통채로 사라지고, 서버는 심각한 장애를 겪게될 것이다.
이를 방어하기 위한 방법은 인풋을 쿼리의 일부로 넣지 않고, 데이터 자체로 인식하게 하는 방법이므로, 아래처럼 해야겠다고 생각할수도 있다.
# ''로 감쌌으니 안전하겠지?
cur.execute(f"SELECT * FROM my_table WHERE id = '{input}'")
# --를 사용한 SQL Injection
input = ' OR TRUE; DELETE FROM my_table--
-> SELECT * FROM my_table WHERE id = '' OR TRUE; DELETE FROM my_table; 실행됨하지만 SQL의 주석('--')을 사용해서 이런 조잡한 방어는 손쉽게 무력화할 수 있다.
2. Query Placeholder
이걸 방지하기 위한 옵션이 placeholder이다. Qmark style, Named style 두가지 방식을 지원한다.
문자열 포맷팅이랑 별반 다른 점이 없어보이지만, 쿼리 생성시 직접 쿼리를 DB에 넘기는 대신 DB-API에 변수를 전달해서 parameter substitution 방식으로 사용한다. 이제 파라미터들은 쿼리 그 자체가 아닌 데이터로 인식된다.
1) Qmark Style (? 사용)
? 자리에 순서대로 값을 넣어 할당한다.
params = (2,)
cur.execute("SELECT * FROM my_table WHERE id = ?", params)
2) Named Style (: 사용)
:이름 형태로 쿼리에 변수를 전달한다.
data = {"id": 2}
cur.execute("SELECT * FROM my_table WHERE id = :id", data)
sqlite3.connect()로 생성한 connection에 set_trace_callback(lambda sql : print(sql))을 등록하고 쿼리를 출력해보면
# 문자열 포맷팅 사용 결과 - 전체 데이터 출력됨
SQL executed: select * from kmooc_instructor where id='' OR TRUE--'
# Parameter substitude 사용 결과 - 출력없음
SQL executed: select * from kmooc_instructor where id=''' OR TRUE--'안전하게 SQL 인젝션을 방어한 것을 볼 수 있다.