Förberett uttalande

I databashanteringssystem (DBMS) är en förberedd sats , parametriserad sats eller parametriserad fråga en funktion som används för att förkompilera SQL-kod och separera den från data. Fördelarna med förberedda uttalanden är:

  • effektivitet, eftersom de kan användas upprepade gånger utan att kompileras om
  • säkerhet, genom att minska eller eliminera SQL- injektionsattacker

En förberedd sats har formen av en förkompilerad mall i vilken konstanta värden ersätts under varje körning, och vanligtvis använder SQL DML- satser som INSERT , SELECT eller UPDATE .

Ett vanligt arbetsflöde för förberedda uttalanden är:

  1. Förbered : Applikationen skapar uttalandemallen och skickar den till DBMS. Vissa värden lämnas ospecificerade, kallas parametrar , platshållare eller bindningsvariabler (märkta "?" nedan):
    INSERT INTO products ( namn , pris ) VALUES ( ? , ? );
  2. Kompilera : DBMS kompilerar (tolkar, optimerar och översätter) satsmallen och lagrar resultatet utan att exekvera det.
  3. Execute : Applikationen tillhandahåller (eller binder ) värden för parametrarna för satsmallen, och DBMS kör satsen (möjligen returnerar ett resultat). Applikationen kan begära att DBMS exekverar satsen många gånger med olika värden. I exemplet ovan kan applikationen tillhandahålla värdena "cykel" för den första parametern och "10900" för den andra parametern, och senare värdena "skor" och "7400".

Alternativet till en förberedd sats är att anropa SQL direkt från applikationens källkod på ett sätt som kombinerar kod och data. Den direkta motsvarigheten till exemplet ovan är:

        INSERT  INTO  products  (  namn  ,  pris  )  VÄRDEN  (  "cykel"  ,  "10900" )  ;

All optimering kan inte utföras vid den tidpunkt då satsmallen kompileras, av två skäl: den bästa planen kan bero på parametrarnas specifika värden, och den bästa planen kan ändras när tabeller och index ändras över tiden.

Å andra sidan, om en fråga endast körs en gång, kan förberedda satser på serversidan vara långsammare på grund av den extra rundresan till servern. Implementeringsbegränsningar kan också leda till prestationspåföljder; till exempel, vissa versioner av MySQL cachelagrade inte resultat av förberedda frågor. En lagrad procedur , som också är förkompilerad och lagrad på servern för senare exekvering, har liknande fördelar. Till skillnad från en lagrad procedur är en förberedd sats normalt inte skriven på ett procedurspråk och kan inte använda eller modifiera variabler eller använda kontrollflödesstrukturer, utan förlitar sig istället på det deklarativa databasens frågespråk. På grund av sin enkelhet och emulering på klientsidan är förberedda uttalanden mer bärbara mellan leverantörer.

Programvarustöd

Större DBMS , inklusive SQLite , MySQL , Oracle , IBM Db2 , Microsoft SQL Server och PostgreSQL stöder förberedda uttalanden. Förberedda satser exekveras normalt genom ett binärt icke-SQL-protokoll för effektivitet och skydd mot SQL-injektion, men med vissa DBMS:er som MySQL är förberedda satser också tillgängliga med en SQL-syntax för felsökningsändamål.

förberedda satser i sina standardbibliotek och kommer att emulera dem på klientsidan även om den underliggande DBMS inte stöder dem, inklusive Javas JDBC , Perls DBI , PHPs PDO och Pythons DB -API. Emulering på klientsidan kan vara snabbare för frågor som körs endast en gång, genom att minska antalet rundresor till servern, men är vanligtvis långsammare för frågor som körs många gånger. Den motstår SQL-injektionsattacker lika effektivt.

Många typer av SQL-injektionsattacker kan elimineras genom att inaktivera literals , vilket effektivt kräver användning av förberedda satser; från och med 2007 stöder endast H2 denna funktion.

Exempel

Java JDBC

Det här exemplet använder Java och JDBC : 

 
 
 
 
 
 
 

   

           
            
        
        

             
                 
                
            

                 
                 
                 
                
                 
                 
                
                 
                 
                
            

                 
                 
                   
                
                
            
        
     importera  com.mysql.jdbc.jdbc2.optional.MysqlDataSource  ;  importera  java.sql.Connection  ;  importera  java.sql.DriverManager  ;  importera  java.sql.PreparedStatement  ;  importera  java.sql.ResultSet  ;  importera  java.sql.SQLException  ;  importera  java.sql.Statement  ;  public  class  Main  {  public  static  void  main  (  String  []  args  )  kastar  SQLException  {  MysqlDataSource  ds  =  new  MysqlDataSource  ();  ds  .  setDatabaseName  (  "mysql"  );  ds  .  setUser  (  "root"  );  try  (  Connection  conn  =  ds  .  getConnection  ())  {  try  (  Statement  stmt  =  conn  .  createStatement  ())  {  stmt  .  executeUpdate  (  "SKAPA TABELL OM INTE FINNS produkter (namn VARCHAR(40), pris INT)" )  ;  }  try  (  PreparedStatement  stmt  =  anslutning  .  prepareStatement  (  "INSERT INTO products VALUES (?, ?)")  )  {  stmt  .  setString  (  1  ,  "cykel"  );  stmt  .  setInt  (  2  ,  10900  );  stmt  .  executeUpdate  ();  stmt  .  setString  (  1  ,  "skor"  );  stmt  .  setInt  (  2  ,  7400  );  stmt  .  executeUpdate  ();  stmt  .  setString  (  1  ,  "telefon"  );  stmt  .  setInt  (  2  ,  29500  );  stmt  .  executeUpdate  ();  }  try  (  PreparedStatement  stmt  =  anslutning  .  prepareStatement  (  "SELECT * FROM products WHERE name = ?"  ))  {  stmt  .  setString  (  1  ,  "skor"  );  ResultSet  rs  =  stmt  .  executeQuery  ();  rs  .  nästa  ();  System  .  ut  .  println  (  rs  .  getInt  (  2  ));  }  }  }  }

Java PreparedStatement tillhandahåller "setters" ( setInt(int), setString(String), setDouble(double), etc.) för alla större inbyggda datatyper.

PHP SUB

Det här exemplet använder PHP och PDO : 



 
    
        

    
    
    

    
      
       
         
         
         
    

    
    
        
        
    

    
      
    
          
    

    
    
         

    
     

    
    
    
       
 <?php  try  {  // Anslut till en databas som heter "mysql", med lösenordet "root"  $connection  =  new  PDO  (  'mysql:dbname=mysql'  ,  'root'  );  // Kör en begäran på anslutningen, vilket kommer att skapa  // en tabell "produkter" med två kolumner, "namn" och "pris"  $connection  ->  exec  (  'SKAPA TABELL OM INTE FINNS produkter (namn VARCHAR(40), pris INT)'  );  // Förbered en fråga för att infoga flera produkter i tabellen  $statement  =  $connection  ->  prepare  (  'INSERT INTO products VALUES (?, ?)' )  ;  $products  =  [  [  'cykel'  ,  10900  ],  [  'skor'  ,  7400  ],  [  'telefon'  ,  29500  ],  ];  // Iterera genom produkterna i "products"-arrayen och  // exekvera den förberedda satsen för varje produkt  för varje   (  $products  as  $product  )  {  $statement  ->  execute  (  $product  );  }  // Förbered en ny sats med en namngiven parameter  $statement  =  $connection  ->  prepare  (  'SELECT * FROM products WHERE name = :name' )  ;  $statement  ->  exekvera  ([  ':name'  =>  'skor'  ,  ]);  // Använd array-destrukturering för att tilldela produktnamnet och dess pris  // till motsvarande variabler  [  $product  ,  $price  ]  =  $statement  ->  fetch  ();  // Visa resultatet för användarekot  "  Priset på produkten  {  $product  }  är  \$ {  $  price  }  ."  ;  // Stäng markören så att `fetch` till slut kan användas igen  $statement  ->  closeCursor  ();  }  catch  (  \Exception  $e  )  {  echo  'Ett fel har inträffat: '  .  $e  ->  getMessage  ();  }

Perl DBI

Det här exemplet använder Perl och DBI : 


 
 

       
     
          
       
          



   
       

  

   


 #!/usr/bin/perl -w  använder  strict  ;  använd  DBI  ;  my  (  $db_name  ,  $db_user  ,  $db_password  )  =  (  'min_databas'  ,  'moi'  ,  'Passw0rD' )  ;  min  $dbh  =  DBI  ->  anslut  (  "DBI:mysql:database=$db_name"  ,  $db_user  ,  $db_password  ,  {  RaiseError  =>  1  ,  AutoCommit  =>  1  })  eller  dö  "ERROR (main:DBI->connect) medan du ansluter till databasen $db_name: "  .  $  DBI::  errstr  .  "\n"  ;  $dbh  ->  do  (  'SKAPA TABELL OM INTE FINNS produkter (namn VARCHAR(40), pris INT)')  ;  my  $sth  =  $dbh  ->  prepare  (  'INSERT IN TO products VALUES (?, ?)')  ;  $sth  ->  exekvera  (  @$_  )  foreach  [  'cykel'  ,  10900  ],  [  'skor'  ,  7400  ],  [  'telefon'  ,  29500  ];  $sth  =  $dbh  ->  prepare  (  "SELECT * FROM products WHERE name = ?"  );  $sth  ->  execute  (  'skor'  );  skriv ut  "$$_[1]\n"  för varje  $sth  ->  fetchrow_arrayref  ;  $sth  ->  finish  ;  $dbh  ->  koppla från  ;

C# ADO.NET

Det här exemplet använder C# och ADO.NET : 

    

      
     
     

        
    
        
    
 med  (  SqlCommand-  kommando  =  anslutning  .  CreateCommand  ())  {  kommando  .  CommandText  =  "VÄLJ * FRÅN användare WHERE USERNAME = @användarnamn OCH RUM = @rum"  ;  kommando  .  Parametrar  .  AddWithValue  (  "@användarnamn"  ,  användarnamn  );  kommando  .  Parametrar  .  AddWithValue  (  "@rum"  ,  rum  );  använder  (  SqlDataReader  dataReader  =  kommando  .  ExecuteReader  ())  {  // ...  }  }

ADO.NET SqlCommand accepterar vilken typ som helst för värdeparametern AddWithValue , och typkonvertering sker automatiskt. Notera användningen av "namngivna parametrar" (dvs. "@användarnamn" ) snarare än "?" —Detta låter dig använda en parameter flera gånger och i valfri ordning i frågekommandotexten.

AddWithValue-metoden bör dock inte användas med datatyper med variabel längd, som varchar och nvarchar. Detta beror på att .NET antar att längden på parametern är längden på det givna värdet, snarare än att få den faktiska längden från databasen via reflektion. Konsekvensen av detta är att en annan frågeplan kompileras och lagras för varje olika längd. I allmänhet är det maximala antalet "dubbletter"-planer produkten av längden på kolumnerna med variabel längd som specificeras i databasen. Av denna anledning är det viktigt att använda standardmetoden Lägg till för kolumner med variabel längd:

kommando . Parametrar . Lägg till ( ParamName , VarChar , ParamLength ). Value = ParamValue , där ParamLength är längden som anges i databasen.

Eftersom standardmetoden Add måste användas för datatyper med variabel längd, är det en god vana att använda den för alla parametertyper.

Python DB-API

Det här exemplet använder Python och DB-API: 

 

    
       
        
           
                   
                   
         
          
         
        0 importera  mysql.connector  med  mysql  .  kontakt  .  ansluta  (  databas  =  "mysql"  ,  användare  =  "root"  )  som  anslutning  :  med  anslutning  .  markör  (  förberedd  =  Sann  )  som  markör  :  markör  .  exekvera  (  "SKAPA TABELL OM INTE FINNS produkter (namn VARCHAR(40), pris INT)"  )  params  =  [(  "cykel"  ,  10900  ),  (  "skor"  ,  7400  ),  (  "telefon"  ,  29500  )]  markör  .  executemany  (  "INSERT INTO products VALUES (  %s  ,  %s  )"  ,  params  )  params  =  (  "skor"  ,)  cursor  .  execute  (  "SELECT * FROM products WHERE name =  %s  "  ,  params  )  print  (  cursor  .  fetchall  ()[  ][  1  ])

Magic Direct SQL

Det här exemplet använder Direct SQL från fjärde generationens språk som eDeveloper, uniPaaS och magic XPA från Magic Software Enterprises 

 Virtuella användarnamn Alpha 20 init: 'syster' Virtuella lösenord Alpha 20 init: 'gul' SQL-kommando:         VÄLJ  *  FRÅN  användare  VAR  ANVÄNDARNAMN  =  :  1  OCH  LÖSENORD  =  :  2  Inmatningsargument: 1: användarnamn 2: lösenord

PureBasic

PureBasic (sedan v5.40 LTS) kan hantera 7 typer av länkar med följande kommandon 

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

Det finns 2 olika metoder beroende på typ av databas

För SQLite , ODBC , MariaDB/Mysql använd: ? 

   0   
        
     
   SetDatabaseString  (#  Database  ,  ,  "test"  )  If  DatabaseQuery  (#  Database  ,  "SELECT * FROM anställd WHERE id=?"  )  ;  ...  EndIf

För PostgreSQL- användning: $1, $2, $3, ... 

   0     
         
              
 
        
     
   SetDatabaseString  (#  Database  ,  ,  "Smith"  )  ;  ->  $  1  SetDatabaseString  (#  Database  ,  1  ,  "Yes"  )  ;  ->  $  2  SetDatabaseLong  (#  Database  ,  2  ,  50  )  ;  ->  $  3  If  DatabaseQuery  (#  Database  ,  "SELECT * FROM anställd WHERE id=$1 AND active=$2 AND years>$3"  )  ;  ...  EndIf

Se även

  1. ^ a b PHP-dokumentationsgruppen. "Förberedda uttalanden och lagrade rutiner" . PHP manual . Hämtad 25 september 2011 .
  2. ^ Petrunia, Sergey (28 april 2007). "MySQL Optimizer och förberedda uttalanden" . Sergey Petrunias blogg . Hämtad 25 september 2011 .
  3. ^ Zaitsev, Peter (2 augusti 2006). "MySQL Prepared Statements" . MySQL Performance Blog . Hämtad 25 september 2011 .
  4. ^ "7.6.3.1. Hur frågecachen fungerar" . MySQL 5.1 Referensmanual . Oracle . Hämtad 26 september 2011 .
  5. ^ "Förberedda uttalandeobjekt" . SQLite . 18 oktober 2021. {{ citera webben }} : CS1 underhåll: url-status ( länk )
  6. ^ Oracle. "20.9.4. C API-förberedda uttalanden" . MySQL 5.5 Referensmanual . Hämtad 27 mars 2012 .
  7. ^ "13 Oracle Dynamic SQL" . Pro*C/C++ Programmeringsguide för förkompilator, version 9.2 . Oracle . Hämtad 25 september 2011 .
  8. ^ "Att använda PREPARE- och EXECUTE-satserna" . i5/OS Information Center, version 5 version 4 . IBM . Hämtad 25 september 2011 . [ permanent död länk ]
  9. ^ "SQL Server 2008 R2: Förbereder SQL-satser" . MSDN bibliotek . Microsoft . Hämtad 25 september 2011 .
  10. ^ "FÖRBEREDA" . Dokumentation för PostgreSQL 9.5.1 . PostgreSQL Global Development Group . Hämtad 27 februari 2016 .
  11. ^ Oracle. "12.6. SQL-syntax för förberedda uttalanden" . MySQL 5.5 Referensmanual . Hämtad 27 mars 2012 .
  12. ^ "Använda förberedda uttalanden" . Java-handledningarna . Oracle . Hämtad 25 september 2011 .
  13. ^ Bunce, Tim. "DBI-1.616-specifikation" . CPAN . Hämtad 26 september 2011 .
  14. ^ "Python PEP 289: Python Database API Specification v2.0" .
  15. ^ "SQL-injektioner: Hur man inte fastnar" . Kodisten. 8 maj 2007 . Hämtad 1 februari 2010 .
Hämtad från " https://en.wikipedia.org/w/index.php?title=Prepared_statement&oldid=1103939861 "