sp_executesql, Safe Dynamic SQL, Metadata-Driven Code, and When CLR/External Logic Fits

Learning outcomes

ServiceHub adds a metadata-driven export endpoint where users can filter work orders and choose an allowed sort column. The first implementation concatenates every value and identifier into a string, then runs EXEC(@sql). It works for normal input and is catastrophically unsafe for hostile input. The repair starts by separating two kinds of variability: data values belong in typed parameters; identifiers/SQL structure cannot be parameters, so they require an allowlist and safe quoting.

This final lesson also draws the boundary around Common Language Runtime (CLR) integration and external code. SQL Server can host managed .NET Framework assemblies, but enabling/deploying them changes the server security and operational surface. CLR is justified by specific computational/interoperability needs—not because T-SQL feels inconvenient.

1. Deliberately unsafe dynamic SQL

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NULL  CREATE DATABASE ServiceHubProgrammabilityLab;GOALTER DATABASE ServiceHubProgrammabilityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubProgrammabilityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab13') IS NULL EXEC(N'CREATE SCHEMA lab13 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrder  (    work_order_id bigint IDENTITY(1001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrder PRIMARY KEY,    customer_code varchar(16) NOT NULL,    region_code char(3) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    description nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrder_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrder_priority CHECK (priority BETWEEN 1 AND 5)  );  ;WITH n AS  (    SELECT TOP (30000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT ops.WorkOrder(customer_code,region_code,status,priority,opened_at,closed_at,amount,description)  SELECT CONCAT('CUST-',RIGHT('000000'+CONVERT(varchar(6),n%9000),6)),         CASE n%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n%23=0 THEN 'ESCALATED'              WHEN n%5=0 THEN 'CLOSED'              WHEN n%2=0 THEN 'ASSIGNED' ELSE 'OPEN' END,         CONVERT(tinyint,1+n%5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n%5=0 THEN DATEADD(minute,n+60,'2026-01-01T00:00:00') END,         CAST(25+(n%25000)/10.0 AS decimal(12,2)),         CONCAT(N'ServiceHub work order ',n)  FROM n;  CREATE INDEX IX_ops_WorkOrder_region_status_opened    ON ops.WorkOrder(region_code,status,opened_at)    INCLUDE(customer_code,priority,amount,closed_at);END;GO

DECLARE @customer_code nvarchar(200)=N'CUST-000001'' OR 1=1 --';DECLARE @sql nvarchar(max)=  N'SELECT TOP (20) work_order_id,customer_code,status ' +  N'FROM ops.WorkOrder WHERE customer_code=''' + @customer_code + N'''';SELECT @sql AS generated_text;-- EXEC(@sql); -- intentionally not executed in the course labGO

The generated string changes the grammar of the statement. Quoting or stripping a few characters is not a robust security model; SQL injection is a parser problem created when untrusted data becomes SQL text. The safe default is to keep the statement text fixed and pass values separately.

2. sp_executesql: text plus an explicit parameter contract

DECLARE @sql nvarchar(max)=N'SELECT TOP (@max_rows)       work_order_id,customer_code,region_code,status,priority,opened_atFROM ops.WorkOrderWHERE customer_code=@customer_codeORDER BY opened_at DESC,work_order_id DESC;';EXEC sys.sp_executesql  @stmt=@sql,  @params=N'@customer_code varchar(16),@max_rows int',  @customer_code='CUST-000001',  @max_rows=20;GO

The parameter declaration is part of the dynamic batch’s contract. Match data types and lengths to the schema instead of passing everything as nvarchar(max). The batch compiles in its own scope, cannot see caller local variables unless they are parameters, and can reuse a plan when the statement text and parameter definition remain stable.

DECLARE @sql nvarchar(max)=N'SELECT COUNT_BIG(*) AS order_countFROM ops.WorkOrderWHERE region_code=@region AND status=@status;';EXEC sys.sp_executesql @sql,N'@region char(3),@status varchar(16)','N01','OPEN';EXEC sys.sp_executesql @sql,N'@region char(3),@status varchar(16)','W02','OPEN';GOSELECT TOP (20) cp.usecounts,cp.size_in_bytes,LEFT(st.text,400) AS sql_textFROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS stWHERE st.text LIKE N'%COUNT_BIG(*) AS order_count%ops.WorkOrder%'ORDER BY cp.usecounts DESC;GO

Cache entries are transient, and matching can be affected by database/session settings and text differences. The goal is not “one plan forever”; it is to avoid generating unique SQL text just because values differ.

3. Identifiers are not values: allowlist, then QUOTENAME

You cannot bind a table name, column name, ASC/DESC keyword or arbitrary SQL fragment as an sp_executesql value parameter. If structure must vary, constrain it to known safe options. QUOTENAME then converts a validated identifier into a delimited identifier and doubles embedded closing delimiters. It accepts a sysname-sized input (128 characters); longer input returns NULL.

DECLARE @sort sysname=N'opened_at';DECLARE @direction varchar(4)='DESC';IF @sort NOT IN (N'opened_at',N'priority',N'amount',N'work_order_id')  THROW 50020,'Unsupported sort column.',1;IF @direction NOT IN ('ASC','DESC')  THROW 50021,'Unsupported sort direction.',1;DECLARE @sql nvarchar(max)=N'SELECT TOP (@max_rows) work_order_id,customer_code,status,priority,opened_at,amountFROM ops.WorkOrderWHERE region_code=@regionORDER BY '+QUOTENAME(@sort)+N' '+@direction+N',work_order_id DESC;';EXEC sys.sp_executesql  @sql,N'@region char(3),@max_rows int',@region='E03',@max_rows=25;GO

QUOTENAME is not an allowlist. It makes one identifier syntactically safe; it does not decide whether the caller should be permitted to reference that identifier. Use both.

4. SQL Server 2025 OPTIMIZED_SP_EXECUTESQL: compilation behavior, not injection defense

SQL Server 2025 adds the database-scoped OPTIMIZED_SP_EXECUTESQL configuration. When enabled, identical sp_executesql batches serialize compilation similarly to stored procedures/triggers: one session obtains the compile lock, produces the plan, and other sessions can reuse it instead of compiling duplicate copies concurrently. It is OFF by default.

SELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name IN (N'OPTIMIZED_SP_EXECUTESQL',N'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY');GOSELECT name,is_auto_update_stats_async_onFROM sys.databasesWHERE database_id=DB_ID();GO-- Optional only after representative compile-concurrency evidence and Microsoft guidance review:-- ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;

Microsoft recommends considering asynchronous statistics plus low-priority async stats-update waits before enabling optimized sp_executesql in environments susceptible to long synchronous statistics refreshes. This feature reduces duplicate concurrent compilation; it does not sanitize SQL, choose good parameters, or solve bad query design.

5. CLR is a server surface-area decision

SQL CLR integration hosts managed .NET Framework code inside SQL Server. It can implement procedures, functions, triggers, aggregates and types. This is useful for some CPU-oriented algorithms, specialized parsing/formatting, or functionality that is awkward in relational T-SQL. But it adds assembly deployment, versioning, security review, server configuration and incident-debugging concerns.

SELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'clr enabled',N'clr strict security',N'lightweight pooling');GOSELECT a.name,a.permission_set_desc,a.is_user_defined,a.create_dateFROM sys.assemblies AS aORDER BY a.is_user_defined DESC,a.name;GO

clr enabled is OFF by default for user CLR integration. Enabling it requires server-level configuration and appropriate permissions. Since SQL Server 2017, clr strict security is enabled by default and treats SAFE/EXTERNAL_ACCESS assemblies as UNSAFE at runtime; Microsoft recommends signing assemblies with a certificate/asymmetric key and granting the corresponding login UNSAFE ASSEMBLY, or explicitly trusting the assembly. Turning TRUSTWORTHY ON just to make CLR work is not the recommended design.

6. When external logic is a better boundary

Database-resident CLR shares the database engine process and operational lifecycle. If a task needs network calls, independent scaling, modern .NET/runtime libraries, long-running workflows, external credentials, or broad OS access, an application/service/worker is usually a clearer boundary. Keep the database responsible for transactional data work and expose an explicit queue/outbox/procedure interface to external components when appropriate.

7. Final chapter cleanup

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubProgrammabilityLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubProgrammabilityLab;END;GOSELECT DB_ID(N'ServiceHubProgrammabilityLab') AS should_be_null;GO

No server-wide CLR or database-scoped optimized-sp-executesql settings were changed by mandatory labs, so cleanup is only the disposable database. If you deliberately tested optional server settings, roll them back using the pre-change configuration snapshot and restart requirements documented for that experiment.

Bridge to Chapter 14

Chapter 13 showed that database code is inseparable from security context: ownership chains, EXECUTE permissions, dynamic SQL and CLR assemblies all depend on who is authenticated, which database principal is active, and which permissions are granted or denied. Chapter 14 turns those mechanics into a complete SQL Server security model covering logins, users, roles, encryption, auditing and least privilege.

Authoritative references

• sp_executesql — typed dynamic batches, plan reuse and SQL Server 2025 OPTIMIZED_SP_EXECUTESQL
• QUOTENAME — safe delimited identifiers and input limits
• Writing secure dynamic SQL — SQL injection and parameterization guidance
• Enable CLR integration — clr enabled and server requirements
• CLR strict security — strict security, signing and permissions
• Create an assembly — assembly deployment security
• Getting started with CLR integration — Linux/.NET Framework restrictions
• SQL Server 2025 build versions — servicing baseline