Prepared Statement에 대해

왜 ORDER BY $1 같은 Prepared Statement는 SQL 문법적으로 허용되지 않을까?

PostgreSQL을 비롯한 대부분의 관계형 데이터베이스에서는 Prepared Statement(준비된 쿼리) 를 사용할 때, 값(value) 에 대해서만 안전하게 바인딩할 수 있도록 설계되어 있음. 하지만 ORDER BY $1 처럼 컬럼명이나 SQL 키워드를 바인딩하려 하면 에러가 발생함.

❓ Prepared Statement는 왜 ORDER BY $1 같은 사용을 허용하지 않을까?

이 문제를 SQL 문법적, 보안적, 최적화적 이유를 중심으로 깊이 분석해보자.

---

🚀 1️⃣ Prepared Statement란?

Prepared Statement는 SQL Injection 방지와 성능 최적화를 위해 SQL 쿼리를 미리 준비해 두고, 실행 시점에 값만 바인딩하는 방식이다.

주요 특징:

• 문자열이 아닌 값(value)만 안전하게 바인딩 가능
• SQL 문 자체는 사전에 고정됨 → 실행 시점에 값만 바뀜
• 미리 컴파일된 실행 계획을 재사용 → 성능 최적화
• SQL Injection 방지

✅ 예제 (허용됨)

sql
복사편집
SELECT * FROM users WHERE id = $1;  -- ✅ "id" 값만 바인딩 (올바른 사용)

❌ 예제 (허용되지 않음)

sql
복사편집
SELECT * FROM users ORDER BY $1;  -- 🚨 컬럼명은 값(value)이 아니라 SQL 문법 요소이므로 허용되지 않음!

---

⚠ 2️⃣ ORDER BY $1가 허용되지 않는 이유

🔹 (1) SQL 문법적 이유: 컬럼명과 SQL 키워드는 값이 아니다!

PostgreSQL에서 Prepared Statement는 값(value)만 바인딩할 수 있다.

하지만 ORDER BY에는 컬럼명 또는 SQL 키워드(ASC/DESC) 가 들어가야 하는데, 이는 값이 아닌 SQL 문법 요소이다.

💡 PostgreSQL이 SQL 문법을 해석하는 방식

• 값(value): WHERE id = $1 (✔ 바인딩 가능)
• SQL 문법 요소(keyword, column): ORDER BY $1 (❌ 바인딩 불가능)

PostgreSQL의 SQL 파서(parser)는 ORDER BY에 들어가는 것은 "값"이 아니라 "식(expression)"으로 처리해야 한다고 인식한다.

하지만 Prepared Statement의 바인딩 변수($1, $2, ...) 는 값을 대체하는 역할을 하므로, SQL 문법적으로 허용되지 않는다.

---

🔹 (2) 보안적 이유: SQL Injection 방지

Prepared Statement의 핵심 목적 중 하나는 SQL Injection을 방지하는 것이다.

만약 ORDER BY $1 같은 바인딩이 가능했다면, 아래와 같은 보안 취약점이 발생할 수 있다.

❌ 만약 ORDER BY $1가 허용되었다면...

sql
복사편집
ORDER BY $1;

사용자가 $1에 DROP TABLE users; 같은 값을 입력하면:

sql
복사편집
ORDER BY DROP TABLE users;  -- 🚨 위험! 데이터 삭제 가능

→ SQL Injection 공격이 가능해짐!

이런 이유로, PostgreSQL은 ORDER BY $1 같은 형태를 문법적으로 허용하지 않도록 설계했다.

---

🔹 (3) 성능 최적화 이유: 실행 계획(Cache Plan) 재사용

Prepared Statement의 또 다른 핵심 목적은 SQL 실행 계획을 미리 최적화하여 재사용하는 것이다.

그러나 ORDER BY는 실행 계획에 영향을 미치므로, 실행 계획을 사전에 고정할 수 없다.

• Prepared Statement는 실행 계획을 캐싱 → 쿼리 실행 속도 향상
• ORDER BY에 변수를 바인딩하면 실행 계획이 매번 바뀜 → 캐싱 효과를 활용할 수 없음

✅ 올바른 Prepared Statement (캐싱 가능)

sql
복사편집
SELECT * FROM users WHERE id = $1;
-- 🟢 실행 계획을 한 번만 만들고 여러 번 재사용 가능!

❌ ORDER BY $1 (캐싱 불가능)

sql
복사편집
SELECT * FROM users ORDER BY $1;
-- 🔴 실행 계획이 매번 바뀌어야 함 → 성능 저하!

결과적으로, 실행 계획을 재사용할 수 없기 때문에 PostgreSQL은 ORDER BY에 대한 바인딩을 허용하지 않는다.

---

🔥 3️⃣ 다른 DBMS에서도 허용되지 않을까?

거의 대부분의 DBMS(Oracle, MySQL, SQL Server, etc.)에서도 ORDER BY에 대한 Prepared Statement 바인딩을 허용하지 않음.

하지만 몇몇 DBMS에서는 제한적인 방법으로 가능할 수도 있다.

✅ MySQL에서는 ORDER BY FIELD()를 사용하여 동적 정렬이 가능

sql
복사편집
SELECT * FROM users ORDER BY FIELD(role, ?, ?, ?);

• ?를 사용하여 동적으로 순서를 지정 가능 (하지만 컬럼명을 동적으로 지정하는 것은 불가능)

✅ SQL Server에서는 CASE 문을 사용하여 동적 정렬 가능

sql
복사편집
ORDER BY
  CASE WHEN @sort_by = 'name' THEN name
       WHEN @sort_by = 'age' THEN age
  END;

• 하지만 완벽한 해결책은 아니며, 여전히 ORDER BY $1 같은 방식은 허용되지 않음.

---

🚀 4️⃣ PostgreSQL에서 ORDER BY를 안전하게 동적으로 처리하는 방법

✅ 방법 1: TypeORM QueryBuilder 사용

typescript
복사편집
const allowedSortBy = ['like', 'play_count', 'genre'];
const sortBy = allowedSortBy.includes(dto.sort_by) ? dto.sort_by : 'play_count';
const sortOrder = dto.asce.toUpperCase() === 'DESC' ? 'DESC' : 'ASC';

const query = this.musicRepository
  .createQueryBuilder('music')
  .where('music.status = :status', { status: 'active' })
  .orderBy(`music.${sortBy}`, sortOrder)
  .getMany();

• 화이트리스트 방식으로 안전하게 동적 정렬 처리
• SQL Injection 방지

✅ 방법 2: 서버에서 동적으로 SQL 생성 (화이트리스트 방식)

typescript
복사편집
const allowedColumns = ['like', 'play_count', 'genre'];
const allowedOrders = ['ASC', 'DESC'];

const sortBy = allowedColumns.includes(dto.sort_by) ? dto.sort_by : 'play_count';
const sortOrder = allowedOrders.includes(dto.asce.toUpperCase()) ? dto.asce.toUpperCase() : 'DESC';

const query = `SELECT * FROM music WHERE status = 'active' ORDER BY ${sortBy} ${sortOrder};`;

• 보안성을 유지하면서도 SQL Injection을 방지하는 방법
• 쿼리를 동적으로 생성하되, 허용된 값만 사용

---

🎯 결론

• ✅ PostgreSQL에서 Prepared Statement는 값(value)만 바인딩할 수 있고, SQL 키워드(ORDER BY, 컬럼명 등)는 허용되지 않음.
• ✅ ORDER BY는 실행 계획(Cache Plan)에 영향을 주기 때문에 PostgreSQL이 허용하지 않도록 설계됨.
• ✅ SQL Injection 방지를 위해 SQL 문법적으로 ORDER BY에 대한 직접적인 바인딩을 금지함.
• ✅ PostgreSQL뿐만 아니라 대부분의 DBMS(Oracle, MySQL, SQL Server 등)에서도 ORDER BY $1 같은 사용법을 허용하지 않음.
• ✅ 해결 방법으로는 TypeORM QueryBuilder 사용 또는 서버 단에서 안전한 동적 SQL 생성(화이트리스트 방식) 을 사용해야 함.