MySQL: Запрос LIMIT OFFSET с подготовленными параметрами PHP PDO
C «ленивой» привязкой (с использованием массива в execute()) параметров PDO обрабатывает каждый параметр как строку. В результате подготовленный запрос LIMIT ?, ? преобразуется в LIMIT ’10’, ’10’. Строковые параметры являются недопустимым синтаксисом. В результате заброс не будет выполнен корректно.
Первое решение
Отключить режим эмуляции подготовленных запросов, который включен по умолчанию. Для этого атрибуту объекта PDO PDO::ATTR_EMULATE_PREPARE нужно задать значение false.
$start = 0; $limit = 30; $conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false ); $sql = "SELECT `id` FROM `test` LIMIT :start, :limit "; $st = $pdo->prepare( $sql ) if( $st->execute([ ':start' => $start, ':limit' => $limit, ]) ) < foreach ( $st as $row ) < print_r($row); >>
Второе решение
Указываем параметры не массивом в execute(), а отдельно с указанием типа данных — PDO::PARAM_INT.
$start = 0; $limit = 30; $sql = "SELECT `id` FROM `test` LIMIT :start, :limit "; $st = $pdo->prepare( $sql ) $st->bindValue(':start', $start, PDO::PARAM_INT); $st->bindValue(':limit', $limit, PDO::PARAM_INT); if( $st->execute() ) < foreach ( $st as $row ) < print_r($row); >> Третье решение
Выходим за рамки подготовленных параметров PDO и используем функицю sprintf().
$start = 0; $limit = 30; $sql = "SELECT `id` FROM `test` LIMIT %d, %d "; $sql = sprintf( $sql, $start, $limit ); $st = $pdo->prepare( $sql ) if( $st->execute() ) < foreach ( $st as $row ) < print_r($row); >>
Как в PDO значение LIMIT при запросе сделать INT?
Т.е. я не знаю по скольким ячейкам будет вестись выборка. по 1, по 2, по 3 или по 7.
Например, по одной понятно.
$mail = 'mail@mail.ru'; $limit = '1'; $stmt = $this -> db -> prepare(SELECT `id` FROM `users` WHERE BINARY `mail` = ? LIMIT ?); $stmt->bindParam(1, $mail); $stmt->bindParam(2, $limit, PDO::PARAM_INT); $stmt -> execute(); $row = $stmt -> fetchColumn(); $print_r($row);А вот как сделать это со множеством?
Цикл с подстановкой в bindParam?
Я читал, что можно еще отключить режим эмуляции, но я не знаю, как это повлияет на безопасность и повлияет ли вообще.
Хороший вопрос. Как раз показывает убогость стандартной системы плейсхолдеров.
В моей библиотеке для работы c MySQL тип указывается самым простым и эффективным способом — прямо в плейсхолдере:
//первый запрос (лимит здесь не нужен) $id = $db->getOne("SELECT id FROM users WHERE mail = ?s AND pass = ?s", $mail, $pass); // второй запрос $sql = "SELECT id, name FROM news WHERE category = ?s AND subcategory = ?s LIMIT ?i"; $news = $db->getIndCol('id', $sql, $cat, $subcat, $limit);Как видно из этих примеров, тип ставится в самом плейсхолдере и все входящие данные обрабатываются корректно.
Вернемся теперь к несчастному PDO.
Цикл, увы, не поможет. Потому что мы не знаем, какой тип использовать для привязки. То есть, все сведется к дефолтному PARAM_STR и в итоге мы получим то же самое execute() с массивом, только в профиль.
Если вдруг возникнет идея определять тип по составу переменной, то делать это НИ В КОЕМ СЛУЧАЕ НЕЛЬЗЯ. Если бы мог, я бы выделил ещё большим шрифтом и красным цветом. Потому что практически каждый продвинутый пользователь похапе рано или поздно наступает на эти грабли. Если число, хранящееся в MySQL, всегда можно безопасно сравнивать со строкой, то наоборот — это будет катастрофа: MySQL будет пытаться привести содержимое поля к числу. То есть, если взять пример из вопроса, и пытаться определить тип привязки по содержимому переменной, то при введенном пароле 12345 ctype_digit() скажет нам использовать INT и в итоге пароль 12345 подойдет к любому паролю вида «12345буквы».
Так что цикл — не вариант.
Режим эмуляции на безопасность не влияет. Некоторые неграмотные разработчики считают, что отключение режима эмуляции повышает безопасность, но это неправда. В обоих вариантах PDO работает одинаково безопасно.
В любом случае, отключение эмуляции действительно решает проблему. Так что в данном случае это будет самое простое решение.
Так что либо в параметрах DSN, либо с помощью
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);отключаем эмуляцию и наслаждается лимитом (правда, в этом случае перестанет работать такая фича, как несколько именованных плейсхолдеров с одним и тем же именем, но тут уж приходится выбирать).
И последнее замечание.
Функцию для выполнения запросов писать в общем-то нет смысла. PDO достаточно лаконичен и сам по себе. Единственное, что мешает писать однострочники — это дурацкая execute(), которая возвращает не себя, а булево значение. Но это легко исправить, и в итоге код получится ненамного длиннее, чем при вызове функции, но гораздо более гибким (ненавижу скроллинг, поэтому выношу параметры на другие строчки):
// с функцией $sql = 'SELECT `user_pass` FROM `users` WHERE `user_mail` = :mail LIMIT :lim'; $param = array(':mail' => 'vlad-dub1994@mail.ru', ':lim' => 1); $data = select($sql,$data); // с патченым PDO $data = DB::prepare($sql)->execute($data)->fetch(); // или другой вариант записи $data = DB::prepare('SELECT user_pass FROM users WHERE user_mail = :mail LIMIT :lim') ->execute([':mail' => 'vlad-dub1994@mail.ru', ':lim' => 1]) ->fetch();$sql = 'INSERT INTO users VALUES(. )'; $param = array(NULL, 'vlad-dub1994@mail.ru', 'pass'); DB::prepare($sql)->execute($data); //OK select($sql,$data); // ошибка из-за fetch()$sql = 'SELECT id FROM tree WHERE parent_id=?'; $subcat = DB::prepare($sql)->execute([$parent])->fetchAll(PDO::FETCH_COLUMN);