InfoSec 3001 — 공격적 관점에서의 정보보안

Step 93. SQL 기초 2 — JOIN과 파이썬 연동

Step 93. SQL 기초 2 — JOIN과 파이썬 연동

Level 1 — 프로그래밍과 컴퓨터 내부 | 난이도 ★★★☆☆ | 예상 소요 시간 3시간

전제: Step 92의 CRUD와 SQLite 조작, Step 41~46의 파이썬 기초를 마쳤다.

  • 준비물: 어제 만든 test.db(지웠다면 다시 만들어도 됩니다)와 파이썬. 이 챕터의 실측은 파이썬 3.12의 내장 sqlite3 모듈로 수행했습니다 — 별도 설치가 없는 것이 SQLite의 미덕입니다.
  • 주의: 후반에 "위험한 코드"를 일부러 짜는 실험이 있습니다. 내 컴퓨터 안의 연습용 DB에서만 진행합니다. 오늘의 하이라이트 — ' OR '1'='1이라는 입력이 SQL 문장을 바꾸는 순간을 눈으로 봅니다.

실제 서비스는 데이터를 한 장의 표에 욱여넣지 않습니다. 회원은 회원 표에, 주문은 주문 표에 — 나눠 담습니다. 회원이 주소를 바꿀 때마다 그 사람의 주문 백 건을 전부 고치는 재앙을 피하기 위해서입니다. 그런데 표가 나뉘면 질문이 생깁니다 — "alice가 주문한 물건 목록"은 어떻게 구할까요? 그 답이 오늘의 첫 번째 주인공 JOIN입니다. 두 번째 주인공은 파라미터 바인딩 — 파이썬에서 SQL을 실행할 때 입력값을 안전하게 끼워 넣는 공식 방법으로, 그 존재 이유가 바로 SQL Injection 방어입니다. 오늘 여러분은 공격이 먹히는 코드와 막히는 코드를 둘 다 직접 짜 보게 됩니다.

1. 학습 목표

이 챕터를 끝내면 다음을 할 수 있습니다:

  • 두 테이블을 ON 조건으로 JOIN해 조회할 수 있다
  • GROUP BY / COUNT / ORDER BY로 표를 요약하는 쿼리를 만들 수 있다
  • 파이썬에서 sqlite3 모듈로 connect → execute → fetch → close의 리듬으로 DB를 다룬다
  • 문자열 결합 쿼리의 위험을 ' OR '1'='1 실험으로 시연할 수 있다
  • 왜 파라미터 바인딩(?)이 같은 입력을 막는지 한 문장으로 말할 수 있다

2. 배경 지식 — 오늘의 도구와 개념

오늘의 도구 한눈에 보기

구분 내용
언어·환경 파이썬 3 + 내장 sqlite3 모듈 (별도 설치 없음), sqlite3 대화형 셸 병행
오늘의 명령 JOIN ... ON, GROUP BY, COUNT(*), 파이썬 connect/execute/fetchall/commit/close, 파라미터 바인딩(?)
필요한 개념 외래키(표와 표의 연결 고리), 튜플, 문자열 결합 vs 바인딩, 주석(--)
오늘의 산출물 안전한 미니 회원 프로그램 + 공격 시연 기록 문서

2-1. 정규화와 JOIN — 나누고 잇는 기술

데이터를 "중복 없이 나누어 담는" 설계 철학을 정규화라고 부릅니다(지금은 이름만 알아 두세요). 나누어 담았으니 조회할 때는 다시 이어야 합니다. 그 연결 고리가 외래키 — 주문 표의 user_id 칸이 회원 표의 id를 가리키는 식입니다.

users:  id=2, name='alice'
orders: user_id=2, item='보안 책'
        user_id=2, item='기계식 키보드'

JOIN은 이 화살표를 따라가 두 표를 한 장의 결과로 붙여 줍니다.

2-2. 파이썬의 sqlite3 모듈 — 프로그램이 창고지기가 되다

파이썬에는 SQLite 드라이버가 기본 내장되어 있습니다. 패턴은 항상 같습니다.

import sqlite3
conn = sqlite3.connect("test.db")   # 창고에 전화 걸기
cur = conn.execute("SQL 문장")       # 명령 내리기
for row in cur: ...                  # 결과 한 줄씩 받기
conn.close()                         # 전화 끊기

어제 프롬프트에서 손으로 치던 일을, 이제 프로그램이 초당 수천 번씩 해 줍니다. 웹 서비스의 회원가입·로그인 뒤에서 돌아가는 것이 바로 이 코드입니다.

2-3. 문자열 결합 vs 바인딩 — 오늘의 승부

사용자 입력으로 SQL을 만드는 방법은 두 가지입니다.

# 방법 A: 문자열 결합 — 위험!
sql = "SELECT * FROM users WHERE name='" + name + "'"

# 방법 B: 파라미터 바인딩 — 안전
conn.execute("SELECT * FROM users WHERE name=?", (name,))

방법 A는 입력을 문장의 일부로 용접해 버립니다. 입력에 따옴표가 있으면? 어제 본 대로 문장이 깨지거나, 바뀝니다. 방법 B는 ? 자리를 마련해 두고 입력을 데이터로만 전달합니다 — 입력이 따옴표를 담고 있어도 "그냥 글자"로만 취급됩니다. 오늘 이 차이를 오류가 아니라 공격의 성공과 실패로 확인합니다.

3. 따라 하기

3-1. 두 번째 테이블과 연결 고리

입력 (어제의 test.db를 열어):

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  item TEXT
);
INSERT INTO orders (user_id, item) VALUES (2, '보안 책');
INSERT INTO orders (user_id, item) VALUES (2, '기계식 키보드');
INSERT INTO orders (user_id, item) VALUES (1, '커피 원두');
INSERT INTO orders (user_id, item) VALUES (3, '모니터');

읽는 법: user_id가 누구의 주문인지를 가리킵니다. 2는 alice, 1은 admin, 3은 bob(어제의 id 번호). alice만 두 건입니다.

왜: 나누어 담기의 완성입니다. 이제 "누가 무엇을 샀는가"라는 질문은 표 두 장을 건너야 답할 수 있습니다.

3-2. JOIN — 두 표를 한 장으로

입력

SELECT users.name, orders.item
FROM users
JOIN orders ON users.id = orders.user_id;

출력 (2026-09-09 실측):

alice|보안 책
alice|기계식 키보드
admin|커피 원두
bob|모니터

읽는 법: "users와 orders를 붙여라(JOIN), 연결 고리는(ON) users.id = orders.user_id로." ON 뒤의 조건이 두 표를 꿰는 바늘입니다. 결과에서는 각 주문이 주인의 이름과 나란히 놓입니다.

왜: 실제 서비스의 조회는 대부분 JOIN입니다. "내 주문 목록", "이 글의 댓글과 작성자 닉네임" — 전부 이 문법의 응용입니다.

3-3. 집계와 정렬 — 요약하는 힘

입력

SELECT users.name, COUNT(*)
FROM users JOIN orders ON users.id = orders.user_id
GROUP BY users.name
ORDER BY COUNT(*) DESC;

출력 (2026-09-09 실측):

alice|2
bob|1
admin|1

읽는 법: GROUP BY는 "같은 이름끼리 묶어라", COUNT(*)는 묶음마다 개수를 셉니다. 마지막으로 개수 큰 순(DESC)으로 정렬했습니다. 참고로 개수가 같은 bob과 admin의 순서는 정해져 있지 않습니다 — 실측에서는 bob이 먼저 나왔지만, 동률의 순서는 환경에 따라 달라질 수 있습니다. 동률까지 정하려면 ORDER BY COUNT(*) DESC, users.name처럼 두 번째 기준을 추가하세요.

왜: 로그 분석의 기본 패턴입니다 — "IP별 접속 횟수", "시간대별 오류 수" 전부 이 구조입니다. 표를 보는 눈에서 표를 요약하는 눈으로 한 단계 올라서는 문법입니다.

3-4. 파이썬에서 DB 다루기 — 안전한 방식

입력 (db_app.py)

import sqlite3

def find_user(name):
    conn = sqlite3.connect("test.db")
    rows = conn.execute(
        "SELECT id, name, is_admin FROM users WHERE name=?", (name,)
    ).fetchall()
    conn.close()
    return rows

print(find_user("alice"))
print(find_user("nobody"))

출력 (2026-09-09 실측, python db_app.py):

[(2, 'alice', 0)]
[]

읽는 법: ?가 자리표시자(플레이스홀더)이고, 뒤의 튜플 (name,)이 그 자리에 들어갈 값입니다. fetchall()은 결과 전부를 리스트로 가져옵니다. 없는 이름은 빈 리스트 [].

왜: 이 함수가 로그인·검색·프로필 조회의 원형입니다. 그리고 ? 방식이 정석이라는 사실을, 이제 곧 대조 실험으로 증명합니다.

3-5. 위험 실험 — 문자열 결합의 최후

입력 (db_bad.py — 교육용 취약 코드, 어디에도 배포 금지):

import sqlite3

def find_user_bad(name):
    conn = sqlite3.connect("test.db")
    sql = "SELECT id, name, pw FROM users WHERE name='" + name + "'"
    print("실행된 SQL:", sql)          # 무슨 문장이 됐는지 관찰
    rows = conn.execute(sql).fetchall()
    conn.close()
    return rows

print(find_user_bad("alice"))

출력 (2026-09-09 실측):

실행된 SQL: SELECT id, name, pw FROM users WHERE name='alice'
[(2, 'alice', 'wonderland')]

정상 동작합니다. 자, 이제 공격자의 입력을 넣어 봅시다. 마지막 줄을 이렇게 바꿉니다.

print(find_user_bad("' OR '1'='1"))

출력 (2026-09-09 실측):

실행된 SQL: SELECT id, name, pw FROM users WHERE name='' OR '1'='1'
[(1, 'admin', 'secret123'), (2, 'alice', 'wonderland'), (3, 'bob', 'builder99'), (4, 'charlie', 'choco789')]

읽는 법: 입력이 문장 안에서 "문자열"이 아니라 문법이 되었습니다. OR '1'='1'이 붙으면서 조건이 "이름이 빈 문자열이거나, 또는 1이 1과 같거나(항상 참)"로 변했고, 결과는 표 전체 유출 — 비밀번호 칸 포함입니다.

왜: 이것이 SQL Injection의 탄생 현장입니다. 같은 입력을 3-4의 바인딩 버전에 넣어 보세요 — find_user("' OR '1'='1")은 빈 리스트 []를 돌려줍니다 (2026-09-09 실측). 그런 이름의 회원이 없으니까요. 먹히는 코드와 막히는 코드의 차이를 방금 두 눈으로 확인했습니다.

⚠️ 보안 연결: 이 실험은 전부 내 컴퓨터의 연습용 DB에서 이루어졌습니다. 모든 공격 실습은 내 랩·합법 플랫폼에서만. 실서비스 무단 공격은 범죄입니다. 이 입력 문자열을 실제 웹사이트의 입력 칸에 넣는 순간, 실험이 아니라 공격이 됩니다.

3-6. 예측해 보기 — 로그인 함수라면?

예측: 방금의 취약한 방식으로 로그인을 구현했다고 합시다.

sql = "SELECT * FROM users WHERE name='" + name + "' AND pw='" + pw + "'"

공격자가 비밀번호를 모릅니다. 아이디 칸에 어떤 입력을 넣으면 비밀번호 확인이 무력화될까요?

  • (a) admin — 정확한 아이디
  • (b) admin'-- — 따옴표와 주석 표시
  • (c) 12345 — 흔한 비밀번호 추측

직접 확인 — 이 구조의 함수를 만들고 (b)를 넣은 출력 (2026-09-09 실측):

실행된 SQL: SELECT * FROM users WHERE name='admin'--' AND pw='아무것도모름'
[(1, 'admin', 'secret123', 1)]

정답은 (b) 입니다. --는 SQL의 주석 표시라, 그 뒤(비밀번호 확인 부분)는 무시됩니다. admin 행이 통째로 반환됐습니다 — 비밀번호 없이 관리자 로그인 성공.

왜 중요한가: 이 입력이 실제로 수많은 서비스를 무너뜨린 역사적 공격입니다. 그리고 여러분은 이제 그것이 "마법의 주문"이 아니라 문법 충돌의 논리적 결과임을 압니다.

3-7. 파이썬으로 쓰기 — INSERT도 바인딩으로

읽기만이 아닙니다. 프로그램이 DB에 쓰는 경우에도 규칙은 같습니다.

입력 (db_add.py)

import sqlite3

def add_user(name, pw):
    conn = sqlite3.connect("test.db")
    conn.execute(
        "INSERT INTO users (name, pw, is_admin) VALUES (?, ?, 0)",
        (name, pw)
    )
    conn.commit()
    conn.close()

add_user("mallory", "pass123")

출력 (2026-09-09 실측, 실행 후 SELECT name FROM users 조회): 목록 맨 끝에 mallory가 추가되어 있습니다.

읽는 법: 두 가지가 새롭습니다. 첫째, 쓰기(INSERT/UPDATE/DELETE) 후에는 conn.commit() — "이 변경을 확정해라"는 도장입니다. 빠뜨리면 연결이 닫힐 때 변경이 롤백될 수 있습니다. 둘째, INSERT에도 ? 바인딩이 그대로 쓰입니다.

왜: 회원가입 폼의 뒷면이 이 함수입니다. 사용자가 가입란에 x'); DROP TABLE users;-- 같은 악의적 입력을 넣어도, 바인딩은 그것을 그냥 "이상한 이름의 회원"으로만 저장합니다. 실제로 확인했습니다 (2026-09-09 실측):

[('admin',), ('alice',), ('bob',), ('charlie',), ("x'); DROP TABLE users;--",)]
users 표 생존: 5 명

표는 멀쩡하고, 이름이 그대로 한 명 추가됐습니다 — 바인딩의 방어력을 쓰기 경로에서도 확인하는 실험입니다.

4. 미션과 연습문제

미션 — 안전한 미니 회원 프로그램 + 공격 시연 기록

  1. 파이썬으로 회원 프로그램을 작성합니다: 회원가입(INSERT·바인딩), 로그인(SELECT·바인딩), 회원 목록(SELECT) 3기능.
  2. JOIN으로 "회원별 주문 수"를 보여 주는 기능을 추가합니다.
  3. 취약 버전(문자열 결합)을 별도 파일로 만들어 ' OR '1'='1와 admin'-- 두 공격의 성공 화면을 캡처합니다. 파일 최상단에 # 교육용 취약 코드 — 어디에도 배포 금지 주석을 명시합니다.
  4. 같은 입력을 안전 버전에 넣어 "빈 결과"로 막히는 것을 캡처합니다.
  5. 위키에 SQL인젝션예고.md를 작성합니다 — 두 캡처 + "바인딩이 막는 이유" 3줄 정리.

연습문제

문제 1. JOIN에서 ON users.id = orders.user_id의 역할을 설명하고, 이 조건을 빠뜨리면 어떻게 되는지 말해 보세요.

문제 2. (name,)에서 마지막 쉼표를 빼면 무슨 일이 생기나요? 왜 쉼표가 필요한지 설명해 보세요.

문제 3. ' OR '1'='1 입력이 문자열 결합 쿼리에서 "항상 참"이 되는 과정을, 완성된 SQL 문장을 써 가며 설명해 보세요.

문제 4. 바인딩 버전에 같은 공격 입력을 넣으면 왜 빈 리스트가 나오나요? "입력이 문법이 아니라 데이터로 취급된다"는 말의 뜻을 풀어 보세요.

5. 모범 답안과 완료 기준

미션 모범 답안

안전한 회원 프로그램의 뼈대:

import sqlite3

DB = "test.db"

def signup(name, pw):
    conn = sqlite3.connect(DB)
    conn.execute("INSERT INTO users (name, pw, is_admin) VALUES (?, ?, 0)",
                 (name, pw))
    conn.commit()
    conn.close()

def login(name, pw):
    conn = sqlite3.connect(DB)
    rows = conn.execute(
        "SELECT id, name FROM users WHERE name=? AND pw=?", (name, pw)
    ).fetchall()
    conn.close()
    return "로그인 성공" if rows else "로그인 실패"

def order_counts():
    conn = sqlite3.connect(DB)
    rows = conn.execute(
        "SELECT users.name, COUNT(*) FROM users "
        "JOIN orders ON users.id = orders.user_id "
        "GROUP BY users.name ORDER BY COUNT(*) DESC"
    ).fetchall()
    conn.close()
    return rows

검증하는 법: ① 취약 버전에서 두 공격 입력이 성공하는 캡처가 있는가 — 실측 기준으로 ' OR '1'='1은 전원 목록을, admin'--은 admin 행을 비밀번호 없이 반환해야 합니다 (3-5, 3-6 실측). ② 안전 버전에서는 둘 다 빈 결과/로그인 실패로 막히는가. ③ 위키 문서에 "바인딩은 입력을 데이터로만 전달하므로 따옴표가 문법으로 승격되지 않는다"는 취지의 설명이 있는가. 셋이 전부 ‘예’이면 완성입니다.

연습문제 해답

문제 1 해답. ON은 두 표를 꿰는 바늘 — "주문의 user_id와 회원의 id가 같은 것끼리 붙여라"라는 연결 조건입니다. 빠뜨리면 모든 조합(카르테시안 곱)이 만들어집니다. 실측에서 회원 4명 × 주문 4건 = 16행이 나왔습니다 (2026-09-09, SELECT COUNT(*) FROM users JOIN orders). 조건 없는 JOIN은 답이 아니라 곱셈입니다.

문제 2 해답. 쉼표가 없으면 (name)은 튜플이 아니라 그냥 괄호로 묶은 값입니다. 파이썬에서 요소 하나짜리 튜플은 (값,)처럼 쉼표로 만듭니다. 개수가 안 맞으면 실측 오류가 납니다 (2026-09-09): ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

문제 3 해답. 결합 결과가 ... WHERE name='' OR '1'='1'이 됩니다. 입력의 첫 따옴표가 문자열을 닫아 버려서 name 조건은 빈 문자열 비교가 되고, 뒤의 OR '1'='1'은 문장의 문법이 되어 항상 참인 조건을 추가합니다. 참 OR 무엇이든 참이므로 모든 행이 조건을 통과합니다 (3-5 실측: 4명 전원 + 비밀번호 칸 유출).

문제 4 해답. 바인딩은 ? 자리의 값을 SQL 문장에 용접하지 않고 "데이터"로 따로 전달하기 때문입니다. DB는 그 값을 글자로만 비교합니다 — ' OR '1'='1이라는 문자열을 이름으로 가진 회원을 찾을 뿐, 따옴표를 문법으로 해석하지 않습니다. 그런 회원은 없으니 빈 리스트가 나옵니다 (2026-09-09 실측).

완료 기준 체크리스트

  • [ ] 두 테이블을 ON 조건으로 JOIN해 조회할 수 있다
  • [ ] GROUP BY / COUNT / ORDER BY로 요약 쿼리를 만들 수 있다
  • [ ] 파이썬에서 바인딩(?) 방식으로 SELECT/INSERT를 실행할 수 있다
  • [ ] 문자열 결합 쿼리의 위험을 ' OR '1'='1 실험으로 시연할 수 있다
  • [ ] admin'--이 비밀번호 확인을 무력화하는 원리를 설명할 수 있다
  • [ ] 왜 바인딩이 같은 입력을 막는지 한 문장으로 말할 수 있다
  • [ ] 미션: 안전한 회원 프로그램과 공격 시연 기록 문서를 완성했다

6. 흔한 실수와 해결

벽 1. JOIN 결과가 엉뚱하게 많아요 (곱하기처럼 불어남)

증상 (2026-09-09 실측): 주문이 4건뿐인데 SELECT COUNT(*) FROM users JOIN orders가 16을 돌려줍니다.

원인: ON 조건을 빠뜨렸거나 틀렸습니다. 조건 없는 JOIN은 "모든 조합"(카르테시안 곱)을 만듭니다 — 4명 × 4건 = 16행.

해결: ON users.id = orders.user_id가 정확한지 확인. 연결 고리는 JOIN의 생명입니다.

벽 2. 바인딩에서 "Incorrect number of bindings" 오류

증상 (2026-09-09 실측):

ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

원인: ? 개수와 값 개수가 안 맞습니다. (name,)의 마지막 쉼표를 빼먹는 사고도 흔합니다 — 쉼표 없으면 튜플이 아니라 그냥 괄호입니다.

해결: ?의 개수를 세고, 튜플의 요소 수와 맞추세요. 값 하나일 때도 (값,) 형태를 지킵니다.

벽 3. 파이썬에서 "database is locked"이 나와요

증상: 프로그램이 DB를 못 열고 잠겼다고 합니다.

원인: sqlite3 대화형 셸에서 test.db를 열어 둔 채 프로그램이 쓰기를 시도했습니다. SQLite는 동시 쓰기를 허용하지 않습니다.

해결: 셸을 .exit으로 닫고 다시 실행. 프로그램 쪽도 conn.close()를 빠뜨리지 않았는지 확인하세요.

벽 4. 공격 입력을 넣었는데 그냥 오류가 나요

증상: ' OR '1'='1에서 syntax error.

원인: 결합 위치에 따라 따옴표 짝이 안 맞는 경우가 있습니다. 본문의 예제와 함수 구조가 정확히 같은지 확인이 필요합니다.

해결: print("실행된 SQL:", sql) 줄이 반드시 있어야 합니다 — 실제로 어떤 문장이 만들어졌는지 보는 것이 이 실험의 핵심입니다. 문장을 읽고 따옴표의 짝을 손가락으로 짚어 보세요.

벽 5. INSERT를 했는데 나중에 열어 보니 저장이 안 됐어요

증상: 프로그램은 오류 없이 끝났는데 DB에 행이 없습니다.

원인: conn.commit()을 빠뜨렸습니다. 쓰기는 commit 전까지 확정되지 않은 상태입니다.

해결: INSERT/UPDATE/DELETE 뒤에는 항상 conn.commit(). "쓰기 후엔 도장"을 리듬으로 외우세요.

7. 정리

오늘의 개념

개념 한 줄 설명
외래키 표와 표를 잇는 화살표 (orders.user_id → users.id)
JOIN … ON 두 표를 연결 조건으로 한 장의 결과로 붙이기
GROUP BY + COUNT 같은 값끼리 묶어 개수 세기 — 로그 분석의 기본 패턴
카르테시안 곱 ON 없는 JOIN의 재앙 — 모든 조합이 만들어짐
파라미터 바인딩(?) 입력을 SQL 문장이 아니라 데이터로 전달하는 공식 방법
-- 주석 그 뒤를 전부 무시 — admin'-- 공격의 핵심 부품

오늘의 명령어

명령 하는 일
SELECT ... FROM a JOIN b ON a.id = b.a_id 두 표 붙여 조회
GROUP BY 칸 + COUNT(*) 묶어서 개수 세기
sqlite3.connect("파일.db") 파이썬에서 DB 열기
conn.execute("... WHERE 칸=?", (값,)) 바인딩으로 안전하게 실행
.fetchall() 결과 전부를 리스트로 받기
conn.commit() 쓰기 변경 확정 (INSERT/UPDATE/DELETE 뒤 필수)
conn.close() 연결 닫기

명령어보다 중요한 감각

문자열 결합 쿼리는 입력을 문법으로 바꾸고, 파라미터 바인딩은 입력을 데이터로 가둡니다 — 이 차이가 인젝션의 성패입니다. ' OR '1'='1과 admin'--는 주문이 아니라 문법 충돌의 결과입니다. 원리를 아는 사람은 두렵지 않습니다.

그리고 이 구조는 SQL에만 있지 않습니다. "입력이 명령의 문법이 된다"는 뿌리는 명령어 인젝션, XSS 등 인젝션 패밀리 전체에 공통입니다 — 코드와 데이터를 한 그릇에 섞은 것이 뿌리입니다. 여러분은 오늘 "취약한 코드"와 "안전한 코드"를 같은 손으로 짜 봤습니다. 취약점은 이론이 아니라 내가 짠 코드 속에 있습니다 — 이 대조 체험이 웹 보안의 진짜 출발점입니다.

전부 체크되면 Step 93 완료입니다. 사이드바의 체크박스를 눌러 진도를 저장하세요.

← 이전 스텝
다음 스텝 →