Filtros dinámicos en SQL Server

 

En este artículo mostrare el uso de filtros dinámicos SQL para su implementación en la construcción de procedimientos almacenados, que pueden ser fuentes de información para reportes generados  a partir de las entradas o condiciones establecidas por el usuario.  El potencial en el uso de filtros dinámicos es muy amplio, en este pequeño artículo introductorio intentare dar una visión general del tema, haciendo hincapié en los elementos centrales que conllevan al desarrollo ordenado y claro de aplicaciones dinámicas. 

Una introducción sobre SQL dinámico

El uso de SQL Dinámico es una técnica basada en la construcción de secciones de código a partir de información suministrada por el usuario, lo cual puede resultar muy efectivo bajo ciertos escenarios de trabajo tanto a nivel de aplicaciones de escritorio y web; pero cuyo uso debe de manejarse con responsabilidad y tomando en cuenta el nivel de riesgo que nuestras aplicaciones pueden correr al estar expuestos a un tipo de ataque conocido como Inyección SQL (basado en la <<implantación>> de código dañino).

 

Supongamos que desarrollamos una pequeña consulta que devolverá el conjunto total de registros de una tabla específica, siendo el nombre de la tabla un valor suministrado por el usuario, utilizaremos la aplicación de ejemplo Northwind, mas adelante en este articulo se provee la dirección web para su descarga (en caso de tener SQL Server 2005).  La aplicación será la siguiente:

 

  Declare @tabla nvarchar(30)

  Declare @consulta nvarchar(500)

  Set @tabla=’Products’

  Set @consulta = ‘Select * From [dbo].[‘ + @tabla + ‘]’

  Print @consulta

  Exec(@consulta)

 

En este segmento de código podemos observar que a la variable @consulta se le asigna una sentencia preconstruida con el “cuerpo” o sintaxis central, que consiste en la selección de todas las columnas de una tabla aun no especificada, es en este punto donde entra en juego la utilización  de SQL dinámico ya que la tabla sobre la que se hará la consulta es suministrada directamente por el usuario, esta se almacena en la variable @tabla y luego es concatenada para formar la consulta. 

 

Un vez que tenemos la consulta construida requerimos que esta pueda ser ejecutada, SQL Server nos brinda dos formas de hacerlo :

 

·         Exec(@variableString) o Exec(Cadena T-SQL)

·         sp_executesql [@stmt =] stmt[     {, [@params =] N’@parameter_name  data_type [,…n]’ }     {, [@param1 =] ‘value1’ [,…n] }]

 

En nuestro ejemplo anterior por asuntos ilustrativos utilizamos la función Exec, pero en términos prácticos esta presenta dos inconvenientes a tener en consideración, el primero es a nivel de seguridad puesto que su estructura hace que  las entradas realizadas por el usuario sean potencialmente más riesgosas a ataques de inyección SQL, y la segunda está relacionada con el rendimiento, ya que si deseáramos incluir dentro de un procedimiento almacenado código dinámico este obligaría a que se realizara una compilación cada vez que se ejecuta, perdiendo de esta manera una de las principales ventajas de los procedimientos almacenados como es la reutilización de los planes de ejecución.

 

Esta fuera del alcance de este articulo abordar en detalle el tema de inyección SQL, pero es muy importante que antes de utilizar técnicas basadas en el uso dinámico de SQL se investiguen los riesgos que este implica y las medidas de precaución que podemos utilizar, al respecto existe abundante información y me permito recomendar a forma de introducción dos artículos cuyos enlaces son los siguientes:

 

http://msdn2.microsoft.com/en-us/library/ms161953(SQL.90).aspx.

http://www.sommarskog.se/index.html

Que es un filtro dinámico? 

Un filtro dinámico es  una variante en el uso de  SQL Dinámico que les permite a los usuarios seleccionar los filtros o condiciones que necesitan, siendo de utilidad en aplicaciones web y de escritorio. 

Los filtros dinámicos son recomendados en situaciones donde es requerido que el propio usuario ingrese las condiciones o filtros para la información que necesita obtener y el numero de estos sea muy  grande , es preciso mencionar que la forma optima de trabajar en la construcción de código dinámico  es a través del uso de procedimientos almacenados que recolectan los filtros de  los usuarios a través de parámetros de entrada, permitiendo una mejor protección y reduciendo los riesgos de seguridad.

En el desarrollo por ejemplo de reportes es posible que decidamos utilizar código estático, construyendo procedimientos almacenados para evaluar las condiciones especificadas por el usuario, pero en este caso debemos tomar en cuenta que el número de procedimientos almacenados que se necesitan crear para N parámetros es equivalente a 2 n, en casos como este  nos puede resultar viable el uso de filtros dinámicos.

Y entonces, construyamos un filtro dinámico

Y ahora que hemos explorado un poco “del que, porque y para que puede” ser útil la implementación de esta técnica, podemos entrar en materia de programación.  La Base de Datos que utilizaremos en el ejemplo es Northwind, en caso de que tenga SQL Server 2005 puede descargarla de la siguiente dirección:

http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Supongamos que estamos bajo un escenario de trabajo en el que se nos asigno la tarea de desarrollar  un procedimiento almacenado que nos devuelva información relacionada con los clientes de la compañía, permitiendo que desde el sistema aplicativo, el cliente pueda seleccionar diferentes parámetros de filtro o simplemente no seleccionar ninguna condición.

Iniciamos con la construcción del procedimiento almacenado cuya información proviene de la tabla Customers (Clientes), al procedimiento lo llamaremos Pa_CustomerReport y los parámetros estarán constituidos por las columnas CustomerID,CompanyName,ContactName y City , a continuación presentamos el código:

 

——————***********INICIO*************———————-

USE Northwind;

GO

IF OBJECT_ID(‘dbo.Pa_CustomerReport’) IS NOT NULL

  DROP PROC dbo.Pa_CustomerReport;

GO

CREATE PROCEDURE Pa_CustomerReport

 @CustomerID nchar(5)=NULL,

 @CompanyName nvarchar(40)=NULL,

 @ContactName nvarchar(30)=NULL,

 @City nvarchar(15)=NULL

As

DECLARE @Seleccion varchar(2000)

DECLARE @Origen varchar(2000)

DECLARE @Condiciones varchar(2000)

DECLARE @Consulta varchar(6000)

Set @Seleccion = ‘SELECT CustomerID, CompanyName, ContactName,Address, City ‘ + Char(10)

 

Set @Origen = ‘FROM Customers ‘ + Char(10)

 

Set @Condiciones=’WHERE 1=1 ‘ + Char(10)

 

IF @CustomerID IS NOT NULL

SET @Condiciones = @Condiciones + ‘ AND CustomerID = ‘ + Quotename(@CustomerID,»») + »

 

IF @CompanyName IS NOT NULL

SET @Condiciones = @Condiciones +’ AND CompanyName = ‘ + Quotename(@CompanyName,»») + »

 

IF @ContactName IS NOT NULL

SET @Condiciones = @Condiciones +’ AND ContactName = ‘ + Quotename(@ContactName,»») + »

 

IF @City IS NOT NULL

SET @Condiciones = @Condiciones +’ AND City = ‘ + Quotename(@City,»») + »

 

SET @Consulta = @Seleccion + @Origen + @Condiciones

 

EXEC sp_executesql  @Consulta,

N’@P_CustomerID AS nchar(5),@P_CompanyName nvarchar(40),@P_ContactName nvarchar(30),@P_City nvarchar(30)’,@CustomerID,@CompanyName,@ContactName,@City;

 

GO

——————*************FIN *************———————-

 

Análisis del código :

IF OBJECT_ID(‘dbo.Pa_CustomerReport’) IS NOT NULL

  DROP PROC dbo.Pa_CustomerReport;

GO

Esta instrucción nos permite conocer si el Procedimiento Pa_CustomerReport existe y en caso de que la condición sea Verdadero lo elimina.  Este método es el más sencillo y lo aprendí del autor Itzik Ben-Gan, tiene como ventajas a favor su sencillez, exactitud y el hecho de no representar carga alguna  para el plan de ejecución.

DECLARE @Seleccion varchar(2000)

DECLARE @Origen varchar(2000)

DECLARE @Condiciones varchar(2000)

DECLARE @Consulta varchar(6000)

 

Utilizamos las variables anteriores para almacenar la información a partir de la cual se construirá la consulta, facilitándonos el mantenimiento futuro al tener un código limpio y fácil de leer al desglosar de manera ordenada los elementos de la consulta como son:

 

@Selección –  Almacena la instrucción Select

@Origen-  Almacena la instrucción From

 @Condiciones-  Almacena la instrucción Where

 

Set @Condiciones=’WHERE 1=1 ‘ + Char(10)

La condición Where 1=1 se establece con el propósito de prevenir la necesidad  de tener que determinar dinámicamente si especificar una clausula Where  , en especial para cuando no hayan entradas o condiciones por parte del usuario.   Esta expresión siempre será Verdadera,  por lo que no genera efecto alguno en el rendimiento.

IF @CustomerID IS NOT NULL

SET @Condiciones = @Condiciones + ‘ AND CustomerID = ‘ + Quotename(@CustomerID,»») + »

Se inicia un proceso de evaluación de cada uno de los parámetros de entrada del procedimiento, si el parámetro tiene un valor Not Null este pasara a formar parte de las condiciones.  Utilizamos la instrucción Quotename con el propósito de mejorar la lectura y facilidad en la escritura del código, además de permitir que en los parámetros del procedimiento almacenado se pueda incluir el uso de comillas (‘) sin que se genere error; por ejemplo si buscáramos a un cliente de nombre Let’s Stop N Shop . Otra ventaja es que representa una mejora sustancial en la seguridad contra posibles ataques  por inyección SQL, esta protección .  La limitación que presenta el uso de Quotename en los parámetros es que  soporta un valor máximo de 128 caracteres.

 

SET @Consulta = @Seleccion + @Origen + @Condiciones

 

EXEC sp_executesql  @Consulta,

N’@P_CustomerID AS nchar(5),@P_CompanyName nvarchar(40),@P_ContactName nvarchar(30),@P_City nvarchar(30)’,@CustomerID,@CompanyName,@ContactName,@City;

 

Una vez que evaluamos todos los parámetros de entrada procedemos a concatenar las variables que conforman la estructura de la consulta y procedemos a ejecutarla.  En caso de que no se haya suministrado parámetro alguno el resultado corresponderá con todos los registros de la tabla Customers (clientes).

Conclusiones

Con este artículo he intentado dar una perspectiva inicial al tema de la construcción de código dinámico SQL, en específico al uso de filtros dinámicos.  El desarrollo de aplicaciones que permitan el ingreso de criterios o filtros por parte del usuario son comunes, pero su implementación debe ser manejada de forma cuidadosa y responsable, sabiendo distinguir los escenarios en donde es optimo su uso.  Como recomendaciones generales podemos mencionar:

·         Evaluar la conveniencia del uso de SQL Dinámico, y compararlo con la programación SQL estática a fin de establecer una decisión basada criterios técnicos, y no simplemente en un capricho o una mala práctica de programación.

 

·         La construcción de filtros dinámicos (y SQL Dinámico en general) se haga a través del uso de procedimientos almacenados y parámetros de entrada dada sus ventajas en aspectos de seguridad, rendimiento, y facilidad de mantenimiento.

 

·         Mantener cierta disciplina en la escritura de código, teniendo especial cuidado con el manejo de los espacios, ya que es probable que el procedimiento compile bien ; pero que genere error durante su ejecución. 

 

·         Nunca otorgar más privilegios de los necesarios, asignando convenientemente permisos de ejecución sobre los procedimientos almacenados (objetos sugeridos para el desarrollo de SQL Dinámico) y en algunos casos permisos Select .

 

·         En el caso de que desarrollemos Filtros dinámicos sobre aplicaciones web evitemos que al usuario se le presenten mensajes de error.

 

 

 

13 comentarios sobre “Filtros dinámicos en SQL Server”

  1. La idea en sí es muy buena, pero ya que los filtros parten del usuario, es más flexible montar la sql en el cliente y pasarsela a sql server. La opción que presentas de montar dinámicamente la sql en un procedimiento almacenado es muy limitada. Por ejemplo ¿como montas una clausula in (x,y,z)?

    Yo tengo una clase específica para ir montando la sql en función de los controles del formulario y de los campos de la tabla o vista de origen. Controla tamaños, tipos de datos e inyección de sql; monta una sql con parámetros y la pasa al servidor.

    Creo que es más flexible y reutilizable.
    En cualquier caso, la idea que transmites de generar la sql dinámicamente, pero con criterio es lo importante.

  2. Hola Miguel, es valida tu observación. De hecho en un texto de «Tecnicas Aplicadas de SQL Server 2005» utilizan un esquema como el que tu señalas, en mi caso el articulo estaba planteando las posibilidades en el uso del SQL dinamico y algunas buenas practicas que se deben tener (obviamente con una utilidad practica como la de los filtros).

    Saludos.

  3. Hola
    Geovanny

    Muy buen articulo, pero como puedo hacer, con los filtros de selección múltiples y que además pueda quedar en NULL

    Espero que me puedas ayudar….. 😀

  4. Bueno ya lo resolví, utilizando un WHILE

    Pero ahora quiero hacer que este procedimiento pueda ejecutar un funcion… se puede hacer eso?? ya que me muestra un error…..

  5. Disculpa que no haya contestado en su momento lo que me habias consultado,seria un poco más ilustrativo si envias el mensaje de error y asi tener una idea clara del problema.

    No deberia de darte un problema, ya que es permitido la utilizacion de una función dentro de un SP, por lo que creo que el error no radica en este punto.

    Bueno, espero que envies el mensaje y asi intentar ayudarte.

    Saludos.

  6. ya lo solucione, el problema era las comillas
    ‘ – » 😀
    y en la consulta tuve que sacar los case when porque la consulta no se ejecutaba y mostraba error…

    gracias de todas maneras, espero que sigas con artículos tan bueno como este…

  7. Hola, que tal. ¿Habria alguna forma de no usar el comando exec de sql y hacer una lista de condiciones dinámica para el where de la consulta? Es decir, si yo le paso al procedimiento algunos parametros vacios y otros con valores que solamente filtre por los campos que le envio

  8. Saludos Miguel, acerca de tu pregunta creo que lo mas conveniente es poner en perspectiva el uso de Dynamic SQL, esta funcionalidad fue desarrollada para casos en los que construyes de forma dinamica las condiciones de tu consulta.

    Si le pasos algunos parametros vacios a tu consulta, esta tomara de forma literal la busqueda con esos parametros, y obviamente los resultados arrojadas no seran los optimos.

    Por ej. Select * from Products where IdCategory =@Id_Category and Price=@Price , si @Id_Category tiene un determinado valor, pero el parametro @Price es Null, esto lo considerara a la hora de hacer la busqueda, es decir que te buscara los registros con una determinada categoria y cuyo precio es Nulo.

    Bueno espero haberte ayudado un poco clarificando tu consulta.

    Saludos a todos.

  9. Hola!

    Me ayudó mucho este ejemplo, solo que me encontré con un error super tonto y lo aclaro acá.

    La declaración de la variable «Consulta» debe ser nvarchar, no varchar.

    Esto no sirve:
    DECLARE @Consulta varchar(6000)

    debe ser:
    DECLARE @Consulta nvarchar(4000)

  10. Gracias Geovanny por tu aporte, vale la pena destacar la prolijidad, el profesionalismo al redactar el informe, el lenguaje utilizado correctamente. Muchas Gracias

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *