Pregunta Parametrizar una cláusula SQL IN


¿Cómo parametrizo una consulta que contiene un IN cláusula con un número variable de argumentos, como este?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

En esta consulta, la cantidad de argumentos podría estar entre 1 y 5.

Preferiría no utilizar un procedimiento almacenado dedicado para esto (o XML), pero si hay alguna manera elegante específica para SQL Server 2008, Estoy abierto a eso.


950
2017-12-03 16:16


origen


Respuestas:


Aquí hay una técnica rápida y sucia que he usado:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

Así que aquí está el código C #:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

Dos advertencias:

  • El rendimiento es terrible. LIKE "%...%" las consultas no están indexadas.
  • Asegúrate de no tener ninguna |, etiquetas en blanco o nulas o esto no funcionará

Hay otras maneras de lograr esto que algunas personas pueden considerar más limpias, así que por favor sigan leyendo.


288
2017-12-03 16:41



Puedes parametrizar cada valor, algo así como:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

Lo cual te dará:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

No, esto no está abierto a inyección SQL. El único texto inyectado en CommandText no se basa en la entrada del usuario. Está basado únicamente en el prefijo "@tag" codificado y el índice de una matriz. El índice lo hará siempre ser un número entero, no generado por el usuario, y es seguro.

Los valores ingresados ​​por el usuario todavía están rellenos en parámetros, por lo que no hay vulnerabilidad allí.

Editar:

Dejando de lado las preocupaciones sobre la inyección, tenga en cuenta que construir el texto de comando para acomodar un número variable de parámetros (como el anterior) impide que el servidor SQL aproveche las consultas en caché. El resultado neto es que casi seguro perderá el valor de usar parámetros en primer lugar (en lugar de simplemente insertar las cadenas de predicados en el SQL mismo).

No es que los planes de consulta en caché no sean valiosos, pero IMO esta consulta no es lo suficientemente complicada como para ver un gran beneficio. Si bien los costos de compilación pueden acercarse (o incluso superar) los costos de ejecución, todavía estás hablando de milisegundos.

Si tiene suficiente memoria RAM, esperaría que SQL Server probablemente también almacenara en caché un plan para los recuentos comunes de parámetros. Supongo que siempre puedes agregar cinco parámetros y dejar que las etiquetas no especificadas sean NULL, el plan de consulta debería ser el mismo, pero me parece bastante feo y no estoy seguro de que valga la micro optimización (aunque, en desbordamiento de pila, bien puede valer la pena).

Además, SQL Server 7 y posterior auto-parametrizar consultas, por lo tanto, el uso de parámetros no es realmente necesario desde el punto de vista del rendimiento; sin embargo, crítico desde un punto de vista de seguridad, especialmente con datos ingresados ​​por el usuario como este.


675
2017-12-03 16:35



Para SQL Server 2008, puede usar un parámetro de tabla de valores. Es un poco de trabajo, pero podría decirse que es más limpio que mi otro método.

Primero, debes crear un tipo

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

Entonces, su código ADO.NET se ve así:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

235
2017-12-03 16:53



La pregunta original era "Cómo parametrizo una consulta ..."

Permítanme decir aquí, que esto es no una respuesta a la pregunta original. Ya hay algunas demostraciones de eso en otras buenas respuestas.

Dicho esto, adelante y marque esta respuesta, declárela, marque como no una respuesta ... haga lo que crea que es correcto.

Vea la respuesta de Mark Brackett para la respuesta preferida que yo (y 231 personas más) votaron en alza. El enfoque dado en su respuesta permite 1) para el uso efectivo de variables de vinculación, y 2) para predicados que son sargables.

Respuesta seleccionada

Lo que quiero abordar aquí es el enfoque dado en la respuesta de Joel Spolsky, la respuesta "seleccionada" como la respuesta correcta.

El enfoque de Joel Spolsky es inteligente. Y funciona razonablemente, exhibirá un comportamiento predecible y un rendimiento predecible, dados los valores "normales" y con los casos de bordes normativos, como NULL y la cadena vacía. Y puede ser suficiente para una aplicación en particular.

Pero en términos de generalización de este enfoque, consideremos también los casos de esquina más oscuros, como cuando el Name La columna contiene un carácter comodín (como lo reconoce el predicado LIKE). El carácter comodín que veo más comúnmente utilizado es % (un signo de porcentaje). Así que hagámoslo ahora, y luego vayamos a otros casos.

Algunos problemas con el carácter%

Considere un valor de Nombre de 'pe%ter'. (Para los ejemplos aquí, utilizo un valor de cadena literal en lugar del nombre de la columna.) Una consulta con el formato devolverá una fila con un valor Name de `'pe% ter':

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

Pero esa misma fila no se devolverá si el orden de los términos de búsqueda se invierte:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

El comportamiento que observamos es un poco extraño. Cambiar el orden de los términos de búsqueda en la lista cambia el conjunto de resultados.

Casi huelga decir que es posible que no deseemos pe%ter para que coincida con la mantequilla de maní, no importa cuánto le gusta.

Caja de esquina oscura

(Sí, estoy de acuerdo en que este es un caso oscuro. Probablemente uno que probablemente no se pruebe. No esperaríamos un comodín en el valor de una columna. Podemos suponer que la aplicación impide que se almacene ese valor. en mi experiencia, rara vez he visto una restricción de la base de datos que específicamente no permitía caracteres o patrones que se considerarían comodines en el lado derecho de un LIKE operador de comparación.

Parcheando un agujero

Un enfoque para parchear este agujero es escapar del % carácter comodín. (Para cualquiera que no esté familiarizado con la cláusula de escape en el operador, aquí hay un enlace al Documentación de SQL Server.

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

Ahora podemos hacer coincidir el% literal. Por supuesto, cuando tenemos un nombre de columna, vamos a necesitar escapar dinámicamente del comodín. Podemos usar el REPLACE función para encontrar ocurrencias de la %Carácter e inserte una barra diagonal inversa delante de cada uno, como esta:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

Entonces eso resuelve el problema con el% comodín. Casi.

Escapar del escape

Reconocemos que nuestra solución ha introducido otro problema. El personaje de escape. Vemos que también vamos a necesitar escapar de cualquier ocurrencia del personaje de escape en sí mismo. Esta vez, usamos el! como el personaje de escape:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

El guión bajo también

Ahora que estamos en una buena racha, podemos agregar otra REPLACE manejar el comodín de guión bajo. Y solo por diversión, esta vez, usaremos $ como el personaje de escape.

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

Prefiero este enfoque para escapar porque funciona en Oracle y MySQL, así como SQL Server. (Usualmente uso \ backslash como el carácter de escape, ya que ese es el personaje que usamos en las expresiones regulares. Pero, ¿por qué estar restringido por la convención?

Esos molestos paréntesis

SQL Server también permite que los caracteres comodín se traten como literales al encerrarlos entre corchetes []. Así que aún no hemos terminado de solucionar, al menos para SQL Server. Como los pares de corchetes tienen un significado especial, necesitaremos escapar también de ellos. Si logramos escapar correctamente de los corchetes, al menos no tendremos que preocuparnos por el guión - y el quilate ^ dentro de los corchetes. Y podemos dejar cualquier %y _ los caracteres dentro de los corchetes escaparon, ya que básicamente habremos deshabilitado el significado especial de los corchetes.

Encontrar pares de paréntesis que coincidan no debería ser tan difícil. Es un poco más difícil que manejar las ocurrencias de singleton% y _. (Tenga en cuenta que no es suficiente simplemente escapar de todas las apariciones de corchetes, porque un corchete de singleton se considera literal, y no necesita ser escapado. La lógica se está volviendo un poco más borrosa de lo que puedo manejar sin ejecutar más casos de prueba .)

La expresión en línea se vuelve desordenada

Esa expresión en línea en el SQL es cada vez más larga y fea. Probablemente podamos hacer que funcione, pero el cielo ayudará a la pobre alma que viene atrás y tiene que descifrarla. Como soy fanático de las expresiones en línea, me inclino a no utilizar ninguna, principalmente porque no quiero dejar un comentario explicando el motivo del desastre y disculparme por ello.

Una función donde?

De acuerdo, entonces, si no manejamos eso como una expresión en línea en el SQL, la alternativa más cercana que tenemos es una función definida por el usuario. Y sabemos que eso no acelerará las cosas (a menos que podamos definir un índice, como lo haríamos con Oracle). Si tenemos que crear una función, podemos hacerlo mejor en el código que llama al SQL. declaración.

Y esa función puede tener algunas diferencias en el comportamiento, dependiendo del DBMS y la versión. (Un agradecimiento a todos los desarrolladores de Java tan interesados ​​en poder usar cualquier motor de base de datos de manera intercambiable).

Conocimiento del dominio

Es posible que tengamos conocimiento especializado del dominio para la columna (es decir, el conjunto de valores permitidos aplicados para la columna. Podemos saber a priori que los valores almacenados en la columna nunca contendrán un signo de porcentaje, un guión bajo o pares de corchetes. En ese caso, solo incluimos un comentario rápido de que esos casos están cubiertos.

Los valores almacenados en la columna pueden permitir caracteres% o _, pero una restricción puede requerir que esos valores se escapen, quizás usando un carácter definido, de modo que los valores sean como la comparación LIKE "segura". De nuevo, un comentario rápido sobre el conjunto de valores permitido, y en particular qué personaje se usa como un personaje de escape, y vaya con el enfoque de Joel Spolsky.

Pero, a falta del conocimiento especializado y una garantía, es importante para nosotros, al menos, considerar el manejo de esos casos oscuros de esquina, y considerar si el comportamiento es razonable y "según la especificación".


Otros problemas recapitulados

Creo que otros ya han señalado suficientemente algunas de las otras áreas de preocupación comúnmente consideradas:

  • inyección SQL (tomando lo que parecería ser información suministrada por el usuario, e incluir eso en el texto SQL en lugar de suministrarlos a través de variables de enlace. No es necesario usar variables de vinculación, es solo un enfoque conveniente para frustrar con la inyección de SQL. Hay otras formas de tratar con él:

  • plan de optimizador utilizando exploración de índice en lugar de búsqueda de índice, posible necesidad de una expresión o función para el escape de comodines (posible índice de expresión o función)

  • usar valores literales en lugar de variables de vinculación afecta la escalabilidad


Conclusión

Me gusta el enfoque de Joel Spolsky. Es inteligente. Y funciona.

Pero tan pronto como lo vi, inmediatamente vi un problema potencial con él, y no es mi naturaleza dejarlo pasar. No me refiero a criticar los esfuerzos de otros. Sé que muchos desarrolladores toman su trabajo muy personalmente, porque invierten tanto en él y les importa tanto. Entonces, por favor entienda, esto no es un ataque personal. Lo que estoy identificando aquí es el tipo de problema que surge en la producción en lugar de probar.

Sí, me he alejado mucho de la pregunta original. Pero, ¿en qué otro lugar para dejar esta nota con respecto a lo que considero un problema importante con la respuesta "seleccionada" para una pregunta?


176
2018-05-29 23:18



Puede pasar el parámetro como una cadena

Entonces tienes la cuerda

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

Entonces todo lo que tienes que hacer es pasar la cadena como 1 parámetro.

Aquí está la función de división que uso.

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

123
2017-12-03 16:27



Escuché a Jeff / Joel hablar sobre esto en el podcast de hoy (episodio 34, 2008-12-16 (MP3, 31 MB), 1 h 03 min 38 segs - 1 h 06 min 45 seg), y pensé que recordaba que Stack Overflow estaba usando LINQ a SQL, pero tal vez fue abandonado. Esto es lo mismo en LINQ to SQL.

var inValues = new [] { "ruby","rails","scruffy","rubyonrails" };

var results = from tag in Tags
              where inValues.Contains(tag.Name)
              select tag;

Eso es. Y, sí, LINQ ya mira hacia atrás lo suficiente, pero el Contains cláusula parece extra hacia atrás para mí. Cuando tuve que hacer una consulta similar para un proyecto en el trabajo, naturalmente traté de hacer esto de la manera incorrecta haciendo una combinación entre la matriz local y la tabla de SQL Server, pensando que el traductor LINQ to SQL sería lo suficientemente inteligente como para manejar el traducción de alguna manera. No fue así, pero proporcionó un mensaje de error que era descriptivo y me indicó usar Contiene.

De todos modos, si ejecuta esto en el altamente recomendado LINQPady ejecute esta consulta, puede ver el SQL real que generó el proveedor SQL LINQ. Le mostrará cada uno de los valores que se parametrizan en un IN cláusula.


63
2017-12-19 05:40



Si llama desde .NET, podría usar Dapper dot net:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

Aquí Dapper piensa, por lo que no es necesario. Algo similar es posible con LINQ a SQL, por supuesto:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

43
2018-06-15 11:04



Esta es posiblemente una manera medio desagradable de hacerlo, lo usé una vez, fue bastante efectivo.

Dependiendo de tus objetivos, podría ser útil.

  1. Crear un mesa temp con una columna
  2. INSERT cada valor de búsqueda en esa columna.
  3. En lugar de usar un IN, puedes usar tu estándar JOIN reglas. (Flexibilidad ++)

Esto tiene un poco de flexibilidad añadida en lo que puede hacer, pero es más adecuado para situaciones donde tiene que consultar una tabla grande, con una buena indexación, y desea usar la lista parametrizada más de una vez. Ahorra tener que ejecutarlo dos veces y tener todo el saneamiento hecho manualmente.

Nunca llegué a perfilar exactamente cómo rápido era, pero en mi situación era necesaria.


25
2017-12-03 17:04



Tenemos una función que crea una variable de tabla a la que puedes unirte:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

Asi que:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

21
2017-12-03 17:11



Esto es asqueroso, pero si tiene la garantía de tener al menos uno, podría:

SELECT ...
       ...
 WHERE tag IN( @tag1, ISNULL( @tag2, @tag1 ), ISNULL( @tag3, @tag1 ), etc. )

Tener IN ('tag1', 'tag2', 'tag1', 'tag1', 'tag1') será fácilmente optimizado por SQL Server. Además, obtienes búsquedas directas del índice


16
2017-12-03 16:31



En mi opinión, la mejor fuente para resolver este problema es lo que se ha publicado en este sitio:

Syscomments. Dinakar Nethi

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

Utilizar:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

CRÉDITOS PARA: Dinakar Nethi


15
2017-07-22 14:47