query - ¿Procedimiento almacenado EXEC vs sp_executesql diferencia?

sp_executesql output

Question

He escrito dos procedimientos almacenados, uno con sp_executesql y el otro no tiene sp_executesql, ambos están ejecutando correctamente los mismos resultados, no entendí cuál es la diferencia entre

EXEC (@SQL) vs EXEC sp_executesql @SQL, N ''@ eStatus varchar (12)'', @eStatus = @Status

y ¿Cómo EXEC (@SQL) es propenso a la inyección de SQL y sp_executesql @SQL ...... no lo es?

Procedimiento almacenado a continuación sin sp_executesql

ALTER proc USP_GetEmpByStatus
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = ''Employee''
Declare @Columns as sysname = ''*''
DECLARE @SQL as nvarchar(128) = ''select '' + @Columns + '' from '' + @TableName + '' where Status='' + char(39) + @Status + char(39)
print (@SQL)
EXEC (@SQL)
END

EXEC USP_GetEmpByStatus ''Active''

Abajo el procedimiento almacenado con sp_executesql

create proc USP_GetEmpByStatusWithSpExcute
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = ''JProCo.dbo.Employee''
Declare @Columns as sysname = ''*''
DECLARE @SQL as nvarchar(128) = ''select '' + @Columns + '' from '' + @TableName + '' where Status='' + char(39) + @Status + char(39)
print @SQL
exec sp_executesql @SQL, N''@eStatus varchar(12)'', @eStatus = @Status
END

EXEC USP_GetEmpByStatusWithSpExcute ''Active''

Answer 1

Además del uso, hay algunas diferencias importantes:

1. sp_executesql permite parametrizar sentencias, por lo tanto, es más seguro que EXEC en términos de inyección SQL

2. sp_executesql puede aprovechar los planes de consulta en caché. La cadena TSQL se crea solo una vez, después de eso, cada vez que se llama a la misma consulta con sp_executesql , SQL Server recupera el plan de consulta del caché y lo reutiliza.

3. Las tablas temporales creadas en EXEC no pueden usar el mecanismo de almacenamiento en caché de tablas temporales

Answer 2

Con sp_executesql , no tienes que construir tu consulta de esa manera. Podrías declararlo así:

DECLARE @SQL as nvarchar(128) = ''select '' + @Columns + '' from '' + 
@TableName + '' where Status=@eStatus''

De esta manera, si su valor de @Status proviene de un usuario, puede usar @eStatus y no tiene que preocuparse por escapar '' . sp_executesql le brinda la posibilidad de colocar variables en su consulta en forma de cadena, en lugar de usar concatenación. Así que tienes menos de qué preocuparte.

Las variables de columna y tabla siguen siendo las mismas, pero es menos probable que sean directamente de un usuario.

Answer 3

Con Exec , no puede tener un marcador de posición en la cadena de su declaración T-Sql.

sp_executesql le da la ventaja de tener un marcador de posición y pasar el valor real en tiempo de ejecución

Answer 4

Su sp_executesql SQL probablemente debería ser;

DECLARE @SQL as nvarchar(128) = ''select '' + @Columns + '' from '' + 
            @TableName + '' where Status=@eStatus''

Esto le permitirá llamar a sp_executesql con @eStatus como un parámetro en lugar de incrustarlo en el SQL. Eso dará la ventaja de que @eStatus puede contener cualquier carácter y la base de datos la eliminará de forma correcta si es necesario para que esté segura.

Contraste eso con el SQL requerido para EXEC ;

DECLARE @SQL as nvarchar(128) = ''select '' + @Columns + '' from '' + 
            @TableName + '' where Status='' + char(39) + @Status + char(39)

... donde un char (39) incrustado en @Status hará que su SQL no sea válido y posiblemente cree una posibilidad de inyección de SQL. Por ejemplo, si @Status se establece en O''Reilly , su SQL resultante sería:

select acol,bcol,ccol FROM myTable WHERE Status=''O''Reilly''