Safe dynamic SQL and injection prevention

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Pass values as parameters to dynamic SQL rather than concatenating them.

Run the example

DECLARE @Minimum decimal(10,2) = 100.00;
DECLARE @Sql nvarchar(max) = N'SELECT ProductID, ListPrice
    FROM dbo.Products WHERE ListPrice > @Limit ORDER BY ProductID';
EXEC sys.sp_executesql @Sql, N'@Limit decimal(10,2)', @Limit = @Minimum;

Read it step by step

1. @Sql contains the fixed query shape and an @Limit placeholder.
2. sp_executesql binds a typed decimal value separately from SQL syntax.
3. The result includes prices above 100 and is ordered by ProductID.

Check the result

No user-provided text is concatenated into the query.

Practice

Run the same parameterized query with a minimum price of 150.

DECLARE @Minimum decimal(10,2) = 150.00;
DECLARE @Sql nvarchar(max) = N'SELECT ProductID, ListPrice
    FROM dbo.Products WHERE ListPrice > @Limit ORDER BY ProductID';
EXEC sys.sp_executesql @Sql, N'@Limit decimal(10,2)', @Limit = @Minimum;