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スローしexception （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では2種類のプレースホルダがサポートされています（プレースホルダは列名またはテーブル名には使用できず、値のみ使用できます）。

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();
   

今までにテーブル名や列名を動的に変更する必要がある場合は、これがセキュリティ上のリスクと悪い習慣であることを理解しておいてください。しかし、それは文字列の連結によって行うことができます。このようなクエリのセキュリティを向上させる1つの方法は、許可された値のテーブルを設定し、連結する値をこのテーブルと比較することです。

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サーバに接続するには2つの方法があります。

標準（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サーババージョン（プリペアドステートメントをサポートしていない）と互換性があるように設計されているため、エミュレーションを明示的に無効にする必要があります。そうしないと、準備されたステートメントを使用して通常与えられる追加の注射予防の利点が失われます。

覚えておかなければならないもう1つの設計上の妥協案は、デフォルトのエラー処理動作です。特に設定されていない場合、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);

UNIX系のシステムでは、ホスト名が'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でのトランザクションを使用した実際の例

次のセクションでは、トランザクションの使用がデータベースの一貫性を保証する現実的な実例を示します。

以下のシナリオを想像してみましょう.eコマースのWebサイトのショッピングカートを構築し、2つのデータベーステーブルに注文を保存することに決めたとします。一つの名前のordersフィールドを持つには、 order_id 、 name 、 address 、 telephoneとcreated_at 。もう1つは、 order_id 、 product_id 、およびquantityというフィールドを持つorders_productsという名前です。最初の表には注文のメタデータが含まれ、2番目の表には注文された実際の製品が含まれています。

データベースに新しい注文を挿入する

新しい注文をデータベースに挿入するには、2つのことを行う必要があります。まずordersのメタデータ （ name 、 addressなど）を含むordersテーブル内に新しいレコードをINSERTする必要があります。そして、注文に含まれている製品ごとにorders_productsテーブルに1つのレコードを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);

予期せぬことが起こり、何らかの理由で2番目のINSERTクエリが失敗するまで、これはデータベースに新しい注文を挿入するのに効果的です。このようなことが起こると、 ordersテーブル内に新しい注文がordersます。 ordersテーブルには関連する商品がありません。幸いなことに、この修正は非常に簡単です。あなたがしなければならないことは、単一のデータベーストランザクションの形でクエリを作成することだけです。

トランザクションを使用してデータベースに新しい注文を挿入する

PDOを使用してトランザクションを開始するには、データベースへのクエリを実行する前にbeginTransactionメソッドを呼び出すだけです。次に、 INSERTおよび/またはUPDATEクエリを実行して、データに変更を加えます。最後に、 PDOオブジェクトのcommitメソッドを呼び出して、変更を永続的にします。 commitメソッドを呼び出すまで、この時点までにデータに対して行ったすべての変更はまだ永続的ではなく、単にPDOオブジェクトのrollbackメソッドを呼び出すだけで簡単に元に戻すことができます。

次の例では、データの一貫性を確保しながら、新しい注文をデータベースに挿入するためにトランザクションを使用する方法を示しています。 2つのクエリのいずれかが失敗した場合、すべての変更が元に戻されます。

// 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つ挿入する例を次に示します。

// 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