Skip to content

placeholders

Jump to bottom
Oleg Grigoriev edited this page Mar 27, 2016 · 20 revisions

Шаблон запроса: плейсхолдеры

Итак, SQL-запрос, который отправляется в базу, формируется на основании шаблона запроса и последовательности входных данных.

Места в которые следует вставлять данные, отмечаются в шаблоне специальными последовательностями, начинающимися со знака вопроса "?": плейсхолдерами. Существует несколько типов плейсхолдеров для вставки данных различной структуры. Ниже приведён их полный список.

Плейсхолдеры, для удобства, имеют, как краткий, так и полный формат.

Плейсхолдеры для вставки данных

Строка (?, ?string, ?scalar)

Данные вставляются, как обычные строки.

В результате выполнения кода

$pattern = 'INSERT INTO `table` VALUES (?, ?, ?)';
$data = ['string with "quotes"', 25, false];
$db->query($pattern, $data);

Выполняется запрос INSERT INTO table VALUES ("string with "quotes"", "25", "");

Как видим, данные заключены в кавычки, а спецсимволы проэкранированы. Нет надобности предварительно пропускать данные через mysql_real_escape_string(), библиотека сделает всё сама.

И абсолютно тоже самое, но с использованием полного формата плейсхолдера:

$pattern = 'INSERT INTO `table` VALUES (?string, ?scalar, ?)';
$data    = ['string with "quotes"', 25, false];
$db->query($pattern, $data);

Список (?l, ?list)

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

$table = 'table';
$list = ['one', 'two', 'three'];

$pattern = 'INSERT INTO ?table VALUES (?list)';
$data = [$table, $list];
$db->query($pattern, $data);

В результате будет выполнен запрос:

INSERT INTO `table` VALUES ("one", "two", "three")

Раздел SET (?s, ?set)

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

$set = [
    'a' => 'This is A',
    'b' => 'And this is B',
    'c' => 25,
];
$pattern = 'UPDATE `table` SET ?set WHERE `name`=?';
$data = [$set, 'Vasya'];
$db->query($pattern, $data);

В результате будет выполнен запрос:

UPDATE `table` SET `a`="This is A", `b`="And this is B", `c`="25" WHERE `name`="Vasya"

Имена столбцов в примере заключаются в апострофы в стиле MySQL. При использовании другого адаптера (например, SQLite), будет использоваться синтаксис принятый в данной СУБД.

См. также расширенный формат SET.

Множественный VALUES (?v, ?values)

Вставка значений во множественный INSERT (MySQL)

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

$values = [
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9],
];
$pattern = 'INSERT INTO `table` (`a`,`b`,`c`) VALUES ?v';
$data = [$values];
$db->query($pattern, $data);

Будет выполнен запрос:

INSERT INTO `table` (`a`,`b`,`c`) VALUES ("1", "2", "3"), ("4", "5", "6"), ("7", "8", "9")

Вставка имён полей

Имя таблицы (?t, ?table)

define('TABLE_USERS', 'users');
$userId = 5;
$pattern = 'SELECT * FROM ?t WHERE `user_id`=?';
$data = [TABLE_USERS, $userId];
$user = $db->query($pattern, $data)->row();

Будет выполнен запрос:

SELECT * FROM `users` WHERE `user_id`="5"

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

Так же подобное указание таблицы можно использовать совместно с префиксом таблиц.

См. также расширенный формат ?table.

Имя столбца (?c, ?col)

$pattern = 'SELECT ?c FROM ?t WHERE ?c=?i';
$data = ['name', 'users', 'user_id', 17];
$user = $db->query($pattern, $data)->row();

Будет выполнен запрос:

SELECT `name` FROM `users` WHERE `user_id`=17

См. также расширенный вариант COL.

Использование плейсхолдеров вместо имён столбцов может быть полезно в некоторых случаях, но злоупотребление этим может привести к снижению читаемости кода.

Список столбцов (?cols)

Полезно, когда список нужных столбцов формируется отдельно.

$cols = ['a', 'b'];
if (true) {
    $cols[] = 'c';
}
$pattern = 'SELECT ?cols FROM `table` WHERE `id`=?i';
$data = [$cols, 5]
$db->query($pattern, $data);

Будет выполнено:

SELECT `a`,`b`,`c` FROM `table` WHERE `id`=5

См. также расширенный вариант COLS.

ORDER BY (?o, ?order)

Строка: имя столбца по возрастанию которого упорядочивать.

$pattern = 'SELECT * FROM `table` ORDER BY ?o';
$data = ['id'];
$db($pattern, $data); 
SELECT * FROM `table` ORDER BY `id` ASC

Массив: столбец => TRUE (ASC) / FALSE (DESC)

$pattern = 'SELECT * FROM `table` ORDER BY ?order';
$data = [['level' => false, 'id' => true]];
$db($pattern, $data);
SELECT * FROM `table` ORDER BY `level` DESC, `id` ASC

Другие плейсхолдеры

?w, ?where

Условие WHERE.

$pattern = 'SELECT ?c FROM `table` WHERE ?w';
$data = ['id', ['x' => 1, 'y' => 2]];
SELECT `id` FROM `table` WHERE `x`=1 AND `y`=2

Для подробностей см. формат WHERE.

?e, ?escape

Вставка части строки (с экранированием спецсимволов).

Пример, где это может быть полезно:

$pattern = 'SELECT * FROM `content` WHERE `text` LIKE "%?e%"';
$data = [$_GET['search']];

Данное экранируется как и при использовании простого плейсхолдера "?", но не заключается в кавычки.

?q, ?query

Вставка части запроса.

Используется, когда часть запроса формируется отдельно:

if ($exp) {
    $where = '`a` IS NULL';
} else {
    $where = '`a` IS NOT NULL';
}
$pattern = 'SELECT * FROM `table` WHERE ?query';
$data = [$where];
$db->query($pattern, $data);

Это имеет отличие от следующего:

$pattern = 'SELECT * FROM `table` WHERE '.$where;

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

При использовании плейсхолдера ?q, часть запроса будет вставлена без обработки.

??

Если требуется вставить знак вопроса, то можно использовать последовательность "??".

Обычно при использовании шаблонов такой необходимости не должно возникнуть.

Если требуется формировать запрос отдельно, пользуйтесь для его выполнения методом plainQuery().

Точка с запятой

В случае возникновения неоднозначностей можно отделять плейсхолдеры от последующих символом точкой с запятой. Например: ?;, ?set;, ??;.

Модификаторы плейсхолдеров

Плейсхолдеры вставки данных расценивают данные, как строки, которые они экранируют и заключают в кавычки. Любое пустое значение (включая FALSE и NULL) расценивается как пустая строка. Модификаторы позволяют изменить такое поведение.

Внимание: в новых версиях null не нужен. Все null из PHP приводится к NULL в SQL. Для подробностей см. совместимость со старыми версиями.

n
null 		Данное с возможным NULL-значением

В случае, если данное содержит null, то в запрос вставляется значение NULL. В других случаях поведение не изменяется.
i
int 		Целочисленное значение

Данное приводится к INT и вставляется, как число.
f
float 		Вещественное значение

Данное приводится к FLOAT и вставляется, как число.
b
bool 		Логическое значение

Данное приводится к BOOL и вставляется в запрос в виде, поддерживаемом данной СУБД для типа "BOOL". Обычно это "0" и "1".

Пример вставки без модификаторов:

$pattern = 'INSERT INTO `table` VALUES (?, ?, ?)';
$data = ['String', '', null];
$db->query($pattern, $data); // INSERT INTO `table` VALUES("String", "", "");

И с модификатором n:

$pattern = 'INSERT INTO `table` VALUES (?n, ?n, ?n)';
$data = ['String', '', null];
$db->query($pattern, $data); // INSERT INTO `table` VALUES("String", "", NULL);

Модификаторы приведения к числам можно использовать для входных параметров:

$pattern = 'SELECT * FROM `users` WHERE `user_id`=?i';
$data = [$_GET['id']];
$user = $db->query($pattern, $data)->row();

На странице /?id=15qwe будет выполнен запрос

SELECT * FROM `users` WHERE `user_id`=15

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

Модификаторы можно в любых комбинациях использовать друг с другом и с различными типами плейсхолдеров.

Например: ?lni - плейсхолдер ?l с модификаторами n и i - список значений, которые будут приведены к INT, а те, которые содержат null, вставлены, как NULL:

$list = ['1qwe', 2, null, ''];
$pattern = 'INSERT INTO `table` VALUES (?lni)';
$data = [$list];
$db->query($pattern, $data);

Будет выполнено:

INSERT INTO `table` VALUES (1, 2, NULL, 0)

Как видно, модификаторы также имеют краткий и полный форматы. Полный формат модификаторов используется с полным форматом плейсхолдеров и записываются они через дефис:

  • ?list-null
  • ?set-int
  • ?values-null-int
  • ?null - тоже, что и ?string-null и ?n
  • ?int - тоже, что и ?string-int и ?i
  • ?null-bool - тоже, что и ?string-null-bool и ?nb

Хотя использовать плейсхолдеры и модификаторы можно в любых комбинациях, модификаторы имеют смысл только для плейсхолдеров вставки данных (например, для ?query смысла в модификаторах нет). Так же модификатор null можно использовать с любым другим, а вот int и float вместе уже бессмысленно.

Вставка таблицы

В запросе можно указывать имена таблиц в фигурных скобках:

$pattern = 'SELECT * FROM {table}'; // SELECT * FROM `table`

Более подробно об этом см. в разделе про префикс таблиц.

Регулярные и именованные плейсхолдеры

Рассмотренные в этой главе плейсхолдеры можно назвать регулярными (порядковыми).

На вход шаблона с регулярными плейсхолдерами подаётся порядковый массив данных, которые по порядку же вставляются в шаблон.

В случае несоответствия количества данных и количества плейсхолдеров в шаблоне, происходит ошибка.

Есть второй подвид плейсхолдеров - именованные плейсхолдеры, которые описаны в соответствующем разделе.

Clone this wiki locally