В системах управления базами данных (СУБД) подготовленный оператор или параметризованный оператор - это функция, используемая для многократного выполнения одних и тех же или похожих операторов базы данных с высокой эффективностью. Обычно используемый с операторами SQL, такими как запросы или обновления, подготовленный оператор принимает форму шаблона, в который при каждом выполнении подставляются определенные постоянные значения.
Типичный рабочий процесс использования подготовленного оператора выглядит следующим образом:
- Подготовка : сначала приложение создает шаблон выписки и отправляет его в СУБД. Некоторые значения остаются неопределенными, они называются параметрами , заполнителями или связываемыми переменными (помечены знаком «?» Ниже):
INSERT INTO products (name, price) VALUES (?, ?);
- Затем СУБД компилирует (анализирует, оптимизирует и переводит) шаблон оператора и сохраняет результат, не выполняя его.
- Выполнить : позднее приложение предоставляет (или связывает ) значения для параметров шаблона оператора, и СУБД выполняет оператор (возможно, возвращая результат). Приложение может выполнять оператор сколько угодно раз с разными значениями. В приведенном выше примере он может сначала указать «велосипед» для первого параметра и «10900» для второго параметра, а затем предоставить «обувь» для первого параметра и «7400» для второго параметра.
По сравнению с непосредственным выполнением операторов подготовленные операторы имеют два основных преимущества: [1]
- Накладные расходы на компиляцию оператора возникают только один раз, хотя оператор выполняется несколько раз. Однако не вся оптимизация может быть выполнена во время компиляции шаблона оператора по двум причинам: лучший план может зависеть от конкретных значений параметров, а лучший план может меняться, поскольку таблицы и индексы меняются с течением времени. [2]
- Подготовленные операторы устойчивы к SQL-инъекции, поскольку значения, которые передаются позже с использованием другого протокола, не компилируются, как шаблон оператора. Если шаблон оператора не получен из внешнего ввода, SQL-инъекция не может произойти.
С другой стороны, если запрос выполняется только один раз, операторы, подготовленные на стороне сервера, могут работать медленнее из-за дополнительного обращения к серверу. [3] Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшировали результаты подготовленных запросов. [4] хранимая процедура , которая также прекомпилирована и хранится на сервере для последующего выполнения, имеет такое же преимущество. В отличие от хранимой процедуры подготовленный оператор обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, вместо этого полагаясь на декларативный язык запросов к базе данных. Благодаря своей простоте и эмуляции на стороне клиента подготовленные операторы более переносимы между поставщиками.
Программная поддержка
Основные СУБД , включая MySQL , [5] Oracle , [6] DB2 , [7] Microsoft SQL Server [8] и PostgreSQL [9], широко поддерживают подготовленные операторы. Подготовленные операторы обычно выполняются через двоичный протокол, отличный от SQL, для эффективности и защиты от внедрения SQL, но в некоторых СУБД, таких как MySQL, подготовленные операторы также доступны с использованием синтаксиса SQL для целей отладки. [10]
Ряд языков программирования поддерживают подготовленные заявления в их стандартных библиотек и будет эмулировать их на стороне клиента , даже если основной СУБД не поддерживает их, в том числе Java «S JDBC , [11] Perl » s DBI , [12] PHP «с PDO [1] и DB-API Python . [13] Эмуляция на стороне клиента может быть быстрее для запросов, которые выполняются только один раз, за счет уменьшения количества циклических обращений к серверу, но обычно медленнее для запросов, выполняемых много раз. Он одинаково эффективно противостоит атакам с использованием SQL-инъекций.
Многие типы атак с использованием SQL-инъекций могут быть устранены путем отключения литералов , что фактически требует использования подготовленных операторов; с 2007 г.[Обновить]только H2 поддерживает эту функцию. [14]
Примеры
Java JDBC
В этом примере используются Java и JDBC :
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource ; import java.sql.Connection ; import java.sql.DriverManager ; импорт java.sql.PreparedStatement ; import java.sql.ResultSet ; import java.sql.SQLException ; import java.sql.Statement ;public class Main { public static void main ( String [] args ) выбрасывает SQLException { MysqlDataSource ds = new MysqlDataSource (); ds . setDatabaseName ( "MySQL" ); ds . setUser ( "корень" ); попробуйте ( Connection соед = дц . GetConnection ()) { попытка ( себе STMT = сопп . createStatement ()) { STMT . executeUpdate ( "СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ продукты (имя VARCHAR (40), цена INT)" ); } попробуйте ( PreparedStatement STMT = подкл . prepareStatement ( "INSERT INTO продукты VALUES (?,?)" )) { STMT . setString ( 1 , «велосипед» ); stmt . setInt ( 2 , 10900 ); stmt . executeUpdate (); stmt . setString ( 1 , «обувь» ); stmt . setInt ( 2 , 7400 ); stmt . executeUpdate (); stmt . setString ( 1 , «телефон» ); stmt . setInt ( 2 , 29500 ); stmt . executeUpdate (); } попробуйте ( PreparedStatement STMT = подкл . prepareStatement ( "SELECT * FROM продуктов WHERE имя =?" )) { STMT . setString ( 1 , «обувь» ); ResultSet rs = stmt . executeQuery (); rs . следующий (); Система . из . println ( rs . getInt ( 2 )); } } } }
Java PreparedStatement
предоставляет "сеттеры" ( setInt(int), setString(String), setDouble(double),
и т. Д.) Для всех основных встроенных типов данных.
PHP PDO
В этом примере используются PHP и PDO :
phptry { // Подключиться к базе данных с именем «mysql» с паролем «root» $ connection = new PDO ( 'mysql: dbname = mysql' , 'root' ); // Выполнение запроса на соединении, который создаст // таблицу "products" с двумя столбцами, "name" и "price" $ connection -> exec ( 'CREATE TABLE IF NOT EXISTS products (name VARCHAR (40), цена INT) ' ); // Готовим запрос для вставки нескольких продуктов в таблицу $ statement = $ connection -> prepare ( 'INSERT INTO products VALUES (?,?)' ); $ products = [ [ 'велосипед' , 10900 ], [ 'обувь' , 7400 ], [ 'телефон » , 29500 ], ]; // Перебираем продукты в массиве «products» и // выполняем подготовленный оператор для каждого продукта foreach ( $ products as $ product ) { $ statement -> execute ( $ product ); } // Готовим новый оператор с указанным параметром $ statement = $ connection -> prepare ( 'SELECT * FROM products WHERE name =: name' ); $ statement -> execute ([ ': name' => 'shoes' , ]); // Использование деструктуризации массива для присвоения названия продукта и его цены // соответствующим переменным [ $ product , $ price ] = $ statement -> fetch (); // Отображаем результат пользователю echo "Цена продукта { $ product } равна \ $ { $ price } ". ; // Закрываем курсор, чтобы можно было снова использовать `fetch` $ statement -> closeCursor (); } catch ( \ Exception $ e ) { echo 'Произошла ошибка:' . $ e -> getMessage (); }
Perl DBI
В этом примере используются Perl и DBI :
#! / usr / bin / perl -w use strict ; использовать DBI ;мои ( $ db_name , $ db_user , $ db_password ) = ( 'my_database' , 'moi' , 'Passw0rD' ); my $ dbh = DBI -> connect ( "DBI: mysql: database = $ db_name" , $ db_user , $ db_password , { RaiseError => 1 , AutoCommit => 1 }) or die "ERROR (main: DBI-> connect) при подключении к базе данных $ db_name: " . $ DBI :: errstr . "\ п" ;$ dbh -> do ( 'СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ продукты (имя VARCHAR (40), цена INT)' );мой $ sth = $ dbh -> prepare ( 'ВСТАВИТЬ В ЗНАЧЕНИЯ продуктов (?,?)' ); $ sth -> выполнить ( @ $ _ ) foreach [ 'велосипед' , 10900 ], [ 'обувь' , 7400 ], [ 'телефон' , 29500 ];$ sth = $ dbh -> prepare ( "ВЫБРАТЬ * ИЗ продуктов, ГДЕ имя =?" ); $ sth -> выполнить ( 'обувь' ); напечатайте "$$ _ [1] \ n" foreach $ sth -> fetchrow_arrayref ; $ sth -> закончить ;$ dbh -> отключить ;
C # ADO.NET
В этом примере используются C # и ADO.NET :
используя ( SqlCommand command = connection . CreateCommand ()) { command . CommandText = "ВЫБРАТЬ * ИЗ пользователей, ГДЕ ИМЯ ПОЛЬЗОВАТЕЛЯ = @ имя пользователя И КОМНАТА = @ комната" ; команда . Параметры . AddWithValue ( "@username" , имя пользователя ); команда . Параметры . AddWithValue ( "@room" , комната ); using ( SqlDataReader dataReader = command . ExecuteReader ()) { // ... } }
ADO.NET SqlCommand
принимает любой тип в качестве value
параметра AddWithValue
, и преобразование типа происходит автоматически. Обратите внимание на использование «именованных параметров» (т.е. "@username"
), а не "?"
- это позволяет вам использовать параметр несколько раз и в любом произвольном порядке в тексте команды запроса.
Однако метод AddWithValue не следует использовать с типами данных переменной длины, такими как varchar и nvarchar. Это связано с тем, что .NET предполагает, что длина параметра равна длине данного значения, а не получает фактическую длину из базы данных посредством отражения. Следствием этого является то, что для каждой длины компилируется и сохраняется отдельный план запроса. Как правило, максимальное количество «повторяющихся» планов - это произведение длин столбцов переменной длины, указанных в базе данных. По этой причине важно использовать стандартный метод Add для столбцов переменной длины:
command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue
, где ParamLength - длина, указанная в базе данных.
Поскольку для типов данных переменной длины необходимо использовать стандартный метод Add, рекомендуется использовать его для всех типов параметров.
Python DB-API
В этом примере используются Python и DB-API:
импортировать mysql.connectorconn = Нет курсор = Нетпопробуйте : conn = mysql . разъем . соединение ( база данных = "MySQL" , пользователь = "корень" ) курсор = соединение . курсор ( подготовленный = True ) курсор . execute ( "СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ продукты (имя VARCHAR (40), цена INT)" ) params = [( "велосипед" , 10900 ), ( "обувь" , 7400 ), ( "телефон" , 29500 )] курсор . executemany ( "INSERT INTO products VALUES ( % s , % s )" , params ) params = ( "shoes" ,) cursor . execute ( "SELECT * FROM products WHERE name = % s " , params ) print ( cursor . fetchall () [ 0 ] [ 1 ]) наконец : если курсор не равен None : cursor . close (), если conn не равно None : conn . закрыть ()
Magic Direct SQL
В этом примере используется Direct SQL от языка четвертого поколения, такого как eDeveloper, uniPaaS и magic XPA от Magic Software Enterprises.
Виртуальное имя пользователя Alpha 20 init: 'sister'Виртуальный пароль Alpha 20 init: 'желтый'Команда SQL: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2
Входные аргументы: 1: имя пользователя2: пароль
PureBasic
PureBasic (начиная с v5.40 LTS) может управлять 7 типами ссылок с помощью следующих команд
SetDatabase Blob , SetDatabase Double , SetDatabase Float , SetDatabase Long , SetDatabase Null , SetDatabase Quad , SetDatabase String
В зависимости от типа базы данных существует 2 разных метода.
Для SQLite , ODBC , MariaDB / Mysql используйте :?
SetDatabaseString (# База данных , 0 , "тест" ) Если DatabaseQuery (# База данных , "ВЫБРАТЬ * ОТ сотрудника WHERE id =?" ) ; ... КонецЕсли
Для PostgreSQL используйте: $ 1, $ 2, $ 3, ...
SetDatabaseString (# База данных , 0 , «Смит» ) ; -> $ 1 SetDatabaseString (# База данных , 1 , «Да» ) ; -> $ 2 SetDatabaseLong (# База данных , 2 , 50 ) ; -> 3 доллара Если DatabaseQuery (# База данных , «ВЫБРАТЬ * ОТ сотрудника, ГДЕ id = $ 1 И активный = $ 2 И годы> $ 3» ) ; ... КонецЕсли
Рекомендации
- ^ a b Группа документации PHP. «Подготовленные отчеты и хранимые процедуры» . Руководство по PHP . Проверено 25 сентября 2011 года .
- ^ Петруня, Сергей (28 апреля 2007 г.). «Оптимизатор MySQL и подготовленные операторы» . Блог Сергея Петруни . Проверено 25 сентября 2011 года .
- ^ Зайцев, Петр (2 августа 2006 г.). «Подготовленные операторы MySQL» . Блог о производительности MySQL . Проверено 25 сентября 2011 года .
- ^ «7.6.3.1. Как работает кэш запросов» . Справочное руководство по MySQL 5.1 . Oracle . Проверено 26 сентября 2011 года .
- ^ Oracle. «20.9.4. Подготовленные операторы C API» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 года .
- ^ «13 Oracle Dynamic SQL» . Руководство программиста прекомпилятора Pro * C / C ++, выпуск 9.2 . Oracle . Проверено 25 сентября 2011 года .
- ^ «Использование операторов PREPARE и EXECUTE» . Информационный центр i5 / OS, версия 5, выпуск 4 . IBM . Проверено 25 сентября 2011 года .[ постоянная мертвая ссылка ]
- ^ «SQL Server 2008 R2: Подготовка операторов SQL» . Библиотека MSDN . Microsoft . Проверено 25 сентября 2011 года .
- ^ «ПОДГОТОВИТЬ» . Документация по PostgreSQL 9.5.1 . Группа глобального развития PostgreSQL . Проверено 27 февраля +2016 .
- ^ Oracle. «12.6. Синтаксис SQL для подготовленных операторов» . Справочное руководство по MySQL 5.5 . Проверено 27 марта 2012 года .
- ^ «Использование подготовленных отчетов» . Учебники по Java . Oracle . Проверено 25 сентября 2011 года .
- ^ Банс, Тим. «Спецификация DBI-1.616» . CPAN . Проверено 26 сентября 2011 года .
- ^ «Python PEP 289: спецификация API базы данных Python v2.0» .
- ^ «SQL-инъекции: как не застрять» . Кодист. 8 мая 2007 . Проверено 1 февраля 2010 года .