Подготовленные запросы и хранимые процедуры
Большинство баз данных поддерживают концепцию подготовленных запросов. Что это такое? Это можно описать, как некий вид скомпилированного шаблона SQL запроса, который будет запускаться приложением и настраиваться с помощью входных параметров. У подготовленных запросов есть два главных преимущества:
- Запрос необходимо однажды подготовить и затем его можно запускать столько раз, сколько нужно, причём как с теми же, так и с отличающимися параметрами. Когда запрос подготовлен, СУБД анализирует его, компилирует и оптимизирует план его выполнения. В случае сложных запросов этот процесс может занимать ощутимое время и заметно замедлить работу приложения, если потребуется много раз выполнять запрос с разными параметрами. При использовании подготовленного запроса СУБД анализирует/компилирует/оптимизирует запрос любой сложности только один раз, а приложение запускает на выполнение уже подготовленный шаблон. Таким образом подготовленные запросы потребляют меньше ресурсов и работают быстрее.
- Параметры подготовленного запроса не требуется экранировать кавычками; драйвер это делает автоматически. Если в приложении используются исключительно подготовленные запросы, разработчик может быть уверен, что никаких SQL-инъекций случиться не может (однако, если другие части текста запроса создаются с неэкранированным вводом, то SQL инъекция по-прежнему возможна).
Подготовленные запросы также полезны тем, что PDO может эмулировать их, если драйвер базы данных не имеет подобной функциональности. Это значит, что приложение может пользоваться одной и той же методикой доступа к данным независимо от возможностей СУБД.
Пример #1 Повторяющиеся вставки в базу с использованием подготовленных запросов
В этом примере 2 раза выполняется INSERT запрос с разными значениями name и value , которые подставляются вместо соответствующих псевдопеременных:
$stmt = $dbh -> prepare ( «INSERT INTO REGISTRY (name, value) VALUES (:name, :value)» );
$stmt -> bindParam ( ‘:name’ , $name );
$stmt -> bindParam ( ‘:value’ , $value );
// вставим одну строку
$name = ‘one’ ;
$value = 1 ;
$stmt -> execute ();
// теперь другую строку с другими значениями
$name = ‘two’ ;
$value = 2 ;
$stmt -> execute ();
?>
Пример #2 Повторяющиеся вставки в базу с использованием подготовленных запросов
В этом примере 2 раза выполняется INSERT запрос с разными значениями name и value , которые подставляются вместо псевдопеременных ? .
$stmt = $dbh -> prepare ( «INSERT INTO REGISTRY (name, value) VALUES (?, ?)» );
$stmt -> bindParam ( 1 , $name );
$stmt -> bindParam ( 2 , $value );
// вставим одну строку
$name = ‘one’ ;
$value = 1 ;
$stmt -> execute ();
// теперь другую строку с другими значениями
$name = ‘two’ ;
$value = 2 ;
$stmt -> execute ();
?>
Пример #3 Выборка данных с использованием подготовленных запросов
В этом примере производится выборка из базы по ключу, который вводит пользователь через форму. Пользовательский ввод автоматически заключается в кавычки, поэтому нет риска SQL инъекции.
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name = ?» );
$stmt -> execute ([ $_GET [ ‘name’ ]]);
foreach ( $stmt as $row ) print_r ( $row );
>
?>
Пример #4 Вызов хранимой процедуры с выходными параметрами
Если СУБД поддерживает выходные параметры, приложение может пользоваться ими также как и входными. Выходные параметры обычно используют для получения данных из хранимых процедур. Пользоваться выходными параметрами несколько сложнее, так как разработчику необходимо знать максимальный размер извлекаемых значений ещё на этапе задания этих параметров. Если извлекаемое значение окажется больше, чем предполагалось, будет вызвана ошибка.
$stmt = $dbh -> prepare ( «CALL sp_returns_string(?)» );
$stmt -> bindParam ( 1 , $return_value , PDO :: PARAM_STR , 4000 );
// вызов хранимой процедуры
$stmt -> execute ();
print «процедура вернула $return_value \n» ;
?>
Пример #5 Вызов хранимой процедуры с входным/выходным параметром
Можно задать параметр одновременно входным и выходным; синтаксис при этом тот же, что и для выходных параметров. В следующем примере строка ‘привет’ передаётся в хранимую процедуру, а затем эта строка будет заменена возвращаемым значением.
$stmt = $dbh -> prepare ( «CALL sp_takes_string_returns_string(?)» );
$value = ‘привет’ ;
$stmt -> bindParam ( 1 , $value , PDO :: PARAM_STR | PDO :: PARAM_INPUT_OUTPUT , 4000 );
// вызов хранимой процедуры
$stmt -> execute ();
print «процедура вернула $value \n» ;
?>
Пример #6 Неправильное использование псевдопеременной
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name LIKE ‘%?%'» );
$stmt -> execute ([ $_GET [ ‘name’ ]]);
// псевдопеременная может использоваться только в виде отдельного значения
$stmt = $dbh -> prepare ( «SELECT * FROM REGISTRY where name LIKE ?» );
$stmt -> execute ([ «% $_GET [ name ] %» ]);
?>
Подготовленные запросы
Подготовленные запросы рекомендуется использовать вместо обычных запросов, потому что подготовленный запрос — это уже готовое, скомпилированное выражение к которому можно обращаться многократно просто передавая необходимые параметры.
Эти запросы работают намного быстрее и намного безопаснее , особенно, если в запросах используются условия с передаваемыми параметрами.
То есть, подготовленные запросы обеспечивают очень высокую безопасность от sql-инъекций.
Запрос на выборку данных :
Выбрать все из таблицы news , где id = 2 .
Обычно параметр id передается из вне, поэтому создадим переменную $id и в ней будем хранить передаваемые данные (в данном случае — 2).
Данный параметр мы получаем, например, из формы: формы авторизации, формы регистрации и т.д.
И, соответственно, этот параметр попадет в запрос ( id = ‘$id’ ).
С помощью стандартного метода query() выполняем запрос и распечатываем его на экран, используя метод fetchAll() .
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >$id ‘» ;
$result = $pdo -> query ( $sql );
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
После выполнения запроса получим:
Успешное подключение
Array ( [0] => Array ( [id] => 2 [title] => Новость 2 ) )
— все успешно работает, но данный код не безопасен .
Чтобы обеспечить безопасность от sql-инъекций будем использовать подготовленное выражение .
Неименованный плейсхолдер
Определив некий sql-запрос, в качестве параметра указываем метку — ? (вопросительный знак) — так называемый неименованный плэйсхолдер (placeholder — заполнитель).
Этим мы как бы указываем, что здесь должен быть указан некий параметр.
Затем данное выражение мы должны подготовить с помощью метода prepare() — то есть, указываем объекту PDO, что необходимо подготовить данное выражение.
Данный запрос будет скомпилирован, подготовлен и будет ждать своего выполнения
Далее, при необходимости, мы можем выполнять некий код, а в конце скрипта мы можем выполнить данный запрос.
Чтобы выполнить запрос обращаемся к объекту PDOStatement ($result) и вызываем на исполнение метод execute()
Метод execute() — выполняет подготовленное выражение .
В качестве параметра метода execute() передаем массив:
— в качестве первой ячейки мы используем переменную, которую заменил плэйсхолдер , в нашем случае — $id .
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >;
$result = $pdo -> prepare ( $sql );
// При необходимости, здесь мы можем выполнять некий код .
$result -> execute ( array ( $id ));
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
После выполнения запроса получим:
Успешное подключение
Array ( [0] => Array ( [id] => 2 [title] => Новость 2 ) )
— после выполнения запроса в браузере ни чего не изменилось.
Данный запрос уже безопасен и ему не страшны sql-инъекции.
Именованный плейсхолдер
Вместо неименованных плейсхолдеров (?) можно использовать именованные плейсхолдеры (например, для нашего случая — :id) — то есть, мы явно указываем имя данного параметра.
А это значит, что при вызове метода execute(), в качестве параметра, мы уже передаем ассоциативный массив.
В качестве ячейки которого указываем ключ ‘id’ , а в качестве значения данной ячейки указываем параметр $id :
.
$sql = «SELECT id, title FROM news WHERE >;
.
$result -> execute ( array ( ‘id’ => $id ));
или (альтернативная запись для массива):
$result -> execute ([ ‘id’ => $id ]);
.
Связывание параметра запроса с некой переменной .
Метод bindParam()
Метод bindParam() привязывает параметр запроса (плэйсхолдер) к определенной переменной .
Первым параметром он принимает имя плэйсхолдера ( ‘:id’ ), а в качестве второго параметра — переменную ( $id ), к значению которой ( 2 ) будет привязан данный плэйсхолдер.
В качестве третьего параметра указываем тип данных, который будет передаваться в данный плэйсхолдер, в нашем случае — PDO::PARAM_INT (целочисленный тип данных).
.
$result -> bindParam ( ‘:id’ , $id , PDO :: PARAM_INT );
$result -> execute ();
.
try <
$pdo = new PDO ( ‘mysql:host=localhost;dbname=test2’;charset=utf-8 , ‘root’ , » );
$pdo -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION );
echo «Успешное подключение
» ;
> catch exit ( ‘Ошибка подключения:
‘ . $e -> getMessage ());
>
——- Без использования плэйсхолдера ———
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >$id ‘» ;
$result = $pdo -> query ( $sql );
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >;
$result = $pdo -> prepare ( $sql );
// При необходимости, здесь мы можем выполнять некий код .
$result -> execute ( array ( $id ));
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >;
$result = $pdo -> prepare ( $sql );
// При необходимости, здесь мы можем выполнять некий код .
$result -> execute ( array ( ‘id’ => $id ));
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
$id = 2 ;
$sql = «SELECT id, title FROM news WHERE >;
$result = $pdo -> prepare ( $sql );
// При необходимости, здесь мы можем выполнять некий код .
$result -> bindParam ( ‘:id’ , $id , PDO :: PARAM_INT );
$result -> execute ();
print_r ( $result -> fetchAll ( PDO :: FETCH_ASSOC ));
> catch exit ( ‘Ошибка в запросе:
‘ . $e -> getMessage ());
>
?>
Во всех случаях получим:
Успешное подключение
Array ( [0] => Array ( [id] => 2 [title] => Новость 2 ) )