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