PHP
PDO

소개

통사론

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // 일부 드라이버는 열 이름을 필요로합니다.

비고

// Retrieving the last inserted id
$id = null;

try {
    $id = $pdo->lastInsertId(); // return value is an integer    
}
catch( PDOException $e ) {
    echo $e->getMessage();
}

기본 PDO 연결 및 검색

PHP 5.0부터 PDO 는 데이터베이스 액세스 레이어로 사용할 수있었습니다. 데이터베이스에 무관하므로 다음 DSN을 변경하여 지원되는 모든 데이터베이스에 대해 다음 연결 예제 코드가 작동해야합니다.

// First, create the database handle

//Using MySQL (connection via local socket):
$dsn = "mysql:host=localhost;dbname=testdb;charset=utf8";

//Using MySQL (connection via network, optionally you can specify the port too):
//$dsn = "mysql:host=127.0.0.1;port=3306;dbname=testdb;charset=utf8";

//Or Postgres
//$dsn = "pgsql:host=localhost;port=5432;dbname=testdb;";

//Or even SQLite
//$dsn = "sqlite:/path/to/database"

$username = "user";
$password = "pass";
$db = new PDO($dsn, $username, $password);

// setup PDO to throw an exception if an invalid query is provided
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Next, let's prepare a statement for execution, with a single placeholder
$query = "SELECT * FROM users WHERE class = ?";
$statement = $db->prepare($query);

// Create some parameters to fill the placeholders, and execute the statement
$parameters = [ "221B" ];
$statement->execute($parameters);

// Now, loop through each record as an associative array
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    do_stuff($row);
}

prepare 함수는 질의 문자열로부터 PDOStatement 객체를 생성한다. 쿼리의 실행과 결과의 검색은이 반환 된 객체에 대해 수행됩니다. 실패한 경우 함수는 false 를 반환하거나 exception throw합니다 (PDO 연결이 구성된 방식에 따라 다름).

매개 변수가있는 쿼리로 SQL 주입 방지

SQL 인젝션은 악의적 인 사용자가 SQL 쿼리를 수정하여 불필요한 명령을 추가하는 것을 허용하는 일종의 공격입니다. 예를 들어 다음 코드는 취약합니다 .

// Do not use this vulnerable code!
$sql = 'SELECT name, email, user_level FROM users WHERE userID = ' . $_GET['user'];
$conn->query($sql);

이 스크립트의 모든 사용자는 기본적으로 데이터베이스를 수정할 수 있습니다. 예를 들어 다음 쿼리 문자열을 생각해보십시오.

page.php?user=0;%20TRUNCATE%20TABLE%20users;

그러면 예제 쿼리가 다음과 같이 보입니다.

SELECT name, email, user_level FROM users WHERE userID = 0; TRUNCATE TABLE users;

이것은 극단적 인 예이지만 (대부분의 SQL 주입 공격은 데이터 삭제를 목표로하지 않으며 대부분의 PHP 쿼리 실행 함수가 다중 쿼리를 지원하지는 않습니다.) 이것은 부주의 한 어셈블리로 SQL 주입 공격을 가능하게하는 방법의 예입니다. 쿼리. 불행히도 이와 같은 공격은 매우 일반적이며, 데이터 보호를 위해 적절한주의를 기울이지 않는 코더 때문에 매우 효과적입니다.

SQL 인젝션을 방지하기 위해 준비된 문 이 권장되는 솔루션입니다. 대신 사용자 데이터를 쿼리에 직접 연결하는 대신 자리 표시자가 대신 사용됩니다. 그런 다음 데이터가 별도로 전송되므로 SQL 엔진이 일련의 지침에 대해 사용자 데이터를 혼동 할 가능성이 없음을 의미합니다.

여기서 주제는 PDO이지만, PHP MySQLi 확장 은 준비된 문장도 지원 한다는 점에 유의하십시오 .

PDO는 두 가지 종류의 자리 표시자를 지원합니다 (자리 표시자는 열 또는 표 이름에는 사용할 수 없으며 값만 사용할 수 없음).

1. 명명 된 자리 표시 자. 콜론 ( : ), 고유 이름 다음 (예. :user )

   // using named placeholders
   $sql = 'SELECT name, email, user_level FROM users WHERE userID = :user';
   $prep = $conn->prepare($sql);
   $prep->execute(['user' => $_GET['user']]); // associative array
   $result = $prep->fetchAll();
   

2. 전통적인 SQL 위치 지정자 ? :

   // using question-mark placeholders
   $sql = 'SELECT name, user_level FROM users WHERE userID = ? AND user_level = ?';
   $prep = $conn->prepare($sql);
   $prep->execute([$_GET['user'], $_GET['user_level']]); // indexed array
   $result = $prep->fetchAll();
   

테이블 또는 열 이름을 동적으로 변경해야하는 경우에는 이것이 자신의 보안 위험 및 나쁜 습관에 있음을 알아 두십시오. 그러나 문자열 연결을 통해 수행 할 수 있습니다. 이러한 쿼리의 보안을 향상시키는 한 가지 방법은 허용 된 값의 테이블을 설정하고 연결하려는 값을이 테이블과 비교하는 것입니다.

DSN을 통해서만 연결 문자셋을 설정하는 것이 중요합니다. 그렇지 않으면 이상한 인코딩이 사용되면 응용 프로그램에 모호한 취약점이 발생할 수 있습니다. 5.3.6 이전의 PDO 버전의 경우 DSN을 통한 charset 설정을 사용할 수 없으므로 유일한 옵션은 연결이 생성 된 직후 PDO::ATTR_EMULATE_PREPARES 속성을 false 설정하는 것입니다.

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

이로 인해 PDO는 기본 DBMS의 원시 준비 문을 대신 사용합니다.

그러나 PDO는 MySQL이 기본적으로 준비 할 수없는 문구를 모방하는 것으로 조용히 대체 합니다. 즉 , 매뉴얼 ( 소스 )에 나열 될 수있는 문구를 자동으로 대체 합니다.

PDO : MySQL / MariaDB 서버에 연결

인프라에 따라 MySQL / MariaDB 서버에 연결하는 두 가지 방법이 있습니다.

표준 (TCP / IP) 연결

$dsn = 'mysql:dbname=demo;host=server;port=3306;charset=utf8';
$connection = new \PDO($dsn, $username, $password);

// throw exceptions, when SQL error is caused
$connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// prevent emulation of prepared statements
$connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

PDO는 이전 MySQL 서버 버전 (준비된 명령문을 지원하지 않음)과 호환되도록 설계되었으므로 명시 적으로 에뮬레이션을 비활성화해야합니다. 그렇지 않으면 준비된 진술을 사용하여 일반적으로 부여되는 추가 예방 주사 혜택을 상실하게됩니다.

염두에 두어야 할 또 다른 설계 절충안은 기본 오류 처리 동작입니다. 별도로 구성하지 않으면 PDO가 SQL 오류를 표시하지 않습니다.

지속성 추상화를 작성할 때 추가 기능을 얻을 수 있기 때문에 "예외 모드"로 설정하는 것이 좋습니다 (예 : UNIQUE 제약 조건을 위반할 때 예외가 발생하는 경우).

소켓 연결

$dsn = 'mysql:unix_socket=/tmp/mysql.sock;dbname=demo;charset=utf8';
$connection = new \PDO($dsn, $username, $password);

// throw exceptions, when SQL error is caused
$connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// prevent emulation of prepared statements
$connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

유닉스 계열 시스템에서 호스트 이름이 'localhost' 이면 서버에 대한 연결은 도메인 소켓을 통해 이루어진다.

PDO와의 데이터베이스 트랜잭션

데이터베이스 트랜잭션은 모든 명령문이 성공할 경우에만 데이터 변경 세트가 영구적으로 유지되도록 보장합니다. 트랜잭션 중 쿼리 또는 코드 오류가 발견되면 시도한 변경 사항을 롤백 할 수 있습니다.

PDO는 트랜잭션을 시작, 커미트 및 롤백하는 간단한 방법을 제공합니다.

$pdo = new PDO(
    $dsn, 
    $username, 
    $password, 
    array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
);

try {
    $statement = $pdo->prepare("UPDATE user SET name = :name");

    $pdo->beginTransaction();

    $statement->execute(["name"=>'Bob']);
    $statement->execute(["name"=>'Joe']);

    $pdo->commit();
} 
catch (\Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollback();
        // If we got here our two data updates are not in the database
    }
    throw $e;
}

트랜잭션 중에 변경된 모든 데이터는 활성 연결에서만 볼 수 있습니다. SELECT 문은 아직 데이터베이스에 커밋되지 않은 경우에도 변경된 변경 사항을 반환합니다.

참고 : 트랜잭션 지원에 대한 자세한 내용은 데이터베이스 공급 업체 설명서를 참조하십시오. 일부 시스템은 트랜잭션을 전혀 지원하지 않습니다. 일부는 중첩 된 트랜잭션을 지원하지만 다른 트랜잭션은 지원하지 않습니다.

PDO와의 거래를 이용한 실제 사례

다음 섹션에서는 트랜잭션을 사용하여 데이터베이스의 일관성을 보장하는 실용적인 실제 예를 보여줍니다.

다음 시나리오를 가정 해보십시오. 전자 상거래 웹 사이트의 장바구니를 만들고 두 개의 데이터베이스 테이블에 주문을 보관하기로 결정했다고 가정 해 보겠습니다. 하나는 order_id , name , address , telephone 및 created_at 필드가있는 orders 입니다. 그리고 order_id , product_id 및 quantity 필드와 함께 orders_products 라는 두 번째 orders_products 이 있습니다. 첫 번째 테이블은 주문의 메타 데이터 를 포함하고 두 번째 테이블은 주문 된 실제 제품을 포함 합니다.

데이터베이스에 새로운 주문 넣기

데이터베이스에 새 주문을 삽입하려면 두 가지 작업을 수행해야합니다. 먼저 orders 테이블 ( name , address 등)의 메타 데이터 를 포함 할 새 레코드를 INSERT 해야합니다. 그런 다음 주문에 포함 된 각 제품에 대해 orders_products 테이블에 하나의 레코드를 INSERT 해야합니다.

다음과 유사한 작업을 수행하여이 작업을 수행 할 수 있습니다.

// Insert the metadata of the order into the database
$preparedStatement = $db->prepare(
    'INSERT INTO `orders` (`name`, `address`, `telephone`, `created_at`)
     VALUES (:name, :address, :telephone, :created_at)'
);

$preparedStatement->execute([
    'name' => $name,
    'address' => $address,
    'telephone' => $telephone,
    'created_at' => time(),
]);

// Get the generated `order_id`
$orderId = $db->lastInsertId();

// Construct the query for inserting the products of the order
$insertProductsQuery = 'INSERT INTO `orders_products` (`order_id`, `product_id`, `quantity`) VALUES';

$count = 0;
foreach ( $products as $productId => $quantity ) {
    $insertProductsQuery .= ' (:order_id' . $count . ', :product_id' . $count . ', :quantity' . $count . ')';
    
    $insertProductsParams['order_id' . $count] = $orderId;
    $insertProductsParams['product_id' . $count] = $productId;
    $insertProductsParams['quantity' . $count] = $quantity;
    
    ++$count;
}

// Insert the products included in the order into the database
$preparedStatement = $db->prepare($insertProductsQuery);
$preparedStatement->execute($insertProductsParams);

예기치 않은 일이 발생하여 어떤 이유로 인해 두 번째 INSERT 쿼리가 실패 할 때까지 데이터베이스에 새로운 순서를 삽입하는 데 INSERT 합니다. 그런 일이 발생하면 orders 테이블 안에 새로운 주문이 생기게되며, orders 테이블에는 연관된 제품이 없습니다. 다행히도이 수정은 매우 간단합니다. 단 하나의 데이터베이스 트랜잭션 형태로 쿼리를 작성하면됩니다.

트랜잭션을 사용하여 데이터베이스에 새 주문 넣기

PDO 사용하여 트랜잭션을 시작하려면 데이터베이스에 대한 쿼리를 실행하기 전에 beginTransaction 메소드를 호출해야합니다. 그런 다음 INSERT 및 / 또는 UPDATE 쿼리를 실행하여 데이터에 원하는대로 변경합니다. 그리고 마지막으로 PDO 객체의 commit 메소드를 호출하여 변경 사항을 영구적으로 만듭니다. commit 메소드를 호출 할 때까지 지금까지 데이터에 수행 한 모든 변경 사항은 아직 영구적이지 않으며 PDO 객체의 rollback 메소드를 호출하여 쉽게 되돌릴 수 있습니다.

다음 예에서는 데이터베이스에 새 주문을 삽입하기 위해 트랜잭션을 사용하는 것과 동시에 데이터의 일관성을 보장하는 방법을 보여줍니다. 두 쿼리 중 하나가 실패하면 모든 변경 사항이 되돌려집니다.

// In this example we are using MySQL but this applies to any database that has support for transactions
$db = new PDO('mysql:host=' . $host . ';dbname=' . $dbname . ';charset=utf8', $username, $password);    

// Make sure that PDO will throw an exception in case of error to make error handling easier
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // From this point and until the transaction is being committed every change to the database can be reverted
    $db->beginTransaction();    
    
    // Insert the metadata of the order into the database
    $preparedStatement = $db->prepare(
        'INSERT INTO `orders` (`order_id`, `name`, `address`, `created_at`)
         VALUES (:name, :address, :telephone, :created_at)'
    );
    
    $preparedStatement->execute([
        'name' => $name,
        'address' => $address,
        'telephone' => $telephone,
        'created_at' => time(),
    ]);
    
    // Get the generated `order_id`
    $orderId = $db->lastInsertId();

    // Construct the query for inserting the products of the order
    $insertProductsQuery = 'INSERT INTO `orders_products` (`order_id`, `product_id`, `quantity`) VALUES';
    
    $count = 0;
    foreach ( $products as $productId => $quantity ) {
        $insertProductsQuery .= ' (:order_id' . $count . ', :product_id' . $count . ', :quantity' . $count . ')';
        
        $insertProductsParams['order_id' . $count] = $orderId;
        $insertProductsParams['product_id' . $count] = $productId;
        $insertProductsParams['quantity' . $count] = $quantity;
        
        ++$count;
    }
    
    // Insert the products included in the order into the database
    $preparedStatement = $db->prepare($insertProductsQuery);
    $preparedStatement->execute($insertProductsParams);
    
    // Make the changes to the database permanent
    $db->commit();
}
catch ( PDOException $e ) { 
    // Failed to insert the order into the database so we rollback any changes
    $db->rollback();
    throw $e;
}

PDO : 쿼리로 영향을받은 행 수 얻기

PDO 클래스의 인스턴스 인 $db 부터 시작합니다. 쿼리를 실행 한 후에는 쿼리의 영향을받는 행 수를 결정하기를 원합니다. PDOStatement 의 rowCount() 메소드는 잘 작동 할 것이다 :

$query = $db->query("DELETE FROM table WHERE name = 'John'");
$count = $query->rowCount();

echo "Deleted $count rows named John";

참고 :이 메서드는 INSERT, DELETE 및 UPDATE 문에 의해 영향을받는 행의 수를 결정하는 데에만 사용해야합니다. 이 방법은 SELECT 문에서도 작동 할 수 있지만 모든 데이터베이스에서 일관성이 없습니다.

PDO :: lastInsertId ()

방금 데이터베이스 테이블에 삽입 한 행에 대한 자동 증분 ID 값을 가져야 할 필요성을 자주 느낄 수 있습니다. lastInsertId () 메서드를 사용하여이 작업을 수행 할 수 있습니다.

// 1. Basic connection opening (for MySQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "mysql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', '[email protected]')";
$query_success = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $pdo->lastInsertId(); // return value is an integer

postgresql과 oracle에는 현재 삽입 / 수정 된 행의 지정된 열을 반환하는 RETURNING 키워드가 있습니다. 하나의 엔트리를 삽입하는 예제는 다음과 같습니다.

// 1. Basic connection opening (for PGSQL)
$host = 'localhost';
$database = 'foo';
$user = 'root'
$password = '';
$dsn = "pgsql:host=$host;dbname=$database;charset=utf8";
$pdo = new PDO($dsn, $user, $password);

// 2. Inserting an entry in the hypothetical table 'foo_user'
$query = "INSERT INTO foo_user(pseudo, email) VALUES ('anonymous', '[email protected]') RETURNING id";
$statement = $pdo->query($query);

// 3. Retrieving the last inserted id
$id = $statement->fetchColumn();  // return the value of the id column of the new row in foo_user