Solutions de rechange PreparedStatement IN clause ?

Question

Quelles sont les meilleures solutions pour l'utilisation de SQL IN clause avec les instances de l' java.sql.PreparedStatement, ce qui n'est pas pris en charge pour plusieurs valeurs en raison d'une attaque par injection SQL des questions de sécurité: Un ? espace réservé représente une valeur, plutôt que d'une liste de valeurs.

Considérons l'instruction SQL suivante:

SELECT my_column FROM my_table where search_column IN (?)

À l'aide de preparedStatement.setString( 1, "'A', 'B', 'C'" ); est essentiellement un travail non-tentative de contournement des raisons pour l'utilisation d' ? dans la première place.

Quelles solutions sont disponibles?

Answer 1

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

Les options proposées sont les suivantes:

• Préparez SELECT my_column FROM my_table WHERE search_column = ?, de l'exécuter pour chaque valeur et de l'UNION les résultats côté client. Ne nécessite qu'une déclaration préparée. Lente et douloureuse.
• Préparez SELECT my_column FROM my_table WHERE search_column IN (?,?,?) et de l'exécuter. Nécessite une instruction préparée par la taille-de-DANS-liste. Rapide et évident.
• Préparez SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... et de l'exécuter. [Ou utiliser UNION ALL au lieu de ces points-virgules. --ed] Nécessite une instruction préparée par la taille-de-DANS-liste. Hébété lent, strictement pire qu' WHERE search_column IN (?,?,?), donc je ne sais pas pourquoi le blogueur a même suggéré.
• L'utilisation d'une procédure stockée pour construire le jeu de résultats.
• Préparer la N de taille différente-de-DANS-la liste des requêtes; dire, avec 2, 10 et 50 valeurs. Pour rechercher une liste avec les 6 valeurs différentes, remplir la taille-10 de la requête de sorte qu'il 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, permettra d'optimiser les valeurs en double avant d'exécuter la requête.

Aucune de ces options est super grand, bien.

Dupliquer les questions ont été répondues dans ces lieux, avec d'aussi sain d'esprit de solutions de rechange, toujours aucun d'eux super un grand:

• JDBC Déclaration Préparée à l'avance, comment définir une liste?
• PreparedStatement avec la liste de paramètres dans une clause
• Comment définir la liste des paramètres de l'instruction préparée?

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:

• PreparedStatement DANS la clause alternatives?

Il ne semble pas être un moyen de le rendre setArray travaillent avec des listes, si.

Answer 2

Solution pour PostgreSQL :

ou

Answer 3

Voici comment faire :

et comment l’utiliser :

Answer 4

Pas de moyen simple autant que je sache. Si la cible est de garder cache d'instruction ratio élevé (j'.e de ne pas créer une déclaration pour chaque paramètre comte de), vous pouvez effectuer les opérations suivantes:

1. créer une instruction avec quelques-uns (par exemple 10) paramètres:

   ... OÙ UN EN (?,?,?,?,?,?,?,?,?,?) ...

2. Lier tous les paramètres actuall

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

3. Lier le reste NULLE

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

NUL jamais ne correspond à rien, donc il s'est optimisée par le plan SQL builder.

La logique est facile à automatiser lorsque vous passez une Liste dans un DAO fonction:

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

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

Answer 5

Un travail désagréable, mais certainement possible est d'utiliser une requête imbriquée. Créer une table temporaire MYVALUES avec une colonne. Insérez votre liste de valeurs dans la MYVALUES table. Puis l'exécuter

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 grand.

Cette technique a l'avantage de potentiellement de meilleurs plans de requête à partir de l'optimiseur (cocher une page pour plusieurs valeurs, tablescan qu'une seule fois au lieu d'une fois par valeur, etc) permet d'économiser sur les frais généraux si votre base de données n'est pas la mise en cache des requêtes préparées. Votre "INSERTS" devra être effectuée dans le lot et le MYVALUES tableau peut avoir besoin d'être modifié pour avoir un minimum de verrouillage ou d'autres haut-frais de protections.