PHP
ChNP

Wprowadzenie

Składnia

• PDO::LastInsertId()
• PDO::LastInsertId($columnName) // niektórzy kierowcy potrzebują nazwy kolumny

Uwagi

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

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

Podstawowe połączenie i pobieranie PDO

Od PHP 5.0 PDO jest dostępna jako warstwa dostępu do bazy danych. Jest niezależny od bazy danych, dlatego poniższy przykładowy kod połączenia powinien działać dla dowolnej obsługiwanej bazy danych po prostu przez zmianę 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);
}

Funkcja prepare tworzy obiekt PDOStatement podstawie ciągu zapytania. Wykonywanie zapytania i wyszukiwanie wyników jest wykonywane na tym zwróconym obiekcie. W przypadku niepowodzenia funkcja zwraca false lub zgłasza exception (w zależności od konfiguracji połączenia PDO).

Zapobieganie iniekcji SQL za pomocą sparametryzowanych zapytań

Wstrzykiwanie SQL jest rodzajem ataku, który pozwala złośliwemu użytkownikowi zmodyfikować zapytanie SQL, dodając do niego niepożądane polecenia. Na przykład podatny jest następujący kod:

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

Pozwala to każdemu użytkownikowi tego skryptu modyfikować naszą bazę danych w zasadzie do woli. Na przykład rozważ następujący ciąg zapytania:

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

Dzięki temu nasze przykładowe zapytanie wygląda tak

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

Chociaż jest to skrajny przykład (większość ataków polegających na wstrzykiwaniu SQL nie ma na celu usunięcia danych, ani większość funkcji wykonywania zapytań PHP nie obsługuje wielu zapytań), jest to przykład tego, w jaki sposób można wykonać atak wstrzykiwania SQL przez nieostrożne połączenie Zapytanie. Niestety, takie ataki są bardzo częste i bardzo skuteczne ze względu na programistów, którzy nie podejmują odpowiednich środków ostrożności w celu ochrony swoich danych.

Aby zapobiec iniekcji SQL, zalecane instrukcje są przygotowanymi instrukcjami . Zamiast konkatenacji danych użytkownika bezpośrednio do zapytania, zamiast niego używany jest symbol zastępczy . Dane są następnie wysyłane osobno, co oznacza, że silnik SQL nie może pomylić danych użytkownika z zestawem instrukcji.

Chociaż tematem jest tutaj PDO, pamiętaj, że rozszerzenie MySQLi PHP obsługuje również przygotowane instrukcje

PDO obsługuje dwa rodzaje symboli zastępczych (symboli zastępczych nie można używać dla nazw kolumn lub tabel, tylko wartości):

1. Nazwane symbole zastępcze. Dwukropka ( : ), a następnie przez inną nazwę (np. :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. Tradycyjne symbole zastępcze pozycji SQL, reprezentowane jako ? :

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

Jeśli kiedykolwiek będziesz potrzebować dynamicznie zmieniać nazwy tabel lub kolumn, wiedz, że wiąże się to z własnym ryzykiem bezpieczeństwa i złą praktyką. Można to jednak zrobić poprzez łączenie łańcuchów. Jednym ze sposobów poprawy bezpieczeństwa takich zapytań jest ustawienie tabeli dozwolonych wartości i porównanie wartości, którą chcesz połączyć z tą tabelą.

Należy pamiętać, że ważne jest, aby ustawić zestaw znaków połączenia tylko za pośrednictwem DSN, w przeciwnym razie aplikacja może być podatna na niejasną lukę, jeśli zostanie użyte nieparzyste kodowanie. W przypadku wersji PDO wcześniejszych niż 5.3.6 ustawienie zestawu znaków za pośrednictwem DSN nie jest dostępne, dlatego jedyną opcją jest ustawienie atrybutu PDO::ATTR_EMULATE_PREPARES na false na połączeniu zaraz po jego utworzeniu.

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

To powoduje, że PDO używa natywnych przygotowanych instrukcji DBMS zamiast tylko emulować je.

Należy jednak pamiętać, że PDO po cichu powróci do emulacji instrukcji, których MySQL nie może przygotować natywnie: te, które można wymienić w instrukcji ( źródłowej ).

PDO: połączenie z serwerem MySQL / MariaDB

Istnieją dwa sposoby połączenia z serwerem MySQL / MariaDB, w zależności od infrastruktury.

Standardowe połączenie (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);

Ponieważ PDO został zaprojektowany tak, aby był zgodny ze starszymi wersjami serwera MySQL (które nie obsługiwały przygotowanych instrukcji), musisz jawnie wyłączyć emulację. W przeciwnym razie utracisz dodatkowe korzyści zapobiegania iniekcji , które są zwykle przyznawane przy użyciu przygotowanych oświadczeń.

Kolejnym kompromisem projektowym, o którym należy pamiętać, jest domyślne zachowanie podczas obsługi błędów. Jeśli nie skonfigurowano inaczej, PDO nie pokaże żadnych błędów SQL.

Zdecydowanie zaleca się ustawienie go na „tryb wyjątkowy”, ponieważ zapewnia to dodatkową funkcjonalność podczas pisania abstrakcji trwałości (na przykład: posiadanie wyjątku, gdy narusza się ograniczenie UNIQUE ).

Połączenie z gniazdem

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

W systemach uniksowych, jeśli nazwa hosta to 'localhost' , połączenie z serwerem jest nawiązywane przez gniazdo domeny.

Transakcje bazy danych z PDO

Transakcje w bazie danych gwarantują, że zestaw zmian danych zostanie trwale zapisany tylko wtedy, gdy każde wyciąganie instrukcji zakończy się powodzeniem. Każde niepowodzenie zapytania lub kodu podczas transakcji może zostać przechwycone, a następnie masz możliwość wycofania prób zmian.

PDO zapewnia proste metody rozpoczynania, zatwierdzania i wycofywania transakcji.

$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;
}

Podczas transakcji wszelkie dokonane zmiany danych są widoczne tylko dla aktywnego połączenia. Instrukcje SELECT zwrócą zmienione zmiany, nawet jeśli nie są jeszcze zatwierdzone w bazie danych.

Uwaga : Szczegółowe informacje na temat obsługi transakcji znajdują się w dokumentacji dostawcy bazy danych. Niektóre systemy w ogóle nie obsługują transakcji. Niektóre obsługują transakcje zagnieżdżone, a inne nie.

Praktyczny przykład wykorzystania transakcji z PDO

W poniższej sekcji przedstawiono praktyczny przykład w świecie rzeczywistym, w którym użycie transakcji zapewnia spójność bazy danych.

Wyobraź sobie następujący scenariusz, powiedzmy, że budujesz koszyk na stronę e-commerce i postanowiłeś zachować zamówienia w dwóch tabelach bazy danych. Jeden nazwany orders z polami order_id , name , address , telephone i created_at . A drugi o nazwie orders_products z polami order_id , product_id i quantity . Pierwsza tabela zawiera metadane zamówienia, a druga rzeczywiste produkty , które zostały zamówione.

Wstawienie nowego zamówienia do bazy danych

Aby wstawić nowe zamówienie do bazy danych, musisz zrobić dwie rzeczy. Najpierw INSERT nowy rekord w tabeli orders który będzie zawierał metadane zamówienia ( name , address itp.). Następnie musisz INSERT jeden rekord do tabeli orders_products dla każdego z produktów objętych zamówieniem.

Możesz to zrobić, wykonując coś podobnego do następującego:

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

Będzie to działać świetnie przy wstawianiu nowego zamówienia do bazy danych, dopóki nie wydarzy się coś nieoczekiwanego i z jakiegoś powodu drugie zapytanie INSERT nie powiedzie się. Jeśli tak się stanie, w tabeli orders pojawi się nowe orders , z którym nie będą powiązane żadne produkty. Na szczęście poprawka jest bardzo prosta, wystarczy wykonać zapytania w formie pojedynczej transakcji bazy danych.

Wstawienie nowego zamówienia do bazy danych z transakcją

Aby rozpocząć transakcję przy użyciu PDO , wystarczy beginTransaction metodę beginTransaction przed wykonaniem jakichkolwiek zapytań do bazy danych. Następnie dokonujesz dowolnych zmian w swoich danych, wykonując zapytania INSERT i / lub UPDATE . Na koniec wywołujesz metodę commit obiektu PDO aby wprowadzić zmiany na stałe. Dopóki nie wywołasz metody commit , każda zmiana dokonana w danych do tego momentu nie jest jeszcze trwała i można ją łatwo cofnąć, po prostu wywołując metodę rollback obiektu PDO .

W poniższym przykładzie pokazano wykorzystanie transakcji do wstawienia nowego zamówienia do bazy danych, zapewniając jednocześnie spójność danych. Jeśli jedno z dwóch zapytań nie powiedzie się, wszystkie zmiany zostaną cofnięte.

// 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: Uzyskaj liczbę dotkniętych wierszy przez zapytanie

Zaczynamy od $db , instancji klasy PDO. Po wykonaniu zapytania często chcemy określić liczbę wierszy, na które miało to wpływ. Metoda rowCount() PDOStatement będzie działać dobrze:

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

echo "Deleted $count rows named John";

UWAGA: Tej metody należy używać wyłącznie do określania liczby wierszy, na które mają wpływ instrukcje INSERT, DELETE i UPDATE. Chociaż ta metoda może również działać w przypadku instrukcji SELECT, nie jest spójna we wszystkich bazach danych.

PDO :: lastInsertId ()

Często może się okazać, że konieczne jest uzyskanie automatycznie zwiększonej wartości identyfikatora dla wiersza, który właśnie wstawiono do tabeli bazy danych. Można to osiągnąć za pomocą metody 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

W postgresql i oracle znajduje się słowo kluczowe RETURNING, które zwraca określone kolumny aktualnie wstawianych / modyfikowanych wierszy. Oto przykład wstawienia jednego wpisu:

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