Day 28: Dynamic SQL & metaprogramming

A. Lesson overview

Activity breakdown

Business scenario. Users want a search screen with any combination of filters and a choice of sort column; administrators want reports that run against “every table in a schema”. Both need SQL text built at run time — and the security team needs proof that no input can change what that SQL does.

Safety. Today includes deliberate SQL injection against a training procedure. Run it only in RetailLab. One task drops a table created for that purpose (Lab.InjectionCanary) — it’s labelled where it happens.

B. Theory explained clearly

Part 1 — When is dynamic SQL justified?

Dynamic SQL builds a statement as a string and executes it. Use it when the shape of the query must change at run time:

Dynamic SQL is harder to read, test and secure, and the compiler can’t check it until it runs. Prefer static SQL whenever it does the job.

Part 2 — EXEC() vs sp_executesql

-- EXEC(): executes a string. Values must be concatenated in → injection risk, poor plan reuse
DECLARE @sql nvarchar(max) = N'SELECT COUNT(*) FROM Sales.SalesOrder WHERE OrderStatus = ''Pending''';
EXEC (@sql);

-- sp_executesql: the statement has typed parameters, supplied separately
DECLARE @n int;
EXEC sys.sp_executesql
     N'SELECT @Count = COUNT(*) FROM Sales.SalesOrder WHERE OrderStatus = @Status;',   -- statement
     N'@Status varchar(10), @Count int OUTPUT',                                        -- parameter definitions
     @Status = 'Pending', @Count = @n OUTPUT;                                          -- values
SELECT @n AS PendingOrders;                                                            -- 4

Rule: values always go in as parameters. Only identifiers and keywords — which can’t be parameters — are ever concatenated, and only after validation.

Part 3 — SQL injection

If user input is concatenated into SQL, the input can become SQL:

SET @sql = N'SELECT … FROM Sales.Customer WHERE LastName = ''' + @LastName + N'''';
-- @LastName = N'Carter'           → … WHERE LastName = 'Carter'                  (as intended)
-- @LastName = N''' OR 1=1 --'     → … WHERE LastName = '' OR 1=1 --'             (every row)
-- @LastName = N'''; DROP TABLE …' → a second statement runs with the caller's rights

Escaping quotes by hand (REPLACE(@x, '''', '''''')) is fragile — easy to forget, wrong for identifiers and numbers, and defeated by truncation tricks. Defences, in order:

1. Parameters for every value (sp_executesql).
2. Whitelists for identifiers and keywords (sort columns, ASC/DESC): accept only known values.
3. QUOTENAME around every identifier, after validation.
4. Least privilege: the dynamic statement runs with as few rights as possible.

Part 4 — Identifiers: validate, then quote

QUOTENAME(N'Sales') returns [Sales]; it doubles any ] inside, so the value can’t break out of the brackets. It returns NULL for input longer than 128 characters — and NULL concatenated into a string makes the whole string NULL, so check for that.

Validate identifiers against the catalog:

IF NOT EXISTS (SELECT 1 FROM sys.tables AS t
               WHERE t.name = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName)
    THROW 50600, N'Unknown table.', 1;
SET @sql = N'SELECT COUNT_BIG(*) FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';

Use the sysname type (nvarchar(128)) for identifier variables. For a sort column, a fixed list (IN ('OrderDate', 'OrderID', 'CustomerID')) is stronger than “any column of the table”.

Part 5 — Practicalities

Metaprogramming means generating code from metadata (sys.tables, sys.columns…), typically with STRING_AGG: row counts for every table, data profiles for every column, maintenance statements.

C. Dataset and starting state

Setup. No script; today’s objects are created as you go. Make sure the core data is intact.

Validation. Expected: 40, 376, 30.

USE RetailLab;
GO
SELECT  (SELECT COUNT(*) FROM Sales.Customer)     AS Customers,
        (SELECT COUNT(*) FROM Sales.SalesOrder)   AS Orders,
        (SELECT COUNT(*) FROM Inventory.Product)  AS Products;

D. Worked examples

Worked example 1 — Injection, then the fix

-- DELIBERATELY VULNERABLE — training only
CREATE OR ALTER PROCEDURE Lab.usp_FindCustomer_Unsafe
    @LastName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max) =
        N'SELECT CustomerID, FirstName, LastName FROM Sales.Customer WHERE LastName = N''' + @LastName + N''';';
    EXEC (@sql);
END;
GO

EXEC Lab.usp_FindCustomer_Unsafe @LastName = N'Carter';
EXEC Lab.usp_FindCustomer_Unsafe @LastName = N''' OR 1=1 --';
GO

-- Safe: the value is a parameter
CREATE OR ALTER PROCEDURE Lab.usp_FindCustomer
    @LastName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    EXEC sys.sp_executesql
         N'SELECT CustomerID, FirstName, LastName FROM Sales.Customer WHERE LastName = @LastName;',
         N'@LastName nvarchar(50)',
         @LastName = @LastName;
END;
GO

EXEC Lab.usp_FindCustomer @LastName = N'Carter';
EXEC Lab.usp_FindCustomer @LastName = N''' OR 1=1 --';

Expected result: unsafe: 1 row (2 Ben Carter), then 40 rows — the input rewrote the WHERE clause to LastName = N'' OR 1=1 --'. Safe: 1 row, then 0 rows — SQL Server looked for a customer whose surname is literally ' OR 1=1 --. (Of course, this search needs no dynamic SQL at all; the point is the mechanism.)

Worked example 2 — Output parameters

DECLARE @Status varchar(10), @n int;
DECLARE @Results TABLE (OrderStatus varchar(10), Orders int);

DECLARE @Statuses TABLE (s varchar(10));
INSERT INTO @Statuses VALUES ('Cancelled'), ('Delivered'), ('Pending'), ('Shipped');

DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT s FROM @Statuses ORDER BY s;
OPEN c;
FETCH NEXT FROM c INTO @Status;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql
         N'SELECT @Count = COUNT(*) FROM Sales.SalesOrder WHERE OrderStatus = @Status;',
         N'@Status varchar(10), @Count int OUTPUT',
         @Status = @Status, @Count = @n OUTPUT;
    INSERT INTO @Results VALUES (@Status, @n);
    FETCH NEXT FROM c INTO @Status;
END;
CLOSE c; DEALLOCATE c;

SELECT * FROM @Results ORDER BY OrderStatus;

Expected result: Cancelled 7, Delivered 357, Pending 4, Shipped 8. The same statement text ran four times with different values: one cached plan. (A GROUP BY would do this in one static query, of course — the loop is here to show output parameters; cursors are otherwise avoided in this course.)

Worked example 3 — A validated identifier

CREATE OR ALTER PROCEDURE Lab.usp_CountRows
    @SchemaName sysname,
    @TableName  sysname,
    @RowCount   bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);

    IF NOT EXISTS (SELECT 1 FROM sys.tables AS t
                   WHERE t.name = @TableName AND t.schema_id = SCHEMA_ID(@SchemaName))
        THROW 50600, N'Unknown schema or table.', 1;

    SET @sql = N'SELECT @n = COUNT_BIG(*) FROM ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N';';
    EXEC sys.sp_executesql @sql, N'@n bigint OUTPUT', @n = @RowCount OUTPUT;
END;
GO

DECLARE @rc bigint;
EXEC Lab.usp_CountRows N'Sales', N'SalesOrder', @rc OUTPUT;
SELECT @rc AS SalesOrderRows;
GO
DECLARE @rc bigint;
EXEC Lab.usp_CountRows N'Sales', N'SalesOrder; DROP TABLE Lab.Anything', @rc OUTPUT;
GO
SELECT QUOTENAME(N'odd]name') AS Quoted;

Expected result: 376; then Msg 50600 … Unknown schema or table.; then [odd]]name] — the ] is doubled so it can’t close the bracket early. Validation rejects anything that isn’t a real table; QUOTENAME is the second layer.

Worked example 4 — Generating SQL from metadata

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(
           CAST(N'SELECT ' + QUOTENAME(s.name + N'.' + t.name, '''') + N' AS TableName, COUNT_BIG(*) AS RowCnt FROM '
                + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS nvarchar(max)),
           NCHAR(13) + NCHAR(10) + N'UNION ALL ')
       WITHIN GROUP (ORDER BY s.name, t.name)
FROM   sys.tables  AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id
WHERE  s.name IN (N'HR', N'Inventory', N'Sales', N'Staging');

SET @sql += N' ORDER BY TableName;';
PRINT @sql;               -- inspect what will run
EXEC sys.sp_executesql @sql;

Expected result: PRINT shows 13 SELECT … UNION ALL lines; the result has 13 rows: HR.Department 6, HR.Employee 16, Inventory.Category 15, Inventory.Product 30, Inventory.Stock 49, Inventory.Warehouse 2, Sales.Customer 40, Sales.Payment 395, Sales.SalesOrder 376, Sales.SalesOrderLine 702, Sales.Store 3, Staging.CustomerImport 15, Staging.ProductPriceUpdate 10 — the same numbers the setup validation script checks. QUOTENAME(…, '''') quotes a string literal safely; CAST(… AS nvarchar(max)) stops STRING_AGG from failing at 8,000 bytes.

E. Guided practical lab — A safe dynamic search with a sortable result

Build the dynamic alternative to Day 24’s catch-all search: only the filters supplied appear in the SQL; the sort column comes from a whitelist.

Step 1 — Create the procedure

1. Do: Build the statement piece by piece.
2. Run:

   USE RetailLab;
   GO
   CREATE OR ALTER PROCEDURE Lab.usp_SearchOrdersDyn
       @CustomerID int         = NULL,
       @FromDate   date        = NULL,
       @ToDate     date        = NULL,           -- exclusive
       @Status     varchar(10) = NULL,
       @SortColumn sysname     = N'OrderDate',
       @SortDesc   bit         = 0,
       @Debug      bit         = 0
   AS
   BEGIN
       SET NOCOUNT ON;
   
       IF @SortColumn NOT IN (N'OrderID', N'OrderDate', N'CustomerID', N'OrderStatus')
           THROW 50600, N'Sort column must be OrderID, OrderDate, CustomerID or OrderStatus.', 1;
   
       DECLARE @sql nvarchar(max) =
           N'SELECT OrderID, CustomerID, OrderDate, OrderStatus' + NCHAR(10) +
           N'FROM   Sales.SalesOrder' + NCHAR(10) +
           N'WHERE  1 = 1';
   
       IF @CustomerID IS NOT NULL SET @sql += NCHAR(10) + N'  AND  CustomerID = @CustomerID';
       IF @FromDate   IS NOT NULL SET @sql += NCHAR(10) + N'  AND  OrderDate >= @FromDate';
       IF @ToDate     IS NOT NULL SET @sql += NCHAR(10) + N'  AND  OrderDate <  @ToDate';
       IF @Status     IS NOT NULL SET @sql += NCHAR(10) + N'  AND  OrderStatus = @Status';
   
       SET @sql += NCHAR(10) + N'ORDER BY ' + QUOTENAME(@SortColumn)
                 + CASE WHEN @SortDesc = 1 THEN N' DESC' ELSE N' ASC' END
                 + N', OrderID;';
   
       IF @Debug = 1
       BEGIN
           PRINT @sql;
           RETURN;
       END;
   
       EXEC sys.sp_executesql @sql,
            N'@CustomerID int, @FromDate date, @ToDate date, @Status varchar(10)',
            @CustomerID = @CustomerID, @FromDate = @FromDate, @ToDate = @ToDate, @Status = @Status;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: Every value is a parameter — even those not used in the text (passing unused parameters is allowed). The only concatenated parts are fixed fragments, a whitelisted, quoted column name, and ASC/DESC chosen by a bit.
5. Confirm: SELECT OBJECT_ID(N'Lab.usp_SearchOrdersDyn'); is not NULL.

Step 2 — Look before you run

1. Do: Use the debug switch.
2. Run:

   EXEC Lab.usp_SearchOrdersDyn @FromDate = '2025-12-01', @ToDate = '2026-01-01', @Status = 'Pending', @Debug = 1;

3. Observe: the Messages tab shows:

   SELECT OrderID, CustomerID, OrderDate, OrderStatus
   FROM   Sales.SalesOrder
   WHERE  1 = 1
     AND  OrderDate >= @FromDate
     AND  OrderDate <  @ToDate
     AND  OrderStatus = @Status
   ORDER BY [OrderDate] ASC, OrderID;

4. Why it matters: Being able to see the generated statement is essential for debugging and for review.
5. Confirm: no literal values appear in the text.

Step 3 — Run the searches

1. Do: Try several combinations.
2. Run:

   EXEC Lab.usp_SearchOrdersDyn @CustomerID = 18, @SortDesc = 1;
   EXEC Lab.usp_SearchOrdersDyn @FromDate = '2025-12-01', @ToDate = '2026-01-01';
   EXEC Lab.usp_SearchOrdersDyn @FromDate = '2025-12-01', @ToDate = '2026-01-01', @Status = 'Pending';
   EXEC Lab.usp_SearchOrdersDyn;

3. Observe: 27 rows (first: order 1351, 2025-12-05); 27 rows; 4 rows; 376 rows.
4. Why it matters: Each combination produces a different, simple statement that the optimiser can plan well.
5. Confirm: the row counts.

Step 4 — Attack it

1. Do: Try to inject through the sort column and the status.
2. Run:

   EXEC Lab.usp_SearchOrdersDyn @SortColumn = N'OrderDate; DROP TABLE Lab.Anything; --';
   GO
   EXEC Lab.usp_SearchOrdersDyn @Status = 'x'' OR 1=1 --';

3. Observe: Msg 50600 … Sort column must be …; then 0 rows (and in fact @Status is varchar(10), so the value is also truncated to x' OR 1=1, which matches nothing).
4. Why it matters: The whitelist stops identifier injection; parameters stop value injection.
5. Confirm: both attacks failed harmlessly.

Step 5 — One plan per shape

1. Do: Look at the plan cache for the generated statements.
2. Run:

   SELECT  cp.usecounts, LEFT(st.text, 120) AS StatementStart
   FROM    sys.dm_exec_cached_plans AS cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
   WHERE   st.text LIKE N'%FROM   Sales.SalesOrder%WHERE  1 = 1%'
     AND   st.text NOT LIKE N'%dm_exec_cached_plans%'
   ORDER BY StatementStart;

3. Observe: one entry per distinct statement shape you ran (four in step 3, possibly more if you experimented); usecounts rises when the same shape is reused with new values.
4. Why it matters: This is why values must be parameters: concatenated values would create a new cached plan for every search.
5. Confirm: each entry’s text contains @ parameters, not values. (Needs VIEW SERVER STATE or VIEW SERVER PERFORMANCE STATE; the owner of a local instance normally has it.)

F. Independent practice

Task 1 — A dynamic pivot

Write Lab.usp_StoreYearPivot that returns one row per store and one column per order year found in Sales.SalesOrder (store orders only), with order counts. Build the column list from the data with STRING_AGG and QUOTENAME.

Success criteria: columns StoreName, [2024], [2025]; rows: Edinburgh Old Town 10 / 29, London Covent Garden 30 / 21, Manchester Northern Quarter 30 / 38. When 2026 orders appear, a [2026] column appears without code changes.

Task 2 — Find and fix an injectable sort (contains a deliberate destructive step in the training database)

Create Lab.InjectionCanary (ID int) with one row, and this vulnerable procedure:

CREATE OR ALTER PROCEDURE Lab.usp_ListProducts_Unsafe @OrderBy nvarchar(200)
AS
BEGIN
    DECLARE @sql nvarchar(max) = N'SELECT ProductCode, ProductName, ListPrice FROM Inventory.Product ORDER BY ' + @OrderBy + N';';
    EXEC (@sql);
END;

Call it with N'ListPrice', then with N'ListPrice; DROP TABLE Lab.InjectionCanary; --'. Confirm the canary is gone. Then write Lab.usp_ListProducts @SortColumn sysname, @SortDesc bit = 0 that is not injectable, and show the attack fails.

Success criteria: the attack drops Lab.InjectionCanary (OBJECT_ID returns NULL). The safe version returns 30 rows for ProductName and raises error 50601 for the attack string.

Task 3 — Profile every text column

Generate one query that returns, for every char/varchar/nchar/nvarchar column of Sales.Customer: the column name, number of NULLs, and number of distinct non-NULL values.

Success criteria: 7 rows. NULLs: Email 4, Phone 10, all others 0. Distinct values: CustomerSegment 3; Email 36.

Task 4 — Dynamic SQL and permissions (challenging)

Create user SearchUser without login and grant only EXECUTE on Lab.usp_SearchOrdersDyn. As SearchUser, run a search. Explain the error. Then fix it with the narrowest change you can justify, and test again.

Success criteria: first attempt: Msg 229 … SELECT permission was denied on the object ‘SalesOrder’… — ownership chaining doesn’t extend into dynamic SQL. After the fix (e.g. GRANT SELECT ON Sales.SalesOrder TO SearchUser, or WITH EXECUTE AS OWNER on the procedure), the search returns 27 rows for customer 18.

Stretch challenge — Metadata-driven reconciliation

Using generated SQL, compare COUNT_BIG(*) with the row count in sys.dm_db_partition_stats for every table in Sales and Inventory, returning schema, table, both counts and whether they match.

Success criteria: 9 rows (5 Sales + 4 Inventory tables), all matching. Explain why the DMV count is instant and the COUNT_BIG(*) count isn’t, and when they might differ.

G. Solutions and explanations

CREATE OR ALTER PROCEDURE Lab.usp_StoreYearPivot
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cols nvarchar(max), @sql nvarchar(max);

    SELECT @cols = STRING_AGG(QUOTENAME(CAST(y AS nvarchar(4))), N', ') WITHIN GROUP (ORDER BY y)
    FROM  (SELECT DISTINCT YEAR(OrderDate) AS y FROM Sales.SalesOrder WHERE StoreID IS NOT NULL) AS d;

    SET @sql = N'
SELECT StoreName, ' + @cols + N'
FROM  (SELECT s.StoreName, YEAR(o.OrderDate) AS OrderYear, o.OrderID
       FROM   Sales.SalesOrder AS o
       JOIN   Sales.Store      AS s ON s.StoreID = o.StoreID) AS src
PIVOT (COUNT(OrderID) FOR OrderYear IN (' + @cols + N')) AS p
ORDER BY StoreName;';

    EXEC sys.sp_executesql @sql;
END;
GO
EXEC Lab.usp_StoreYearPivot;

-- DESTRUCTIVE TEST (training database only): the attack drops this table
DROP TABLE IF EXISTS Lab.InjectionCanary;
CREATE TABLE Lab.InjectionCanary (ID int);
INSERT INTO Lab.InjectionCanary VALUES (1);
GO
-- (create Lab.usp_ListProducts_Unsafe as given in the task)
GO
EXEC Lab.usp_ListProducts_Unsafe N'ListPrice';
EXEC Lab.usp_ListProducts_Unsafe N'ListPrice; DROP TABLE Lab.InjectionCanary; --';
SELECT OBJECT_ID(N'Lab.InjectionCanary') AS CanaryObjectID;      -- NULL: the table is gone
GO

CREATE OR ALTER PROCEDURE Lab.usp_ListProducts
    @SortColumn sysname,
    @SortDesc   bit = 0
AS
BEGIN
    SET NOCOUNT ON;
    IF @SortColumn NOT IN (N'ProductCode', N'ProductName', N'ListPrice', N'CategoryID')
        THROW 50601, N'Invalid sort column.', 1;

    DECLARE @sql nvarchar(max) =
        N'SELECT ProductCode, ProductName, ListPrice FROM Inventory.Product ORDER BY '
        + QUOTENAME(@SortColumn) + CASE WHEN @SortDesc = 1 THEN N' DESC' ELSE N' ASC' END
        + N', ProductCode;';
    EXEC sys.sp_executesql @sql;
END;
GO
EXEC Lab.usp_ListProducts N'ProductName';
EXEC Lab.usp_ListProducts N'ListPrice; DROP TABLE Lab.InjectionCanary; --';   -- 50601

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST(
           N'SELECT ' + QUOTENAME(c.name, '''') + N' AS ColumnName, '
         + N'SUM(CASE WHEN ' + QUOTENAME(c.name) + N' IS NULL THEN 1 ELSE 0 END) AS NullCount, '
         + N'COUNT(DISTINCT ' + QUOTENAME(c.name) + N') AS DistinctValues FROM Sales.Customer'
         AS nvarchar(max)), N' UNION ALL ') WITHIN GROUP (ORDER BY c.column_id)
FROM   sys.columns AS c
JOIN   sys.types   AS ty ON ty.user_type_id = c.user_type_id
WHERE  c.object_id = OBJECT_ID(N'Sales.Customer')
  AND  ty.name IN (N'char', N'varchar', N'nchar', N'nvarchar');

EXEC sys.sp_executesql @sql;

CREATE USER SearchUser WITHOUT LOGIN;
GRANT EXECUTE ON Lab.usp_SearchOrdersDyn TO SearchUser;
GO
EXECUTE AS USER = 'SearchUser';
    EXEC Lab.usp_SearchOrdersDyn @CustomerID = 18;          -- Msg 229
REVERT;
GO

-- Option A: grant read on exactly the table the dynamic SQL reads
GRANT SELECT ON Sales.SalesOrder TO SearchUser;
GO
EXECUTE AS USER = 'SearchUser';
    EXEC Lab.usp_SearchOrdersDyn @CustomerID = 18;          -- 27 rows
REVERT;
GO
REVOKE SELECT ON Sales.SalesOrder FROM SearchUser;
DROP USER SearchUser;

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(CAST(
           N'SELECT ' + QUOTENAME(s.name, '''') + N' AS SchemaName, ' + QUOTENAME(t.name, '''') + N' AS TableName, '
         + N'(SELECT COUNT_BIG(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N') AS CountedRows, '
         + N'(SELECT SUM(ps.row_count) FROM sys.dm_db_partition_stats AS ps WHERE ps.object_id = '
         + CAST(t.object_id AS nvarchar(20)) + N' AND ps.index_id IN (0, 1)) AS MetadataRows'
         AS nvarchar(max)), N' UNION ALL ') WITHIN GROUP (ORDER BY s.name, t.name)
FROM   sys.tables  AS t
JOIN   sys.schemas AS s ON s.schema_id = t.schema_id
WHERE  s.name IN (N'Sales', N'Inventory');

SET @sql = N'SELECT *, CASE WHEN CountedRows = MetadataRows THEN ''Match'' ELSE ''Differs'' END AS Result FROM ('
         + @sql + N') AS x ORDER BY SchemaName, TableName;';
EXEC sys.sp_executesql @sql;

H. Knowledge check

1. Understanding. Why can’t a table name be passed as a parameter to sp_executesql, and what do you do instead?

2. Debugging. A procedure builds @sql by concatenating several long N'…' literals and variables declared as nvarchar(4000); the generated statement is cut off mid-word. Why?

3. Understanding. A colleague escapes values with REPLACE(@x, '''', '''''') and concatenates them. Give two reasons to switch to sp_executesql parameters.

4. Query interpretation. A static procedure works for a user with only EXECUTE; the dynamic version fails with SELECT permission denied. Why?

5. Understanding. When is OPTION (RECOMPILE) a better choice than dynamic SQL for a search with optional filters, and when is dynamic SQL better?

I. Troubleshooting and cleanup

Cleanup. Remove today’s procedures and test objects:

DROP PROCEDURE IF EXISTS Lab.usp_FindCustomer_Unsafe, Lab.usp_FindCustomer, Lab.usp_CountRows,
                         Lab.usp_SearchOrdersDyn, Lab.usp_ListProducts_Unsafe, Lab.usp_ListProducts,
                         Lab.usp_StoreYearPivot;
DROP TABLE IF EXISTS Lab.InjectionCanary;
DROP USER  IF EXISTS SearchUser;

Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• The unsafe search returned 40 rows for ' OR 1=1 --; the safe one returned 0.
• Status counts via output parameters: 7 / 357 / 4 / 8.
• The metadata-generated query returned 13 tables with the setup-validation counts.
• The dynamic search returned 27, 27, 4 and 376 rows and resisted both attacks.
• Tasks 1–4 match the success criteria.

Recap

• Use dynamic SQL only when the query’s shape must vary.
• Values → sp_executesql parameters, always. Identifiers → validate (catalog or whitelist), then QUOTENAME.
• Build in nvarchar(max); add a debug switch; inspect generated code.
• One cached plan per statement shape; concatenated values flood the cache.
• Dynamic SQL breaks ownership chaining: grant narrowly, EXECUTE AS carefully, or sign modules.
• Metaprogramming with STRING_AGG over catalog views automates repetitive SQL.

Next: Day 29 — Real-world scenarios part 1: gaps and islands, running balances and other business questions that combine everything so far.

References (Microsoft Learn)

• sp_executesql (Transact-SQL)
• EXECUTE (Transact-SQL)
• SQL injection
• QUOTENAME (Transact-SQL)
• STRING_AGG (Transact-SQL)
• Tutorial: Ownership chains and context switching
• EXECUTE AS clause (Transact-SQL)