Подготовленное заявление

В системах управления базами данных (СУБД) подготовленный оператор , параметризованный оператор (не путать с параметризованным запросом ) — это функция, при которой база данных предварительно компилирует код SQL и сохраняет результаты, отделяя их от данных. Преимущества подготовленных операторов: ^[1]

• эффективность, поскольку их можно использовать многократно без повторной компиляции
• безопасность, за счет сокращения или устранения атак SQL-инъекций

Подготовленный оператор принимает форму предварительно скомпилированного шаблона , в который во время каждого выполнения подставляются постоянные значения, и обычно использует операторы SQL DML, такие как INSERT , SELECT или UPDATE .

Обычный рабочий процесс для подготовленных отчетов выглядит следующим образом:

1. Подготовка : приложение создает шаблон оператора и отправляет его в СУБД. Некоторые значения остаются неуказанными, называются параметрами , заполнителями или переменными привязки (помечены "?" ниже):

   INSERT INTO products (name, price) VALUES (?, ?);

2. Компиляция : СУБД компилирует (анализирует, оптимизирует и транслирует) шаблон оператора и сохраняет результат, не выполняя его.
3. Выполнить : приложение предоставляет (или связывает ) значения для параметров шаблона оператора, а СУБД выполняет оператор (возможно, возвращая результат). Приложение может запросить СУБД выполнить оператор много раз с разными значениями. В приведенном выше примере приложение может предоставить значения "велосипед" для первого параметра и "10900" для второго параметра, а затем значения "обувь" и "7400".

Альтернативой подготовленному оператору является вызов SQL напрямую из исходного кода приложения таким образом, чтобы объединить код и данные. Прямой эквивалент приведенного выше примера:

ВСТАВИТЬ В продукты ( название , цена ) ЗНАЧЕНИЯ ( 'велосипед' , '10900' );       

Не все оптимизации могут быть выполнены во время компиляции шаблона оператора по двум причинам: лучший план может зависеть от конкретных значений параметров, и лучший план может меняться по мере изменения таблиц и индексов с течением времени. ^[2]

С другой стороны, если запрос выполняется только один раз, подготовленные на стороне сервера операторы могут быть медленнее из-за дополнительного кругового пути к серверу. ^[3] Ограничения реализации также могут привести к снижению производительности; например, некоторые версии MySQL не кэшируют результаты подготовленных запросов. ^[4] Хранимая процедура , которая также предварительно компилируется и сохраняется на сервере для последующего выполнения, имеет схожие преимущества. В отличие от хранимой процедуры, подготовленный оператор обычно не пишется на процедурном языке и не может использовать или изменять переменные или использовать структуры потока управления, полагаясь вместо этого на декларативный язык запросов к базе данных. Благодаря своей простоте и эмуляции на стороне клиента подготовленные операторы более переносимы между поставщиками.

Основные СУБД , включая SQLite , ^[5] MySQL , ^[6] Oracle , ^[7] IBM Db2 , ^[8] Microsoft SQL Server ^[9] и PostgreSQL ^[10] поддерживают подготовленные операторы. Подготовленные операторы обычно выполняются через не-SQL двоичный протокол для эффективности и защиты от SQL-инъекций, но в некоторых СУБД, таких как MySQL, подготовленные операторы также доступны с использованием синтаксиса SQL для целей отладки. ^[11]

Ряд языков программирования поддерживают подготовленные операторы в своих стандартных библиотеках и будут эмулировать их на стороне клиента, даже если базовая СУБД их не поддерживает, включая JDBC Java , ^[12] DBI Perl , [ 13 ^] PDO PHP ^[1] и DB-API Python. [ ^{14] Эмуляция на стороне клиента может быть быстрее для запросов, которые выполняются только один раз} , за ​​счет сокращения количества обращений к серверу, но обычно медленнее для запросов, выполняемых много раз. Она одинаково эффективно противостоит атакам SQL-инъекций.

Многие типы атак с использованием SQL-инъекций можно устранить, отключив литералы , что фактически потребует использования подготовленных операторов; по состоянию на 2007 год эту функцию поддерживает ^{[обновлять]}только H2 . ^[15]

// Определить тип BookModel, который оборачивает пул соединений sql.DB. type BookModel struct { DB * sql . DB }    // Это добавит новую книгу в базу данных. func ( m * BookModel ) Insert ( title , author string ) ( int , error ) { stmt := "INSERT INTO book(title, author , created) VALUES ( ?, ? , UTC_TIMESTAMP ())" result , err := m.DB.Exec ( stmt , title , author ) if err != nil { return 0 , err }                     id , err := result . LastInsertId () // Не поддерживается в драйвере postgress if err != nil { return 0 , err }          // Возвращаемый идентификатор имеет тип int64, поэтому мы преобразуем его в тип int // перед возвратом. return int ( id ), nil }  

Синтаксис параметра-заполнителя отличается в зависимости от вашей базы данных. MySQL, SQL Server и SQLite используют обозначение ?, но PostgreSQL использует обозначение $N. Например, если бы вы использовали PostgreSQL, вы бы написали:

_ , err := m . DB . Exec ( "ВСТАВИТЬ В ... ЗНАЧЕНИЯ ($1, $2, $3)" , ... )    

В этом примере используются Java и JDBC :

импорт com.mysql.jdbc.jdbc2.optional.MysqlDataSource ; импорт java.sql.Connection ; импорт java.sql.DriverManager ; импорт java.sql.PreparedStatement ; импорт java.sql.ResultSet ; импорт java.sql.SQLException ; импорт java.sql.Statement ;       публичный класс Main {    public static void main ( String [] args ) выдает SQLException { MysqlDataSource ds = new MysqlDataSource (); ds . setDatabaseName ( "mysql" ); ds . setUser ( "root" );               try ( Connection conn = ds . getConnection ()) { try ( Statement stmt = conn . createStatement ()) { stmt . executeUpdate ( "СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ products (name VARCHAR(40), price INT)" ); }              try ( PreparedStatement stmt = conn . prepareStatement ( " ВСТАВИТЬ В ЗНАЧЕНИЯ products (?, ?) " )) { 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 (); }                      try ( PreparedStatement stmt = conn . prepareStatement ( "SELECT * FROM products WHERE name = ?" )) { stmt . setString ( 1 , " обувь " ); ResultSet rs = stmt . executeQuery (); rs . next (); System . out . println ( rs . getInt ( 2 )); } } } }                

Java PreparedStatementпредоставляет «сеттеры» ( setInt(int), setString(String), setDouble(double),и т. д.) для всех основных встроенных типов данных.

В этом примере используются PHP и PDO ^[16] :

<?php// Подключаемся к базе данных с именем "mysql" с паролем "root" $connection  =  new  PDO ( 'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4' ,  'root' );// Выполнить запрос к соединению, который создаст // таблицу "products" с двумя столбцами, "name" и "price" $connection -> exec ( 'CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price 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 } ." ;

В этом примере используются Perl и DBI :

#!/usr/bin/perl -w использовать строгий ; использовать DBI ;  my ( $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 "ОШИБКА (main:DBI->connect) при подключении к базе данных $db_name: " . $ DBI:: errstr . "\n" ;                          $dbh -> do ( 'СОЗДАТЬ ТАБЛИЦУ, ЕСЛИ НЕ СУЩЕСТВУЕТ products (name VARCHAR(40), price INT)' );мой $sth = $dbh -> подготовить ( 'ВСТАВИТЬ В ЗНАЧЕНИЯ products (?, ?)' ); $sth -> выполнить ( @$_ ) foreach [ 'велосипед' , 10900 ], [ 'обувь' , 7400 ], [ 'телефон' , 29500 ];          $sth = $dbh -> prepare ( "SELECT * FROM products WHERE name = ?" ); $sth -> execute ( 'shoes' ); print "$$_[1]\n" foreach $sth -> fetchrow_arrayref ; $sth -> finish ;     $dbh -> отключиться ;

В этом примере используются C# и ADO.NET :

using ( SqlCommand command = connection . CreateCommand ()) { command . CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room" ; command . Parameters . AddWithValue ( "@username" , username ); command . Parameters . AddWithValue ( "@room" , room );            использование ( SqlDataReader dataReader = команда . 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:

импорт  mysql.connectorwith  mysql.connector.connect ( database = "mysql" ,user="root")asconn: with conn.cursor(poded=True)ascursor: cursor.execute ( " CREATE TABLEIF NOT EXISTS products ( name VARCHAR ( 40 ) , price INT ) " ) params = [ ( " велосипед " , 10900 ) , ( " обувь " , 7400 ) , ( "телефон" , 29500 ) ] cursor.executemany ( " INSERT INTO products VALUES( %s , % s ) " , params ) params = ( "обувь" ,) cursor.execute ( " SELECT * FROM productsWHERE name= % s " , params ) print ( cursor.fetchall ( )[ 0 ][ 1 ] )                        

В этом примере используется Direct SQL из языков четвертого поколения, таких как eDeveloper, uniPaaS и magic XPA от Magic Software Enterprises.

Виртуальное имя пользователя Alpha 20 init: 'sister'Виртуальный пароль Alpha 20 init: 'yellow'SQL-команда: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2Входные аргументы:1: имя пользователя2: пароль

PureBasic (начиная с версии 5.40 LTS) может управлять 7 типами ссылок с помощью следующих команд

SetDatabase Blob , SetDatabase Double , SetDatabase Float , SetDatabase Long , SetDatabase Null , SetDatabase Quad , SetDatabase String

Существует 2 различных метода в зависимости от типа базы данных.

Для SQLite , ODBC , MariaDB/Mysql используйте: ?

SetDatabaseString ( # База данных , 0 , "test" ) If DatabaseQuery ( # База данных , "SELECT * FROM employee WHERE id=?" ) ; ... EndIf       

Для PostgreSQL используйте: $1, $2, $3, ...

SetDatabaseString ( # База данных , 0 , "Смит" ) ; -> $1 SetDatabaseString ( # База данных , 1 , "Да" ) ; -> $2 SetDatabaseLong ( # База данных , 2 , 50 ) ; -> $3          Если DatabaseQuery ( # База данных , "SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3" ) ; ... EndIf    

• Вставка кода