PreparedStatement IN-Klausel Alternativen?

Question

Was sind die besten Umgehungsmöglichkeiten für die Verwendung eines SQL IN Klausel mit Instanzen von java.sql.PreparedStatement die aufgrund von Sicherheitsproblemen bei SQL-Injection-Angriffen für mehrere Werte nicht unterstützt wird: Eine ? Platzhalter steht für einen Wert und nicht für eine Liste von Werten.

Betrachten Sie die folgende SQL-Anweisung:

SELECT my_column FROM my_table where search_column IN (?)

Verwendung von preparedStatement.setString( 1, "'A', 'B', 'C'" ); ist im Wesentlichen ein nicht funktionierender Versuch einer Umgehung der Gründe für die Verwendung von ? an erster Stelle.

Welche Umgehungsmöglichkeiten gibt es?

Answer 1

Die Beschränkung des in()-Operators ist die Wurzel allen Übels.

Es funktioniert für triviale Fälle, und man kann es mit "automatischer Generierung der vorbereiteten Anweisung" erweitern, aber es hat immer seine Grenzen.

• wenn Sie eine Anweisung mit einer variablen Anzahl von Parametern erstellen, wird bei jedem Aufruf ein Sql-Parse-Overhead erzeugt
• auf vielen Plattformen ist die Anzahl der Parameter des Operators in() begrenzt
• auf allen Plattformen ist die Gesamtgröße des SQL-Textes begrenzt, was das Senden von 2000 Platzhaltern für die in params
• das Senden von Bindungsvariablen von 1000-10k ist nicht möglich, da der JDBC-Treiber an seine Grenzen stößt

Der in()-Ansatz kann für einige Fälle gut genug sein, ist aber nicht raketenfest :)

Die raketensichere Lösung besteht darin, die beliebige Anzahl von Parametern in einem separaten Aufruf zu übergeben (z. B. durch Übergabe einer Gruppe von Parametern) und dann eine Ansicht (oder einen anderen Weg) zu haben, um sie in SQL darzustellen und in Ihren Where-Kriterien zu verwenden.

Eine Brute-Force-Variante finden Sie hier http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

Wenn Sie jedoch PL/SQL verwenden können, kann dieses Chaos ziemlich ordentlich werden.

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

Dann können Sie eine beliebige Anzahl von durch Komma getrennten Kundennummern als Parameter übergeben und:

• wird keine Verzögerung beim Parsen auftreten, da die SQL für select stabil ist
• keine Komplexität von Pipeline-Funktionen - es ist nur eine einzige Abfrage
• das SQL verwendet eine einfache Verknüpfung anstelle eines IN-Operators, was recht schnell ist
• Schließlich ist es eine gute Faustregel, dass no auf die Datenbank mit einem einfachen Select oder DML zuzugreifen, da es sich um Oracle handelt, das weitaus mehr bietet als MySQL oder ähnliche einfache Datenbank-Engines. PL/SQL ermöglicht es Ihnen, das Speichermodell von Ihrem Anwendungsdomänenmodell auf effektive Weise zu trennen.

Der Trick dabei ist:

• Wir brauchen einen Aufruf, der die lange Zeichenkette annimmt und an einem Ort speichert, an dem die DB-Sitzung darauf zugreifen kann (z. B. eine einfache Paketvariable oder dbms_session.set_context)
• dann brauchen wir eine Ansicht, die diese in Zeilen zerlegen kann
• und dann haben Sie eine Ansicht, die die abgefragten IDs enthält, so dass Sie nur eine einfache Verknüpfung mit der abgefragten Tabelle benötigen.

Die Ansicht sieht so aus:

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

wobei aux_in_list.getpayload sich auf die ursprüngliche Eingabezeichenfolge bezieht.

---

Ein möglicher Ansatz wäre die Übergabe von pl/sql-Arrays (die nur von Oracle unterstützt werden), allerdings können diese nicht in reinem SQL verwendet werden, weshalb immer ein Konvertierungsschritt erforderlich ist. Die Konvertierung kann nicht in SQL erfolgen, so dass die Übergabe eines Clob mit allen Parametern als String und dessen Konvertierung in einer Ansicht die effizienteste Lösung ist.

Answer 2

So habe ich das Problem in meiner eigenen Anwendung gelöst. Idealerweise sollten Sie einen StringBuilder verwenden, anstatt + für Strings zu verwenden.

    String inParenthesis = "(?";
    for(int i = 1;i < myList.size();i++) {
      inParenthesis += ", ?";
    }
    inParenthesis += ")";

    try(PreparedStatement statement = SQLite.connection.prepareStatement(
        String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
      int x = 1;
      statement.setLong(x++, race.startTime);
      statement.setString(x++, race.name);
      statement.setInt(x++, traderIdx);

      for(String str : race.betFair.winners) {
        statement.setString(x++, str);
      }

      int effected = statement.executeUpdate();
    }

Die Verwendung einer Variablen wie x anstelle von konkreten Zahlen ist sehr hilfreich, wenn Sie die Abfrage zu einem späteren Zeitpunkt ändern möchten.

Answer 3

Ich habe es nie versucht, aber würde .setArray() tun, was Sie suchen?

Update : Offensichtlich nicht. setArray scheint nur mit einem java.sql.Array zu funktionieren, das aus einer ARRAY-Spalte stammt, die Sie aus einer vorherigen Abfrage abgerufen haben, oder einer Unterabfrage mit einer ARRAY-Spalte.

Answer 4

Meine Abhilfe ist:

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

Jetzt können Sie eine Variable verwenden, um einige Werte in einer Tabelle zu erhalten:

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

Die vorbereitete Erklärung könnte also lauten:

  "select * from TABLE where COL in (select * from table(split(?)))"

リガードです。

Javier Ibanez

Answer 5

Sie können die setArray-Methode verwenden, wie sie in diese Javadoku :

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"E1", "E2","E3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();