How to Safely Parameterize Dynamic SQL Queries in T-SQL?

Parametrizing Dynamic SQL Queries in T-SQL

In dynamic SQL queries, parameters are crucial for preventing SQL injection attacks and ensuring type safety. When working with parameters in T-SQL dynamic SQL, it's important to pass them correctly to the sp_executesql stored procedure.

Problem:

Consider the following dynamic query that expects a UNIQUEIDENTIFIER parameter for the WHERE clause:

CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */
(
@p_CreatedBy UNIQUEIDENTIFIER
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql ='

DECLARE @p_CreatedBY UNIQUEIDENTIFIER

SELECT 
  DateTime,
  Subject,
  CreatedBy
FROM
(
  SELECT 
    DateTime, Subject, CreatedBy, 
    ROW_NUMBER() OVER(ORDER BY DateTime ) AS Indexing
  FROM
    ComposeMail
  WHERE 
    CreatedBy = @p_CreatedBy /* <--- the problem is in this condition */
) AS NewDataTable
'

EXEC sp_executesql @sql

When this query is executed without passing the parameter, no result is returned.

Solution:

To resolve this issue, parameters must be passed to sp_executesql. Modify the WHERE clause as follows:

WHERE 
    CreatedBy = @p

And pass the parameter using the @p parameter name:

EXECUTE sp_executesql @sql, N'@p UNIQUEIDENTIFIER', @p = @p_CreatedBY

This ensures that the WHERE clause condition uses the parameterized value from the caller, preventing SQL injection and ensuring type safety.

The above is the detailed content of How to Safely Parameterize Dynamic SQL Queries in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!