Przygotowane oświadczenie

W systemach zarządzania bazami danych (DBMS) przygotowana instrukcja , sparametryzowana instrukcja lub sparametryzowane zapytanie to funkcja używana do wstępnej kompilacji kodu SQL , oddzielająca go od danych. Korzyści z przygotowanych wyciągów to:

  • wydajność, ponieważ mogą być używane wielokrotnie bez ponownej kompilacji
  • bezpieczeństwa poprzez ograniczenie lub wyeliminowanie ataków typu SQL injection

Przygotowana instrukcja ma postać wstępnie skompilowanego szablonu , w którym podczas każdego wykonania są zastępowane wartości stałe i zwykle używa instrukcji SQL DML, takich jak INSERT , SELECT lub UPDATE .

Typowy przepływ pracy dla przygotowanych wyciągów to:

  1. Przygotuj : Aplikacja tworzy szablon wyciągu i wysyła go do DBMS. Pewne wartości pozostają nieokreślone, nazywane parametrami , symbolami zastępczymi lub zmiennymi wiążącymi (oznaczone poniżej „?”):
    INSERT INTO products ( name , price ) VALUES ( ? , ? );
  2. Kompiluj : DBMS kompiluje (parsuje, optymalizuje i tłumaczy) szablon instrukcji i zapisuje wynik bez jego wykonywania.
  3. Execute : Aplikacja dostarcza (lub wiąże ) wartości parametrów szablonu instrukcji, a DBMS wykonuje instrukcję (prawdopodobnie zwracając wynik). Aplikacja może wielokrotnie żądać od DBMS wykonania instrukcji z różnymi wartościami. W powyższym przykładzie aplikacja może podać wartości „rower” dla pierwszego parametru i „10900” dla drugiego parametru, a następnie wartości „buty” i „7400”.

Alternatywą dla przygotowanej instrukcji jest wywołanie SQL bezpośrednio z kodu źródłowego aplikacji w sposób łączący kod i dane. Bezpośrednim odpowiednikiem powyższego przykładu jest:

        WSTAW  DO  produktów  (  nazwa  ,  cena  )  WARTOŚCI  (  "rower"  ,  "10900"  );

Nie wszystkie optymalizacje można przeprowadzić w czasie kompilacji szablonu zestawienia z dwóch powodów: najlepszy plan może zależeć od określonych wartości parametrów, a najlepszy plan może się zmieniać wraz ze zmianami tabel i indeksów w czasie.

Z drugiej strony, jeśli zapytanie jest wykonywane tylko raz, instrukcje przygotowane po stronie serwera mogą być wolniejsze z powodu dodatkowej podróży w obie strony do serwera. Ograniczenia wdrożeniowe mogą również prowadzić do kar za wykonanie; na przykład niektóre wersje MySQL nie buforowały wyników przygotowanych zapytań. Procedura składowana , która jest również prekompilowana i przechowywana na serwerze do późniejszego wykonania, ma podobne zalety. W przeciwieństwie do procedury składowanej, przygotowana instrukcja nie jest zwykle napisana w języku proceduralnym i nie może używać ani modyfikować zmiennych ani używać struktur przepływu sterowania, zamiast tego polega na deklaratywnym języku zapytań do bazy danych. Ze względu na swoją prostotę i emulację po stronie klienta, przygotowane wyciągi są bardziej przenośne między dostawcami.

Wsparcie oprogramowania

Główne systemy DBMS , w tym SQLite , MySQL , Oracle , IBM Db2 , Microsoft SQL Server i PostgreSQL obsługują przygotowane instrukcje. Przygotowane instrukcje są zwykle wykonywane za pośrednictwem protokołu binarnego innego niż SQL w celu zwiększenia wydajności i ochrony przed iniekcją SQL, ale w przypadku niektórych systemów DBMS, takich jak MySQL, dostępne są również przygotowane instrukcje przy użyciu składni SQL do celów debugowania.

Wiele języków programowania obsługuje przygotowane instrukcje w swoich standardowych bibliotekach i emuluje je po stronie klienta, nawet jeśli podstawowy DBMS ich nie obsługuje , w tym JDBC Java , DBI Perla , PDO PHP i DB Pythona -API. Emulacja po stronie klienta może być szybsza w przypadku zapytań wykonywanych tylko raz, zmniejszając liczbę podróży w obie strony do serwera, ale zwykle jest wolniejsza w przypadku zapytań wykonywanych wiele razy. Równie skutecznie opiera się atakom typu SQL injection.

Wiele rodzajów ataków typu SQL injection można wyeliminować poprzez wyłączenie literałów , co skutecznie wymaga użycia przygotowanych instrukcji; od 2007 roku tylko H2 obsługuje tę funkcję.

Przykłady

JavaJDBC

W tym przykładzie użyto języka Java i JDBC : 

 
 
 
 
 
 
 

   

          import  com.mysql.jdbc.jdbc2.opcjonalny.MysqlDataSource  ;  import  java.sql.Connection  ;  import  java.sql.DriverManager  ;  import  java.sql.PreparedStatement  ;  importuj  java.sql.ResultSet  ;  importuj  wyjątek java.sql.SQL  ;  import  java.sql.Statement  ;  klasa  publiczna  Main  {  public  static  void  main  (  String  []  args  )  rzuca   
            
        
        

             
                 
                 SQLException  {  MysqlDataSource  ds  =  nowy  MysqlDataSource  ();  ds  .  setDatabaseName  (  "mysql"  );  ds  .  setUser  (  "root"  );  try  (  połączenie  conn  =  ds  .  getConnection  ())  {  try  (  instrukcja  stmt  =  conn  .  createStatement  ())  {  stmt 
            

                 
                 
                 
                 .  wykonaćUpdate  (  "UTWÓRZ TABELĘ JEŚLI NIE ISTNIEJE produktów (nazwa VARCHAR(40), cena INT)"  );  }  try  (  PreparedStatement  stmt  =  conn  .  readyStatement  (  "WSTAWIĆ WARTOŚCI produktów (?,?)"  ))  {  stmt  .  setString  (  1  ,  "rower"  );  stt  .  setInt  (  2  ,  10900  );  stt  . 
                 
                 
                
                 
                 
                
             wykonaj aktualizację  ();  stt  .  setString  (  1  ,  "buty"  );  stt  .  setInt  (  2  ,  7400  );  stt  .  wykonaj aktualizację  ();  stt  .  setString  (  1  ,  "telefon"  );  stt  .  ustawInt  (  2  ,  29500  );  stt  .  wykonaj aktualizację  ();  } 

                 
                 
                   
                
                 try  (  PreparedStatement  stmt  =  conn.readyStatement  ( "   WYBIERZ  * Z produktów WHERE nazwa =?"  )  )  {  stmt  .  setString  (  1  ,  "buty"  );  ResultSet  rs  =  stmt  .  wykonajZapytanie  ();  rs  .  następny  ();  systemu  .  na  zewnątrz  println  (  rs  .  getInt  ( 
            
        
     2  ));  }  }  }  }

Java PreparedStatement udostępnia „settery” ( setInt(int), setString(String), setDouble(double) itp.) dla wszystkich głównych wbudowanych typów danych.

PHP PDO

W tym przykładzie użyto PHP i PDO : 



 
    
        

    
    
     <?php  try  {  // Połącz się z bazą danych o nazwie "mysql", używając hasła "root"  $connection  =  new  PDO  (  'mysql:dbname=mysql'  ,  'root'  );  // Wykonaj żądanie na połączeniu, które utworzy  // tabelę „produkty” z dwiema kolumnami „nazwa” i „cena”  $connection  ->  exec  (  'CREATE TABLE IF NOT EXISTS products (nazwa VARCHAR(40), cena INT)'  ); 

    
      
       
         
         
         
    

     // Przygotuj zapytanie, aby wstawić wiele produktów do tabeli  $statement  =  $connection  ->  Preparation  (  'INSERT INTO products VALUES (?, ?)'  );  $products  =  [  [  'rower'  ,  10900  ],  [  'buty'  ,  7400  ],  [  'telefon'  ,  29500  ],  ];  // Przejrzyj produkty w tablicy "products" i 
    
        
        
    

    
      
    
           // wykonaj przygotowaną instrukcję dla każdego produktu  foreach  (  $products  as  $product  )  {  $statement  ->  execute  (  $product  );  }  // Przygotuj nową instrukcję z nazwanym parametrem  $statement  =  $connection  ->  Preparation  (  'SELECT * FROM products WHERE name = :name'  );  $instrukcja  ->  wykonaj  ([  ':nazwa'  =>  'buty'  , 
    

    
    
         

    
     

    
     ]);  // Użyj destrukturyzacji tablicy, aby przypisać nazwę produktu i jego cenę  // do odpowiednich zmiennych  [  $product  ,  $price  ]  =  $statement  ->  fetch  ();  // Wyświetl wynik użytkownikowi  echo  "Cena produktu  {  $product  }  to  \$  {  $price  }  ".  ;  // Zamknij kursor, aby `fetch` mogło zostać ponownie użyte  $statement  ->  closeCursor  (); 
    
       
 }  catch  (  \Exception  $e  )  {  echo  'Wystąpił błąd: '  .  $e  ->  pobierzWiadomość  ();  }

Perl DBI

W tym przykładzie użyto Perla i DBI : 


 
 

       
     
      #!/usr/bin/perl -w  użyj  strict  ;  użyj  DBI  ;  my  (  $ nazwa_bazy danych  ,  $ użytkownik_ bazy danych  ,  hasło_ bazy danych  )  =  (  'moja_baza danych'  ,  'moi'  ,  'Passw0rD'  );  my  $dbh  =  DBI  ->  połącz  (  "DBI:mysql:database=$db_name"  ,  $db_user  ,  $db_password  ,  {  RaiseError      
       
          



    =>  1  ,  AutoCommit  =>  1  })  lub  umrzeć  „BŁĄD (main:DBI->connect) podczas łączenia się z bazą danych $db_name:”  .  $  DBI::  errstr  .  "\n"  ;  $dbh  ->  do  (  'UTWÓRZ TABELĘ JEŚLI NIE ISTNIEJE produkty (nazwa VARCHAR(40), cena INT)'  );  my  $sth  =  $dbh  ->  przygotuj  (  'WSTAWIĆ DO produktów WARTOŚCI (?,?)'  ); 
       

  

  $sth  ->  wykonaj  (  @$_  )  foreach  [  'rower'  ,  10900  ],  [  'buty'  ,  7400  ],  [  'telefon'  ,  29500  ];  $sth  =  $dbh  ->  przygotuj  (  "WYBIERZ * Z produktów WHERE nazwa =?"  );  $sth  ->  wykonaj  (  'buty'  );  wypisz  "$$_[1]\n"   


 foreach  $sth  ->  fetchrow_arrayref  ;  $sth  ->  zakończ  ;  $dbh  ->  rozłącz  ;

C# ADO.NET

W tym przykładzie użyto C# i ADO.NET : 

    

      
     
      za pomocą  (  polecenie  SqlCommand  =  połączenie  .  Utwórz polecenie  ())  {  polecenie  .  CommandText  =  "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room"  ;  polecenie  .  Parametry  .  AddWithValue  (  "@nazwa_użytkownika"  ,  nazwa_użytkownika  );  polecenie  .  Parametry  .  AddWithValue  (  "@pokój"  ,  pokój  ); 

        
    
        
    
 za pomocą  (  SqlDataReader  dataReader  =  polecenie  .  ExecuteReader  ())  {  // ...  }  }

ADO.NET SqlCommand zaakceptuje dowolny typ dla parametru value AddWithValue , a konwersja typu nastąpi automatycznie. Zwróć uwagę na użycie "parametrów nazwanych" (np. "@username" ) zamiast "?" — pozwala to na wielokrotne użycie parametru w dowolnej kolejności w tekście polecenia zapytania.

Jednak metody AddWithValue nie należy używać z typami danych o zmiennej długości, takimi jak varchar i nvarchar. Dzieje się tak, ponieważ .NET zakłada, że ​​długość parametru jest długością podanej wartości, zamiast pobierać rzeczywistą długość z bazy danych poprzez odbicie. Konsekwencją tego jest kompilacja i przechowywanie innego planu zapytań dla każdej innej długości. Ogólnie rzecz biorąc, maksymalna liczba „duplikatów” planów jest iloczynem długości kolumn o zmiennej długości określonych w bazie danych. Z tego powodu ważne jest, aby używać standardowej metody Add dla kolumn o zmiennej długości:

polecenie . Parametry . Dodaj ( nazwa_parametru , VarChar , długość_parametru ). Value = ParamValue , gdzie ParamLength to długość określona w bazie danych.

Ponieważ standardowa metoda Add musi być używana dla typów danych o zmiennej długości, dobrym zwyczajem jest używanie jej dla wszystkich typów parametrów.

Python DB-API

W tym przykładzie użyto Pythona i DB-API: 

 

    
       
         zaimportuj  mysql.connector  z  mysql  .  złącze  .  connect  (  baza danych  =  „mysql”  ,  użytkownik  =  „root”  )  as  conn  :  with  conn  .  kursor  (  przygotowany  =  Prawda  )  jako  kursor  :  kursor  .  wykonaj  (  "UTWÓRZ TABELĘ, JEŚLI NIE ISTNIEJE produkty (nazwa VARCHAR(40), cena INT)"  ) 
           
                   
                   
         
          
         params  =  [(  "rower"  ,  10900  ),  (  "buty"  ,  7400  ),  (  "telefon"  ,  29500  )]  kursor  .  wykonaćwiele  (  "WSTAW DO produktów WARTOŚCI (  %s  ,  %s  )"  ,  params  )  params  =  (  "buty"  ,)  kursor  .  wykonać  (  
        0 "WYBIERZ * Z produktów WHERE nazwa =  %s  "  ,  params  )  print  (  kursor  .  fetchall  ()[  ][  1  ])

Magic Direct SQL

W tym przykładzie użyto Direct SQL z języka czwartej generacji, takiego jak eDeveloper, uniPaaS i magic XPA firmy Magic Software Enterprises 

 Wirtualna nazwa użytkownika Alpha 20 init: 'siostra' Wirtualne hasło Alpha 20 init: 'yellow' Polecenie SQL:         SELECT  *  FROM  users  WHERE  USERNAME  =  :  1  AND  PASSWORD  =  :  2  Argumenty wejściowe: 1: nazwa użytkownika 2: hasło

PureBasic

PureBasic (od wersji 5.40 LTS) może zarządzać 7 rodzajami łączy za pomocą następujących poleceń 

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

Istnieją 2 różne metody w zależności od typu bazy danych

Dla SQLite , ODBC , MariaDB/Mysql użyj: ? 

   0   
        
     
   SetDatabaseString  (#  Database  ,  ,  "test"  )  If  DatabaseQuery  (#  Database  ,  "SELECT * FROM pracownik WHERE id =?"  )  ;  ...  KoniecJeżeli

Do użytku PostgreSQL : 1 $, 2 $, 3 $, ... 

   0     
         
              
 
    SetDatabaseString  (#  Baza danych  ,  ,  "Smith"  )  ;  ->  $  1  SetDatabaseString  (#  Baza danych  ,  1  ,  "Tak"  )  ;  ->  $  2  SetDatabaseLong  (#  Baza danych  ,  2  ,  50  )  ;  ->  3  $  Jeśli  zapytanie do bazy danych  (#  Baza danych  ,      
     
   "SELECT * FROM pracownik WHERE id=$1 AND active=$2 AND years>$3"  )  ;  ...  KoniecJeżeli

Zobacz też

  1. ^ a b Grupa dokumentacji PHP. „Przygotowane instrukcje i procedury składowane” . Podręcznik PHP . Źródło 25 września 2011 r .
  2. ^ Petrunia, Siergiej (28 kwietnia 2007). „Optymalizator MySQL i przygotowane zestawienia” . Blog Siergieja Petrunii . Źródło 25 września 2011 r .
  3. ^ Zajcew, Piotr (2 sierpnia 2006). „Przygotowane zestawienia MySQL” . Blog dotyczący wydajności MySQL . Źródło 25 września 2011 r .
  4. ^ "7.6.3.1. Jak działa pamięć podręczna zapytań" . Podręcznik użytkownika MySQL 5.1 . Wyrocznia . Źródło 26 września 2011 r .
  5. ^ „Przygotowane obiekty instrukcji” . SQLite . 18 października 2021 r. {{ cite web }} : CS1 maint: url-status ( link )
  6. Bibliografia _ „20.9.4. Przygotowane instrukcje C API” . Podręcznik referencyjny MySQL 5.5 . Źródło 27 marca 2012 r .
  7. ^ „13 Oracle Dynamic SQL” . Podręcznik programisty prekompilatora Pro*C/C++, wersja 9.2 . Wyrocznia . Źródło 25 września 2011 r .
  8. ^ „Korzystanie z instrukcji PREPARE i EXECUTE” . Centrum informacyjne i5/OS, wersja 5 wydanie 4 . IBM . Źródło 25 września 2011 r . [ stały martwy link ]
  9. ^ „SQL Server 2008 R2: przygotowywanie instrukcji SQL” . Biblioteka MSDN . Microsoftu . Źródło 25 września 2011 r .
  10. Bibliografia _ _ Dokumentacja PostgreSQL 9.5.1 . Globalna grupa ds. rozwoju PostgreSQL . Źródło 27 lutego 2016 r .
  11. Bibliografia _ „12.6. Składnia SQL dla przygotowanych instrukcji” . Podręcznik referencyjny MySQL 5.5 . Źródło 27 marca 2012 r .
  12. ^ „Korzystanie z przygotowanych wyciągów” . Samouczki Javy . Wyrocznia . Źródło 25 września 2011 r .
  13. Bibliografia _ „Specyfikacja DBI-1.616” . CPAN . Źródło 26 września 2011 r .
  14. ^ „Python PEP 289: Specyfikacja interfejsu API bazy danych języka Python w wersji 2.0” .
  15. ^ „Zastrzyki SQL: jak nie utknąć” . Kodysta. 8 maja 2007 . Źródło 1 lutego 2010 r .
Pobrano z „ https://en.wikipedia.org/w/index.php?title=Prepared_statement&oldid=1103939861 ”