축구팀 관리 프로젝트 22일차 - 중간발표 끝, SQL 인젝션 해결방안

축구팀 관리 프로젝트 22일차 - 중간발표 끝, SQL 인젝션 해결방안

 

 

 

프로젝트 중간발표를 마쳤습니다. 발표전 리허설과 함께 발표자 외에도 각자 구현한 기능에 대한 질문을 대응하기 위한 준비를 철저히 한 덕분에 발표 후 여러 질문에도 적절히 대답할 수 있었습니다. 간략한 발표 소개와 함께 필자가 작성한 부분의 지적사항 및 개선방안에 대해 작성해보겠습니다.

 

 

중간발표를 마치고

 

3주간 작업한 내용에 대해 발표를 마쳤습니다. 발표자는 팀원이 하였으나 질문시 본인이 맡은 파트의 질문이 오면 그에 대해 답을 하기로 했습니다. 신기하게도 팀원 전부 한가지씩 질문을 받게 되었고, 그에 대한 답안도 적절히 대응할 수 있었습니다.

 

 

 

 

SQL 인젝션 이슈 발생

필자는 typeORM에서 Raw  Query 작성시 SQL 인젝션 이슈를 발견하게 되어 이부분에 관한 트러블 슈팅을 발표내용에 넣었습니다. 필자는 아래 전술 화면에서 오른쪽 중앙에 '최근 3경기간 최다 누적 경고자'라는 항목을 추가하였습니다. 이 항목을 말 그대로 최근 3경기간 경고를 많이 받은 선수들을 보여주는 기능을 합니다. 

 

 

축구팀 관리 프로젝트 21일차 - 추천 알고리즘? 백엔드로 가기 위해선

전날 작업한 기록입니다

 

위 항목을 구현하기 위해 queryBuilder를 활용하여 쿼리를 작성하였는데, 기능 동작은 원활히 되지만 조회시 속도가 느렸던 불편함이 있었습니다. 

 

    /**
     * 최근 3경기간 최다 누적 경고자 조회
     * @param  teamId
     * @param  matchId
     * @returns
     */
    async getWarningmember(teamId: number) {

        // 먼저 최근 3개의 match_id를 조회합니다.
        const recentMatchIds = await this.dataSource
            .createQueryBuilder()
            .select("DISTINCT(match_id)", "match_id")
            .from("player_statistics", "ps")
            .where("ps.team_id = :teamId", { teamId })
            .orderBy("ps.match_id", "DESC")
            .limit(3)
            .getRawMany();

        // recentMatchIds에서 match_id만 추출합니다.
        const matchIds = recentMatchIds.map(match => match.match_id);

        // 추출한 matchIds를 사용하여 메인 쿼리를 실행합니다.
        const rawResults = await this.dataSource
            .createQueryBuilder()
            .select("member_id", "member_id")
            .addSelect("SUM(yellow_cards)", "yellowCards")
            .from(subQuery => {
                return subQuery
                    .select("member_id")
                    .addSelect("yellow_cards")
                    .from("player_statistics", "ps")
                    .where("ps.team_id = :teamId", { teamId: teamId })
                    .andWhere("ps.match_id IN (:...matchIds)", { matchIds: matchIds })
            }, "members")
            .groupBy("member_id")
            .having("SUM(yellow_cards) > 0")
            .orderBy("SUM(yellow_cards)", "DESC")
            .limit(3)
            .getRawMany();

        const result = await Promise.all(
            rawResults.map(async (member) => {
                const memberData =  await this.memberRepository.findOne({
                    relations:{
                        user: true
                    },
                    select : {
                        user:{
                            name: true
                        }
                    },where :{
                        id:member.member_id
                    }
                });
                return { ...member, memberData};
            }),
        );

        return result;
    }

 

이를 개선하기 위해 여러 방법을 강구하다가 typeORM에서 제공하는 dataSource.query를 활용하면 raw query를 직접 작성할 수 있다는것을 알게 되었습니다.

 

    /**
     * 최근 3경기간 최다 누적 경고자 조회
     * @param  teamId
     * @param  matchId
     * @returns
     */
    async getWarningmember(teamId: number) {

        const rawResults = await this.dataSource.query(`

            SELECT member_id, yellowCards
            FROM(
                SELECT member_id, SUM(yellow_cards) yellowCards
                FROM player_statistics
                WHERE team_id = ${teamId} AND match_id IN (
                SELECT match_id
                FROM (
                    SELECT DISTINCT match_id
                    FROM player_statistics
                    WHERE team_id = ${teamId}
                    ORDER BY match_id DESC
                    LIMIT 3
                ) AS subquery
                )
                GROUP BY member_id
                ORDER BY SUM(yellow_cards) DESC
            ) AS members
            WHERE yellowCards > 0
            LIMIT 3

        `);

        const result = await Promise.all(
            rawResults.map(async (member) => {
                const memberData =  await this.memberRepository.findOne({
                    relations:{
                        user: true
                    },
                    select : {
                        user:{
                            name: true
                        }
                    },where :{
                        id:member.member_id
                    }
                });
                return { ...member, memberData};
            }),
        );

        return result;
    }

 

dataSource.query를 사용하니 쿼리문을 직접 입력할 수 있었습니다. 하지만 위 코드에는 치명적인 위험성이 있었습니다. 바로 SQL 인젝션 이슈였습니다.

 

 

SQL 인젝션이란

SQL 인젝션이란 악의적 사용자가 웹 어플리케이션의 보안 취약점을 이용하여 데이터베이스에 임의의 SQL 코드를 실행하는 공격 기법을 말합니다.

 

예를 들어, 사용자 입력을 필터링 없이 SQL 쿼리에 직접 삽입할 때 발생합니다. 가령, 로그인 폼에서 ' OR '1'='1 같은 입력을 통해 인증 과정을 우회할 수 있습니다. 이런 공격으로 인해 민감한 정보가 유출되거나 데이터가 손상될 위험이 있습니다.

 

아래 쿼리는 users 테이블에서 username과 password를 입력받아 조회하는 과정을 담고 있습니다.

 

String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

 

여기서 username과 password는 사용자 입력을 통해 받아오는 값으로 공격자가 username에 ' OR '1'='1와 같은 값을 입력한다면, SQL 쿼리는 다음과 같이 변합니다.

 

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '...';

 

'1'='1'은 항상 참이 되므로, 이 쿼리는 비밀번호 검증 없이 모든 사용자 정보를 반환하게 됩니다. 이는 공격자가 시스템에 무단으로 접근할 수 있게 만듭니다.

 

 

SQL 인젝션 해결 방안

SQL 인젝션을 해결하는 가장 쉽고 강력한 방법은 사용자 입력을 직접 포함시키지 않고, 파라미터화된 쿼리(prepared statements)를 사용하는 것입니다.

 

아래처럼 사용자의 입력을 받을 쿼리 자리에 ? 를 달아 쿼리 밖에서 입력받은 값을 주입하여 SQL안에 악의적인 코드가 반영되지 않도록 하였습니다. 

    /**
     * 최근 3경기간 최다 누적 경고자 조회
     * @param  teamId
     * @param  matchId
     * @returns
     */
    async getWarningmember(teamId: number) {

        const rawResults = await this.dataSource.query(`

            SELECT member_id, yellowCards
            FROM(
                SELECT member_id, SUM(yellow_cards) yellowCards
                FROM player_statistics
                WHERE team_id = ? AND match_id IN (
                SELECT match_id
                FROM (
                    SELECT DISTINCT match_id
                    FROM player_statistics
                    WHERE team_id = ?
                    ORDER BY match_id DESC
                    LIMIT 3
                ) AS subquery
                )
                GROUP BY member_id
                ORDER BY SUM(yellow_cards) DESC
            ) AS members
            WHERE yellowCards > 0
            LIMIT 3

        `,[teamId,teamId]);

        const result = await Promise.all(
            rawResults.map(async (member) => {
                const memberData =  await this.memberRepository.findOne({
                    relations:{
                        user: true
                    },
                    select : {
                        user:{
                            name: true
                        }
                    },where :{
                        id:member.member_id
                    }
                });
                return { ...member, memberData};
            }),
        );

        return result;
    }

 

 

코드 변경 후 응답속도 개선

이를 통해 query builder에서 raw query로 안전하게 변경할 수 있었습니다. 코드의 가독성은 조금 떨어졌을 수도 있지만 변경 목적인 속도개선은 해낼 수 있었습니다. 속도 측정은 Thunder Client에 api 응답속도롤 하였으며 각각 5회씩 조회 후 그 시간을 측정하였습니다.

 

 

query builder에서 raw query로 작성시 응답속도를 비교한 결과는 다음과 같습니다.

 

 

Measurement	Query Builder	Raw Query
Run 1	1.58	0.833
Run 2	1.17	0.804
Run 3	1.17	0.807
Run 4	1.14	0.819
Run 5	1.12	1.090
Average	1.236	0.8706

단위 : s

 

그래프와 도표를 확인해보면 Query Builder 로 작성했을 때보다 Raw Query로 할 때 평균 응답속도가 약 0.4초(약 30% 속도개선) 빨라짐을 알 수 있습니다. 

 

이러한 개선사항을 토대로 중간발표 트러블 슈팅에 반영하였으며, 원활히 질문에 대해 답할 수 있었습니다. 작은 이슈일수도 있지만 직접해보면서 쿼리 최적화만으로도 응답속도를 상당수 개선시킬 수 있다는 걸을 볼 수 있었습니다. 프로젝트의 규모가 커질수록 이러한 해결방안은 더욱 유용히 쓰일 것이라 생각했습니다.

 

▼ 이전 진행한 프로젝트들 ▼

 

내일배움캠프 Node트랙 심화 프로젝트 역할 및 진행사항

 

내일배움캠프 백오피스 프로젝트 - 펫시터 매칭 사이트 후기, 소감