PHP: 데이터베이스 (MySQLi, PDO)

데이터베이스

데이터베이스를 사용하는 일은 웹 어플리케이션을 작성한다면 대부분 있는 일이기에 이 부분을 빼놓을 수는 없다. PHP도 물론 오라클이나 MS-SQL 같은 다양한 데이터베이스에 연결할 수 있으나, 가장 많이 사용하는 것이 아무래도 MySQL 이기에 이부분을 중점으로 이야기하고자 한다. 또한 데이터베이스 관련 함수는 실무에서도 조차 올바르지 못한 방법으로 사용되어 해킹에 노출되고 있기 때문에 바르게 익혀둘 필요가 있다.

MySQLi

레거시 프로젝트에는 mysql_ 로 시작하는 비교적 오래된 함수가 사용된 것을 볼 수 있는데, 이는 문제가 있어 mysqli_ 로 대체할 것을 권장하고 있다. mysqli 조차도 요즘에 쓸만한 함수도 아니고, API 수준에서 바라보자면 상당히 로우한 수준이기에 그다지 현업에서 사용할 것을 권장하지는 않는다. PDO(PHP Data Object)를 놔두고 mysqli 를 굳이 사용할 이유는 없다.

참고로 mysqli 는 객체지향과 함수를 둘다 제공하지만, 객체지향을 사용하고자 한다면 차라리 PDO를 쓰도록 하자. 

mysqli 를 사용하기 위해서는 php mysqli extension 이 활성화 되어 있어야 한다.

mysqli_connect([string, [string, [string, [string [...]]]]): mysqli

이 함수는 단순히 MySQL 에 연결해주고 관련 리소스를 리턴해준다. 참고로 이는 함수형으로 사용할 때 사용해야 하므로 잘 보관해두어야 한다.

mysqli_connect — Alias of mysqli::__construct()

// new mysqli(...)
$mysqli = mysqli_connect('__HOST_NAME__', '__USER_NAME__', '__PASSWORD__', '__DATABASE__');

// ...

mysqli_close($mysqli);

mysqli_query(mysqli, string, [, int]): mixed

이 함수는 쿼리를 날려주긴 하지만, 조심해서 사용해야 한다. 안전하지 않은 쿼리를 날릴 경우에는 SQL Injection 공격의 맛있는 먹잇감이 될 수 있으므로 주의해야 한다. 이에 대해서는 아래에서 언급하겠지만, 늘 강조한다. 절대 검증되지 않은 변수는 쿼리에 대입해서는 안 된다. 사용자는 어떤 값을 넣을지 알 수 없다.

mysqli::query -- mysqli_query — Performs a query on the database

mysqli_query($mysqli, 'SELECT 1');

SELECT

다만 결과값에 대해서는 SELECT, SHOW, DESCRIBE 와 같은 경우에는 mysqli_result 객체를, 그렇지 않다면 bool 을 반환해준다고 적혀있으니 이는 참고해서 사용할 수 있다.

if ($result = mysqli_query($mysqli, 'SELECT * FROM posts LIMIT 10')) {
    while ($row = mysqli_fetch_object($result)) {
        var_dump($row);
    }
    mysqli_free_result($result);
}

mysqli_prepare(mysqli, string): mysqli_stmt

Prepare Statement 는 보안에 있어 매우 중요하다. 사용법은 mysqli_query() 와 비슷하다곤 하지만, 이것을 사용하게 되면 SQL Injection 에 대한 대처가 가능해진다. 허나 일부 레거시 프로젝트에서는 아직도 mysqli_query() 에다가 변수를 대입해서 사용하고 있기때문에 보안에 있어서 큰 문제가 되고 있다.

mysqli::prepare -- mysqli_prepare — Prepare an SQL statement for execution

mysqli_stmt

mysqli_prepare() 를 실행하고 나면 리턴되는 객체다. 이것을 따로 언급하는 이유는 바로 값 바인딩을 해야하기 때문이다. mysqli_stmt_bind_param() 을 사용하여 변수에 있는 값을 쿼리에 바인딩 해줄 수 있다.

$stmt = mysqli_prepare($mysqli, 'SELECT * FROM posts WHERE id = ?');

$id = 12;
mysqli_stmt_bind_param($stmt, 'i', $id);

if (mysqli_stmt_execute($stmt)) {
    $result = mysqli_stmt_get_result($stmt);
    // ...
    
    mysqli_stmt_close($stmt);
}

관련 함수들에 대해서는 자세히 언급하지는 않겠지만, mysqli_stmt_execute() 는 쿼리를 실행해주고, mysqli_stmt_get_result() 는 mysqli_result 를 얻어온다.

mysqli_stmt_fetch() 함수가 있으나 이는 직접 변수를 mysqli_stmt_bind_result() 로 바인딩 시켜주어야 하는 번거로움이 있다.

TRANSACTION

트랜잭션의 경우는 자세한 것은 생략하겠으나, mysqli_begin_transaction() 으로 트랜잭션을 시작한 다음 mysqli_commit() 또는 mysqli_rollback() 으로 처리할 수 있다. mysqli_autocommit() 을 사용하면 자동 커밋을 해제할 수 있다.

// mysqli_autocommit($mysqli, false);
mysqli_begin_transaction($mysqli, MYSQLI_TRANS_START_READ_ONLY);

// ...

// mysqli_commit($mysqli);
mysqli_rollback($mysqli);

PDO(PHP Data Object)

PDO(PHP Data Object)는 모던 PHP를 사용하고자 한다면 사용해야한다. 이는 데이터베이스를 보다 기능적으로 인터페이스화 시켜서 데이터베이스의 종류가 어떻든 공통 인터페이스의 형태로 쿼리를 전송하고 소통할 수 있기 때문에 로우한 API라 볼 수 없다. 따라서 함수의 이름이나 특정 데이터베이스만이 가지는 특별한 함수에 대해 결합도가 떨어지게 된다. 

게다가 mysqli 같은 경우는 비슷한 이름의 함수들, mysqli_query(), mysqli_real_query() 등 사용법이 헷갈리는 반면 PDO는 조금 더 간결하기 때문에 사용하기 좋을 것이다.

PDO 또한 php pdo extension 이 활성화 되어 있어야하며, 사용할 수 있는 드라이버가 여러 개이기 때문에 사용하고자 하는 데이터베이스를 확인하여야 한다.

PDO::__construct([string [, string [, string [, array]]]])

PDO 객체를 생성한다. 생성한 객체는 어플리케이션에서 발생하는 데이터베이스 처리를 위한 핸들이 될 것이다. 

$pdo = new PDO('__DSN__', '__USERNAME__', '__PASSWORD__');

PDO::query(string): PDOStatement

mysqli_query() 처럼 쿼리를 던져준다. 이 메서드를 사용할 때는 준비된 쿼리를 사용해야 한다. 즉, 안전하지 않은 쿼리를 사용하면 안 된다는 이야기.

$pdo->query('SELECT 1');

SELECT

SELECT 와 같은 결과값은 mysqli 와 비슷한 코드로 긁어오면 된다. $sth->fetch_* 를 사용하면 된다.

while ($row = $sth->fetchObject()) {
    var_dump($row);
}

PDO::prepare(string): PDOStatement

mysqli_prepare() 와 비슷하다. 준비된 쿼리를 사용할 수 있고, ? 이외에도 또 다른 치환자를 사용할 수도 있다.

$sth = $pdo->prepare('SELECT * FROM posts WHERE id = :id');

if ($sth->execute([ ':id' => 12 ])) {
    // ...
}

PDO::bindParam(mixed, mixed [, int]): bool

mysqli_stmt_bind_param() 이라고 생각할 수 있는데, 위에서는 execute() 메서드에 바인딩을 하였다. 둘의 차이는 execute() 의 경우 PDO::PARAM_STR, 즉 문자열로 바인딩해주는 반면, 직접 바인딩을 시켜주게되면 다른 데이터 타입으로도 바인딩 할 수 있다.

$id = 12;
$sth->bindParam(':id', $id, PDO::PARAM_INT);

if ($sth->execute()) {
    // ...
}

TRANSACTION

트랜잭션 처리는 beginTransaction() 메서드와 commit() 또는 rollback() 메서드로 처리해줄 수 있다.

$pdo->beginTransaction();

if ($pdo->inTransaction()) {
    $pdo->exec('DELETE FROM posts WHERE id = 12');
    $pdo->rollback();
}

여기서 exec() 메서드를 사용했는데, 둘은 리턴값이 다르다. query() 메서드는 PDOStatement 를, exec() 는 int(affected rows) 를 반환한다.