[UMC] SQL Query

1. SQL - 두 개의 정렬 기준과 페이지네이션을 구현하는 쿼리

이번 주 첫번째 미션은 저번주에 설계한 DB에서 다음과 같은 기능을 구현하는 것이다.

진행 중, 진행 완료한 미션 모아서 보는 쿼리에서 정렬 기준을 1순위를 포인트, 2순위는 최신순으로 하여 Cursor 기반 페이지네이션을 구현하라.

 

일단 저번주에 설계한 DB를 살펴보자.

진행 중, 진행 완료를 표현하기 위해 필요한 정보를 테이블 단위로 정리해보자.

 

1. Mission 테이블

• 미션의 내용
• 미션의 status(완료, 미완료 상태 구별)
• 미션의 포인트 내역

2. Shop 테이블

• 상점의 이름

3. User 테이블

• 유저의 ID(검색 기준)

이렇게 총 2개의 테이블에서 정보를 얻어와야 한다. 바로 SQL로 구현해보자.

 

SELECT
    m.content,
    m.point,
    m.status,
    s.shop_name
FROM mission AS m
JOIN shop AS s
ON m.shop_id = s.id
JOIN user AS u
ON m.user_id = u.id
WHERE u.id = 1
AND m.status = 2 #1=미수락, 2=진행중, 3=완료
AND m.id > 1
ORDER BY m.point DESC, m.created_at DESC
LIMIT 10;

 

미션 테이블을 기준으로 shop 테이블과 user 테이블을 JOIN하여 필요한 정보를 얻어온다. 하지만 이 쿼리문에는 오류가 존재한다.

 

커서 기반 페이지네이션을 구현할 때에는 기준이 '마지막으로 조회된 레코드의 정보'가 되어야 한다. 지금의 쿼리문은 mission테이블의 id를 기준으로 페이지네이션을 구현하였고, 일반적인 조회 쿼리였다면 아마 정상적으로 작동되었을 것이다.

 

하지만 현재의 쿼리문은 두 개의 기준으로 정렬을 한 상태이다. 하나는 포인트이고, 다른 하나는 최신순이다. 따라서 조회할때, id를 기준으로 한 것이 아닌 포인트와 최신순이라는 기준으로 커서를 지정해주어야 할 것이다.

 

이에 따라 위의 쿼리문을 수정해보자.

 

SELECT
    m.content,
    m.point,
    m.status,
    s.shop_name
FROM mission AS m
JOIN shop AS s
ON m.shop_id = s.id
JOIN user AS u
ON m.user_id = u.id
WHERE u.id = 1
AND m.status = 2 #1=미수락, 2=진행중, 3=완료
AND (m.point < 50 OR (m.point = 50 AND m.created_at < '2025-03-30 22:54:00'))
ORDER BY m.point DESC, m.created_at DESC
LIMIT 10;

 

m.id > 1 이라는 기준을 없애고, 내림차순으로 정렬했으니 기준 포인트인 50을 기준으로 더 낮은 값을 조회하고, 같은 포인트일 때는 더 나중에 생성된 것을 기준으로 조회를 하고 있다. LIMIT은 10으로하여 한번에 10개씩 불러온다.

---

2. SQL Injection

SQL Injection은 웹 페이지의 기본 일반 입력 또는 입력 양식 필드에 SQL 쿼리를 삽입하여 애플리케이션의 데이터베이스에 접근하는 사이버 공격이다. 로그인과 같은 인증 프로세스를 구현할 때 SQL query문을 사용할텐데, 이때 입력창에서 유효성 검사를 수행하지 않으면 프로그램에서 입력을 잘못 해석하고 SQL문으로 그대로 흘러갈 수 있다.

 

예를 들어 SELECT * FROM user WHERE username = "user"라는 쿼리문이 있다고 가정하자. 여기에 SQL injection 공격을 하면 웹페이지 입력창에 특정 사용자의 이름" OR "1"="1과 같은 쿼리를 주입할 수 있다. 그렇게 된다면 쿼리문은 다음과 같이 변하게 된다.

SELECT * FROM user WHERE username = "username" OR "1"="1";

 

이렇게 된다면, 사용자 이름의 레코드가 아닌 "1"="1"이라는 조건을 만족하는 쿼리문이 되어버리므로 모든 user의 레코드를 반환하게 된다.

 

SQL Injection의 유형

• 대역 내 SQLi : 대역 내 공격은 http 요청과 같은 매체를 사용하여 공격을 수행하고 결과를 수집한다. 공격 대상 데이터베이스를 상대로 오류 메시지를 생성하려는 오류기반 SQLi 공격과 SQL UNION 연산자를 사용하여 select문을 병합하는 통합 기반 SQLi로 구분된다.
• 블라인드 SQLi : 블라인드 공격에서는 서버에서 데이터를 받지 않고 서버의 동작에 따라 공격을 한다. 입력이 다르면 작업의 결과에 성공, 실패, 딜레이를 주는 등 여러 기능을 먹통으로 만들 수 있다.
• 대역 외 SQLi :  데이터베이스 서버에서 사용자 이름 및 암호와 같은 데이터를 얻을 수 있도록 DNS 또는 HTTP 요청을 생성하게 유도한다.

SQL Injection 방지하기

가장 대표적인 방법은  SQL 쿼리에 유저가 요청하는 데이터를 포함하기 전에 입력 유효성 검사를 실행하는 것이다. 많은 SQL 주입 공격은 입력칸에 작은따옴표나 큰따옴표를 작성할 수 없게 필터링을 하면 방지할 수 있다. 하지만 모든 SQL 주입을 막을 수는 없다.

따라서 웹 애플리케이션 방화벽 (WAF) 또는 웹 애플리케이션 및 API 보호 (WAAP) 기능을 사용하는 것이 바람직하다. SQL 쿼리를 사용하는 웹 애플리케이션과 API에 SQL 주입 공격을 하려는 악의적인 요청을 식별하고 차단해준다.

---

3. TABLE JOIN

 

Inner JOIN

두 테이블을 연결할 때 가장 많이 사용하는 내부 조인이다. 생략해서 그냥 조인이라고만 해도 된다. 두 테이블에 모두 데이터가 있어야 결과가 나온다.

SELECT *
FROM table1
    INNER JOIN table2
    ON table1.id = table2.id

 

Outer JOIN

내부 조인과는 다르게 한쪽에만 데이터가 있어도 결과가 출력된다.

• LEFT OUTER JOIN : 왼쪽 테이블의 모든 값이 출력되는 조인
• RIGHT OUTER JOIN : 오른쪽 테이블의 모든 값이 출력되는 조인
• FULL OUTER JOIN : 왼쪽 조인과 오른쪽 조인이 합쳐진 것

SELECT *
FROM table1
    (LEFT RIGHT FULL) JOIN table2
     ON table1.id = table2.id

 

Cross JOIN

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인한다. 따라서 전체 행 개수는 두 테이블의 모든 행의 개수를 곱한 값이다. 카테시안 곱이라고도 한다.

SELECT *
FROM table1, table2

 

Self JOIN

셀프 조인은 자기 자신과 조인하므로 1개의 테이블에서만 이루어진다. 보통 추천인 기능과 같은 곳에서 유저 테이블을 스스로 참조할 때 사용된다.

SELECT *
FROM table1
	INNER JOIN table1

---

느낀 점

이번 주 미션은 데이터베이스를 다룰 수 있는 DBMS 중 대표적인 MySQL의 쿼리문을 직접 실습해보는 시간을 가졌다. 7기 미션 때에는 SQL 문법을 제대로 다뤄본적 없이 실습해서 굉장히 낯설고 다른 언어들과는 달리 이질감이 드는 느낌이 들었따. 현재 3학년에 들어서고 데이터베이스 수업과 병행하며 SQL을 공부해보니, 굉장히 활용도가 높고 어떻게 사용하느냐에 따라 내가 만든 API와 데이터베이스의 최적화와 성능이 결정되겠다는 것을 깨달았다. 특히 prisma와 같은 orm과는 달리 제한된 기능이 없고, 조금 불편하게 긴 쿼리문이 필요할지라도 여러 테이블을 조인하고 여러 조건을 최적화 시키는 대에는 raw query문이 필요할 것이다.