Prepared Statements
Prepared Statements 직역하면 ‘사전 선언’으로 는 대부분의 관계형 데이터베이스 시스템에서 사용 가능한 방식으로, SQL Injection을 방지하기 위해 매개변수화된 쿼리를 사용하여 쿼리 문자열과 데이터를 분리하는 방식이다.
기본적으로 인자 형식과 길이에 대해 검증을 하고, 아래 링크와 같이 SQL Escape 함수를 사용하는 등의 방식으로 막을 수도 있지만 SQL Injection을 공격하는 방식은 무척이나 다양하기 때문에 SQL 구조적으로 예방하는 것이 가장 좋다.
Statements vs Prepared Statements
기존 방식 (Dynamic SQL Statements):
•
클라이언트는 SQL 쿼리 문자열을 만든다. 이때 사용자 입력이 직접 쿼리 문자열에 포함된다.
•
완성된 쿼리 문자열이 데이터베이스 서버로 전송된다.
•
데이터베이스 서버에서 쿼리 문자열을 파싱, 최적화, 컴파일한 후 실행한다.
이 과정에서 사용자 입력이 쿼리 문자열에 직접 포함되기 때문에 공격자가 SQL 구문을 조작해 SQL Injection을 수행하게 된다.
Prepared Statements 방식:
•
클라이언트는 두 부분(쿼리 템플릿과 변수 값)으로 나누어진 쿼리를 생성한다.
•
쿼리 템플릿이 데이터베이스 서버로 전송된다.
•
데이터베이스 서버에서 쿼리 템플릿을 파싱, 최적화, 컴파일하고, 실행 계획을 저장해 놓는다.
•
클라이언트는 변수 값(사용자 입력)을 데이터베이스 서버로 전송한다.
•
데이터베이스 서버에서 저장된 실행 계획에 변수 값을 바인딩한 후, 쿼리를 실행한다.
Prepared Statements 방식에서는 쿼리 템플릿과 변수 값이 분리되어 전송되므로, 사용자 입력이 SQL 구문에 직접 포함되지 않아 SQL 문법으로 처리되지 않기에 SQL Injection을 효과적으로 방지할 수 있다.
DB별 사용 방법
예를 들면 SQL문 안에 ? (물음표)를 사용하여 이 물음표와 인자를 매칭시키는 Placeholder 방식을 통해 바인딩 변수를 넘겨주는 구조로 이루어진다.
간단히 보면 다음과 같다.
1.
PHP
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = ? AND password = ?');
$stmt->execute([$username, $password]);
PHP
복사
2.
Python
cursor.execute('SELECT * FROM users WHERE username = %s AND password = %s', (username, password))
Python
복사
3.
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
복사
4.
Ruby
stmt = conn.prepare('SELECT * FROM users WHERE username = ? AND password = ?')
stmt.execute(username, password)
Ruby
복사
5.
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
복사
