El poder de sp_executesql

Recuerdo hace mucho tiempo haber escuchado que la ejecución de código Transact SQL dinámico era peligrosa pues podía prestarse a ser parte de las llamadas SQL Injections, bien en parte es cierta esa afirmación sin embargo, recordemos cómo hacíamos esas ejecuciones dinámicas en el pasado.

NOTA: si desea bajar el script completo, hacer clic en la imagen:

–Creamos una base de datos de prueba
USE MASTER
GO

IF DB_ID(‘db01’) IS NOT NULL
DROP DATABASE db01
GO

CREATE DATABASE db01
GO

–Creamos una tabla de prueba
USE db01
GO

CREATE TABLE dbo.Tabla01 (
Id int,
Nombre varchar(50)
Apellido varchar(50))
GO

–Insertamos registros de prueba
INSERT INTO dbo.Tabla01 (Id, Nombre, Apellido) 
VALUES (1, ‘Alberto’, ‘De Rossi’),
       (2, ‘Alan’, ‘Ferrandiz’),
       (3, ‘Nicolas’, ‘Nakasone’)
GO

–Creamos un procedimiento almacenando utilizando EXEC
CREATE PROC dbo.usp_Selección (@Id varchar(50))
AS
EXEC (‘SELECT * FROM dbo.Tabla01 WHERE Id = ‘ + @Id)
GO

–LLAMADA AL PROCEDIMIENTO
EXEC dbo.usp_Seleccion1 ‘1’
EXEC dbo.usp_Seleccion1 ‘2’
EXEC dbo.usp_Seleccion1 ‘3’
GO

–SQL INJECTION
EXEC usp_Seleccion1 ‘1; drop table tabla01’
GO

Como apreciamos, esta ejecución dinámica se realizaba con el comando EXEC y solamente podía recibir variables de tipo cadena, dado que posteriormente se concatenaban en una cadena principal a ejecutarse finalmente. Bien, el procedimiento almacenado sp_executesql fue introducido en SQL Server mucho después que el comando EXEC, principalmente para brindar mejor soporte en la reutilización de planes de ejecución.

El procedimiento almacenado sp_executesql es mucho más flexible que el comando EXEC([cadena_de_texto]) pues el primero posee una interfaz que soporta tanto parámetros de entrada como de salida, lo que permite que se pueda utilizar cadenas de texto con argumentos y que a su vez puedan reutilizar los planes de ejecución de manera más eficiente que EXEC. Los componentes del procedimiento almacenado sp_executesql incluyen: un lote de código, una sección de declaración de parámetros y una sección de asignación de parámetros,

EXEC sp_executesql
@codigo = <codigo>, — cadena UNICODE, similar al cuerpo de un procedimiento almacenado
@parametros = <parametros>, —- cadena UNICODE, similar a la parte de declaración de parámetros de un procedimiento almacenado
<asignacion de parametros> —- como una llamada de procedimiento almacenado

Veamos ahora, qué pasaría si usáramos el procedimiento almacenado sp_executesql para indicar el tipo adecuado de dato para el parámetro @id:

–Creamos otro procedimiento almacenado usando sp_executesql
CREATE PROC dbo.usp_Seleccion2 (@Id int)
AS
DECLARE @Codigo nvarchar(4000), @Parametros nvarchar(4000)
SET @Codigo =
N’SELECT * FROM dbo.Tabla01 Where Id = @Id’

SET @Parametros = N’@Id int’
SET @Id = 1
EXEC sp_executesql @Codigo, @Parametros,
@Id
GO

–LLAMADA AL PROCEDIMIENTO
EXEC dbo.usp_Seleccion2 1

Como se ve en ejemplo anterior, ahora es posible indicar el tipo de dato que corresponde realmente al parámetro @id, es decir el tipo de dato int. Sin embargo, veamos también cómo podríamos manejar un tipo de dato de cadena que incluso con el procedimiento almacenado sp_executesql es propenso a sufrir SQL Injection, si es que usamos concatenación en los parámetros.

–Creamos un tercer procedimiento almacenado para evitar sql injection 
CREATE PROC dbo.usp_Seleccion3 (@Id int, @Nombre nvarchar(50))
AS
DECLARE @Codigo nvarchar(4000), @Parametros nvarchar(4000)
SET @Codigo =
N’SELECT * FROM dbo.Tabla01 WHERE Id = @Id AND Nombre = @Nombre’
SET @Parametros =
N’@Id int, @Nombre nvarchar(50)’
EXEC sp_executesql @Codigo, @Parametros, @Id, @Nombre

GO

–LLAMADA AL PROCEDIMIENTO
EXEC dbo.usp_Seleccion3 1, ‘Alberto’

–LLAMADA AL PROCEDIMIENTO CON INYECCIÓN SQL
EXEC dbo.usp_Seleccion3 1, ‘Alberto; drop table tabla01’

En este ultimo caso, no aparece ningún resultado pero la tabla no se elimina:

En conclusión, la ejecución de sentencias dinámicas con el procedimiento almacenado sp_executesql es la evolución natural del comando EXEC, para, de una manera más efectiva y segura, realizar la ejecución dinámica de código.

5 comentarios en “El poder de sp_executesql

  • Hola Alan, y dime? cómo puedo enviar el nombre de una tabla temporal (@) como parámetro? si esta solicita indicar tipo de dato de la variable??

     

  • Mi idea es la siguiente:
    intento crear una tabla temporal para reutilizar el código para otras sesiones, el nombre de la tabla será dinámica

    mi código es el siguiente:

    declare @nomTablax varchar(max)

    declare @parametros varchar(max)

    set @nomTablax = ‘#tabla’+convert(nvarchar,round(9999*RAND(),0))

    set @parametros = N’@nomTablax varchar(max)’

    set @sqlx = N’create table @nomTablax (info_tipo varchar(100))’

    exec sp_executesql @sqlx, @parametros, @nomTablaX

     

    pero me arroja el siguiente error

    Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

    Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

     

    esa es mi pregunta. como manejamos el nombre de la tabla??

  • de una u otra manera me arroja el mismo error:

    set @nomTablax = ‘#tabla’+convert(nvarchar,round(9999*RAND(),0))

    set @parametros = N’@nomTablax varchar(max)’

    set @sqlx = N’create table ‘+@nomTablax+‘ (info_tipo varchar(100))’

    exec sp_executesql @sqlx, @parametros, @nomTablaX
    muchas gracias por tu ayuda.

     

Deja un comentario

Tu dirección de correo electrónico no será publicada.