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 (в зависимости от того, как было настроено соединение 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, иначе ваше приложение может быть подвержено неясной уязвимости, если используется некоторая нечетная кодировка. Для версий PDO до 5.3.6 установка кодировки через DSN недоступна, и поэтому единственной опцией является установка атрибута PDO::ATTR_EMULATE_PREPARES на false в соединении сразу после его создания.

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

Это заставляет PDO использовать базовые подготовленные инструкции базовой СУБД вместо того, чтобы просто имитировать его.

Тем не менее, имейте в виду, что 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);

В 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

В следующем разделе показан практический реальный пример, когда использование транзакций обеспечивает согласованность базы данных.

Представьте себе следующий сценарий, предположим, что вы создаете корзину покупок для веб-сайта электронной коммерции, и вы решили сохранить заказы в двух таблицах базы данных. Один из названных orders с поля order_id , name , address , telephone и created_at . А второй - orders_products с поля order_id , product_id и quantity . Первая таблица содержит метаданные порядка, а вторая - фактические продукты , которые были заказаны.

Вставка нового заказа в базу данных

Чтобы вставить новый заказ в базу данных, вам нужно сделать две вещи. Сначала вам нужно INSERT новую запись в таблицу orders которая будет содержать метаданные заказа ( name , address и т. Д.). И тогда вам нужно INSERT одну запись в orders_products таблицу, для каждого из продуктов, которые включены в порядок.

Вы можете сделать это, выполнив что-то похожее на следующее:

// 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 завершится с ошибкой. Если это произойдет, вы получите новый порядок внутри таблицы orders , в котором не будет связанных с ним продуктов. К счастью, исправление очень просто, все, что вам нужно сделать, - это сделать запросы в виде одной транзакции базы данных.

Вставка нового заказа в базу данных с транзакцией

Чтобы начать транзакцию с использованием PDO все, что вам нужно сделать, это вызвать метод beginTransaction прежде чем выполнять какие-либо запросы в вашей базе данных. Затем вы производите любые изменения, которые вы хотите использовать, выполняя запросы INSERT и / или UPDATE . И, наконец, вы вызываете метод commit объекта PDO чтобы изменения были постоянными. Пока вы не назовете метод commit каждое изменение, которое вы сделали с вашими данными до этого момента, еще не является постоянным, и его можно легко вернуть, просто вызвав метод rollback объекта PDO .

В следующем примере показано использование транзакций для вставки нового заказа в базу данных, при одновременном обеспечении согласованности данных. Если один из двух запросов не удался, все изменения будут отменены.

// 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: получить количество затронутых строк по запросу

Мы начинаем с $db , экземпляра класса PDO. После выполнения запроса мы часто хотим определить количество строк, на которые оно повлияло. Метод rowCount() PDOStatement будет работать красиво:

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

echo "Deleted $count rows named John";

ПРИМЕЧАНИЕ. Этот метод следует использовать только для определения количества строк, на которые влияют операторы INSERT, DELETE и UPDATE. Хотя этот метод может работать и для операторов SELECT, он не является согласованным во всех базах данных.

PDO :: lastInsertId ()

Вы часто можете найти необходимость получения значения с добавочным значением для автоматической инкреции для строки, которую вы только что вставили в таблицу базы данных. Вы можете добиться этого с помощью метода 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 существует КОДИРОВАНИЕ ВОЗВРАЩЕНИЯ, которое возвращает указанные столбцы вставленных / модифицированных строк. Здесь пример для вставки одной записи:

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