🛡️

SQL Injection Hardening - Prepared Statements

Prepared Statements

Prepared Statements 직역하면 ‘사전 선언’으로 는 대부분의 관계형 데이터베이스 시스템에서 사용 가능한 방식으로, SQL Injection을 방지하기 위해 매개변수화된 쿼리를 사용하여 쿼리 문자열과 데이터를 분리하는 방식이다.

기본적으로 인자 형식과 길이에 대해 검증을 하고, 아래 링크와 같이 SQL Escape 함수를 사용하는 등의 방식으로 막을 수도 있지만 SQL Injection을 공격하는 방식은 무척이나 다양하기 때문에 SQL 구조적으로 예방하는 것이 가장 좋다.

SQL Injection Hardening - SQL Escape Function

Statements vs Prepared Statements

기존 방식 (Dynamic SQL Statements):

•

클라이언트는 SQL 쿼리 문자열을 만든다. 이때 사용자 입력이 직접 쿼리 문자열에 포함된다.

•

완성된 쿼리 문자열이 데이터베이스 서버로 전송된다.

•

데이터베이스 서버에서 쿼리 문자열을 파싱, 최적화, 컴파일한 후 실행한다.

이 과정에서 사용자 입력이 쿼리 문자열에 직접 포함되기 때문에 공격자가 SQL 구문을 조작해 SQL Injection을 수행하게 된다.

Prepared Statements 방식:

•

클라이언트는 두 부분(쿼리 템플릿과 변수 값)으로 나누어진 쿼리를 생성한다. 

•

쿼리 템플릿이 데이터베이스 서버로 전송된다.

•

데이터베이스 서버에서 쿼리 템플릿을 파싱, 최적화, 컴파일하고, 실행 계획을 저장해 놓는다.

•

클라이언트는 변수 값(사용자 입력)을 데이터베이스 서버로 전송한다.

•

데이터베이스 서버에서 저장된 실행 계획에 변수 값을 바인딩한 후, 쿼리를 실행한다.

Prepared Statements 방식에서는 쿼리 템플릿과 변수 값이 분리되어 전송되므로, 사용자 입력이 SQL 구문에 직접 포함되지 않아 SQL 문법으로 처리되지 않기에 SQL Injection을 효과적으로 방지할 수 있다.

DB별 사용 방법

예를 들면 SQL문 안에 ? (물음표)를 사용하여 이 물음표와 인자를 매칭시키는 Placeholder 방식을 통해 바인딩 변수를 넘겨주는 구조로 이루어진다.

간단히 보면 다음과 같다.

PHP

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);
PHP
복사

Python

cursor.execute('SELECT * FROM users WHERE username = %s AND password = %s', (username, password))
Python
복사

Java

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE username = ? AND password = ?");
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
Java
복사

Ruby

stmt = conn.prepare('SELECT * FROM users WHERE username = ? AND password = ?')
stmt.execute(username, password)
Ruby
복사

Node.js

db.query('SELECT * FROM users WHERE username = ? AND password = ?', [username, password], (err, result) => {
  // handle result or error
});
JavaScript
복사

이를 다음 4개 DB에 대해 Python, JS, PHP 방식에 대해 정리했다.

대부분 import 방식만 다를 뿐 거의 동일한 형태를 띈다.

•

MySQL

•

postgresql

•

oracle

•

mariadb

1. MySQL

python

import MySQLdb

conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db)
cursor = conn.cursor()

query = "SELECT * FROM mytable WHERE column1 = %s AND column2 = %s"
params = ("value1", "value2")
cursor.execute(query, params)

result = cursor.fetchall()
Python
복사

javascript(node.js)

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

const query = 'SELECT * FROM mytable WHERE column1 = ? AND column2 = ?';
const values = ['value1', 'value2'];

connection.query(query, values, function (error, results, fields) {
  if (error) throw error;
  console.log('The result is: ', results);
});
JavaScript
복사

PHP

PHP에서 MySQL Prepared Statements를 작성하기 위해서는 mysqli또는 PDO클래스를 사용한다

대체로 PDO 클래스가 대부분의 DB에서 사용되기 때문에 PDO를 사용하는 것이 좋다.

mysqli

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Prepare statement
$stmt = mysqli_prepare($conn, "SELECT * FROM mytable WHERE column1 = ? AND column2 = ?");

// Bind parameters
mysqli_stmt_bind_param($stmt, "ss", $value1, $value2);

// Set parameters and execute
$value1 = "value1";
$value2 = "value2";
mysqli_stmt_execute($stmt);

// Fetch results
$result = mysqli_stmt_get_result($stmt);
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
PHP
복사

PDO (PHP Data Objects)

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM mytable WHERE column1 = :value1 AND column2 = :value2");

// Bind parameters
$stmt->bindParam(':value1', $value1);
$stmt->bindParam(':value2', $value2);

// Set parameters and execute
$value1 = 'value1';
$value2 = 'value2';
$stmt->execute();

// Fetch results
$result = $stmt->fetchAll();
PHP
복사

2. PostgreSQL

python

import psycopg2

conn = psycopg2.connect(host=host, user=user, password=password, dbname=dbname)
cursor = conn.cursor()

query = "SELECT * FROM mytable WHERE column1 = %s AND column2 = %s"
params = ("value1", "value2")
cursor.execute(query, params)

result = cursor.fetchall()
Python
복사

javascript(node.js)

const { Pool } = require('pg');
const pool = new Pool({
  user: 'username',
  host: 'localhost',
  database: 'mydb',
  password: 'password',
  port: 5432
});

const query = {
  text: 'SELECT * FROM mytable WHERE column1 = $1 AND column2 = $2',
  values: ['value1', 'value2'],
};

pool.query(query, (err, res) => {
  if (err) {
    console.error(err);
    return;
  }
  console.log(res.rows);
  pool.end();
});
JavaScript
복사

PHP

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// Create connection
$conn = new PDO("pgsql:host=$servername;dbname=$dbname", $username, $password);

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM mytable WHERE column1 = :value1 AND column2 = :value2");

// Bind parameters
$stmt->bindParam(':value1', $value1);
$stmt->bindParam(':value2', $value2);

// Set parameters and execute
$value1 = 'value1';
$value2 = 'value2';
$stmt->execute();

// Fetch results
$result = $stmt->fetchAll();
PHP
복사

3. Oracle

python

import cx_Oracle

conn = cx_Oracle.connect(user, password, dsn)
cursor = conn.cursor()

query = "SELECT * FROM mytable WHERE column1 = :value1 AND column2 = :value2"
params = {"value1": "value1", "value2": "value2"}
cursor.execute(query, params)

result = cursor.fetchall()
Python
복사

javascript(node.js)

const oracledb = require('oracledb');

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection({
      user: 'hr',
      password: 'welcome',
      connectString: 'localhost/orcl'
    });

    const result = await connection.execute(
      `SELECT * FROM mytable WHERE column1 = :value1 AND column2 = :value2`,
      ['value1', 'value2']
    );

    console.log(result.rows);
  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();
JavaScript
복사

PHP

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// Create connection
$conn = new PDO("oci:host=$servername;dbname=$dbname", $username, $password);

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM mytable WHERE column1 = :value1 AND column2 = :value2");

// Bind parameters
$stmt->bindParam(':value1', $value1);
$stmt->bindParam(':value2', $value2);

// Set parameters and execute
$value1 = "value1";
$value2 = "value2";
$stmt->execute();

// Fetch results
$result = $stmt->fetchAll();
PHP
복사

4. MariaDB

python

import mysql.connector

cnx = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="mydb"
)

cursor = cnx.cursor(prepared=True)
stmt = "SELECT * FROM mytable WHERE column1 = ? AND column2 = ?"
params = ("value1", "value2")
cursor.execute(stmt, params)

result = cursor.fetchall()
for row in result:
  print(row)

cursor.close()
cnx.close()
Python
복사

javascript(node.js)

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'username',
  password: 'password',
  database: 'mydb'
});

const query = 'SELECT * FROM mytable WHERE column1 = ? AND column2 = ?';
const values = ['value1', 'value2'];

connection.query(query, values, function (error, results, fields) {
  if (error) throw error;
  console.log('The result is: ', results);
});
JavaScript
복사

PHP

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

// Create connection
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// Prepare statement
$stmt = $conn->prepare("SELECT * FROM mytable WHERE column1 = ? AND column2 = ?");

// Bind parameters
$stmt->bindParam(1, $value1);
$stmt->bindParam(2, $value2);

// Set parameters and execute
$value1 = "value1";
$value2 = "value2";
$stmt->execute();

// Fetch results
$result = $stmt->fetchAll();
PHP
복사