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.

 

 

 

Mi presentación en Geeks

Es para mi un enorme placer poder dirigirme a toda la comunidad Geeks, en donde todos compartimos un especial afecto por este mundo especial de la  informática, tan duro, pero gratificante a la vez.  Quiero agradecer de forma muy especial a Rodrigo Corral quien me brindo la oportunidad de tener mi blog, herramienta con la que podré expresarme de forma abierta, apasionada y critica sobre la tecnología, la programación y diversas temáticas de mi interés.Llevo sobre 7 años metido en este rollo de la programación, vivo en Nicaragua en donde los cambios en la tecnología tienen un ritmo «especial», pero esto lejos de detenerme me impulsa a mantenerme actualizado y en constante aprendizaje e investigación.  Trabajo como analista de sistemas para una institución bancaria, y me toca convivir con la agitación propia de estos lugares, aunque disfruto mucho lo que hago.

Entre los temas que mas escribiré esta sobre todo SQL Server 2005, y en especial T-SQL. Procurare una alta participación, no solo escribiendo, sino también haciendo comentarios sobre los artículos de mis colegas, y apoyando la comunidad.

 

Un Saludo a todos y hasta pronto.

Geovanny “Gio” Hernandez 

email: geohernandez@gmail.com