Różnica między wyciągiem a PreparedStatement


222

Przygotowane oświadczenie jest nieco bardziej rozbudowaną wersją oświadczenia i zawsze powinno być co najmniej tak szybkie i łatwe w obsłudze jak oświadczenie.
Przygotowane oświadczenie można sparametryzować

Większość relacyjnych baz danych obsługuje zapytanie JDBC / SQL w czterech krokach:

  1. Analizuj przychodzące zapytanie SQL
  2. Skompiluj zapytanie SQL
  3. Zaplanuj / zoptymalizuj ścieżkę akwizycji danych
  4. Wykonaj zoptymalizowane zapytanie / pozyskaj i zwróć dane

Instrukcja będzie zawsze przebiegać przez cztery powyższe kroki dla każdego zapytania SQL wysłanego do bazy danych. Przygotowana instrukcja wstępnie wykonuje kroki (1) - (3) w powyższym procesie wykonywania. Dlatego podczas tworzenia Przygotowanej instrukcji natychmiast wykonywana jest wstępna optymalizacja. Efektem jest zmniejszenie obciążenia silnika bazy danych w czasie wykonywania.

Teraz moje pytanie brzmi: „Czy jest jakaś inna zaleta korzystania z Prepared Statement?”


12
według mnie najbardziej wydajnym jest to, że zapytanie można sparametryzować dynamicznie
Hussain Akhtar Wahid „Ghouri”

Odpowiedzi:


198

Zalety PreparedStatement:

  • Wstępna kompilacja i buforowanie po stronie DB instrukcji SQL prowadzi do ogólnie szybszego wykonania i możliwości ponownego użycia tej samej instrukcji SQL w partiach .

  • Automatyczne zapobieganie SQL injection ataków przez wbudowane ucieczki od cytatów i innych znaków specjalnych. Należy pamiętać, że wymaga to użycia dowolnej PreparedStatement setXxx()metody do ustawienia wartości

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
    preparedStatement.setString(1, person.getName());
    preparedStatement.setString(2, person.getEmail());
    preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime()));
    preparedStatement.setBinaryStream(4, person.getPhoto());
    preparedStatement.executeUpdate();

    i dlatego nie wstawiaj wartości w ciągu SQL przez konkatenację ciągu.

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'");
    preparedStatement.executeUpdate();
  • Ułatwia ustawień niestandardowych obiektów Javy w ciąg SQL, na przykład Date, Time, Timestamp, BigDecimal, InputStream( Blob) i Reader( Clob). W większości tych typów nie można po prostu „zrobić” tak, toString()jak w prosty sposób Statement. Możesz nawet zrefaktoryzować to wszystko do użycia PreparedStatement#setObject()wewnątrz pętli, jak pokazano w poniższej metodzie użyteczności:

    public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
        for (int i = 0; i < values.length; i++) {
            preparedStatement.setObject(i + 1, values[i]);
        }
    }

    Które mogą być używane jak poniżej:

    preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)");
    setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto());
    preparedStatement.executeUpdate();

4
Tekst opisowy i wyjaśniający, w połączeniu z odniesieniami i przykładem, stanowi doskonałą odpowiedź. +1
XenoRo

1
@RD Może to być prawda, ponieważ przygotowana instrukcja wymaga 2 podróży w obie strony do bazy danych: pierwsza do przygotowania, druga do wykonania. Jednak przetestowałbym to. Zakładam, że plan nadal będzie buforowany na serwerze bazy danych Statement, ale może być warty przetestowania.
Brandon

2
Nie mogę powiedzieć z całą pewnością o Javie, ale generalnie przygotowane oświadczenie nie poprzedza „wbudowanego unikania cudzysłowów i innych znaków specjalnych”; zamiast tego wykonuje separację wykonywalnego SQL i danych , wysyłając parametry do DBMS jako osobne pakiety informacji po przekonwertowaniu SQL na plan zapytań.
IMSoP,

@BalusC - Dziękujemy za szczegółowe wyjaśnienie.
CodeBee ..

49
  1. Są one wstępnie skompilowane (raz), dzięki czemu są szybsze w przypadku wielokrotnego wykonywania dynamicznego SQL (w przypadku zmiany parametrów)

  2. Buforowanie instrukcji bazy danych zwiększa wydajność wykonywania DB

    Bazy danych przechowują pamięci podręczne planów wykonania wcześniej wykonanych instrukcji. Umożliwia to silnikowi bazy danych ponowne wykorzystanie planów dla instrukcji, które zostały wcześniej wykonane. Ponieważ PreparedStatement używa parametrów, za każdym razem, gdy jest wykonywany, pojawia się jako ten sam SQL, baza danych może ponownie użyć poprzedniego planu dostępu, zmniejszając przetwarzanie. Instrukcje „wstawiają” parametry do ciągu SQL, a więc nie pojawiają się w DB jako ten sam SQL, co uniemożliwia użycie pamięci podręcznej.

  3. Binarny protokół komunikacyjny oznacza mniejszą przepustowość i szybsze połączenia z serwerem DB

    Przygotowane instrukcje są zwykle wykonywane za pomocą binarnego protokołu innego niż SQL. Oznacza to, że w pakietach jest mniej danych, więc komunikacja z serwerem jest szybsza. Zasadniczo operacje sieciowe są o rząd wielkości wolniejsze niż operacje na dyskach, które są o rząd wielkości wolniejsze niż operacje CPU w pamięci. Dlatego każde zmniejszenie ilości danych przesyłanych przez sieć będzie miało dobry wpływ na ogólną wydajność.

  4. Chronią przed wstrzyknięciem SQL, usuwając tekst dla wszystkich podanych wartości parametrów.

  5. Zapewniają silniejszą separację między kodem zapytania a wartościami parametrów (w porównaniu do połączonych ciągów SQL), zwiększając czytelność i pomagając opiekunom kodu szybko zrozumieć dane wejściowe i wyjściowe zapytania.

  6. W języku Java można wywoływać metody getMetadata () i getParameterMetadata () w celu odzwierciedlenia odpowiednio pól zestawu wyników i pól parametrów

  7. W java inteligentnie akceptuje obiekty java jako typy parametrów za pomocą setObject, setBoolean, setByte, setDate, setDouble, setDouble, setFloat, setInt, setLong, setShort, setTime, setTimestamp - konwertuje na format typu JDBC, który jest zrozumiały dla DB (nie tylko toString () format).

  8. W języku Java akceptuje tablice SQL jako typ parametru za pomocą metody setArray

  9. W języku Java akceptuje CLOB, BLOB, OutputStreams i czytniki jako parametr „feeds” za pomocą metod setClob / setNClob, setBlob, setBinaryStream, setCharacterStream / setAsciiStream / setNCharacterStream odpowiednio

  10. W java pozwala na ustawienie wartości specyficznych dla DB dla SQL DATALINK, SQL ROWID, SQL XML i NULL za pomocą metod setURL, setRowId, setSQLXML i setNull

  11. W java dziedziczy wszystkie metody z instrukcji. Dziedziczy metodę addBatch, a ponadto umożliwia dodanie zestawu wartości parametrów w celu dopasowania zestawu zestawionych poleceń SQL za pomocą metody addBatch.

  12. W java specjalny typ PreparedStatement (podklasa CallableStatement) pozwala na wykonywanie procedur przechowywanych - obsługujących wysoką wydajność, enkapsulację, programowanie proceduralne i SQL, administrację DB / konserwację / ulepszanie logiki oraz wykorzystanie zastrzeżonej logiki i funkcji DB


Jak to możliwe, że oba te cuda są tylko interfejsami?!?!
Rafael

1
„Cuda” są możliwe dzięki standardowym metodom fabrycznym, które zwracają (specyficzne dla dostawcy) implementacje interfejsów: Connection.createStatement i Connection.prepareStatement. Ten projekt zmusza cię do pracy przeciwko interfejsom, więc nie musisz znać konkretnych klas implementacji i unikać niepotrzebnego ścisłego powiązania z takimi klasami implementacji. Wszystko wyjaśnione przykładami w dokumentach Java JDBC i Dokumentach Java. :)
Glen Best

Twoja „ogólna zasada” nie ma sensu, czy nie jest na odwrót 🤔
bhathiya-perera

38

PreparedStatementjest bardzo dobrą obroną (ale nie niezawodną) w zapobieganiu atakom SQL injection . Wartości parametrów wiązania to dobry sposób na ochronę przed „małymi stołami Bobby'ego” przed niepożądaną wizytą.


6
Jak zatem wykonać zastrzyk SQL za pomocą przygotowanej instrukcji?
Michael Borgwardt,

2
Michael, Zmienne przekazane jako argumenty do przygotowanych instrukcji automatycznie uciekną przez sterownik JDBC.
CodeBee ..

3
Czy możesz podać przykład, w jaki sposób atak wstrzyknięcia SQL działałby na przygotowane oświadczenie? Czy zakładasz błąd w kodzie bazy danych?
Peter Recore

2
Tak, ale to znacznie więcej niż „głupie”. To oszałamiająca głupota. Nikt z odrobiną wiedzy nie zrobiłby tego.
duffymo

2
Również wielu producentów baz danych nie obsługują parametryzacji nazwy kolumn (myśleć ORDER BY) i / lub stałe numeryczne w niektórych miejscach (myślę LIMIT, OFFSETi innych rozwiązań paginacji), więc te mogą być atakowane przez SQL injection, nawet gdy sporządzanych sprawozdań i parametryzacja jest używany wszędzie tam, gdzie możliwy.
dnet

31

Niektóre zalety PreparedStatement nad wyciągiem to:

  1. PreparedStatement pomaga nam zapobiegać atakom typu SQL injection, ponieważ automatycznie ucieka przed znakami specjalnymi.
  2. PreparedStatement pozwala nam wykonywać zapytania dynamiczne z danymi wejściowymi parametrów.
  3. PreparedStatement zapewnia różne typy metod ustawiających do ustawiania parametrów wejściowych dla zapytania.
  4. PreparedStatement jest szybszy niż Instrukcja. Staje się bardziej widoczny, gdy ponownie wykorzystamy PreparedStatement lub użyjemy jego metod przetwarzania wsadowego do wykonania wielu zapytań.
  5. PreparedStatement pomaga nam pisać kod zorientowany obiektowo metodami ustawiającymi, podczas gdy w przypadku instrukcji musimy utworzyć ciąg kwerendy. Jeśli trzeba ustawić wiele parametrów, pisanie zapytania za pomocą konkatenacji ciągów wygląda bardzo brzydko i jest podatne na błędy.

Przeczytaj więcej o problemie z iniekcją SQL na stronie http://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example


Przeczytałem twój artykuł, naprawdę dobry. Moje pytanie brzmi teraz: dlaczego ktoś miałby używać oświadczenia ?! nawet dla statycznego zapytania ?!
pedram bashiri

Zawsze używam PreparedStatement, nie znam żadnego konkretnego scenariusza, w którym oświadczenie mogłoby przynieść więcej korzyści.
Pankaj,

13

nic więcej do dodania,

1 - jeśli chcesz wykonać zapytanie w pętli (więcej niż 1 raz), przygotowana instrukcja może być szybsza, ze względu na wspomnianą optymalizację.

2 - sparametryzowane zapytanie to dobry sposób na uniknięcie SQL Injection. Zapytania sparametryzowane są dostępne tylko w PreparedStatement.


10

Wyciąg jest statyczny, a przygotowany wyciąg jest dynamiczny.

Instrukcja jest odpowiednia dla DDL i przygotowana deklaracja dla DML.

Wyciąg jest wolniejszy, a wyciąg przygotowany szybciej.

więcej różnic (zarchiwizowane)


7

Nie można wykonywać CLOBów w wyciągu.

Oraz: (OraclePreparedStatement) ps


7

Jak cytował Mattjames

Użycie instrukcji w JDBC powinno być w 100% zlokalizowane do użycia w DDL (ALTER, CREATE, GRANT itp.), Ponieważ są to jedyne typy instrukcji, które nie mogą zaakceptować ZMIENNYCH WIĄŻĄCYCH. PreparedStatements lub CallableStatements należy stosować do KAŻDEGO INNEGO typu instrukcji (DML, Zapytania). Ponieważ są to typy instrukcji, które akceptują zmienne wiązania.

To jest fakt, reguła, prawo - używaj przygotowanych oświadczeń WSZĘDZIE. Używaj OŚWIADCZEŃ prawie nie gdzie.


5

Wstrzykiwanie SQL jest ignorowane przez przygotowane polecenie, więc zwiększa się bezpieczeństwo przygotowanego polecenia


4
  • Łatwiej jest czytać
  • Możesz łatwo ustawić ciąg zapytania

4

Instrukcja będzie używana do wykonywania statycznych instrukcji SQL i nie może zaakceptować parametrów wejściowych.

PreparedStatement będzie używany do wykonywania instrukcji SQL wiele razy dynamicznie. Przyjmie parametry wejściowe.


4

Inna cecha przygotowanego lub sparametryzowanego zapytania: odniesienie zaczerpnięte z tego artykułu.

Ta instrukcja jest jedną z cech systemu bazy danych, w której ta sama instrukcja SQL jest wykonywana wielokrotnie z wysoką wydajnością. Przygotowane instrukcje są jednym z rodzajów szablonów i są używane przez aplikację o różnych parametrach.

Szablon instrukcji jest przygotowywany i wysyłany do systemu bazy danych, a system bazy danych wykonuje parsowanie, kompilację i optymalizację tego szablonu i przechowuje go bez wykonywania.

Niektóre parametry, takie jak klauzula nie jest przekazywana podczas tworzenia późniejszej aplikacji szablonu, wysyłają te parametry do systemu bazy danych, a system bazy danych korzysta z szablonu instrukcji SQL i wykonuje się zgodnie z żądaniem.

Przygotowane instrukcje są bardzo przydatne przeciwko SQL Injection, ponieważ aplikacja może przygotować parametr przy użyciu różnych technik i protokołów.

Gdy liczba danych rośnie i indeksy często się zmieniają w tym czasie Przygotowane instrukcje mogą zawieść, ponieważ w tej sytuacji wymagają nowego planu zapytań.


3

Statement interfejs wykonuje statyczne instrukcje SQL bez parametrów

PreparedStatement interfejs (instrukcja rozszerzająca) wykonuje wstępnie skompilowaną instrukcję SQL z / bez parametrów

  1. Wydajny w przypadku wielokrotnych egzekucji

  2. Jest wstępnie skompilowany, więc jest szybszy


2

Nie wpadaj w zamieszanie: po prostu pamiętaj

  1. Instrukcja służy do zapytań statycznych, takich jak DDL, tj. Tworzenie, upuszczanie, modyfikowanie i przygotowanie. Informacja jest stosowana do zapytań dynamicznych, np. Zapytań DML.
  2. W instrukcji, zapytanie nie jest wstępnie kompilowane podczas przygotowywania zapytania, zapytanie jest wstępnie kompilowane, ponieważ dzięki temu preparat jest oszczędny czasowo.
  3. PreparStatement pobiera argumenty w momencie tworzenia, a Instrukcja nie przyjmuje argumentów. Na przykład, jeśli chcesz utworzyć tabelę i wstawić element, to :: Utwórz tabelę (statyczny) za pomocą instrukcji i wstaw element (dynamiczny) za pomocą preparStatement.

1
PreparStatement pobiera argument w momencie tworzenia, a Instrukcja nie przyjmuje argumentów.

1

Postępowałem zgodnie ze wszystkimi odpowiedziami na to pytanie, aby zmienić działający stary kod, używając - Statement(ale mając SQL Injections) na rozwiązanie korzystające PreparedStatementze znacznie wolniejszego kodu z powodu słabego zrozumienia semantyki wokół Statement.addBatch(String sql)& PreparedStatement.addBatch().

Podaję więc tutaj mój scenariusz, aby inni nie popełnili tego samego błędu.

Mój scenariusz był

Statement statement = connection.createStatement();

for (Object object : objectList) {
    //Create a query which would be different for each object 
    // Add this query to statement for batch using - statement.addBatch(query);
}
statement.executeBatch();

Tak więc w powyższym kodzie miałem tysiące różnych zapytań, wszystkie dodane do tej samej instrukcji, a ten kod działał szybciej, ponieważ instrukcje, które nie były buforowane, były dobre i ten kod był wykonywany rzadko w aplikacji.

Teraz, aby naprawić Zastrzyki SQL, zmieniłem ten kod na,

List<PreparedStatement> pStatements = new ArrayList<>();    
for (Object object : objectList) {
    //Create a query which would be different for each object 
    PreparedStatement pStatement =connection.prepareStatement(query);
    // This query can't be added to batch because its a different query so I used list. 
    //Set parameter to pStatement using object 
    pStatements.add(pStatement);
}// Object loop
// In place of statement.executeBatch(); , I had to loop around the list & execute each update separately          
for (PreparedStatement ps : pStatements) {
    ps.executeUpdate();
}

Widzicie, zacząłem tworzyć tysiące PreparedStatementobiektów, a potem ostatecznie nie byłem w stanie korzystać z grupowania, ponieważ mój scenariusz tego wymagał - istnieją tysiące zapytań UPDATE lub INSERT, a wszystkie te zapytania są różne.

Naprawianie wstrzykiwania SQL było obowiązkowe bez kosztów obniżenia wydajności i nie sądzę, aby było możliwe PreparedStatementw tym scenariuszu.

Ponadto, gdy korzystasz z wbudowanej funkcji grupowania, musisz martwić się o zamknięcie tylko jednej instrukcji, ale w przypadku tej metody Listy musisz zamknąć instrukcję przed ponownym użyciem, Ponowne użycie PreparedStatement

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.