Большинство баз данных поддерживают концепцию подготовленных запросов. Что это такое? Это можно описать, как некий вид скомпилированного шаблона SQL запроса, который будет запускаться приложением и настраиваться с помощью входных параметров. У подготовленных запросов есть два главных преимущества:
Подготовленные запросы также полезны тем, что PDO может эмулировать их, если драйвер базы данных не имеет подобной функциональности. Это значит, что приложение может пользоваться одной и той же методикой доступа к данным независимо от возможностей СУБД.
Пример #1 Повторяющиеся вставки в базу с использованием подготовленных запросов
В этом примере 2 раза выполняется INSERT запрос с разными значениями
name и value, которые подставляются
вместо соответствующих псевдопеременных:
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);
// вставим одну строку
$name = 'one';
$value = 1;
$stmt->execute();
// теперь другую строку с другими значениями
$name = 'two';
$value = 2;
$stmt->execute();
?>
Пример #2 Повторяющиеся вставки в базу с использованием подготовленных запросов
В этом примере 2 раза выполняется INSERT запрос с разными значениями
name и value, которые подставляются вместо
псевдопеременных ?.
<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
// вставим одну строку
$name = 'one';
$value = 1;
$stmt->execute();
// теперь другую строку с другими значениями
$name = 'two';
$value = 2;
$stmt->execute();
?>
Пример #3 Выборка данных с использованием подготовленных запросов
В этом примере производится выборка из базы по ключу, который вводит пользователь через форму. Пользовательский ввод автоматически заключается в кавычки, поэтому нет риска SQL инъекции.
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
$stmt->execute([$_GET['name']]);
foreach ($stmt as $row) {
print_r($row);
}
?>
Пример #4 Вызов хранимой процедуры с выходными параметрами
Если СУБД поддерживает выходные параметры, приложение может пользоваться ими также как и входными. Выходные параметры обычно используют для получения данных из хранимых процедур. Пользоваться выходными параметрами несколько сложнее, так как разработчику необходимо знать максимальный размер извлекаемых значений ещё на этапе задания этих параметров. Если извлекаемое значение окажется больше, чем предполагалось, будет вызвана ошибка.
<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);
// вызов хранимой процедуры
$stmt->execute();
print "процедура вернула $return_value\n";
?>
Пример #5 Вызов хранимой процедуры с входным/выходным параметром
Можно задать параметр одновременно входным и выходным; синтаксис при этом тот же, что и для выходных параметров. В следующем примере строка 'привет' передаётся в хранимую процедуру, а затем эта строка будет заменена возвращаемым значением.
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'привет';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);
// вызов хранимой процедуры
$stmt->execute();
print "процедура вернула $value\n";
?>
Пример #6 Неправильное использование псевдопеременной
<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute([$_GET['name']]);
// псевдопеременная может использоваться только в виде отдельного значения
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(["%$_GET[name]%"]);
?>Note that when using name parameters with bindParam, the name itself, cannot contain a dash '-'.
example:
<?php
$stmt = $dbh->prepare ("INSERT INTO user (firstname, surname) VALUES (:f-name, :s-name)");
$stmt -> bindParam(':f-name', 'John');
$stmt -> bindParam(':s-name', 'Smith');
$stmt -> execute();
?>
The dashes in 'f-name' and 's-name' should be replaced with an underscore or no dash at all.
See http://bugs.php.net/43130
AdamExample #5 gives an 1414 wenn tried on MariaDB. Use this function to call a stored procedure with the last parameter as INOUT returning a value like a (uu)id or a count;
<?php
/**
* call_sp Call the specified stored procedure with the given parameters.
* The first parameter is the name of the stored procedure.
* The remaining parameters are the (in) parameters to the stored procedure.
* the last (out) parameter should be an int like state or number of affected rows.
*
* @param mixed $sp_name The name of the stored procedure to call.
* @param mixed $params The parameters to pass to the stored procedure.
* @return int The number of affected rows.
*/
function call_sp( \PDO $db, string $sp_name, ...$params ): mixed
{
$placeholders = array_fill( 0, count( $params ), "?" );
$placeholders[] = "@new_id";
$sql = "CALL $sp_name( " . implode( ", ", $placeholders ) . " ); SELECT @new_id AS `new_id`";
try {
LOG->debug( "calling Stored Procedure", [ "sql" => $sql ] );
$stmt = $db->prepare( $sql );
$i = 0;
foreach( $params as $param ) {
$stmt->bindValue( ++$i, $param );
}
$stmt->execute();
$new_id = $stmt->fetch( PDO::FETCH_ASSOC )['new_id'];
return $new_id;
} catch ( \Exception $e ) {
LOG->error( "Error calling Stored Procedure", [ "sql" => $sql, "params" => $params, "error" => $e->getMessage() ] );
throw $e;
}Insert a multidimensional array into the database through a prepared query:
We have an array to write the form:
$dataArr:
Array
(
[0] => Array
(
[0] => 2020
[1] => 23
[2] => 111111
)
[1] => Array
(
[0] => 2020
[1] => 24
[2] => 222222222
)
....
Task: prepare a request and pass through binds
$array = [];
foreach ($dataArr as $k=>$v) {
// $x = 2020, the variable is predetermined in advance, does not change the essence
$array[] = [$x, $k, $v];
}
$sql = ("INSERT INTO `table` (`field`,`field`,`field`) VALUES (?,?,?)");
$db->queryBindInsert($sql,$array);
public function queryBindInsert($sql,$bind) {
$stmt = $this->pdo->prepare($sql);
if(count($bind)) {
foreach($bind as $param => $value) {
$c = 1;
for ($i=0; $i<count($value); $i++) {
$stmt->bindValue($c++, $value[$i]);
}
$stmt->execute();
}
}
}