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

Ich bin auf eine Reihe von Einschränkungen im Zusammenhang mit vorbereiteten Erklärungen gestoßen:

1. Die vorbereiteten Anweisungen werden nur innerhalb der gleichen Sitzung (Postgres) zwischengespeichert, so dass es nur mit Verbindungspooling funktioniert
2. Viele verschiedene vorbereitete Anweisungen, wie von @BalusC vorgeschlagen, können dazu führen, dass der Cache überfüllt wird und zuvor zwischengespeicherte Anweisungen gelöscht werden
3. Die Abfrage muss optimiert werden und Indizes verwenden. Klingt offensichtlich, aber z.B. die ANY(ARRAY...)-Anweisung, die von @Boris in einer der Top-Antworten vorgeschlagen wurde, kann keine Indizes verwenden und die Abfrage wird trotz Caching langsam sein
4. Die vorbereitete Anweisung speichert auch den Abfrageplan, und die tatsächlichen Werte der in der Anweisung angegebenen Parameter sind nicht verfügbar.

Unter den vorgeschlagenen Lösungen würde ich diejenige wählen, die die Abfrageleistung nicht beeinträchtigt und eine geringere Anzahl von Abfragen ermöglicht. Dies wäre die #4 (Stapelverarbeitung von wenigen Abfragen) aus dem Link von @Don oder die Angabe von NULL-Werten für nicht benötigte '?'-Markierungen, wie von @Vladimir Dyuzhev vorgeschlagen

Answer 2

Ich habe gerade eine PostgreSQL-spezifische Option dafür ausgearbeitet. Es ist ein bisschen ein Hack und hat seine eigenen Vor- und Nachteile und Einschränkungen, aber es scheint zu funktionieren und ist nicht auf eine bestimmte Entwicklungssprache, Plattform oder einen PG-Treiber beschränkt.

Der Trick ist natürlich, einen Weg zu finden, um eine beliebige Länge Sammlung von Werten als ein einzelner Parameter übergeben, und haben die DB erkennen es als mehrere Werte. Die Lösung, die ich habe, ist eine begrenzte Zeichenfolge aus den Werten in der Sammlung zu konstruieren, übergeben Sie diese Zeichenfolge als ein einzelner Parameter, und verwenden Sie string_to_array() mit der erforderlichen Casting für PostgreSQL, um richtig zu verwenden.

Wenn Sie also nach "foo", "blah" und "abc" suchen wollen, können Sie sie zu einer einzigen Zeichenkette verketten: 'foo,blah,abc'. Hier ist das direkte SQL:

select column from table
where search_column = any (string_to_array('foo,blah,abc', ',')::text[]);

Sie würden natürlich ändern Sie die explizite Cast zu was auch immer Sie wollten Ihre resultierende Wert-Array zu sein - int, Text, uuid, etc. Und da die Funktion einen einzelnen String-Wert annimmt (oder zwei, wenn Sie auch das Trennzeichen anpassen möchten), können Sie ihn als Parameter in einer vorbereiteten Anweisung übergeben:

select column from table
where search_column = any (string_to_array($1, ',')::text[]);

Dies ist sogar flexibel genug, um Dinge wie LIKE-Vergleiche zu unterstützen:

select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);

Auch dies ist zweifellos ein Hack, aber er funktioniert und ermöglicht es Ihnen, vorkompilierte vorbereitete Anweisungen zu verwenden, die die *hust* diskrete Parameter, mit den damit einhergehenden Sicherheits- und (vielleicht) Leistungsvorteilen. Ist dies ratsam und tatsächlich leistungsfähig? Das kommt natürlich darauf an, denn bevor die Abfrage überhaupt ausgeführt wird, findet bereits ein String-Parsing und möglicherweise ein Casting statt. Wenn Sie drei, fünf oder ein paar Dutzend Werte übermitteln wollen, ist das wahrscheinlich in Ordnung. Ein paar Tausend? Ja, vielleicht nicht so sehr. YMMV, Einschränkungen und Ausschlüsse gelten, keine ausdrückliche oder stillschweigende Garantie.

Aber es funktioniert.

Answer 3

Erzeugen Sie den Abfrage-String in der PreparedStatement, um eine Anzahl von ?'s zu haben, die der Anzahl der Elemente in Ihrer Liste entspricht. Hier ist ein Beispiel:

public void myQuery(List<String> items, int other) {
  ...
  String q4in = generateQsForIn(items.size());
  String sql = "select * from stuff where foo in ( " + q4in + " ) and bar = ?";
  PreparedStatement ps = connection.prepareStatement(sql);
  int i = 1;
  for (String item : items) {
    ps.setString(i++, item);
  }
  ps.setInt(i++, other);
  ResultSet rs = ps.executeQuery();
  ...
}

private String generateQsForIn(int numQs) {
    String items = "";
    for (int i = 0; i < numQs; i++) {
        if (i != 0) items += ", ";
        items += "?";
    }
    return items;
}

Answer 4

SetArray ist die beste Lösung, aber für viele ältere Treiber nicht verfügbar. Der folgende Workaround kann in java8 verwendet werden

String baseQuery ="SELECT my_column FROM my_table where search_column IN (%s)"

String markersString = inputArray.stream().map(e -> "?").collect(joining(","));
String sqlQuery = String.format(baseSQL, markersString);

//Now create Prepared Statement and use loop to Set entries
int index=1;

for (String input : inputArray) {
     preparedStatement.setString(index++, input);
}

Diese Lösung ist besser als andere hässliche while-Schleifenlösungen, bei denen der Abfrage-String durch manuelle Iterationen aufgebaut wird

Answer 5

Niemand scheint bisher vorgeschlagen zu haben, einen handelsüblichen Query Builder zu verwenden, wie jOOQ ou AbfrageDSL oder sogar Kriterium Abfrage die verwalten dynamisch IN Listen out of the box", möglicherweise einschließlich des Managements aller Randfälle, die auftreten können, wie z. B.:

• Die von Oracle vorgegebene Obergrenze von 1000 Elementen pro IN Liste (unabhängig von der Anzahl der Bindungswerte)
• Überschreitung der maximalen Anzahl von Bindungswerten eines Treibers, die ich in dieser Antwort dokumentiert habe
• Cursor-Cache-Probleme, weil zu viele unterschiedliche SQL-Strings "hart geparst" werden und Ausführungspläne nicht mehr gecached werden können (jOOQ und seit kurzem auch Hibernate umgehen dies durch IN Listenpolsterung )

(Haftungsausschluss: Ich arbeite für das Unternehmen hinter jOOQ)