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

In manchen Situationen kann eine Regexp helfen. Hier ist ein Beispiel, das ich mit Oracle überprüft habe, und es funktioniert.

select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')

Aber es gibt auch eine Reihe von Nachteilen:

1. Jede angewandte Spalte sollte zumindest implizit in varchar/char umgewandelt werden.
2. Mit Sonderzeichen muss man vorsichtig sein.
3. Das kann die Leistung verlangsamen - in meinem Fall verwendet die IN-Version einen Index- und Bereichsscan und die REGEXP-Version einen vollständigen Scan.

Answer 2

Es gibt verschiedene alternative Ansätze, die wir für die IN-Klausel in PreparedStatement verwenden können.

1. Verwendung einzelner Abfragen - langsamste Leistung und ressourcenintensiv
2. StoredProcedure verwenden - am schnellsten, aber datenbankspezifisch
3. Erstellen einer dynamischen Abfrage für PreparedStatement - Gute Leistung, aber kein Vorteil durch Caching und PreparedStatement wird jedes Mal neu kompiliert.

4. Verwenden Sie NULL in PreparedStatement-Abfragen - Optimale Leistung, funktioniert hervorragend, wenn Sie die Grenze der IN-Klausel Argumente kennen. Wenn es kein Limit gibt, können Sie Abfragen im Batch ausführen. Beispiel-Codeausschnitt ist;

       int i = 1;
       for(; i <=ids.length; i++){
           ps.setInt(i, ids[i-1]);
       }
   
       //set null for remaining ones
       for(; i<=PARAM_SIZE;i++){
           ps.setNull(i, java.sql.Types.INTEGER);
       }

Weitere Einzelheiten zu diesen alternativen Ansätzen finden Sie unter aquí .

Answer 3

Nach Adams Idee. Machen Sie Ihre vorbereitete Anweisung zu einer Art select my_column from my_table where search_column in (#) Erstellen Sie einen String x und füllen Sie ihn mit einer Anzahl von "?,?,?", je nach Ihrer Liste von Werten Ändern Sie dann einfach das # in der Abfrage für Ihren neuen String x und füllen Sie

Answer 4

Nur der Vollständigkeit halber: Solange die Menge der Werte nicht zu groß ist, können Sie könnte auch einfach eine Anweisung wie

... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?

die Sie dann an prepare() weitergeben können, und dann setXXX() in einer Schleife verwenden, um alle Werte zu setzen. Das sieht eklig aus, aber viele "große" kommerzielle Systeme machen so etwas routinemäßig, bis sie an DB-spezifische Grenzen stoßen, z. B. 32 KB (ich glaube, das ist es) für Anweisungen in Oracle.

Natürlich müssen Sie sicherstellen, dass die Menge nie unangemessen groß wird, oder Sie müssen eine Fehlerüberfüllung vornehmen, falls dies doch der Fall sein sollte.

Answer 5

Das hat bei mir funktioniert (Psuedocode):

public class SqlHelper
{
    public static final ArrayList<String>platformList = new ArrayList<>(Arrays.asList("iOS","Android","Windows","Mac"));

    public static final String testQuery = "select * from devices where platform_nm in (:PLATFORM_NAME)";
}

die Bindung spezifizieren:

public class Test extends NamedParameterJdbcDaoSupport
public List<SampleModelClass> runQuery()
{
    //define rowMapper to insert in object of SampleClass
    final Map<String,Object> map = new HashMap<>();
    map.put("PLATFORM_LIST",DeviceDataSyncQueryConstants.platformList);
    return getNamedParameterJdbcTemplate().query(SqlHelper.testQuery, map, rowMapper)
}