Методы для работы с базой данных и защиты от sql-инъекций.
Для работы напрямую с методами расширения mysqli требуется объект, представляющий текущую базу данных.
Возвращает объект, представляющий подключение к серверу MySQL
Если вы решите обойтись только функциями и методами расширения mysqli, то из всех методов класса Sql, вам понадобиться только метод Sql::getDb().
Пример (процедурный стиль)
$sql = 'SELECT * FROM '.Blox::getTbl('news').' WHERE dat3=\'политика\'';
if ($result = mysqli_query(Sql::getDb(), $sql)) {
$num = mysqli_num_rows($result);
mysqli_free_result($result); // рекомендуется
}
Пример с параметризованным выражением (объектно-ориентированный стиль)
$db = Sql::getDb();
$city = 'Екатеринбург';
$sql = 'SELECT region FROM cities WHERE city=?' # параметризованное выражение
if ($stmt = $db->prepare($sql)) {
$stmt->bind_param('s', $city); # связываем параметры с метками
$stmt->execute(); # выполняем запрос
$stmt->bind_result($region); # связываем переменные с результатами запроса
$stmt->fetch(); # получаем значения
echo 'Город '.$city.' находится в регионе: '.$region;
$stmt->close(); # закрываем запрос
}
Параметризованные запросы называются также подготавливаемыми — и вот почему. Если необходимо многократно выполнять один и тот же sql-запрос, например, в цикле, то лучше один раз подготовить sql-запрос, а затем его выполнять, меняя только данные согласно следующего примера:
$db = Sql::getDb(); $sql = 'SELECT dat3 FROM '.Blox::getTbl('shop/admin/sale/histories').' WHERE dat1=?'; if ($stmt = $db->prepare($sql)) { $stmt->bind_param('i', $k); // $k — указатель на переменную echo' <ul>'; foreach ($tab as $dat) { $k = $dat['rec']; $stmt->execute(); $stmt->bind_result($v); $stmt->fetch(); echo' <li>'.$dat[1].': '.$v.'</li>'; } echo' </ul>'; $stmt->close(); }
Для поиска в ваших кодах запросов, которые можно было бы оптимизировать, можно включить логирование одинаковых SQL-запросов.
В представленных ниже методах работа с параметризованными выражениями сильно упрощена, так как там не нужно каждый раз вызывать все эти методы: prepare, bind_param, execute, bind_result, fetch, close.
Не требуется также определять объект $db — это делается автоматически.
Единственный их минус — они неэффективны при многократном вызове в цикле. В этом случае лучше вернуться к методу prepare().
Метод для выполнения sql-запросов любого вида.
Возвращает
if (false === Sql::query($sql))$sql — строка обычного sql-запроса или параметризованного (подготовленного) sql-выражения.
В параметризованном выражении конкретные данные заменяются знаком: ?, а сами данные нужно в том же порядке поместить в массив $params.
Имена таблиц и столбцов параметризовать запрещено. В таких случаях для защиты от sql-инъекций используйте метод Sql::sanitizeName().
Если данное из массива $params имеет тип string, или это явно указано в параметре $types, то в sql-выражении соответствующую метку (?) кавычками обрамлять не нужно.
$params (необязательный параметр) — данные для параметризованного sql-выражения.
Если типы данных в массиве $params будут соответствовать типам столбцов таблицы, третий параметр $types можно не применять.
Поэтому, если тип данного неизвестен, желательно делать преобразование типа, соответствующее типу поля, например: $params=[(int)$var1, (string)$var2].
Параметризация null данных
Данные null могут также быть параметрами при операциях вставки и обновления. Использовать нужно именно php-данное null, а не строку 'NULL', пример: $params[]=null;
Значение null нельзя использовать в сравнениях, то есть, запрещено выражение WHERE dat1=?. В этом случае нужно писать WHERE dat1 IS NULL или WHERE dat1 IS NOT NULL.
Запрещено передавать данное null в поле типа NOT NULL. Чтобы в такое поле не попал null, делайте, например, преобразование типа $params[]=(int)$var;
$types (необязательный параметр) — строка, содержащая один или более символов, каждый из которых явно задает тип данного из массива $params. Возможные символы: i(integer), d(double), s(string), b(blob). Используйте этот параметр, если без явного указания типа данных запрос не работает.
Пример
$result = Sql::query(
'SELECT * FROM '.Blox::getTbl('media_nav').' WHERE `block-id`=? AND dat3=?',
[200,'photo.jpg']
);
if ($result) {
while ($row = $result->fetch_assoc()) {
...
}
# Число полученных записей
$numOfRows = $result->num_rows;
# Освободить память, занятую результатами запроса
$result->free();
}
Для запроса SELECT лучше применять метод Sql::select(), который рассматривается ниже. При его использовании освобождение памяти производится автоматически.
$result->free();
Метод для выполнения запросов вида: SELECT * FROM. Подробности, касающиеся параметров $sql, $params, $types, см. в описании метода Sql::query().
Возвращает нумерованный массив ассоциативных массивов (данные строки таблицы).
$sql — строка sql-запроса вида "SELECT * FROM ..." или параметризованное sql-выражение.
Если вы имеете дело с таблицей штатных данных (вида префикс$шаблон), и в sql-запросе нет сортировки, не забывайте добавлять сортировку по колонке sort: ORDER BY sort.
$params (необязательный параметр) — данные параметризованного sql-выражения.
$types (необязательный параметр) — строка, содержащая один или более символов, каждый из которых задает тип данного из массива $params.
$isTab — выводить в формате переменной шаблона $tab, то есть, вместо наименований полей таблицы (dat9), ключами будут просто номера (9). При этом, должны извлекаться все данные, то есть запрос должен начинаться с SELECT * .
Этот параметр имеет смысл применять только для таблиц со штатными данными (вида префикс$шаблон). Для этого случая, когда нужно извлекать только одну строку, есть более простой метод: Dat::get().
Пример
$tab = Sql::select(
'SELECT * FROM '.Blox::getTbl('media_nav').' WHERE `block-id`=? ORDER BY sort',
[200], '', true
);
Метод для параметризации данных в sql-выражениях. Его следует применять, когда бывает трудно отследить порядок следования данных в sql-выражении, а следовательно, и объявления этих данных в массиве $params метода Sql::query().
Пример: sql-выражение собирается из фрагментов, разбросанных по разным частям кода.
$sSql = '';
$wSql = '';
...
$sSql .= ' dat3='.Sql::parameterize($_GET['dat3']);
...
if (...)
$wSql .= ' dat1='.Sql::parameterize($_GET['dat1']);
else
$wSql .= ' dat2='.Sql::parameterize($_GET['dat2']);
...
Sql::query('UPDATE '.Blox::getTbl('news').' SET'.$sSql.' WHERE'.$wSql);
Примечания
Защита от sql-инъекций.
$options = [
'zero', # Считать "правильными" число 0 и строку '0'
'negative', # Считать "правильными" отрицательные числа (например: -9 и '-9')
];
Для булевых опций можно применять короткую форму записи. Подробности см. в описании метода Arr::formatOptions(), который и применен внутри описываемого метода.
Выражение Sql::sanitizeInteger($value, ['zero','negative']); эквивалентно (string)(int)$value; за исключением случая $value=''; которое дает "0".
Пример:
if (Sql::tableExists('%users'))
...
$options = [
'parameterize'=>false, # Данные будут параметризованы с помощью Sql::parameterize(). В этом случае остальные данные всего sql-выражения должны также параметризоваться с помощью этого метода.
'case-sensitive'=>false, # Сделать запросы регистро-зависимыми.
'tbl'=>'', # Имя таблицы, если вам нужно ставить его перед именем столбца, например '$news'
];
Для булевых опций можно применять короткую форму записи. Подробности см. в описании метода Arr::formatOptions(), который и применен внутри описываемого метода.
Пример:
Query::capture();
$pickRequest = Query::get('pick');
$sql = Sql::build('pick', $pickRequest, 'parameterize');
$table = Sql::select('SELECT * FROM $news WHERE '.$sql);
Если нужно делать обращения только к одной записи в базе данных, то удобнее пользоваться классом Dat или Data. В них все данные параметризуются автоматически, поэтому никаких специальных мероприятий по защите от sql-инъекций делать не нужно.