[PHP 수업] 13강 - 데이터베이스 연동 (MySQL)

안녕하세요! 그레이해커 월횽입니다. 이번 강의에서는 PHP와 MySQL을 연동하여 데이터베이스 작업을 수행하는 방법에 대해 알아봅니다. 데이터베이스의 기본 개념부터 시작하여 PHP에서 MySQL을 연결하고, CRUD 작업을 수행하며, 보안 고려사항에 대해서도 다룰 것입니다.

 

 

1. 데이터베이스 개념 및 MySQL 소개

 

1-1. 데이터베이스 개념

· 데이터베이스는 데이터를 효율적으로 저장하고 관리할 수 있는 구조화된 시스템입니다.
· 테이블: 데이터베이스 내에서 데이터를 행과 열로 저장하는 구조입니다.
· 쿼리: 데이터베이스에 명령을 보내는 SQL 문입니다.

 

1-2. MySQL 소개

· MySQL은 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)입니다.
· SQL(Structured Query Language)을 사용하여 데이터베이스를 관리하고 쿼리합니다.

 

 

2. PHP에서 MySQL 연결

 

2-1. PDO (PHP Data Objects)

· PDO는 데이터베이스에 대한 일관된 인터페이스를 제공하는 PHP 확장 모듈입니다.
· 다양한 데이터베이스 시스템을 지원하며, 준비된 문장(prepared statements)을 사용하여 보안을 강화할 수 있습니다.

 

2-1-1. PDO를 사용한 MySQL 연결

<?php
$dsn = 'mysql:host=localhost;dbname=my_database;charset=utf8';
$username = 'root';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    // PDO 에러 모드 설정
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "MySQL 데이터베이스에 성공적으로 연결되었습니다.";
} catch (PDOException $e) {
    echo "데이터베이스 연결 실패: " . $e->getMessage();
}
?>

728x90

2-2. MySQLi (MySQL Improved)

· MySQLi는 MySQL 데이터베이스와 상호작용하는데 사용하는 PHP 확장 모듈입니다.
· 객체 지향 및 절차적 접근 방식을 지원합니다.

 

2-2-1. MySQLi를 사용한 MySQL 연결

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$database = "my_database";

// MySQLi 객체 지향 방식으로 연결
$conn = new mysqli($servername, $username, $password, $database);

// 연결 확인
if ($conn->connect_error) {
    die("연결 실패: " . $conn->connect_error);
}
echo "MySQL 데이터베이스에 성공적으로 연결되었습니다.";
?>

 

 

 

3. CRUD 작업 (Create, Read, Update, Delete)

 

3.1 Create (데이터 삽입)

 

3.1.1 PDO를 사용한 데이터 삽입

<?php
// PDO 예제
$sql = 'INSERT INTO users (username, email) VALUES (:username, :email)';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':username' => 'john_doe',
    ':email' => 'john.doe@example.com'
]);
echo "새 사용자 데이터가 삽입되었습니다.";
?>

반응형

3-1-2. MySQLi를 사용한 데이터 삽입

<?php
// MySQLi 예제
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john.doe@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "새 사용자 데이터가 삽입되었습니다.";
} else {
    echo "데이터 삽입 오류: " . $conn->error;
}
?>

 

 

3.2 Read (데이터 조회)

 

3.2.1 PDO를 사용한 데이터 조회

<?php
// PDO 예제
$sql = 'SELECT * FROM users';
foreach ($pdo->query($sql) as $row) {
    echo $row['username'] . ' - ' . $row['email'] . '<br>';
}
?>

 

3-2-2. MySQLi를 사용한 데이터 조회

<?php
// MySQLi 예제
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row['username'] . ' - ' . $row['email'] . '<br>';
    }
} else {
    echo "조회된 데이터가 없습니다.";
}
?>

 

 

3-3. Update (데이터 수정)

 

3-3-1. PDO를 사용한 데이터 수정

<?php
// PDO 예제
$sql = 'UPDATE users SET email = :email WHERE username = :username';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':email' => 'new.email@example.com',
    ':username' => 'john_doe'
]);
echo "사용자 데이터가 성공적으로 수정되었습니다.";
?>

 

 

3.3.2 MySQLi를 사용한 데이터 수정

<?php
// MySQLi 예제
$sql = "UPDATE users SET email = 'new.email@example.com' WHERE username = 'john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "사용자 데이터가 성공적으로 수정되었습니다.";
} else {
    echo "데이터 수정 오류: " . $conn->error;
}
?>

 

 

3-4. Delete (데이터 삭제)

 

3-4-1. PDO를 사용한 데이터 삭제

<?php
// PDO 예제
$sql = 'DELETE FROM users WHERE username = :username';
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':username' => 'john_doe'
]);
echo "사용자 데이터가 성공적으로 삭제되었습니다.";
?>

 

SMALL

3-4-2. MySQLi를 사용한 데이터 삭제

<?php
// MySQLi 예제
$sql = "DELETE FROM users WHERE username = 'john_doe'";
if ($conn->query($sql) === TRUE) {
    echo "사용자 데이터가 성공적으로 삭제되었습니다.";
} else {
    echo "데이터 삭제 오류: " . $conn->error;
}
?>

 

 

 

4. SQL 문법 및 쿼리 실행

 

4-1. SQL 기본 문법

· SELECT: 데이터 조회
· INSERT: 데이터 삽입
· UPDATE: 데이터 수정
· DELETE: 데이터 삭제

 

4-2, SQL 쿼리 예제

-- 데이터 조회
SELECT * FROM users;

-- 데이터 삽입
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane.doe@example.com');

-- 데이터 수정
UPDATE users SET email = 'updated.email@example.com' WHERE username = 'jane_doe';

-- 데이터 삭제
DELETE FROM users WHERE username = 'jane_doe';

 

 

5. 보안 고려사항 (준비된 문장, SQL 인젝션 방지)

 

5-1. 준비된 문장 (Prepared Statements)

· 준비된 문장을 사용하면 SQL 인젝션 공격을 방지할 수 있습니다.
· 사용자 입력을 쿼리에 직접 포함시키지 않고, 쿼리와 파라미터를 분리합니다.

 

5-1-1. PDO에서 준비된 문장 사용

<?php
$sql = 'SELECT * FROM users WHERE username = :username';
$stmt = $pdo->prepare($sql);
$stmt->execute([':username' => $username]);
$results = $stmt->fetchAll();
?>

 

5-1-2. MySQLi에서 준비된 문장 사용

<?php
$sql = 'SELECT * FROM users WHERE username = ?';
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $username); // 's'는 문자열 타입을 의미
$stmt->execute();
$results = $stmt->get_result();
?>

 

5-2 SQL 인젝션 방지

· SQL 인젝션은 공격자가 악의적인 SQL 코드를 주입하여 데이터베이스를 조작하는 공격입니다.
· 준비된 문장과 매개변수화된 쿼리를 사용하여 SQL 인젝션 공격을 방지할 수 있습니다.

 

5-2-1. 입력 데이터 검증 (Input Validation)

· 서버 측 검증: 모든 사용자 입력을 서버 측에서 검증하고 필터링하여 악의적인 데이터가 데이터베이스 쿼리에 포함되지 않도록 합니다.
· 화이트리스트 사용: 사용자 입력 값에 대해 화이트리스트를 설정하여 허용된 값만 처리합니다.

<?php
$allowed_roles = ['admin', 'editor', 'viewer'];
if (in_array($_POST['role'], $allowed_roles)) {
    // 유효한 역할이므로 처리
} else {
    // 유효하지 않은 역할, 에러 처리
}
?>

 

 

5-2-2. 출력 데이터 인코딩 (Output Encoding)

· HTML 엔티티 인코딩: 웹 페이지에서 데이터를 출력할 때 HTML 엔티티를 인코딩하여 스크립트 삽입 공격(XSS)을 방지합니다.
· URL 인코딩: URL에 포함되는 데이터는 URL 인코딩을 적용하여 안전하게 전송합니다.

<?php
// HTML 엔티티 인코딩
echo htmlspecialchars($userInput, ENT_QUOTES, 'UTF-8');
?>

 

 

5-2-3. 최소 권한 원칙 (Least Privilege Principle)

· 최소 권한: 데이터베이스 사용자에게 필요한 최소한의 권한만 부여합니다. 예를 들어, 웹 애플리케이션이 데이터를 읽기만 하면 읽기 권한만 부여하고, 데이터베이스 구조를 변경할 필요가 없다면 변경 권한을 부여하지 않습니다.

-- 예제: 읽기 전용 권한 부여
GRANT SELECT ON my_database.* TO 'web_user'@'localhost';

 

 

5-2-4. 에러 메시지 숨기기 (Error Hiding)

· 생산 환경에서 에러 숨기기: 사용자에게 에러 메시지를 노출하지 않고, 개발 환경에서만 자세한 에러 정보를 표시하여 공격자가 데이터베이스 구조나 기타 중요한 정보를 파악하지 못하도록 합니다.

<?php
// 개발 환경에서 에러 표시
ini_set('display_errors', 1);
error_reporting(E_ALL);

// 운영 환경에서 에러 숨기기
ini_set('display_errors', 0);
error_reporting(0);
?>

 

 

5-2-5. ORM 사용 (Object-Relational Mapping)

· ORM 라이브러리: 데이터베이스와 객체 지향 프로그래밍 간의 매핑을 처리하는 ORM(Object-Relational Mapping) 라이브러리를 사용하면, ORM이 자동으로 SQL 쿼리를 안전하게 생성하고 실행하여 SQL 인젝션 위험을 줄일 수 있습니다.

<?php
// Doctrine ORM 예제
$user = $entityManager->find(User::class, $userId);
?>

 

 

5-2-6. 데이터베이스 쿼리 작성 시 보안 고려

· 적절한 쿼리 작성: 동적 쿼리 작성 시 사용자 입력을 직접 포함하지 않도록 하며, 항상 검증된 입력만 쿼리에 포함되도록 합니다.

<?php
// 안전한 쿼리 작성 예
$sql = 'SELECT * FROM users WHERE username = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([$username]);
?>

 

 

5-2-7. 패치 및 업데이트 (Patch and Update)

· 정기적인 패치: 데이터베이스 시스템과 PHP 라이브러리를 최신 상태로 유지하여 알려진 보안 취약점으로부터 보호합니다.

 

 

- 이전 수업 목록