값과 문장을 섞지 않는다

한 줄 요약

SQL 문장과 사용자가 보낸 값은 따로 보냅니다. 값은 자리표시자(?)로 넘기고, 자리표시자로 넘길 수 없는 열 이름과 정렬 방향은 코드에 적은 허용 목록에서 고릅니다. 그러면 DB 에 가는 SQL 문자열에는 사용자가 보낸 글자가 한 글자도 들어 있지 않습니다.

왜 이 요구가 필요한가

"... where name like '%" + q + "%'" 처럼 값을 문장에 이어 붙이면, 값에 든 글자가 문장의 문법으로 읽힙니다. 이름이 O'Neil 인 고객을 찾는 순간 작은따옴표가 문장을 끊어 500 이 나고, 다른 글자 조합은 문장의 구조 자체를 바꿉니다. OWASP 의 Query Parameterization Cheat Sheet 는 이 결함을 가장 위험한 웹 취약점 가운데 하나로 꼽고, 매개변수 질의로 막는 것이 가장 좋다고 적습니다.

'위험한 글자만 바꾸기' 는 답이 되지 못합니다. 작은따옴표를 두 번 쓰는 식으로 막으면 오늘은 버티지만, 어떤 글자가 위험한지를 우리가 DB 의 문법보다 정확히 알아야 합니다. 문자 인코딩, 역슬래시 처리, DB 종류마다 다른 규칙을 전부 따라가야 합니다. 값과 문장을 섞지 않으면 그 질문 자체가 사라집니다.

자리표시자로 해결되지 않는 자리가 두 곳 있습니다. 하나는 LIKE 의 와일드카드입니다. 값을 자리표시자로 넘겨도 LIKE 는 그 안의 % 와 _ 를 '아무 글자' 로 읽습니다. 검색어 % 가 모든 고객을 돌려준다면 검색어를 글자 그대로 다루지 못한 것입니다. 다른 하나는 ORDER BY 의 열 이름입니다. 자리표시자는 값만 받으므로 열 이름과 방향은 문장에 넣을 수밖에 없고, 그래서 허용 목록이 필요합니다.

어떻게 지키나

def like_pattern(text):
    # 이스케이프 문자(역슬래시) 자신을 먼저 바꾼다
    escaped = text.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
    return "%" + escaped + "%"

ORDER_BY = {"name": "name asc, id asc", "-name": "name desc, id asc",
            "created_at": "created_at asc, id asc", "-created_at": "created_at desc, id asc"}

sql = ("select id, name from customers where name like ? escape '\\' order by "
       + ORDER_BY[sort])                         # sort 가 목록에 없으면 먼저 거절한다
conn.execute(sql, (like_pattern(q),))

정렬값이 목록에 없으면 기본 정렬로 바꾸지 말고 거절합니다(HTTP 422). 조용히 바꾸면 화면의 오타가 숨고, 무엇이 허용되는지 아무도 모르게 됩니다. 정렬값이 열 이름처럼 생겼는지만 확인하는 방식도 부족합니다. 그 확인은 city 나 id 처럼 보여 주기로 하지 않은 열까지 통과시킵니다. 같은 값끼리의 순서를 id 로 고정해 두면 페이지를 넘길 때 행이 사라지거나 두 번 나오는 일도 막힙니다.

시험은 결과와 함께 무엇이 DB 로 갔는지를 봅니다. execute 를 가진 작은 기록 객체를 넘겨, SQL 문자열에 사용자가 보낸 글자가 없고 값은 두 번째 인자로 갔는지 확인합니다. 결과만 보는 시험은 '따옴표 두 번 쓰기' 같은 수정을 통과시키지만, 무엇이 갔는지 보는 시험은 붙잡습니다.

현장에서 만나는 모습

검색 API 는 매개변수 질의로 고쳤는데 같은 화면의 개수 API, 내보내기 API, 관리자용 통계 쿼리는 문자열 조립으로 남아 있는 경우가 흔합니다. ORM 을 쓰더라도 raw(), text(), extra() 처럼 문자열을 받는 출구가 있고, 정렬과 필터를 '편하게' 받으려고 그 출구를 쓰는 순간 같은 결함이 돌아옵니다. 감사 로그에는 거절한 정렬값의 필드 이름과 검색 결과 수를 남기고, 검색어는 남기지 않습니다. 고객 이름을 찾는 검색어는 그 자체가 개인정보이기 때문입니다.

'SQL 마스터리' 코스가 질의를 잘 쓰는 법을, 'DB 동시성과 잠금' 코스가 트랜잭션을 다룬다면, 이 모듈은 사용자 입력이 질의에 닿는 경계 하나만 봅니다.

English corner

「parameterized query」 와 「bind variable」 같은 것을 가리킵니다. Python DB-API 에서는 ? 나 %s 자리표시자를 쓰고 값은 execute(sql, params) 의 두 번째 인자로 넘깁니다.
「escape character」 LIKE 에서 % 와 _ 를 글자로 만드는 표시입니다. SQLite 문서는 ESCAPE 절로 그 문자를 정한다고 적습니다.
「allowlist the sort column」 리뷰에서 자주 쓰는 표현입니다. "Column names cannot be bound, so allowlist them." 처럼 이유와 함께 적습니다.

다음 실습에서 할 것

고객 검색과 도시별 개수가 사용자 값을 SQL 문자열에 이어 붙이고, 정렬값은 그대로 ORDER BY 에 들어갑니다. 요구 A(값은 자리표시자로, LIKE 의 와일드카드까지 글자 그대로)와 요구 B(정렬은 허용 목록에서만)를 시험으로 쓰고 고칩니다.