PHP MySQL Prepared
PHP MySQL Prepared Statements : 파라미터화된 쿼리로 안전한 데이터 처리
- *Prepared Statements(준비된 쿼리)**는 SQL 인젝션과 같은 보안 취약점을 방지하기 위해 PHP와 MySQL에서 데이터를 안전하게 처리하는 방법입니다. Prepared Statements는 데이터베이스 쿼리를 미리 준비하고, 실행할 때 파라미터를 바인딩하는 방식으로 SQL 쿼리와 데이터를 분리하여 보안 강화와 성능 최적화를 동시에 제공합니다.
이 가이드에서는 **Prepared Statements(준비된 쿼리)**의 개념과, 이를 MySQLi와 PDO에서 구현하는 방법, 그리고 보안적인 고려 사항을 설명합니다.
1. Prepared Statements의 개념
Prepared Statements는 쿼리 구조를 먼저 데이터베이스에 보내고, 값을 나중에 바인딩하여 쿼리를 실행하는 방식입니다. 이를 통해 쿼리 자체에 포함된 데이터 값이 실행 중에 직접적으로 영향을 미치지 않기 때문에, SQL 인젝션 공격을 효과적으로 방지할 수 있습니다.
Prepared Statements는 다음 두 단계로 나뉩니다:
- 쿼리 준비: SQL 쿼리의 구조만 서버에 보내고 준비합니다.
- 파라미터 바인딩 및 실행: 준비된 쿼리에 값을 바인딩하고 쿼리를 실행합니다.
2. MySQLi를 사용한 Prepared Statements
2.1 객체 지향 방식으로 Prepared Statements
<?php
// MySQL 데이터베이스 연결 정보
$host = "localhost";
$username = "root";
$password = "";
$database = "my_database";
// MySQL 데이터베이스 연결
$conn = new mysqli($host, $username, $password, $database);
// 연결 확인
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepared Statement 준비
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
// 파라미터 바인딩
$stmt->bind_param("ss", $username, $email);
// 사용자 입력 값
$username = "JohnDoe";
$email = "john@example.com";
// 쿼리 실행
$stmt->execute();
echo "New record created successfully";
// 연결 종료
$stmt->close();
$conn->close();
?>
설명:
$conn->prepare(): SQL 쿼리를 준비합니다. **?*로 표현된 부분은 나중에 파라미터로 바인딩될 자리 표시자입니다.$stmt->bind_param(): 준비된 쿼리에 값을 바인딩합니다. 여기서 **ss*는 두 개의 문자열(s)을 의미합니다.$stmt->execute(): 쿼리를 실행하여 데이터를 삽입합니다.
2.2 절차적 방식으로 Prepared Statements
<?php
// MySQL 데이터베이스 연결 정보
$host = "localhost";
$username = "root";
$password = "";
$database = "my_database";
// MySQL 데이터베이스 연결
$conn = mysqli_connect($host, $username, $password, $database);
// 연결 확인
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Prepared Statement 준비
$stmt = mysqli_prepare($conn, "INSERT INTO users (username, email) VALUES (?, ?)");
// 파라미터 바인딩
mysqli_stmt_bind_param($stmt, "ss", $username, $email);
// 사용자 입력 값
$username = "JaneDoe";
$email = "jane@example.com";
// 쿼리 실행
mysqli_stmt_execute($stmt);
echo "New record created successfully";
// 연결 종료
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>
설명:
mysqli_prepare(): 절차적 방식에서 준비된 쿼리를 실행합니다.mysqli_stmt_bind_param(): 값 바인딩을 통해 SQL 인젝션을 방지합니다.
3. PDO를 사용한 Prepared Statements
PDO에서는 파라미터화된 쿼리와 Prepared Statements를 사용하여 데이터를 안전하게 처리할 수 있습니다. Named Parameters와 Positional Parameters를 지원합니다.
3.1 Named Parameters를 사용한 Prepared Statements
<?php
// 데이터베이스 연결 정보
$host = "localhost";
$dbname = "my_database";
$username = "root";
$password = "";
try {
// PDO 객체 생성 및 MySQL 서버 연결
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepared Statement 준비
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
// 파라미터 바인딩 및 실행
$stmt->execute(['username' => 'JohnDoe', 'email' => 'john@example.com']);
echo "New record created successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
설명:
:username,:email: Named Parameters를 사용하여 직관적인 바인딩을 할 수 있습니다.$stmt->execute(): 바인딩된 값들을 사용해 쿼리를 실행합니다.
3.2 Positional Parameters를 사용한 Prepared Statements
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=my_database", "root", "");
// Prepared Statement 준비
$sql = "INSERT INTO users (username, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
// 파라미터 바인딩 및 실행
$stmt->execute(['JohnDoe', 'john@example.com']);
echo "New record created successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
설명:
?: 자리 표시자에 Positional Parameters를 사용하여 파라미터를 위치 기반으로 전달합니다.$stmt->execute(): 배열을 통해 파라미터 순서대로 값을 전달합니다.
4. Prepared Statements의 장점
4.1 SQL 인젝션 방지
Prepared Statements는 SQL 인젝션을 방지하는 가장 효과적인 방법 중 하나입니다. 쿼리와 데이터를 분리하여 값이 SQL 쿼리 내에서 직접 실행되지 않도록 만듭니다. 값은 SQL 쿼리 자체가 아닌 데이터로서 처리되므로, SQL 명령어로 간주되지 않습니다.
4.2 성능 최적화
Prepared Statements는 쿼리를 한 번 준비하고 여러 번 실행할 수 있기 때문에, 반복적인 데이터베이스 작업에서 성능이 향상됩니다. 특히 대량의 데이터를 처리할 때 이점이 큽니다.
4.3 코드 가독성 향상
파라미터화된 쿼리는 쿼리 내에서 데이터와 명령어가 명확히 분리되기 때문에, 코드 가독성과 유지보수성이 크게 향상됩니다. 데이터베이스 작업을 보다 안전하고 직관적으로 작성할 수 있습니다.
5. 여러 데이터의 Prepared Statements 실행
Prepared Statements를 사용하면 반복적으로 여러 데이터를 삽입할 때도 성능을 최적화할 수 있습니다.
예시: 여러 데이터를 한 번에 삽입
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=my_database", "root", "");
// Prepared Statement 준비
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);
// 여러 사용자 데이터
$users = [
['username' => 'JohnDoe', 'email' => 'john@example.com'],
['username' => 'JaneDoe', 'email' => 'jane@example.com'],
['username' => 'SamSmith', 'email' => 'sam@example.com']
];
// 반복문을 통해 여러 데이터 삽입
foreach ($users as $user) {
$stmt->execute(['username' => $user['username'], 'email' => $user['email']]);
}
echo "All records created successfully";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
설명:
- Prepared Statement는 쿼리를 한 번만 준비하고, 여러 데이터를 바인딩하여 여러 번 실행할 수 있어 성능이 최적화됩니다.
- 반복문을 사용해 여러 데이터를 안전하게 처리할 수 있습니다.
6. 보안 고려 사항
6.1 SQL 인젝션 방지
Prepared Statements는 SQL 쿼리
에서 값을 별도로 처리하므로 SQL 인젝션을 완벽히 방지할 수 있습니다. 입력 데이터가 쿼리 명령어로 처리되지 않고 데이터로만 처리되기 때문에, 해커가 쿼리 구조를 조작할 수 없습니다.
6.2 사용자 입력 유효성 검사
Prepared Statements를 사용하더라도 사용자 입력에 대한 유효성 검사는 반드시 수행해야 합니다. 예를 들어, 이메일 형식이 맞는지 확인하거나, 문자열 길이를 제한하는 등의 검사는 여전히 필요합니다.
if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
// 유효한 이메일일 경우 쿼리 실행
}
요약
- *PHP의 Prepared Statements(준비된 쿼리)**는 SQL 인젝션을 방지하고, 안전하고 효율적으로 MySQL 데이터베이스와 상호작용할 수 있게 해줍니다. MySQLi와 PDO 모두에서 사용 가능하며, 파라미터화된 쿼리로 데이터를 처리하여 보안을 강화할 수 있습니다. Prepared Statements는 성능 최적화, 보안 강화, 코드 가독성 향상의 장점을 제공하며, 데이터베이스와의 반복적인 작업에서 특히 유용합니다.