쿼리 러너를 읽기 전용으로 만들기 위해 SQL을 파싱하지 마세요
SQL 문자열에서 키워드를 확인하여 데이터베이스를 보호하려는 시도를 멈추세요.
SQL을 실행하는 도구를 만든다면 읽기 전용(read-only) 모드가 필요할 것입니다. 실수로 실행된 UPDATE 명령이 데이터를 삭제하는 것을 방지하고 싶을 테니까요. 이때 가장 먼저 떠오르는 생각은 DELETE나 DROP 같은 단어를 차단하는 것일 수 있습니다.
이렇게 하지 마세요.
문자열 검사는 우회하기 매우 쉽습니다. 사용자는 WITH 절을 사용하여 DELETE를 숨길 수 있습니다. 주석을 사용하여 명령어를 숨길 수도 있고, 테이블에 데이터를 쓰는 함수를 호출할 수도 있습니다. 결국 끝도 없는 '두더지 잡기' 게임을 하게 될 뿐입니다.
보안은 데이터베이스가 처리하도록 맡기세요.
Postgres에는 이를 위한 내장 기능이 있습니다. 트랜잭션을 읽기 전용으로 선언할 수 있습니다. 그러면 서버는 모든 쓰기 명령을 거부합니다. 이는 CTE, 함수, DDL 모두에 적용됩니다.
Python에서 이를 올바르게 구현하는 방법은 다음과 같습니다:
autocommit을False로 설정하여 실제 트랜잭션을 사용하세요.- 첫 번째 명령으로
SET TRANSACTION READ ONLY를 실행하세요. - 실행 시간이 긴 쿼리가 시스템을 잠그지 않도록
statement_timeout을 설정하세요. - 마지막에
rollback()을 사용하여 잠금(lock)과 스냅샷을 해제하세요.
이 방식은 SQL 텍스트를 검사하지 않습니다. 쿼리는 작성된 그대로 서버로 전달됩니다. 대신 엔진에 규칙을 강제하도록 명령하는 것입니다.
안전을 위해서는 두 가지 요소가 필요합니다:
- 쓰기 방지: 읽기 전용 트랜잭션을 사용하세요.
- 리소스 남용 방지: 타임아웃과 행(row) 제한을 사용하세요.
읽기 전용 쿼리라도 거대한 조인(join)을 수행하면 시스템을 다운시킬 수 있습니다. 읽기 전용 트랜잭션은 쓰기를 중단시키지만, 과도한 리소스 사용까지 막지는 못합니다. ad-hoc SQL을 안전하게 만들려면 이 두 가지가 모두 필요합니다.
SQL 파싱을 멈추세요. 데이터베이스가 제 역할을 하도록 맡기세요.
출처: https://dev.to/hitoshi1964/dont-parse-sql-to-make-a-query-runner-read-only-b62