[Node.js] PostgreSQL에서 order by에 parameter를 넣고 싶다면

최근 Node.js와 PostgreSQL을 이용하여 프로젝트를 진행했다.

Node.js에서 PostgreSQL을 사용하는 방법은 여타 RDBMS를 사용하는 방법과 유사하다.

커넥션 풀을 생성하고, 해당 커넥션 풀에서 커넥션을 빌려오고, 빌려온 커넥션을 통해서 쿼리문을 수행하는 과정을 ES7의 async await를 이용하여 비동기식 프로그램의 순차 처리를 진행해서 데이터를 가져온다.

이때 커넥션 풀 생성 및 커넥션을 빌려오는 부분은 별도로 모듈화하여 사용하고 있었고, 쿼리문을 수행하는 과정만 코드로 확인해 보면, 다음과 같은 형식으로 활용했다.

const getLectures = async (client, lectureId) => {
  const { rows } = await client.query(
    `
    SELECT * from lecture
    WHERE id = $1
    `,
    [lectureId]
  );
  return convertSnakeToCamel.keysToCamel(rows[0]);
};

다음 코드는 lecture 테이블에서 id=${lectureId} 인 값을 가져오는 코드이다. 위 코드와 같이, 백틱(`)과 ${num} 형태를 이용하여 쿼리문을 작성하고 차후에 값을 넣어주는 방식을 Node.js 에서의 Prepared Statement라고 한다.

Prepared Statement란?

위키백과에서는 Prepared Statement를 다음과 같이 정의하고 있다.

Prepared Statement란
데이터베이스 관리 시스템에서 동일하거나 비슷한 데이터베이스 문을
효율적이고 반복적으로 실행하기 위해 사용하는 기능이다
(위키백과)

실제로 Prepared Statement는 쿼리문을 반복 사용하기 위해 사용되는 방식이었으나, 현재는 해당 용도 외에 보안적인 요소로 프로그래밍에 사용된다. 주로 SQL Injection 공격을 막기 위해 활용되는데, 방어가 가능한 원리는 아래 SK인포섹 블로그 게시글을 보면 자세히 설명되어 있다.

https://m.blog.naver.com/PostView.nhn?blogId=skinfosec2000&logNo=220482240245 

[SQL인젝션] Prepared Statement를 쓰면 SQL 인젝션 공격이 되지 않는 이유는?

우리는 SQL Injection 공격을 막기 위해 Prepared Statement를 활용한다는 점만 확인하고 넘어가자.

문제 발생

프로젝트를 진행하던 중, 문제점이 발생했다.

order by를 이용해서 정렬을 수행해야 하는데, 정렬을 위한 값들이 사용자에 의해 프론트에서 파라미터 형태로 들어오기 때문에, 평소와 같이 Prepared Statement를 활용하여 order by 문에 파라미터를 넣어 줬다.

const getLectures = async (client, lectureId, order) => {
  const { rows } = await client.query(
    `
    SELECT * from lecture
    WHERE id = $1
    order by $2
    `,
    [lectureId, order]
  );
  return convertSnakeToCamel.keysToCamel(rows[0]);
};

하지만, 쿼리문이 정상적으로 작동하지 않았고, order by 문이 없는 것처럼 동작되었다.

해당 문제점에 대해 확인해 본 결과,

• https://stackoverflow.com/questions/32425052/using-limit-order-by-with-pg-postgres-nodejs-as-a-parameter
• https://stackoverflow.com/questions/66164434/javascript-parameter-in-a-postgres-query-e-g-order-by-1-not-working-proper
• https://stackoverflow.com/questions/67344790/order-by-command-using-a-prepared-statement-parameter-pg-promise

이러한 스택오버플로우 게시글들을 확인할 수 있었다.

게시글들을 확인해 본 결과, 해당 문제점이 나에게만 발생하는 문제가 아니라는 것을 확인할 수 있었고, Prepared Statement를 사용하는 경우 쿼리의 파라미터로 컬럼 또는 테이블 이름을 넣을 수는 없다는 것을 확인할 수 있었고, 직접 적절한 order by 구문을 작성해 주는 방법밖에 없었다.

해당 문제를 해결하기 위해, pg-format이라는 npm 모듈을 활용하였다.

pg-format이란

pg-format이란 dynamic SQL Query를 안전하게 작성하기 위한 postgreSQL용 formatter로, 포맷을 identifier와 literal, string으로 구분하여 SQL Injection을 회피할 수 있게 해 준다. order by 구문의 경우 해당 컬럼의 값을 이용해서 직접적인 데이터 조회는 불가능하기 때문에 파라미터를 단순 string으로 넣어 줘도 괜찮은데, 이러한 상황을 위해 여러 가지 포맷 스트링을 이용하여 구분해서 데이터를 넣어 주는 모듈이다.

pg-format의 사용법은 기본적으로 npm 모듈 페이지에 잘 작성되어 있다. 해당 모듈의 사용법을 간단히 정리해 보았다.

포맷의 기본값으로, %I, %L, %s 3가지의 포맷이 존재한다.

%I : SQL Identifier - 식별자, 개체의 이름으로 활용되는 포맷(컬럼명, 테이블명)

%L : SQL Literal - 리터럴, Dynamic SQL에서 변수 형태로 활용되는 포맷(WHERE문의 조건)

%s : simple string - 일반적인 String 값에 활용되는 포맷(LIMIT 조건 등)

기본 사용법

const format = require('pg-format');
const getLectures = async (client, lectureId, order) => {
  const { rows } = await client.query(
    format(
      `
      SELECT * from lecture
      WHERE id = %L
      order by %I
      `,
      lectureId, order
    )
  );
  return convertSnakeToCamel.keysToCamel(rows[0]);
};

위의 코드 형태처럼, 기존에 Prepared Statement의 ${num} 부분에 적절한 포맷 스트링을 넣어 주고, 뒤에 순서대로 각 포맷에 해당하는 값들을 넣어 주면 된다. 다른 언어에서의 포맷 스트링 사용법과 유사하다.

보다 자세한 사용법은 https://www.npmjs.com/package/pg-format 을 참고하면 된다.

pg-format

 

참고문헌

• https://stackoverflow.com/questions/32425052/using-limit-order-by-with-pg-postgres-nodejs-as-a-parameter
• https://stackoverflow.com/questions/66164434/javascript-parameter-in-a-postgres-query-e-g-order-by-1-not-working-proper
• https://stackoverflow.com/questions/67344790/order-by-command-using-a-prepared-statement-parameter-pg-promise
• https://m.blog.naver.com/PostView.nhn?blogId=skinfosec2000&logNo=220482240245
• https://www.npmjs.com/package/pg-format