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

Eine Analyse der verschiedenen verfügbaren Optionen sowie der Vor- und Nachteile jeder Option ist verfügbar aquí .

Die vorgeschlagenen Optionen sind:

• Vorbereiten SELECT my_column FROM my_table WHERE search_column = ? auszuführen und die Ergebnisse clientseitig zu UNION zu vereinen. Erfordert nur eine vorbereitete Anweisung. Langsam und mühsam.
• Vorbereiten SELECT my_column FROM my_table WHERE search_column IN (?,?,?) und führen sie aus. Erfordert eine vorbereitete Anweisung pro Größe der IN-Liste. Schnell und offensichtlich.
• Vorbereiten SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... und führen sie aus. [Oder verwenden Sie UNION ALL anstelle dieser Semikolons. --ed] Erfordert eine vorbereitete Anweisung pro size-of-IN-list. Dumm und langsam, schlechter als WHERE search_column IN (?,?,?) Ich weiß also nicht, warum der Blogger dies überhaupt vorgeschlagen hat.
• Verwenden Sie eine gespeicherte Prozedur, um die Ergebnismenge zu erstellen.
• Bereiten Sie N verschiedene Abfragen zur Größe einer IN-Liste vor, z. B. mit 2, 10 und 50 Werten. Um nach einer IN-Liste mit 6 verschiedenen Werten zu suchen, füllen Sie die Größe-10-Abfrage so aus, dass sie wie folgt aussieht SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6) . Jeder anständige Server optimiert die doppelten Werte, bevor die Abfrage ausgeführt wird.

Keine dieser Optionen ist ideal.

Die beste Option, wenn Sie JDBC4 und einen Server verwenden, der die x = ANY(y) ist zu verwenden PreparedStatement.setArray comme hier beschrieben

Es scheint keine Möglichkeit zu geben, die setArray jedoch mit IN-Listen arbeiten.

---

Manchmal werden SQL-Anweisungen zur Laufzeit geladen (z. B. aus einer Eigenschaftsdatei), erfordern aber eine variable Anzahl von Parametern. In solchen Fällen definieren Sie zunächst die Abfrage:

query=SELECT * FROM table t WHERE t.column IN (?)

Als nächstes laden Sie die Abfrage. Bestimmen Sie dann die Anzahl der Parameter, bevor Sie sie ausführen. Sobald die Anzahl der Parameter bekannt ist, führen Sie sie aus:

sql = any( sql, count );

Zum Beispiel:

/**
 * Converts a SQL statement containing exactly one IN clause to an IN clause
 * using multiple comma-delimited parameters.
 *
 * @param sql The SQL statement string with one IN clause.
 * @param params The number of parameters the SQL statement requires.
 * @return The SQL statement with (?) replaced with multiple parameter
 * placeholders.
 */
public static String any(String sql, final int params) {
    // Create a comma-delimited list based on the number of parameters.
    final StringBuilder sb = new StringBuilder(
        String.join(", ", Collections.nCopies(possibleValue.size(), "?")));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // Return the modified comma-delimited list of parameters.
    return sql;
}

Für bestimmte Datenbanken, bei denen die Übergabe eines Arrays über die JDBC 4 Spezifikation nicht unterstützt wird, kann diese Methode die Umwandlung der langsamen = ? in die schnellere IN (?) Klauselbedingung, die dann durch den Aufruf der any método.

Answer 2

Lösung für PostgreSQL:

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

o

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

Answer 3

Kein einfacher Weg AFAIK. Wenn das Ziel ist, die Anweisungs-Cache-Quote hoch zu halten (d.h. nicht für jeden Parameter eine Anweisung zu erstellen), können Sie Folgendes tun:

1. eine Anweisung mit wenigen (z. B. 10) Parametern erstellen:

   ... WENN A IN (?,?,?,?,?,?,?,?,?,?,?,?) ...

2. Alle aktuellen Parameter binden

   setString(1, "foo"); setString(2, "bar");

3. Binden Sie den Rest als NULL

   setNull(3,Typen.VARCHAR) ... setNull(10,Typen.VARCHAR)

NULL passt nie zu irgendetwas, daher wird es vom SQL Plan Builder optimiert.

Die Logik ist leicht zu automatisieren, wenn Sie eine Liste an eine DAO-Funktion übergeben:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

Answer 4

Sie können verwenden Collections.nCopies um eine Sammlung von Platzhaltern zu erzeugen und sie mit String.join :

List<String> params = getParams();
String placeHolders = String.join(",", Collections.nCopies(params.size(), "?"));
String sql = "select * from your_table where some_column in (" + placeHolders + ")";
try (   Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql)) {
    int i = 1;
    for (String param : params) {
        ps.setString(i++, param);
    }
    /*
     * Execute query/do stuff
     */
}

Answer 5

Eine unangenehme Umgehung, aber durchaus machbar, ist die Verwendung einer verschachtelten Abfrage. Erstellen Sie eine temporäre Tabelle MYVALUES mit einer Spalte darin. Fügen Sie Ihre Liste von Werten in die Tabelle MYVALUES ein. Führen Sie dann

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

Hässlich, aber eine brauchbare Alternative, wenn die Liste der Werte sehr groß ist.

Diese Technik hat den zusätzlichen Vorteil, dass der Optimierer potenziell bessere Abfragepläne erstellt (Überprüfung einer Seite auf mehrere Werte, Tablescan nur einmal statt einmal pro Wert usw.) und möglicherweise Overhead spart, wenn Ihre Datenbank keine vorbereiteten Anweisungen zwischenspeichert. Ihre "INSERTS" müssten im Batch-Verfahren durchgeführt werden, und die Tabelle MYVALUES müsste möglicherweise so angepasst werden, dass sie nur minimale Sperrungen oder andere Schutzmaßnahmen mit hohem Overhead aufweist.