6.5Security Around PostgreSQL

PostgreSQL API 보안: SQL Injection 방어와 Prepared Statement

지난번 포스팅에서 우리가 작성한 코드가 안전하지 않고, 보안에 취약하다고 했는데 이번 포스팅에선 보안 문제에 대해 알아보자.

SQL Injection

아래와 같은 쿼리를 request.http 파일에 적고 send request로 요청을 보내보자.

text
http://localhost:3005/users/1;DROP TABLE users;

별로 효과가 있어 보이지 않는 쿼리이고, 실행하면 에러가 반환된다. 하지만 이 쿼리는 실제로 users 테이블을 삭제한다!

어떻게 이런 일이 일어난걸까? 우선, http://localhost:3005/users/1은 우리 쿼리에 추가가 되어, SELECT * FROM users WHERE id = 1처럼 작동한다. 즉, 위의 http request는 아래와 같은 sql 쿼리를 실행한 것이다.

sql
SELECT * FROM users WHERE id = 1;
DROP TABLE users;

이런 식으로 유저가 입력한 값이 이어붙은 쿼리가 데이터베이스에서 직접 실행되는 것을 SQL Injection이라고 한다. 따라서, 유저가 작성한 인풋을 sql 쿼리에 붙이는 코드는 절대로 작성해선 안된다. 예를 들면, 지난번 포스팅에서 본 아래와 같은 코드다.

javascript
static async findById(id) {
    // WARNING: 큰 보안 문제 있음!! 다음 포스팅에서 수정 예정
    const { rows } = await pool.query(`
      SELECT * FROM users
      WHERE id = ${id};
      `);
    return toCamelCase(rows)[0];
  }

Prepared Statements로 SQL 주입 공격 막기

두 가지 방법으로 SQL 주입을 막을 수 있다.

1. Postgres가 자체적으로 유저 인풋값을 새니타이즈(sanitize)할 수 있도록 한다.

sanitize란 "소독하다"라는 의미로, 프로그래밍에선 데이터를 안전하게 만드는 과정을 뜻하는 용어다.

• 'prepared' statement

sql
PREPARE asdfasdf (string) as
  SELECT * 
  FROM users
  WHERE id = $1;

• execute the prepared statement

sql
EXECUTE asdfasdf('127');

다만, 이 방식으로는 특정 컬럼을 고른다거나, 특정 테이블을 고른다거나 하는 것은 불가능하다. 오직 값만 변경할 수 있다.

2. 코드를 추가해 유저가 입력한 값을 sanitize 한다.

pg 모듈에서는 $1, $2 등으로 placeholder 값을 입력하고, 이후 Array 안에 해당 값을 넣으면 자동으로 PREPARE 쿼리를 보낸다.

javascript
static async findById(id) {
    const { rows } = await pool.query(
      `
      SELECT * FROM users
      WHERE id = $1;
      `,
      [id]
    );
    return toCamelCase(rows)[0];
  }

위의 코드를 작동시키기 위해, pool.js에서 쿼리를 받을 때 두번째 파라미터로 params을 받게 코드를 수정한다.

javascript
query(sql, params) {
    return this._pool.query(sql, params);
  }

유저 추가하기(POST 요청 보내기)

users.js에 아래와 같이 post 메소드 코드를 추가한다.

javascript
router.post("/users", async (req, res) => {
  const { username, bio } = req.body;
  const user = await UserRepo.insert( username, bio );
  res.send(user);
});

user-repo.js에는 UserRepo.insert실행 시 작동할 코드를 작성한다. 보통, 이렇게 데이터를 추가하고 나면 해당 데이터를 다시 받아와서 유저에게 보여주는 등의 작업을 해야하는데 이를 위해 RETURNING 키워드를 사용한다.

javascript
static async insert(username, bio) {
    const { rows } = await pool.query(
      `
      INSERT INTO users (username, bio)
      VALUES ($1, $2)
      RETURNING *;
      `,
      [username, bio]
    );
    return toCamelCase(rows)[0];
  }

정보 업데이트하기(PUT 요청)

users.js를 업데이트한다.

javascript
router.put("/users/:id", async (req, res) => {
  const { id } = req.params;
  const { username, bio } = req.body;

  const user = await UserRepo.update(id, username, bio);
  if (user) {
    res.send(user);
  } else {
    res.sendStatus(404);
  }
});

UserRepo.update 작동을 위해 user-repo.js에도 코드를 추가한다.

javascript
static async update(id, username, bio) {
    const { rows } = await pool.query(
      `
      UPDATE users
      SET username = $1, bio = $2
      WHERE id = $3
      RETURNING *;
      `,
      [username, bio, id]
    );
    return toCamelCase(rows)[0];
  }

정보 지우기(DELETE 요청)

users.js에 코드를 추가한다.

javascript
router.delete("/users/:id", async (req, res) => {
  const { id } = req.params;

  const user = await UserRepo.delete(id);

  if (user) {
    res.send(user);
  } else {
    res.sendStatus(404);
  }})

앞선 다른 메소드들과 마찬가지로, user-repo.js도 업데이트한다.

javascript
static async delete(id) {
    const { rows } = await pool.query(
      `
      DELETE FROM users
      WHERE id = $1
      RETURNING *;
      `,
      [id]
    );
    return toCamelCase(rows)[0];
  }