psycopg2 파라미터 바인딩과 SQL Injection

프로젝트에서 사용된 코드가 왜 SQL Injection에 열려 있는지 짚고, 파라미터 바인딩으로 바꾸는 두 가지 방식과 바꾸면서 헷갈렸던 정수형·null 처리에 대해 정리하였다.

따옴표 하나로 조건이 늘어난다

SQL Injection은 입력값에 SQL 조각을 섞어 넣어 개발자가 의도하지 않은 쿼리를 데이터베이스가 실행하게 만드는 공격이다.

어떤 POST API가 정상적으로는 이런 인자를 받는다고 가정.

{ testKey : "testValue" }

이 값이 쿼리에 들어가면 이렇게 된다.

select * from testTable where key = 'testValue'

악의적인 사용자는 값 안에 따옴표를 넣어 보낸다.

{ testKey : "testValue' and '해킹'='해킹" }

같은 코드에 넣으면 쿼리가 이렇게 바뀐다.

select * from testTable where key = 'testValue' and '해킹'='해킹'

입력값의 첫 따옴표가 문자열을 닫고, 그 뒤는 SQL 문법으로 읽힌다. 값으로 받은 것이 쿼리의 구조가 되는 순간이 Injection이다. 문자열을 이어 붙이는 한 입력값과 SQL 문법 사이에 경계가 없다.

대응 전: 입력값을 쿼리에 이어 붙이던 코드

실무에서 쓰던 코드는 이런 모양이었다. psycopg2로 PostgreSQL에 붙고, fetch 함수가 완성된 쿼리 문자열 하나를 받아 실행한다.

import psycopg2
from psycopg2.extras import RealDictCursor

# DB 연결 준비
conn = psycopg2.connect("dbname={} user={} host={} password={}".format(db_name, db_user, db_host, db_pass))

# DB 호출함수 선언
def fetch(conn, query):
    result = []
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    cursor.execute(query) # SQL 실행

    # DB조회 결과값 담기
    raw = cursor.fetchall()
    for line in raw:
        result.append(line)

    return result

# SQL 쿼리 선언
# ❌ 입력값(pjt_no, item_type)이 그대로 쿼리 문자열이 된다
query = "select pjt_no, item_type from testTable "
query += "where pjt_no = '" + pjt_no + "' "
query += "and item_type = '" + item_type + "'"

# DB 호출함수 실행
result = fetch(conn=conn, query=query)

pjt_no나 item_type에 앞의 예시 같은 값이 들어오면 그대로 쿼리가 된다.

쿼리와 값을 따로 넘긴다

고치는 방법은 쿼리 문자열에는 자리표시만 두고, 값은 cursor.execute()의 두 번째 인자로 따로 넘기는 것이다. psycopg2가 값을 SQL 리터럴로 바꿔 끼울 때 따옴표를 이스케이프하므로 입력값에 따옴표가 섞여 있어도 문자열 밖으로 나가지 못한다. 따옴표를 직접 치환하는 식으로 막을 수도 있겠지만 그 일은 드라이버가 이미 제대로 하고 있다.

fetch 함수는 값을 받을 인자 하나만 늘리면 된다.

# DB 호출함수 선언
def fetch(conn, query, sql_arg):
    result = []
    cursor = conn.cursor(cursor_factory=RealDictCursor)
    cursor.execute(query, sql_arg) # ✓ 쿼리와 값을 분리해서 넘긴다

    # DB조회 결과값 담기
    raw = cursor.fetchall()
    for line in raw:
        result.append(line)

    return result

값을 넘기는 방식은 두 가지다.

딕셔너리로 이름 붙여 넘기기

자리표시를 %(이름)s로 쓰고 같은 key를 가진 딕셔너리를 넘긴다.

# SQL 쿼리 선언
query = "select pjt_no, item_type from testTable "
query += "where pjt_no = %(pjt_no)s "
query += "and item_type = %(item_type)s"
sql_arg = {
  'pjt_no' : pjt_no,
  'item_type' : item_type,
}

# DB 호출함수 실행
result = fetch(conn=conn, query=query, sql_arg=sql_arg)

튜플로 순서대로 넘기기

자리표시를 %s로 쓰고 순서에 맞춘 튜플을 넘긴다.

# SQL 쿼리 선언
query = "select pjt_no, item_type from testTable "
query += "where pjt_no = %s "
query += "and item_type = %s"
sql_arg = (pjt_no, item_type)

# DB 호출함수 실행
result = fetch(conn=conn, query=query, sql_arg=sql_arg)

튜플은 짧지만 자리표시가 늘어나면 순서를 맞추기 어렵다. 같은 값을 쿼리에서 두 번 쓰거나 조건이 많으면 딕셔너리 쪽이 읽기 쉽다.

%s에 따옴표를 붙이지 않는다는 점도 기억해 둔다. '%s'로 쓰면 psycopg2가 넣은 따옴표와 겹친다. 문자열을 이어 붙일 때는 줄 끝 공백도 챙겨야 한다. "... = %(pjt_no)s" 뒤에 공백 없이 "and ..."를 붙이면 %(pjt_no)sand가 된다.

정수 컬럼에도 %s를 쓴다

처음에 헷갈린 부분이다. 파이썬 문자열 포맷팅에서 %s는 문자열, %d는 정수다. 그럼 DB 컬럼이 int일 때는 %(변수명)d나 %d로 받아야 할까.

확인해 보니 그대로 %s를 쓰면 됐다. 당시에는 인자를 문자열로 형변환해서 넘기는 방식으로 정리했다.

test = 1 # 인자값

query = 'select * from testTable where key = %(str_test)s'
query_paramDict = { 'str_test' : str(test) }

cursor.execute(query, query_paramDict)

이 코드도 동작하지만 str() 변환이 꼭 필요하지는 않다. psycopg2의 %s는 파이썬 포맷 문자가 아니라 드라이버의 자리표시라서, 타입과 상관없이 항상 %s다. 공식 문서도 정수에 %d, 실수에 %f를 쓰지 말라고 적어 두었다. 정수를 그대로 넘기면 psycopg2가 SQL 정수 리터럴로 바꿔 넣는다.

# 형변환 없이 넘겨도 된다
query_paramDict = { 'str_test' : test }

빈 값을 null로 넣을 때

update나 insert에서 전달받은 값이 빈 문자열('')이면 테이블에는 null을 넣어야 하는 경우가 있었다. 당시에는 쿼리 문자열의 자리표시를 null로 바꿔 치웠다.

# 'test'라는 변수에 전달받은 인자값이 공백일 경우

query = 'select * from testTable where key = %(str_test)s'
query_paramDict = { 'str_test' : str(test) }

if test is '': query = query.replace('%(str_test)s', 'null')

cursor.execute(query, query_paramDict)

바뀐 쿼리는 where key = 'null'(문자열 null)이 아니라 where key = null이 된다. 의도한 대로 null이 들어가긴 하지만 지금 보면 손볼 곳이 세 군데 있다.

null도 값으로 넘기면 된다. psycopg2는 파이썬 None을 SQL NULL로 바꿔 넣으므로 쿼리 문자열을 고칠 필요가 없다. 이때 str(None)은 문자열 'None'이 되니 str()로 감싸지 않는다.
is ''는 == ''여야 한다. is는 값이 아니라 객체가 같은지 비교한다. 빈 문자열이 우연히 같은 객체라서 동작할 수는 있어도 보장되지 않고, Python 3.8부터는 리터럴과 is 비교에 SyntaxWarning이 뜬다.
조회 조건에서는 = null이 아니라 is null이다. SQL에서 key = null은 참이 되지 않아 어떤 행도 고르지 못한다. 예시 쿼리가 select라서 짚어 둔다. update의 set key = null, insert의 values (null)은 문제없다.

# ✓ 빈 문자열이면 None을 넘긴다. 쿼리 문자열은 그대로 둔다
query_paramDict = { 'str_test' : test if test != '' else None }

cursor.execute(query, query_paramDict)

정리

Injection은 값이 쿼리의 구조가 될 때 생긴다. 문자열을 이어 붙이면 입력값과 SQL 문법 사이에 경계가 없다.
쿼리에는 %s나 %(이름)s 자리표시만 두고, 값은 cursor.execute(query, args)의 두 번째 인자로 넘긴다. 딕셔너리와 튜플 둘 다 된다.
자리표시는 타입과 상관없이 %s다. 정수도, null(None)도 값으로 넘긴다.

바꾼 건 fetch 함수의 인자 하나와 쿼리 몇 줄이다. 코드량으로 치면 작은 수정인데 쿼리 문자열을 손으로 고치던 null 처리까지 따라 정리됐다. 값은 값으로 넘긴다는 원칙 하나가 생각보다 많은 걸 덮는다.

따옴표 하나로 조건이 늘어난다#

대응 전: 입력값을 쿼리에 이어 붙이던 코드#

쿼리와 값을 따로 넘긴다#

딕셔너리로 이름 붙여 넘기기#

튜플로 순서대로 넘기기#

정수 컬럼에도 %s를 쓴다#

빈 값을 null로 넣을 때#

정리#