Добавление данных в PDO и параметризация запросов

Последнее обновление: 08.06.2021

Для добавления данных в БД MySQL применяется sql-команда INSERT, которая имеет следующий синтаксис:

INSERT INTO название_таблицы (столбец1, столбец2, столбецN) VALUES ( значение1, значение2, значениеN)

Данная команда также выполняется методом exec() объекта PDO. Стоит отметить, что для sql-команд INSERT, UPDATE и DELETE метод exec() возвращает количество затронутных командой строк (добавленных, измененных или удаленных). Таким образом, мы можем узнать сколько строк было добавлено.

Сначала рассмотрим простейшее добавление одного объекта в БД. Для примера возьмем созданную в прошлой теме базу данных "testdb1" и созданную в ней таблицу Users со следующим определением:

CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)

И для добавления определим следующий скрипт PHP:

<?php
try {
	$conn = new PDO("mysql:host=localhost;dbname=testdb1", "root", "mypassword");
	
	// SQL-выражение для добавления данных
	$sql = "INSERT INTO Users (name, age) VALUES ('Tom', 37)";
	
	$affectedRowsNumber = $conn->exec($sql);
    echo "В таблицу Users добавлено строк: $affectedRowsNumber";
}
catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

Команда на добавление здесь выглядит следующим образом:

"INSERT INTO Users (name, age) VALUES ('Tom', 37)"

То есть в столбец name добавляется строка "Tom", а в столбец age - число 37. Для столбца id не добавляется никакого значения, потому что при создании таблицы для него указан параметр AUTO_INCREMENT - то есть значение этого столбца у каждой добавляемой строки будет автоматически увеличиваеться по сравнению с предыдущей на единицу.

При добавлении мы получаем количество добавленных строк в переменую $affectedRowsNumber и затем выводим ее значение в браузере. Поэтому при успешном добавлении мы увидим

В таблицу Users добавлено строк: 1

Множественное добавление

Также мы можем добавить сразу несколько объектов:

<?php
try {
	$conn = new PDO("mysql:host=localhost;dbname=testdb1", "root", "mypassword");
	
	$sql = "INSERT INTO Users (name, age) VALUES 
			('Sam', 41), 
			('Bob', 29), 
			('Alice', 32)";
	$affectedRowsNumber = $conn->exec($sql);
    echo "В таблицу Users добавлено строк: $affectedRowsNumber";
}
catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
}
?>

Здесь в таблицу добавляется три строки. Соответственно в браузере мы увидим:

В таблицу Users добавлено строк: 3

Добавление данных из формы HTML

В большинстве случаев добавляемые данные будут приходить из вне, например, присылаться в запросе пользователя. Рассмотрим добавление данных, отправленных из формы HTML. Для этого определим следующий скрипт:

<!DOCTYPE html>
<html>
<head>
<title>METANIT.COM</title>
<meta charset="utf-8" />
</head>
<body>
<?php
if (isset($_POST["username"]) && isset($_POST["userage"])) {
	
	$username = $_POST["username"];
	$userage = $_POST["userage"];
	try {
		$conn = new PDO("mysql:host=localhost;dbname=testdb1", "root", "mypassword");
		$sql = "INSERT INTO Users (name, age) VALUES ('$username', $userage)";
		$affectedRowsNumber = $conn->exec($sql);
		// если добавлена как минимум одна строка
		if($affectedRowsNumber > 0 ){
			echo "Data successfully added: name=$username  age= $userage";	
		}
	}
	catch (PDOException $e) {
		echo "Database error: " . $e->getMessage();
	}
}
?>
<h3>Create a new User</h3>
<form method="post">
    <p>User Name:
    <input type="text" name="username" /></p>
	<p>User Age:
    <input type="number" name="userage" /></p>
    <input type="submit" value="Save">
</form>
</body>
</html>

Здесь мы проверяем, пришли ли с сервера данные в POST-запросе, которые имеют ключи "username" и "userage":

if (isset($_POST["username"]) && isset($_POST["userage"])) {

Если эти данные имеются, то есть был отправлен post-запрос с данными на добавление, то мы получаем эти данные в переменные и добавляем их в бд.

$sql = "INSERT INTO Users (name, age) VALUES ('$username', $userage)";

Если была добавлена строка, то есть метод exec() возвратил число больше нуля, то выводим пользователю соответствующее сообщение.

После кода php собственно определена форма на добавление данных с помощью post-запроса.

Запустим скрипт. Введем в форму данные и нажмем на кнопку:

Insert data in PHP and PDO in MySQL

И данные будут добавлены. Вроде все хорошо. Однако здесь есть большой недостаток.

Параметризация запросов

Недостаток выше приведенного скрипа заключается в том, что мы никак не констролируем присылаемые данные и сохраняем их в базу данных как есть. Что несет потенциальную угрозу безопасности, особенно при добавлении строк типа "; DELETE FROM `Users`; --. Кроме того, в ряде случае может быть проблематично добавить даже безопасные данные, например, строку, которая содержит одинарную кавычку, типа "Tom O'Brian".

Для решения этих проблем PDO предлагает параметризацию запросов с помощью применения заранее подготовленных выражений - prepared statement. Выражения prepared statement вместо жестко установленных значений или переменных принимают параметры, которые не привязаны к конкретным значениям. Эти выражения prepared statement посылаются серверу базы данных до того, как станут известны используемые данные, что позволяет серверу приготовить их к выполнению, но при этом они не выполняются. А когда пользователь присылает данные - параметры заменяются пришедшими данными, и выражение prepared statement выполняется.

Перепишем предыдущий пример с использованием параметров:

<!DOCTYPE html>
<html>
<head>
<title>METANIT.COM</title>
<meta charset="utf-8" />
</head>
<body>
<?php
if (isset($_POST["username"]) && isset($_POST["userage"])) {
	
	try {
		$conn = new PDO("mysql:host=localhost;dbname=testdb1", "root", "mypassword");
		$sql = "INSERT INTO Users (name, age) VALUES (:username, :userage)";
		// определяем prepared statement
		$stmt = $conn->prepare($sql);
		// привязываем параметры к значениям
		$stmt->bindValue(":username", $_POST["username"]);
		$stmt->bindValue(":userage", $_POST["userage"]);
		// выполняем prepared statement
		$affectedRowsNumber = $stmt->execute();
		// если добавлена как минимум одна строка
		if($affectedRowsNumber > 0 ){
			echo "Data successfully added: name=" . $_POST["username"] ."  age= " . $_POST["userage"];	
		}
	}
	catch (PDOException $e) {
		echo "Database error: " . $e->getMessage();
	}
}
?>
<h3>Create a new User</h3>
<form method="post">
    <p>User Name:
    <input type="text" name="username" /></p>
	<p>User Age:
    <input type="number" name="userage" /></p>
    <input type="submit" value="Save">
</form>
</body>
</html>

В SQL-выражении теперь применяются параметры:

$sql = "INSERT INTO Users (name, age) VALUES (:username, :userage)";

:username и :userage - это названия параметров. Причем они начинаются с символа двоеточия :.

Само выражение prepared statement создается с помощью метода prepare() объекта PDO, в который передается выполняемая sql-команда:

$stmt = $conn->prepare($sql);

Фактически здесь создается объект PDOStatement, который сохраняется в переменную $stmt.

Чтобы связать параметр с конкретным значением у объекта PDOStatement вызывается метод bindValue(). Первый параметр этого метода - собственно параметр из sql-команды, а второй параметр - передаваемое ему значение.

$stmt->bindValue(":username", $_POST["username"]);

Так, в данном случае параметр :username привязывается к значению из $_POST["username"]

Причем привязка может производиться и к конкретным значениям и обычным переменным, например:

$user = "Tom"
// привязка к переменной $user
$stmt->bindValue(":username", $user);

Для выполнения sql-выражения у объекта PDOStatement вызывается метод execute(), который для команды INSERT возвращает число добавленных строк.

Передача значений параметрам через массив по имени

В примере выше для параметризации применялся метод bindValue():

$sql = "INSERT INTO Users (name, age) VALUES (:username, :userage)";
$stmt = $conn->prepare($sql);
// привязываем параметры к значениям
$stmt->bindValue(":username", $_POST["username"]);
$stmt->bindValue(":userage", $_POST["userage"]);
// выполняем prepared statement
$affectedRowsNumber = $stmt->execute();

Но есть и другой способ привязки параметров к значениям - мы можем передать в метод execute() параметры и их значения в виде ассоциативного массива:

$sql = "INSERT INTO Users (name, age) VALUES (:username, :userage)";
$stmt = $conn->prepare($sql);
// через массив передаем значения параметрам по имени
$rowsNumber = $stmt->execute(array(":username" => $_POST["username"], ":userage" => $_POST["userage"]));

В этом случае названия параметров являются ключами.

Передача значений параметрам через массив по позиции

Третий способ привязки значений к параметрам представляет передачу значений по позиции:

$sql = "INSERT INTO Users (name, age) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
// через массив передаем значения параметрам по позиции
$rowsNumber = $stmt->execute(array($_POST["username"], $_POST["userage"]));

В этом случае вместо названий параметров применяются знаки вопроса ?. Для передачи этим параметрам значений в метод execute() также передается массив. Первое значение массива привязывается к первому параметру (условно добавляется вместо первого знака вопроса), второе значение привязывается ко второму параметру и т.д.

НазадСодержаниеВперед

Помощь сайту
Юмани:
410011174743222
Перевод на карту
Номер карты:
4048415020898850