Класс Sql

Методы для работы с базой данных и защиты от sql-инъекций.

Sql::getDb()

Для работы напрямую с методами расширения mysqli требуется объект, представляющий текущую базу данных.

  • Возвращает объект, представляющий подключение к серверу MySQL

Основные примеры применения расширения mysqli

Если вы решите обойтись только функциями и методами расширения 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-выражения

Многократное выполнение запросов с помощью подготавливаемых выражений

Параметризованные запросы называются также подготавливаемыми — и вот почему. Если необходимо многократно выполнять один и тот же 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::query($sql, $params=[], $types='')

Метод для выполнения sql-запросов любого вида.

  • Возвращает

    • объект при запросах SELECT, SHOW, DESCRIBE, EXPLAIN (см. $result в примере) ;
    • число затронутых записей при запросах INSERT, UPDATE, DELETE и др.;
      Обратите внимание на то, что 0 не является ошибкой, а означает, что изменения ни в одной записи не призошли.
    • false в случае неудачи.
      то есть, для выявления ошибки нужно применять код 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'],
    'is' // необязательно
);
if ($result) {
    while ($row = $result->fetch_assoc()) {
        ...
    }
    # Число полученных записей
    $numOfRows = $result->num_rows;
    # Освободить память, занятую результатами запроса
    $result->free();
}

Для запроса SELECT лучше применять метод Sql::select(), который рассматривается ниже. При его использовании освобождение памяти производится автоматически.

После извлечения большого количество данных с помощью запросов SELECT, SHOW, DESCRIBE, EXPLAIN рекомендуется освобождать память, применяя к результату метод free:
$result->free();

Sql::select($sql, $params=[], $types='', $isTab=false)

Метод для выполнения запросов вида: 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::parameterize($value)

Метод для параметризации данных в sql-выражениях. Его следует применять, когда бывает трудно отследить порядок следования данных в sql-выражении, а следовательно, и объявления этих данных в массиве $params метода Sql::query().

  • Возвращает именованный параметр (строка).
  • $value — данное, вставляемое в sql-выражение (строка).

Пример: 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-выражения с помощью метода Sql::parameterize() и одновременно с помощью параметра $params в методе Sql::query() запрещено.
  • Метод Sql::parameterize() регистрирует данные и заменяет их на нумерованные параметры вида {{1}}, так что полученное таким образом sql-выражение можно исполнять только в методах Sql::query() и Sql::select().

Имена таблиц и столбцов параметризовать запрещено!

Sql::sanitize($value)

Защита от sql-инъекций.

  • Возвращает строку, в которой экранированы все специальные символ.
  • $value — строка данных, полученная от внешнего потенциально опасного источника, и используемая в построении sql-запроса.

Подвергайте обработке только данные, а не весь sql-запрос!

Sql::sanitizeInteger($value, $options=[])

  • Возвращает целое число в виде строки.
  • $options — массив с опциями. Если опция только одна, то параметр можно записывать как строку. Ниже приведены доп.опции:
$options = [
    'zero', # Считать "правильными" число 0 и строку '0'    
    'negative', # Считать "правильными" отрицательные числа (например: -9 и '-9')    
];

Для булевых опций можно применять короткую форму записи. Подробности см. в описании метода Arr::formatOptions(), который и применен внутри описываемого метода.

Выражение Sql::sanitizeInteger($value, ['zero','negative']); эквивалентно (string)(int)$value; за исключением случая $value=''; которое дает "0".

Sql::sanitizeName($str)

  • Возвращает ту же строку $str, если она является корректным именем идентификатора MySQL (имя базы данных, таблицы, колонки, индекса, алиаса и т.д.) либо false в противном случае.
  • $str — строка.

Sql::sanitizeTpl($tpl)

  • Возвращает ту же строку $tpl, если она является корректным именем шаблона Blox CMS либо false в противном случае.
  • $tpl — строка.

Sql::tableExists($table)

  • Возвращает true, если таблица существует.
  • $table — имя таблицы. Можно применять символы подстановки:
    • % соответствует 0 или более символов,
    • _ соответствует ровно одному символу.

Пример:

if (Sql::tableExists('%users')) 
    ...    

Sql::build($filter, $filterRequest, $options=[])

  • Возвращает фрагмент строки sql-запроса, созданного на основе массива, полученного из Request::get($blockId, $filter) или Query::get($filter)
  • $filter — строка с одним из значений: 'pick', 'p'. Пока не реализованы: 'search', ...
  • $filterRequest — массив, полученный, например, с помощью Request::get(22, 'pick') или Query::get('pick').
  • $options — массив с опциями (указаны значения по умолчанию):
    $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-инъекций делать не нужно.


См. также