SQL запросы в SKY Framework

SKY / WINGS / FIRST /
SQL2
дочерние связи:
Файл первого крыла main/sky.php для работы с БД предоставляет функции: sql(), sqlf(), multi_sql(), qp(), rows(), cnt(), escape(), константа `r`, методы SQL::begin(), SQL::end(), SQL::_(), SQL::pre(), SQL::row(). Для защиты от SQL инъекций, в коде приложений используются шаблоны запросов, а парсингом SQL шаблонов занимается класс SQL. Все шаблоны SQL в SKY нужно писать в не интерполируемой строке (использовать одинарные кавычки), это будет стимулировать применение шаблонов и уменьшит вероятность написания кода допускающего SQL инъекции. Для парсинга используются шаблоны:

шаблоны внутри запросов, для которых подставляется параметр: $. $+ $` $@ $$ @@ !!
шаблоны внутри запросов, для которых не нужен параметр: $_ $_имя-таблицы \1..\9
комбинированный шаблон для подстановки переменного имени таблицы: $_`
префиксы к запросам для изменения выдачи результата: + - ~ > ^ @ % #
(для префиксов параметр в функциях также не нужен)

Функции и методы, работающие с шаблонами SQL, возможно применять в 2 синтаксисах, например:
001
002
003
004
005
<?php
 
mixed sql ((string|SQL$sql_template [, mixed $arg [, mixed $...  ]] );
# или
mixed sql (int $flags, (string|SQL)  $sql_template [, mixed $arg [, mixed $... ]] );

Если первый параметр функции/метода типа integer - будет использован синтаксис номер 2, когда указываются флаги и глубина вызова для показа трассировки. Флаги:
SQL::PARSE - включить парсинг, даже если нет параметров для шаблона
SQL::NO_TRACE - отключить трассировку
SQL::_UPD - генерировать запрос insert в стиле update
SQL::_NC - не использовать запятую при соединении элементов массива
SQL::_OR - соединять элементы массива через OR вместо AND (по умолчанию) при составлении условий WHERE запросов (и др.)
Если после шаблона SQL нет ни одного параметра парсинг не происходит. Но если есть, хотя бы один параметр, парсинг работает и количество параметров должно совпадать с количеством шаблонных подстановок, иначе генерируется ошибка.
Также глубина (deep) для показа места кода где произошла ошибка или в трассировке (0 по умолчанию), может повышаться от 1 до 7. Все константы и глубина должны (могут) соединяться через логическое или "|". Значения для параметра $flags, и синтаксис с флагами, в основном, применяется при core-development (редко), поэтому используются длинные описания, привлекающие внимание. В коде приложений, в основном, используется синтаксис без флагов, короткий, так как, SQL запросы применяются очень часто и в большинстве приложений. Досадно (а может нет?), что запросы к БД во всех PHP трендовых фреймвок идут через использование каких-то бредовых классов, вместо простой функции, с естественным именем sql(). Здесь же, хочу отметить, класс "Query Builder", позволяющий строить запросы унифицированно для разных БД, конечно же нужен, но редко, и в SKY будет оформлен в виде кода третьего крыла в main/w3/q_builder.php. А вот целесообразность существования понятия ORM как такового, сильно сомнительна.

Во все шаблоны, начинающиеся со знака "$", можно подставлять непроверенные внешние данные гарантировано
безопасно с точки зрения SQL инъекций
.

$. - в этот шаблон подставляются числа или значение BOOL (будет преобразовано в 0 или 1). Во время парсинга делается проверка, на числовое значение, если значение не числовое, выполняется инструкция PHP "die" т.е. реакция на любые ошибки как для "error on debug only".

$+ - шаблон для подстановки строк. Данные эскейпятся с помощью функции mysqli_real_escape_string и заключаются в одинарные кавычки. Реакция на массив, как "error on debug only", на остальные ошибки как регистрируемые.

$@ - В этот шаблон можно подставлять массивы, безопасный. Все значения массива также эскейпятся или проверяются на числовое значение, ключи массива приводятся к целочисленным с помощью PHP функции array_values(). Шаблон удобно применять для генерации списков SQL in (...). Этого шаблона можно было бы не делать, но, к сожалению люди, выпускающие стандарты "проглючили". Имхо, совершенно очевидно, что передача массивов-параметров GET в виде par[k']=1&par[k']=2 и др. (допускающее произвольный ключ, включая одинарные кавычки) в 99% случаях совершенно бессмысленно, а чрезвычайно редко, когда это может понадобиться, понятно что функционал приложений всегда можно реализовать по иному. Стандарт должен быть определен так, чтобы ключем массивов, могли быть только строки-простые идентификаторы - /^[a-z][a-z\d_]+$/. Иначе, протокол допускает дополнительную возможность для уязвимостей. Имхо, такое решение, выбрано из-за стремления выбрать идеалистическое решение - "нет плохих символов". Но идеализм, здесь вообще не коррелирует с практикой и только вредит. На "чаше весов" однозначно перевешивает необходимость защитить протокол, а не идеалистическая прихоть. Люди, выпускающие стандарты, не должны выбирать всегда идеалистические решения, но нужно выбирать решения продиктованные практической необходимостью. Этот философский вопрос, здесь описан кратко.

$$ - в этот шаблон подставляются объекты класса SQL (уже пропарсенные строки), также можно подставить без ошибки числа, пустую строку, это не даст возможности для SQL инъекций, но позволяет сократить код приложения.

$` - безопасный. Подставляется массив. Применяется для безопасной подстановки имени колонки таблицы из непроверенных внешних данных. Первым элементом массива должны быть эти сами данные, а вторым callable или несколько элементов массива - строк (белый список). Если белый список большой, нужно использовать callable функцию или метод, который соберет этот массив. Имена колонок. которые совпадают с именами ключевых слов MySQL (или другой БД), необходимо писать прямо в шаблоне.
001
002
003
004
005
006
007
<?
$desc sql('+select `desc` from article limit 1'); # имя колонки прямо в шаблоне заключено в обр. кавычки
 
$q sql('select * from article order by $`', [
  $_GET['column_name'],
  'id''desc''date_added'
]);

Если переменная $_GET['column_name'] не входит в белый список, зарегистрированной ошибки не будет, но выполнится инструкция PHP die; и будет показана страница "Ошибка 404".

$_` - шаблон для подстановки имен таблиц

@@ - шаблон для подстановки массивов. В этот шаблон нельзя подставлять непроверенные внешние данные напрямую. Он предназначен для подстановки массивов, сформированных программистом. Нужно помнить одно простое правило: если шаблон начинается не со знака "$", это может быть опасно. В этом шаблоне значения массива по умолчанию эскейпятся. Для каждого элемента массива допустимо модифицировать поведение шаблонизатора, чтобы это сделать, нужно первым символом ключа установить "!" или "." или "$" или "`". Установка "+" не имеет смысла так как, по умолчанию, все значения массива эскейпятся и без того. В этом примере:
001
002
003
004
005
006
007
008
<?
if (in_array($_GET['col'], ['c1''c2''c3'])) {
sql('update $_ set @@ where id=$.', [
  'name' => $_GET['name'], # эскейпинг значения есть
  '!upd_time' => 'now()',
  '`' $_GET['col'] => $_GET['data'], # эскейпинг есть, защита ключа (обратными кавычками) также есть
], $_GET['id']);
}
если забыть сделать проверку в белом списке допустимых колонок таблицы, для переменной $_GET['col'], возможно неожиданное разрушение данных в этой таблице, но префикс "`" не даст возможности для SQL инъекций в классическом понимании. Кстати, это единственное место, где без конкатенации или строки с интерполяцией не обойтись.

!! - тоже как и предыдущий шаблон, опасный, данные по умолчанию не эскейпятся. Нужно стараться как можно меньше использовать данный шаблон. Элементы массива можно делать безопасными используя символы "." или "$" или "+" или "`"

\1 .. \9 - обратная ссылка. Чтобы не подставлять повторно параметр, можно указать этот шаблон. Цифра от 1 до 9 указывает номер параметра, который нужно продублировать в другом месте шаблона SQL.

$_... - для этого шаблона не нужно использовать параметр в функциях sql(), qp(), SQL::_(), SQL::pre(). Шаблонизатор заменит $_ на имя дежурной таблицы (значение имени берется из SKY::$me) заключенную в косые кавычки. А такой шаблон $_tbl будет заменен на `tbl`. Если в SKY проекте указанно использование префиксов к таблицам, то замена будет, например такая: `my_tbl`. Здесь "my_" - префикс для имен таблиц, указанный в конфигурации проекта.

Функция sql() заменяет функцию mysqli_query() (если, к примеру, используется модуль mysqli_), кроме того, проверяет результат выполнения на наличие ошибок и регистрирует их, делает трассировку SQL запросов. Для запросов DELETE, UPDATE, REPLACE, дополнительно выполняет mysqli_affected_rows(), что часто бывает необходимо. Для запросов INSERT, дополнительно выполняет mysqli_insert_id(). Для запросов возвращающих данные, SELECT... или например SHOW, имеет расширенный синтаксис.

Если делать обычный запрос, без префиксов, функция наиболее близко соответствует простому запросу с использованием mysqli_query(). При использовании префиксов, модификация функционала следующая:

Одно значение:
+select.. - часто бывает нужно просто прочитать в БД содержимое одной ячейки одного ряда. В этом случае удобно использовать такой префикс. Функция прямо вернет значение этой ячейки.

Один ряд:
-select.. - дополнительно выполняется mysqli_fetch_row(). Префикс удобно использовать, когда нужно считать один ряд таблицы и выдать результат в виде числового массива, например доступ к значению `id` - $ary[0]. С этим префиксом удобно использовать оператор list:
001
002
<?
list ($id$name) = sql('-select id, name from ...');

~select.. - дополнительно выполняется mysqli_fetch_assoc(). Функционал подобен предыдущему случаю, но результат возвращается в виде ассоциативного массива, например доступ к значению `id` - $ary['id'].

>select.. - дополнительно выполняется mysqli_fetch_object(). Результат выдается в виде объекта, например $row->id.

^select.. - запрос с таким префиксом, предназначен для работы по схеме: eval(sql("^select ..")). При таких вызовах генерируются переменные с префиксом $r_ соответствующие именам колонок запроса, например $r_id.

Множество рядов:
@select.. - считывает все ряды результата и помещает их в числовой массив, причем ключами массивов, являются значения первой указанной в запросе SELECT колонки. Это более удобно в практическом использовании, чем, когда ключами массивов, является просто порядковая нумерация рядов запроса, результат который возвращается функциями типа fetchAll().

%select.. - тоже самое как в предыдущем случае: ключи - значения первой колонки, а ключи вложенных массивов - имена колонок таблицы. Если в запросе всего 2 колонки, то значения не массивы, а значения второй колонки результатов. Такая выдача результатов, опять таки более удобна и практична.

#select.. - тоже самое, но вместо массивов, используется для каждого ряда результата объекты класса stdClass. Например:

001
002
003
<?
$all sql('#select id, ...');
foreach ($all as $id => $row) if ($row->title) ... 

Функция sqlf() не использует для парсинга класс SQL, но вместо него использует парсинг на основе синтаксиса шаблонов функции printf. При этом, парсинг работает быстрее, но функция не позволяет строить произвольные запросы. Она полностью безопасна с точки зрения SQL инъекций. В шаблон `%d` гарантированно подставится число, а в `%s` будет использован эскейпинг. Во втором случае можно подставлять и массивы:

001
002
003
004
<?php
 
sqlf('update $_ set q=1+q where mode in (%s)'$_POST['modes']); # parameter is an array
# update articles set q=1+q where mode in ('read', 'write')

Функция multi_sql() предназначена для выполнения нескольких SQL запросов, перечисленных через запятую. Запросы передаваемые в эту функцию должны иметь тип объекта SQL и должны быть сформированы функцией qp() и методами SQL::_() или SQL::pre().

Функция qp() и методы SQL->_() и SQL->pre() используются для построения динамически составленных запросов и могут иметь теже параметры что и функция sql(), например:

001
002
003
004
005
006
007
008
009
010
011
<php
 
in_array($_GET['mode_id'], range(17)) or die; # deny hacking attempt via white list
$where qp('where mode_id=$.'$_GET['mode_id']);
if ('' !== $_GET['txt']) {
  $where->_(' and txt like $+''%' preg_replace('/%/u''%%'$_GET['txt']) . '%'); # used "append"
}
for ($q sql('select * from blog $$'$where); eval(r); ) {
  ...
}
 

Константа `r` содержит исполняемый код и предназначена для работы по схеме:

001
002
003
004
005
$q sql("select * from articles");
for (; eval(r); ) {
  # you can access column value like $r_id for each row
  # or all row like $r, so one column like $r['id']
}

При этом значения, каждого считанного ряда помещаются в переменные с префиксом $r_, например $r_id. Также весь ряд значений помещается в числовой массив, подобно как работает $r = sql("~select * from ..."); # one row

Методы SQL::begin() и SQL::end() предназначены для организации транзакций в таблицах innoDB, по схеме:

001
002
003
004
005
<?php
if (SQL::begin()) {
    # several SQL queries and PHP code
    SQL::end()
}

Если начало транзакции удачно, выполняется несколько SQL запросов и PHP код. В конце транзакции нужно выполнить SQL::end(). Если были ошибки в SQL или PHP, делается ROLLBACK, иначе выполняется COMMIT и транзакция закрывается. Если SQL::begin() имеет параметр, дополнительно можно сделать "LOCK TABLES" ... вначале транзакции и "UNLOCK TABLES" в конце. Если SQL::end() имеет "callable" параметр и в течении транзакции произошла ошибка, - метод или функция, содержащаяся в параметре, будет вызвана. Если ошибок не было, функция не будет вызвана.

Цель создания класса SQL и используемых шаблонов - максимально обезопасить код приложений от SQL инъекций, сделать работу с БД гибкой, простой и удобной, генерировать максимальное количество ошибок, в случае передачи в шаблон неверных данных. Последнее будет стимулировать разработчиков писать верный код валидации или крышевания переменных перед передачей их в шаблоны SQL.

В будущем планируется создание утилиты в DEV.SKY, с помощью которой можно автоматически проверить защиту всего кода приложений от SQL инъекций. Идея такая: если во всех запросах используется неинтерполируемая строка и не используется шаблон "!!" (или используется, но значение - неинтерполируемая строка или использование только безопасных шаблонов) и шаблон "@@" также используется безопасно - то код гарантировано защищен от самых опасных инъекций первого рода.
опубликовано ENERGY - 19 Sep 2015 05:29 GMT
последнее редактирование - 10 Jul 2018 14:54 GMT
 +  0  -  комментировать