¿Alternativas a la cláusula PreparedStatement IN?

¿Cuáles son las mejores soluciones para usar una INcláusula SQL con instancias de java.sql.PreparedStatement, que no se admite para múltiples valores debido a problemas de seguridad de ataques de inyección SQL? Un ?marcador de posición representa un valor, en lugar de una lista de valores.

Considere la siguiente declaración SQL:

SELECT my_column FROM my_table where search_column IN (?)

El uso preparedStatement.setString( 1, "'A', 'B', 'C'" );es esencialmente un intento fallido de solucionar los motivos del uso ?en primer lugar.

¿Qué soluciones alternativas están disponibles?

Oct 07 '08 20:10 Chris Mazzola

Un análisis de las diversas opciones disponibles y los pros y los contras de cada una está disponible en la entrada Batching Select Statements de Jeanne Boyarsky en JDBC en JavaRanch Journal.

Las opciones sugeridas son:

Prepárelo SELECT my_column FROM my_table WHERE search_column = ?, ejecútelo para cada valor y UNION los resultados del lado del cliente. Requiere sólo una declaración preparada. Lento y doloroso.
Prepárelo SELECT my_column FROM my_table WHERE search_column IN (?,?,?)y ejecútelo. Requiere una declaración preparada por tamaño de lista IN. Rápido y obvio.
Prepárelo SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...y ejecútelo. [O utilizar UNION ALLen lugar de esos puntos y comas. --ed] Requiere una declaración preparada por tamaño de lista IN. Estúpidamente lento, estrictamente peor que WHERE search_column IN (?,?,?), así que no sé por qué el blogger lo sugirió.
Utilice un procedimiento almacenado para construir el conjunto de resultados.
Prepare N consultas de lista IN de diferentes tamaños; digamos, con 2, 10 y 50 valores. Para buscar una lista IN con 6 valores diferentes, complete la consulta de tamaño 10 para que se vea como SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Cualquier servidor decente optimizará los valores duplicados antes de ejecutar la consulta.

Ninguna de estas opciones es ideal.

La mejor opción si está utilizando JDBC4 y un servidor que lo admita x = ANY(y)es utilizarlo PreparedStatement.setArraycomo se describe en la respuesta de Boris .

Sin embargo , no parece haber ninguna forma de trabajar setArraycon listas IN.

A veces las sentencias SQL se cargan en tiempo de ejecución (por ejemplo, desde un archivo de propiedades) pero requieren un número variable de parámetros. En tales casos, primero defina la consulta:

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

A continuación, cargue la consulta. Luego determine la cantidad de parámetros antes de ejecutarlo. Una vez conocido el recuento de parámetros, ejecute:

sql = any( sql, count );

Por ejemplo:

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

Para ciertas bases de datos donde no se admite pasar una matriz a través de la especificación JDBC 4, este método puede facilitar la transformación de = ?la condición de cláusula lenta en más rápida IN (?), que luego se puede expandir llamando al anymétodo.

Oct 09 '2008 22:10 Dónal

Solución para 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...
    }
}

Apr 20 '2012 04:04 Boris

No es una manera sencilla que yo sepa. Si el objetivo es mantener alta la proporción de caché de declaraciones (es decir, no crear una declaración por cada recuento de parámetros), puede hacer lo siguiente:

cree una declaración con algunos (por ejemplo, 10) parámetros:

... DONDE A EN (?,?,?,?,?,?,?,?,?,?) ...
Vincular todos los parámetros reales

setString(1,"foo"); setString(2,"barra");
Vincular el resto como NULL

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

NULL nunca coincide con nada, por lo que el creador del plan SQL lo optimiza.

La lógica es fácil de automatizar cuando pasa una Lista a una función DAO:

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

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

Oct 09 '2008 21:10 Vladimir Dyuzhev