it

SQL Injection 감사 챌린지 1주차

발행: (2025년 12월 14일 오전 02:35 GMT+9)
9 min read
원문: Dev.to

Source: Dev.to

Introduction

Master SQL Injection Detection: 15 Real‑World Exercises for AppSec Engineers

The $1.5 Billion Wake‑Call

2015년에 해커들은 영국 통신사 TalkTalk의 SQL injection 취약점을 악용해 157,000명의 고객 개인정보(이름, 주소, 생년월일, 15,600개의 은행 계좌 번호)를 탈취했습니다. 회사는 £400,000의 벌금을 내고 직접 비용 및 고객 보상으로 추정 £60 million을 손실했습니다1. 공격자는? 웹 보안에서 가장 오래되고 예방 가능한 취약점 중 하나를 사용한 17세 청소년이었습니다.

OWASP Top 10 2025(2025년 11월 6일 발표)에 따르면, 인젝션 공격은 A05:2025 – Injection으로 분류되며, 테스트된 모든 애플리케이션(100 %)이 어떤 형태든 인젝션에 노출될 수 있다고 보고되었습니다2. 프레임워크 개선과 인식 제고 덕분에 SQL injection 발생 빈도는 감소했지만, 그 영향은 여전히 파괴적입니다:

  • Low frequency, high impact – 인젝션 카테고리에서 14 000개 이상의 CVE가 보고됨2
  • Average impact: 전체 데이터 유출
  • Testing coverage: 인젝션 취약점에 대해 100 %의 애플리케이션이 테스트됨2
  • 37 CWEs가 인젝션 카테고리에 포함돼, OWASP Top 10 2025 카테고리 중 CVE 수가 가장 많음2

Translation: SQL injection은 과거보다 덜 흔하지만, 발생하면 파괴적입니다. OWASP은 SQL injection을 “low frequency/high impact”(낮은 빈도/높은 영향)이라고 명시하고 있습니다. 즉, 취약한 애플리케이션 수는 적지만, 취약한 경우 전체 데이터가 유출됩니다.

GitLab, Stripe, Coinbase, Trail of Bits, NCC Group 등에서 Security Engineering 또는 Application Security 직무 면접을 보게 된다면, 다음과 같은 SQL‑injection 질문을 마주하게 됩니다:

  • 익숙하지 않은 프레임워크에서 취약 코드를 식별하기
  • 영향을 보여주는 실제 익스플로잇 페이로드 작성하기
  • 파라미터화된 쿼리를 사용한 안전한 수정 제시하기
  • 단순 검증만으로는 방어가 왜 실패하는지 설명하기

이러한 역량은 SQL injection에 대해 책만 읽은 지원자와 실제 프로덕션 코드에서 찾아내고 수정할 수 있는 지원자를 구분합니다.

아래 15개의 연습문제는 제가 AppSec 엔지니어링 역량을 키우면서 만든 것으로, 다음 출처들을 참고했습니다:

  • PortSwigger Web Security Academy SQL Injection labs3
  • OWASP SQL Injection Prevention Cheat Sheet4
  • Secure by Design (Manning, 2017) – Chapters 1‑3
  • API Security in Action (Manning, 2020) – Chapter 2

각 연습문제는 보안 리뷰나 침투 테스트 중 마주할 수 있는 실제 취약 코드를 제공합니다. 모든 연습문제에는 다음이 포함됩니다:

  • ✅ 취약점 식별
  • ✅ 익스플로잇 페이로드 구성
  • ✅ 파라미터화된 쿼리 적용 보안 수정
  • ✅ 방어가 실패하는 이유 설명

⭐ 더 많은 연습문제가 필요하신가요? 제 레포지토리에 ⭐를 눌러 주세요: AppSec‑Exercises on GitHub (LeetCode‑스타일 보안 코딩 챌린지)

Exercise 1 – WHERE Clause Injection

Difficulty: Beginner
Vulnerability Type: WHERE Clause Injection

import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    query = "SELECT id, username FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

    cursor.execute(query)
    user = cursor.fetchone()
    conn.close()

    if user:
        return jsonify({"message": "Login successful", "user_id": user[0]})
    else:
        return jsonify({"message": "Invalid credentials"}), 401

Questions

  1. Is this vulnerable to SQL injection? (Yes/No)
  2. Craft a payload for the username field that bypasses authentication without knowing any passwords.
  3. Provide the secure fix using SQLite parameterized queries.

💡 Solution at the end of this post.

Exercise 2 – Mixed Parameterization Anti‑Pattern

Difficulty: Beginner
Vulnerability Type: Mixed Parameterization Anti‑Pattern

import psycopg2
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/search', methods=['GET'])
def search_products():
    search_term = request.args.get('q', '')
    category = request.args.get('category', 'all')

    # Attempted escaping
    search_term = search_term.replace("'", "''")

    conn = psycopg2.connect(database="store", user="app", password="secret")
    cursor = conn.cursor()

    if category == 'all':
        query = "SELECT * FROM products WHERE name LIKE '%" + search_term + "%'"
        cursor.execute(query)
    else:
        query = "SELECT * FROM products WHERE name LIKE %s AND category = %s"
        cursor.execute(query, ('%' + search_term + '%', category))

    results = cursor.fetchall()
    conn.close()

    return jsonify({"products": results})

Questions

  1. Is the category == 'all' branch vulnerable? (Yes/No)
  2. Why does the string escaping (replace("'", "''")) fail to prevent SQL injection?
  3. Provide the correct parameterized‑query fix for the vulnerable branch.

💡 Solution at the end of this post.

Exercise 3 – LIKE Clause Injection

Difficulty: Intermediate
Vulnerability Type: LIKE Clause Injection

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2),
    category TEXT
);

CREATE TABLE admin_notes (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    note TEXT,
    created_by TEXT
);
import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/search', methods=['GET'])
def search_products():
    search_term = request.args.get('q', '')

    conn = sqlite3.connect('shop.db')
    cursor = conn.cursor()

    query = f"SELECT id, name, price FROM products WHERE name LIKE '%{search_term}%'"

    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()

    return jsonify({"products": results})

Questions

  1. Is this vulnerable? The search term is wrapped in % wildcards—does that prevent injection?
  2. Craft a payload that extracts sensitive data from the admin_notes table.
  3. Provide the secure fix with proper wildcard handling.

💡 Solution at the end of this post.

Exercise 4 – Second‑Order Injection

Difficulty: Intermediate
Vulnerability Type: Second‑Order Injection

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT,
    password_hash TEXT
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    author TEXT,
    content TEXT,
    created_at DATETIME
);
import psycopg2
from flask import Flask, request, jsonify

app = Flask(__name__)

# Endpoint 1: Update profile (SAFE)
@app.route('/update-profile', methods=['POST'])
def update_profile():
    user_id = request.form.get('user_id')
    bio = request.form.get('bio')

    conn = psycopg2.connect(host='localhost', user='app', password='pass', database='social')
    cursor = conn.cursor()

    query = "UPDATE users SET bio = %s WHERE id = %s"
    cursor.execute(query, (bio, user_id))
    conn.commit()
    conn.close()

    return jsonify({"message": "Profile updated successfully"})

# Endpoint 2: Get user posts (VULNERABLE)
@app.route('/get-user-posts', methods=['GET'])
def get_user_posts():
    user_id = request.args.get('user_id')

    conn = psycopg2.connect(host='localhost', user='app', password='pass', database='social')
    cursor = conn.cursor()

    cursor.execute("SELECT username FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()

    if not user:
        return jsonify({"error": "User not found"}), 404

    username = user[0]

    query = "SELECT post_id, content, created_at FROM posts WHERE author = '" + username + "' ORDER BY created_at DESC"
    cursor.execute(query)
    posts = cursor.fetchall()

    conn.close()

    return jsonify({"username": username, "posts": posts})

Questions

  1. Which endpoint is vulnerable? (update-profile, get-user-posts, or both?)
  2. Explain the attack flow: Which endpoint stores malicious data and which triggers the injection?
  3. Craft a malicious username that would extract password hashes when viewing posts.
  4. Provide the secure fix for the vulnerable endpoint.

💡 Solution at the end of this post.

Exercise 5 – ORDER BY Clause Injection

Difficulty: Intermediate
Vulnerability Type: ORDER BY Clause Injection

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2),
    category TEXT,
    stock_quantity INTEGER
);

(Further code and questions continue in the original challenge set.)

Solutions

Exercise 1 – WHERE Clause Injection

1. Vulnerable? Yes.

2. Payload (username):

' OR '1'='1

When submitted, the query becomes:

SELECT id, username FROM users WHERE username = '' OR '1'='1' AND password = ''

The condition '1'='1' is always true, bypassing authentication.

3. Secure fix (SQLite parameterized query):

query = "SELECT id, username FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))

Exercise 2 – Mixed Parameterization Anti‑Pattern

1. Vulnerable? Yes.

2. Why escaping fails:
Replacing a single quote with two single quotes only protects against simple literal injection. It does not prevent attackers from breaking out of the string context when the surrounding query is built via concatenation, especially when other characters (e.g., %, _) are interpreted by the database.

3. Parameterized fix:

if category == 'all':
    query = "SELECT * FROM products WHERE name LIKE %s"
    cursor.execute(query, ('%' + search_term + '%',))
else:
    query = "SELECT * FROM products WHERE name LIKE %s AND category = %s"
    cursor.execute(query, ('%' + search_term + '%', category))

Exercise 3 – LIKE Clause Injection

1. Vulnerable? Yes. Wrapping the input in % does not neutralize injection; the input is still interpolated directly into the SQL string.

2. Payload to extract admin_notes:

' UNION SELECT id, note, created_by FROM admin_notes --

Resulting query:

SELECT id, name, price FROM products WHERE name LIKE '%' UNION SELECT id, note, created_by FROM admin_notes --'%'

3. Secure fix:

query = "SELECT id, name, price FROM products WHERE name LIKE ?"
param = f"%{search_term}%"
cursor.execute(query, (param,))

Exercise 4 – Second‑Order Injection

1. Vulnerable endpoint: get-user-posts.

2. Attack flow:

  • Step 1 – Store malicious data: An attacker (or another compromised component) injects a crafted username value into the users table via any operation that writes to that column (e.g., registration, profile update).
  • Step 2 – Trigger injection: When get-user-posts reads the username and concatenates it into a query, the malicious payload executes, allowing data exfiltration.

3. Malicious username payload:

' UNION SELECT password_hash, NULL, NULL FROM users WHERE username='admin' --

When the vulnerable query runs, it becomes:

SELECT post_id, content, created_at FROM posts WHERE author = '' UNION SELECT password_hash, NULL, NULL FROM users WHERE username='admin' --' ORDER BY created_at DESC

4. Secure fix (parameterized query):

query = "SELECT post_id, content, created_at FROM posts WHERE author = %s ORDER BY created_at DESC"
cursor.execute(query, (username,))

Exercise 5 – ORDER BY Clause Injection

(Solution omitted for brevity; apply the same pattern: validate/whitelist column names and use parameterized queries or safe mapping.)

Footnotes

  1. TalkTalk breach details – https://example.com/talktalk-breach ↩

  2. OWASP Top 10 2025 – https://owasp.org/Top10/2025/A05-Injection/ ↩ ↩2 ↩3 ↩4

  3. PortSwigger Web Security Academy – https://portswigger.net/web-security/sql-injection ↩

  4. OWASP SQL Injection Prevention Cheat Sheet – https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html ↩

Back to Blog

관련 글

더 보기 »