PDO를 활용한 데이터베이스 연동

// PDO 객체 생성
$pdo = new PDO("mysql:host=localhost;dbname=DB이름", "유저아이디", '유저비번');

// 에러 모드 설정
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// SQL 쿼리 작성
$sql = "SELECT service_name, memo, userid, pw, updated FROM MyAccounts.accounts;";

// 쿼리 준비

$statements = $pdo->prepare($sql);

// 쿼리 실행
$statements->execute();


while($row = $statements->fetch(PDO::FETCH_ASSOC)) {
    $serviceName = $row['service_name'];
    $memo = $row['memo'];
    $userid = $row['userid'];
    $pw = $row['pw'];
    $updated = $row['updated'];

    echo "\n{$serviceName} - {$memo} - {$userid} - {$pw} - {$updated}";
}

 

 

mysql 데이터베이스와 연결하고 데이터를 가져와서 바인딩하기까지의 과정이다.

 

1. 먼저 데이터베이스 연결 문자열을 작성해서 붙여준다.

2. 에러 처리를 위한 에러모드를 설정한다.

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 

 

- 이를 통해서 PDO가 오류를 발생시켰을 때 어떻게 처리할 지를 결정한다.

- PDO::ERRMODE_SILENT(기본값)

아무런 에러 메시지를 출력하지 않고, 단순히 오류가 발생하면 내부적으로만 처리한다.

이 경우에 오류가 발생했을 때, 수동으로 확인해야 한다.

이 방식은 가장 조용하지만, 에러 발생 시에 추가 처리가 필요하기 때문에 디버깅이 어렵다.

 

- PDO::ERRMODE_WARNING

에러가 발생하면 경고 warning 을 출력한다.

스크립트가 중단되지는 않지만, 오류정보를 확인할 수 있다.

이 모드는 디버깅에 도움이 되지만, 여전히 스크립트를 중단하지 않기 때문에 주의해야 한다.

 

- PDO::ERRMODE_EXCEPTION ( 추천 )

이 모드를 사용한 상태인데

에러가 발생하면 예외 exception을 던진다.

이 예외는 try-catch 블록으로 잡을 수 있고, 스크립트가 중단된다.

오류 발생 시에 명확한 처리가 가능하고, 개발 단계에서 디버깅 및 문제 해결에 매우 도움이 된다. 

가장 추천하는 방법이다.

 

3. 다음으로는 쿼리를 작성해서 변수에 넣어준다.

$sql = "SELECT service_name, memo, userid, pw, updated FROM ACCOUNTS.accounts;";

 

4. 다음은 쿼리 준비과정을 거친다.

$statements = $pdo->prepare($sql);

prepare()

- SQL 쿼리를 미리 준비한다.

SQL 쿼리를 작성하지만, 아직 실제로 실행하지는 않고, 나중에 데이터를 입력해 실행할 준비를 한다.

prepare()가 호출되면 PDO는 서버로 쿼리를 보내서 미리 컴파일된 상태로 준비해둔다.

 

- 변수를 바인딩할 수 있는 환경을 제공한다.

쿼리에 변수가 포함된 경우에, 나중에 bindValue()나 bindParam()을 사용해서 안전하게 값을 바인딩할 수 있다. 

이를 통해서 SQL 삽입 공격을 방지한다.

// SQL 쿼리 작성 (변수는 :placeholder 형식으로 사용)
$sql = "SELECT service_name, memo, userid, pw, updated FROM ACCOUNTS.accounts WHERE userid = :userid;";

// 쿼리 준비
$statements = $pdo->prepare($sql);

// 변수 바인딩 (사용자 ID를 안전하게 바인딩)
$statements->bindValue(':userid', $userId);

// 쿼리 실행
$statements->execute();

 

이게 특이하다면 좀 특이할 수 있다.

prepare() 메서드는 PDO를 사용할 때 SQL 쿼리를 미리 준비하는 역할을 한다.

이건 Prepared Statement라고 불리고, 보안과 성능 면에서 많은 장점을 가진다.

 

Prepared Statement 준비된 문 은 SQL쿼리를 미리 컴파일하여 실행할 수 있게 만드는 방식이다.

prepare() 메서드는 SQL 쿼리를 미리 준비하고 이후에 데이터를 바인딩 한 후에 실행하는 방법이다. 

이를 통해서 쿼리 실행 과정에서 SQL 삽입 공격을 방지할 수 있다.

 

정리하자면 나중에 바인딩을 따로 함으로써 SQL삽입 공격을 방지하기 때문에 보안이 강화된다.

쿼리를 한번만 컴파일하고, 여러번 다른 값으로 실행할 수 있기 때문에 성능이 향상된다. 특히 반복적으로 같은 쿼리를 실행해야 할 때 유리하다.

SQL과 데이터 바인딩이 분리되어 코드가 더 명확하고 읽기 쉬워진다.

 

 

그렇다면 만약에 prepared statement 를 거치지 않고 바로 execute()를 하면 어떻게 될까?

애초에 prepare()와 execute() 는 세트이다.

쿼리를 값을 받아서 구성할 때 자바스크립트에서는 종종 문자열 리터럴을 써서 넣곤한다.

하지만 이것은 SQL 삽입공격에 매우 취약하다. 

 

때문에 prepare()에서 쿼리를 받고, 이후에 바인딩을 한 후에 execute()에서 이를 합쳐서 실행하는 것이다.

 

그럼에도 불구하고 execute() 만을 쓴다면 

이는 query() 메서드를 사용한 것과 같은 동작이 된다. 

 

즉 query()는 단일 SQL 쿼리를 실행할 때 사용되는 메서드이다.

$sql = "SELECT * FROM accounts WHERE userid = 'john_doe'";
$stmt = $pdo->query($sql);

query()

- query() 메서드는 쿼리를 준비하고 바로 실행한다.

- 단순한 쿼리에서 사용할 수 있다. 한번만 실행될 SQL에 적합하다.

- 변수를 쿼리 안에 직접 포함 시켜 실행할 경우에 이 방법을 사용할 수는 있지만, 보안 상 위험하다. SQL 삽입 공격이 가능하다.

 

 

prepare()와 execute()

// 쿼리 준비
$sql = "SELECT * FROM accounts WHERE userid = :userid";
$stmt = $pdo->prepare($sql);

// 안전하게 변수를 바인딩한 후 실행
$stmt->execute([':userid' => $userInput]);

- prepare()는 쿼리를 미리 준비하고 execute()에서 변수를 안전하게 바인딩해서 실행하는 방식이다. 

- 변수가 퐇마된 쿼리나 여러 번 실행할 쿼리에 적합하다.

- 특히 사용자가 입력한 데이터를 처리할 때는 반드시 prepare() - execute() 를 사용하는 것이 보안 상 중요하다.

 

 

5. 준비된 문을 실행한다. 우리는 따로 데이터바인딩 과정은 없었다. 

$statements->execute();

 

6. 결과를 변수에 할당해준다.

fetch()는 SQL 쿼리 결과를 가져올 때 사용한다.

그 결과를 다양한 방식으로 반환할 수가 있는데 해당 메서드 인자로 상수를 전달하면 된다.

그 상수들은 데이터를 어떤 형태로 반환할 지 결정하는데에 사용된다.

 

fetch()에서도 fetchAll()에서도 사용할 수 있다.

 

굉장히 많은데 한번 다 살펴보긴하자..

1. PDO::FETCH_ASSOC ( 위의 예시에서는 이 상수를 사용 )

• 설명: 쿼리 결과를 **연관 배열(associative array)**로 반환합니다. 각 컬럼의 이름을 배열의 키로 사용하여 데이터를 반환합니다.

• 형태: ['컬럼명' => 값]

• 예시:

$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);  // ['userid' => 'john_doe', 'email' => 'john@example.com']

 

2. PDO::FETCH_NUM

• 설명: 쿼리 결과를 숫자 인덱스 배열로 반환합니다. 컬럼명 대신 숫자 인덱스를 사용합니다.

• 형태: [0 => 값, 1 => 값]

• 예시:

$row = $stmt->fetch(PDO::FETCH_NUM);
print_r($row);  // [0 => 'john_doe', 1 => 'john@example.com']

 

3. PDO::FETCH_BOTH

• 설명: 연관 배열과 숫자 인덱스 배열 모두를 포함하는 방식으로 결과를 반환합니다.

• 형태: ['컬럼명' => 값, 0 => 값]

• 예시:

$row = $stmt->fetch(PDO::FETCH_BOTH);
print_r($row);  // ['userid' => 'john_doe', 'email' => 'john@example.com', 0 => 'john_doe', 1 => 'john@example.com']

 

4. PDO::FETCH_OBJ

• 설명: 쿼리 결과를 객체 형태로 반환합니다. 컬럼명은 객체의 속성으로 접근할 수 있습니다.

• 형태: $row->컬럼명

• 예시:

$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->userid;  // 'john_doe'
echo $row->email;    // 'john@example.com'

 

5. PDO::FETCH_CLASS

• 설명: 쿼리 결과를 지정된 클래스 객체로 반환합니다. 클래스의 속성으로 쿼리 결과를 바인딩합니다. 이 옵션을 사용할 때는 미리 정의된 클래스를 사용하거나 클래스를 정의해야 합니다.

• 형태: 클래스의 인스턴스

• 예시:

class User {
    public $userid;
    public $email;
}

$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$row = $stmt->fetch();
echo $row->userid;  // 'john_doe'
echo $row->email;   // 'john@example.com'

 

6. PDO::FETCH_LAZY

• 설명: 결과를 연관 배열, 숫자 배열, 그리고 객체로 모두 접근할 수 있게 반환합니다. 데이터에 접근하는 방법을 유연하게 제공하지만 성능 면에서는 다소 비효율적일 수 있습니다.

• 형태: $row['컬럼명'], $row[0], $row->컬럼명

• 예시:

$row = $stmt->fetch(PDO::FETCH_LAZY);
echo $row->userid;  // 객체 방식
echo $row['userid'];  // 연관 배열 방식
echo $row[0];  // 숫자 인덱스 방식

 

 

7. PDO::FETCH_BOUND

• 설명: 쿼리 결과를 바인딩된 변수에 직접 저장합니다. bindColumn() 메서드와 함께 사용되며, 결과를 반환하지 않고, 바인딩된 변수에 값을 저장합니다.

• 형태: 변수를 통해 값에 접근

• 예시:

$stmt->bindColumn('userid', $userid);
$stmt->fetch(PDO::FETCH_BOUND);
echo $userid;  // 'john_doe'

 

8. PDO::FETCH_KEY_PAIR

• 설명: 쿼리 결과의 첫 번째 컬럼을 배열의 **키(key)**로, 두 번째 컬럼을 배열의 **값(value)**으로 사용하는 방식으로 반환합니다.

• 형태: [key => value]

• 예시:

$sql = "SELECT userid, email FROM accounts";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
print_r($rows);  // ['john_doe' => 'john@example.com', 'jane_doe' => 'jane@example.com']

 

9. PDO::FETCH_UNIQUE

• 설명: 첫 번째 컬럼의 값을 배열의 키로 사용하고, 나머지 컬럼을 값으로 하는 배열을 반환합니다. 첫 번째 컬럼의 값은 고유해야 합니다.

• 형태: [unique_key => value]

• 예시:

$sql = "SELECT userid, email FROM accounts";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(PDO::FETCH_UNIQUE);
print_r($rows);  // ['john_doe' => ['email' => 'john@example.com'], 'jane_doe' => ['email' => 'jane@example.com']]