Question PreparedStatement IN alternatives de la clause?


Quelles sont les meilleures solutions pour utiliser un SQL IN clause avec des instances de java.sql.PreparedStatement, qui n'est pas pris en charge pour plusieurs valeurs en raison de problèmes de sécurité d'attaque par injection SQL: un ? placeholder représente une valeur plutôt qu'une liste de valeurs.

Considérez l'instruction SQL suivante:

SELECT my_column FROM my_table where search_column IN (?)

En utilisant preparedStatement.setString( 1, "'A', 'B', 'C'" ); est essentiellement une tentative non fonctionnelle de contourner les raisons de l'utilisation ? en premier lieu.

Quelles solutions de contournement sont disponibles?


296
2017-10-07 13:41


origine


Réponses:


Une analyse des différentes options disponibles, et les avantages et les inconvénients de chacun est disponible ici.

Les options suggérées sont les suivantes:

  • Préparer SELECT my_column FROM my_table WHERE search_column = ?, l'exécuter pour chaque valeur et UNION les résultats côté client. Nécessite seulement une déclaration préparée. Lente et douloureuse.
  • Préparer SELECT my_column FROM my_table WHERE search_column IN (?,?,?) et l'exécuter. Requiert une instruction préparée par taille-de-IN-list. Rapide et évident.
  • Préparer SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... et l'exécuter. [Ou utiliser UNION ALL à la place de ces points-virgules. --ed] Requiert une instruction préparée par taille-de-IN-list. Stupidement lent, strictement pire que WHERE search_column IN (?,?,?), donc je ne sais pas pourquoi le blogueur l'a même suggéré.
  • Utilisez une procédure stockée pour créer le jeu de résultats.
  • Préparer N différentes requêtes de taille d'IN-list; Par exemple, avec 2, 10 et 50 valeurs. Pour rechercher une liste IN avec 6 valeurs différentes, remplissez la requête size-10 pour qu'elle ressemble à SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Tout serveur décent optimisera les valeurs en double avant d'exécuter la requête.

Aucune de ces options n'est super géniale, cependant.

Des questions en double ont été répondues dans ces endroits avec des alternatives tout aussi saines, mais aucune n’est encore géniale:

La bonne réponse, si vous utilisez JDBC4 et un serveur qui prend en charge x = ANY(y), est d'utiliser PreparedStatement.setArray comme décrit ici:

Il ne semble pas y avoir de moyen de faire setArray travailler avec des listes IN, cependant.


162
2017-10-09 22:13



Solution pour 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));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

ou

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));
final ResultSet rs = statement.executeQuery();
try {
    while(rs.next()) {
        // do some...
    }
} finally {
    rs.close();
}

104
2018-04-20 04:32



Pas de manière simple AFAIK. Si la cible est de conserver un ratio de cache d'instruction élevé (c'est-à-dire de ne pas créer d'instruction pour chaque nombre de paramètres), vous pouvez procéder comme suit:

  1. créer une déclaration avec quelques paramètres (par exemple 10):

    ... O A UN IN (?,?,?,?,?,?,?,?,?,?) ...

  2. Lier tous les paramètres actuall

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

  3. Lier le reste à NULL

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

NULL ne correspond à rien, il est donc optimisé par le générateur de plan SQL.

La logique est facile à automatiser lorsque vous transmettez une liste à une fonction DAO:

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

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

18
2017-10-09 21:52



Un travail désagréable, mais certainement réalisable, consiste à utiliser une requête imbriquée. Créez une table temporaire MYVALUES avec une colonne. Insérez votre liste de valeurs dans la table MYVALUES. Ensuite, exécutez

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

Moche, mais une alternative viable si votre liste de valeurs est très grande.

Cette technique présente l'avantage supplémentaire d'avoir de meilleurs plans de requête de l'optimiseur (vérifiez une page pour plusieurs valeurs, une seule fois par table, etc.) peut économiser sur la surcharge si votre base de données ne met pas en cache les instructions préparées. Votre "INSERTS" devra être fait en batch et la table MYVALUES devra peut-être être ajustée pour avoir un verrouillage minimal ou d’autres protections à haut risque.


10
2017-10-07 23:49



Les limites de l'opérateur in () sont la racine de tout mal.

Cela fonctionne pour les cas triviaux, et vous pouvez l'étendre avec la "génération automatique de l'instruction préparée" mais elle a toujours ses limites.

  • si vous créez une instruction avec un nombre variable de paramètres, cela fera un overhead SQL à chaque appel
  • sur de nombreuses plateformes, le nombre de paramètres de l'opérateur in () est limité
  • sur toutes les plates-formes, la taille totale du texte SQL est limitée, rendant impossible l'envoi de 2 000 espaces réservés pour les paramètres in
  • l'envoi de variables de liaison de 1000-10k n'est pas possible, car le pilote JDBC a ses limites

L'approche in () peut être assez bonne dans certains cas, mais pas à l'épreuve des fusées :)

La solution infaillible est de passer le nombre arbitraire de paramètres dans un appel séparé (en passant un clob de params, par exemple), puis avoir une vue (ou tout autre moyen) pour les représenter en SQL et utiliser dans votre Critères.

Une variante de force brute est ici http://tkyte.blogspot.hu/2006/06/varying-in-lists.html 

Cependant, si vous pouvez utiliser PL / SQL, ce gâchis peut devenir très soigné.

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;

Ensuite, vous pouvez transmettre un nombre arbitraire d’ID de client séparés par des virgules dans le paramètre et:

  • n'obtiendra aucun délai d'analyse, car le SQL pour select est stable
  • pas de complexité des fonctions en pipeline - ce n'est qu'une requête
  • le SQL utilise une jointure simple, au lieu d'un opérateur IN, ce qui est assez rapide
  • après tout, c'est une bonne règle de base de ne pas frapper la base de données avec une sélection simple ou DML, car c'est Oracle, qui offre des années de lumière plus que MySQL ou des moteurs de base de données simples similaires. PL / SQL vous permet de masquer le modèle de stockage de votre modèle de domaine d'application de manière efficace.

L'astuce ici est:

  • nous avons besoin d'un appel qui accepte la chaîne longue et stocke quelque part où la session de base de données peut y accéder (par exemple, variable de package simple ou dbms_session.set_context)
  • alors nous avons besoin d'une vue qui peut analyser ceci en lignes
  • et puis vous avez une vue qui contient les identifiants que vous interrogez, donc tout ce dont vous avez besoin est une simple jointure à la table demandée.

La vue ressemble à:

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

où aux_in_list.getpayload fait référence à la chaîne d'entrée d'origine.


Une approche possible serait de passer des tableaux pl / sql (pris en charge par Oracle uniquement), mais vous ne pouvez pas les utiliser en SQL pur. Par conséquent, une étape de conversion est toujours nécessaire. La conversion ne peut pas se faire en SQL, donc après tout, passer un clob avec tous les paramètres dans string et le convertir en vue est la solution la plus efficace.


7
2018-02-17 14:44



Je n'ai jamais essayé, mais est-ce que .setArray () ferait ce que vous cherchez?

Mettre à jour: Évidemment pas. setArray ne semble fonctionner qu'avec un fichier java.sql.Array provenant d'une colonne ARRAY que vous avez extraite d'une requête précédente ou d'une sous-requête avec une colonne ARRAY.


5
2017-10-07 13:45



Ma solution de contournement est la suivante:

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;
/

Maintenant, vous pouvez utiliser une variable pour obtenir des valeurs dans une table:

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

Ainsi, la déclaration préparée pourrait être:

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

Cordialement,

Javier Ibanez


5
2018-02-24 12:44



Voici comment je l'ai résolu dans ma propre application. Idéalement, vous devriez utiliser un StringBuilder au lieu d'utiliser + pour les chaînes.

    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();
    }

Utiliser une variable comme x ci-dessus au lieu de nombres concrets aide beaucoup si vous décidez de modifier la requête ultérieurement.


5
2018-04-08 05:06



Je suppose que vous pourriez (en utilisant la manipulation de chaîne de base) générer la chaîne de requête dans le PreparedStatement avoir un certain nombre de ?correspond au nombre d'éléments dans votre liste.

Bien sûr, si vous faites cela, vous êtes à deux pas de générer un géant enchaîné OR dans votre requête, mais sans avoir le bon nombre de ? dans la chaîne de requête, je ne vois pas comment vous pouvez contourner cela.


3
2017-10-07 13:47



Vous pouvez utiliser la méthode setArray comme mentionné dans ce javadoc:

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();

2
2018-06-09 19:34