Mission 1. Cursor기반 페이지네이션 구현

• 다음과 같은 내가 진행중, 진행 완료한 미션 모아서 보는 쿼리(페이징 포함)에서 정렬 기준을 1순위는 포인트로 2순위는 최신순으로 하여 Cursor기반 페이지네이션을 구현해야한다.

Solution

• [미션의 포인트, 지불할 금액, 미션이 주어진 가게 이름의 정보]를 [포인트, 미션의 생성 시간(유저 기준)]을 기준으로 정렬하여 사용하기 위해 left join 문을 사용했습니다.
• where 조건을 통해 user_mission 테이블에서 해당 사용자의 id를 가진 mission을 선택하고, 진행중/진행완료 중 어떤 미션을 선택할지 정한 후 left join을 사용해 목표 정보를 가져옵니다.
• 이후 point, created_time 컬럼을 기준으로 페이지네이션을 하기 위해 point, created_time을 concat, cursor를 제작하고 직전 cursor의 값을 기준으로 이후의 index를 limit로 조절한 개수만큼 보여줍니다.

Code

#목적 : 미션의 포인트, 지불할 금액, 미션이 주어진 가게 이름의 정보를 포인트, 미션의 생성 시간(유저 기준)을 기준으로 정렬하여 사용
SELECT
	m.point, 
    m.payment, 
    s.name, 
    um.created_time,
    concat(LPAD(m.point, 10, '0'), DATE_FORMAT(um.created_time, '%Y%m%d%H%i%s')) AS cursor
from 
	user_mission as um
join 
	mission as m on um.mission_id = m.id
join 
	store as s on m.store_id = s.id
where 
    CONCAT(LPAD(m.point, 10, '0'), '-', DATE_FORMAT(um.created_time, '%Y%m%d%H%i%s')) < '이전 커서값' #최근의 미션을 cursor로 새로 페이징
and
	um.user_id = 3 #사용자의 아이디
and 
	um.is_completed = 1  # 미션의 진행중/진행완료 여부 (0 or 1)
order by
	cursor DESC #cursor값을 사용해 정렬 (1. 포인트, 2. 최근 순 정렬)
limit 10; #한번에 보여질 개수

Mission 2. SQL Injection이란?

SQL Injection

• SQL 인젝션은 사이버 범죄자가 사용자 이름 또는 암호와 같은 일반 입력 또는 양식 필드에 SQL 쿼리를 삽입하여 애플리케이션 코드의 취약성을 악용하려고 시도하는 공격 유형으로, 데이터베이스에 대한 무단 접근을 얻거나 데이터베이스에서 직접 정보를 검색할 수 있습니다.

Example

• 예를 들어 다음과 같은 로그인 sql문이 있습니다.

"SELECT Count(*) FROM Users WHERE Username=' " + txt.User.Text+" ' AND Password=' "+ txt.Password.Text+" ' ";

• 이때 해커가 다음과 같은 값을 입력하면(Magic String이라고 불립니다)

  사용자 이름: admin

  비밀번호: anything 'or'1'='1

"SELECT Count(*) FROM Users WHERE Username=' admin ' AND Password=' anything 'or'1'='1 ' ";

• Passward의 value가 True가 되어 인증을 우회할 수 있게 되고, 전체 데이터베이스가 손상될 수 있습니다.
• 이를 막기 위해 다음과 같이 변경하는 방법이 있습니다.

$id = $_GET['id']

(1) $id = Stripslashes($id)

(2) $id = mysql_real_escape_String($id)

• (1)로 인해 입력 문자열의 각 작은 따옴표(')가 큰따옴표(")로 바뀌고, (2)로 인해 모든 작은 따옴표(') 앞에 (/)가 추가됩니다.

Solution

이처럼 다양한 SQL 인젝션이 존재하는데,

• 대역 내 SQLi
• 추론(블라인드) SQLi
• 대역 외 SQLi

등의 인젝션이 존재하고,

방어의 방법으로는

• 입력 기능을 제한
• 필터링 및 검증
• SQL 명령 범위 제한
• 안전하지 않은 URL 매개 변수 방지

등이 존재합니다.

Mission 3. 다양한 JOIN 방법들

Solution

• INNER JOIN(내부 조인) : 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.

SELECT <열 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인 조건>
[WHERE 검색 조건]

• OUTER JOIN(외부 조인) : 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.

SELECT <열 목록>
FROM <첫 번째 테이블(LEFT 테이블)>
    <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)>
     ON <조인 조건>
[WHERE 검색 조건]

• CROSS JOIN(상호 조인) : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.

SELECT *
FROM <첫 번째 테이블>
    CROSS JOIN <두 번째 테이블>

• SELF JOIN(자체 조인) : 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

SELECT <열 목록>
FROM <테이블> 별칭A
    INNER JOIN <테이블> 별칭B
[WHERE 검색 조건]