Safe Dynamic SQL With sp_executesql and QUOTENAME

What stops a search box from becoming a second query window? Building safe dynamic SQL keeps user values separate from executable statements. Object names need a different rule from values.

Know What Must Be Dynamic

I ask why the statement needs dynamic text before reviewing its escaping. Optional search values usually fit parameters. Changing a table or ordering column requires a different approach.

A parameter represents a value, not a SQL identifier. You cannot substitute a table name with @Table in a FROM clause. SQL Server needs the identifier while it compiles the statement.

That distinction gives us two boundaries. Pass business values through typed parameters. Validate and quote the small set of identifiers the application is allowed to choose.

Run the following setup in a disposable database. The rows are demonstration inputs. The example doesn't grant callers permission to browse arbitrary tables.

CREATE TABLE dbo.DynamicSearchDemo
(
    ItemId int NOT NULL PRIMARY KEY,
    ItemName nvarchar(80) NOT NULL,
    Price decimal(12,2) NOT NULL
);
INSERT dbo.DynamicSearchDemo VALUES
(1, N'Notebook', 5.00), (2, N'Pen', 2.00), (3, N'Folder', 3.00);

Pass Values through sp_executesql

The SQL text contains parameter names rather than pasted values. The parameter declaration supplies their types. The final argument supplies the actual value separately.

This prevents a quote inside the input from ending a string literal in the executable text. It also keeps the statement text stable across ordinary searches. Stable text supports plan reuse when other cache conditions match.

Matching parameter lengths and types still matters. An nvarchar search column deserves an nvarchar parameter of an appropriate size. Parameterization doesn't excuse a mismatched database contract.

The next query searches one exact name. Replace the supplied value to test other ordinary names. You don't need to edit the statement text to do that.

DECLARE @Name nvarchar(80) = N'Notebook';
EXEC sys.sp_executesql
    N'SELECT ItemId, ItemName, Price
      FROM dbo.DynamicSearchDemo WHERE ItemName = @Name;',
    N'@Name nvarchar(80)', @Name = @Name;

Validate Object Names before Quoting Them

QUOTENAME wraps an identifier and escapes embedded closing delimiters. It isn't permission checking. Quoting a prohibited table correctly still leaves you querying a prohibited table.

Use an explicit object policy before building the statement. This example permits one table in dbo. It then checks sys.objects to confirm that the approved user table exists.

Metadata visibility depends on the caller's permissions. An absent metadata row isn't proof that an object doesn't exist for every identity. Test under the execution context intended for the application.

The names use sysname because identifiers have a bounded length. QUOTENAME returns NULL when input exceeds its supported identifier length. Validate untrusted lengths before converting broader input into sysname.

DECLARE @Schema sysname = N'dbo', @Table sysname = N'DynamicSearchDemo';
IF @Schema <> N'dbo' OR @Table <> N'DynamicSearchDemo'
    THROW 51000, 'The requested table is not approved.', 1;
IF NOT EXISTS
(
    SELECT 1 FROM sys.objects AS o
    JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    WHERE s.name = @Schema AND o.name = @Table AND o.type = 'U'
)
    THROW 51001, 'The approved table is unavailable.', 1;
DECLARE @Statement nvarchar(max) =
    N'SELECT ItemId, ItemName, Price FROM '
    + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table)
    + N' WHERE Price >= @MinimumPrice;';
EXEC sys.sp_executesql @Statement, N'@MinimumPrice decimal(12,2)', @MinimumPrice = 3.00;

Choose Sorting from a Small Allow List

A sorting request includes a column and a direction. Neither should arrive as arbitrary SQL text. Map application choices to approved identifiers and fixed direction tokens.

The next example accepts ItemName or Price. It accepts ASC or DESC after normalization. The final ItemId order makes tied results deterministic.

An allow list is easier to review than an attempt to detect every hostile string. Rejected input stays rejected rather than becoming a creatively sanitized expression. An expression and a column name aren't the same contract.

I keep the list beside the application's sorting choices. A new visible option then requires an explicit database review. The database shouldn't infer new capabilities from user spelling.

DECLARE @SortColumn sysname = N'Price', @Direction varchar(4) = 'DESC';
SET @Direction = UPPER(@Direction);
IF @SortColumn NOT IN (N'ItemName', N'Price') OR @Direction NOT IN ('ASC', 'DESC')
    THROW 51002, 'The requested sort is not approved.', 1;
DECLARE @SortSql nvarchar(max) =
    N'SELECT ItemId, ItemName, Price FROM dbo.DynamicSearchDemo ORDER BY '
    + QUOTENAME(@SortColumn) + N' ' + @Direction + N', ItemId;';
EXEC sys.sp_executesql @SortSql;

Test Safe Dynamic SQL with Hostile Input

An injection demonstration doesn't need to damage a table. Send a string containing a quote and an OR expression as a value. The safe statement searches for that entire string.

The punctuation never becomes part of the executable statement. No extra predicate is introduced. Check the returned rows and compare the statement text with the ordinary call.

This is a semantic demonstration, not a measured security benchmark. It verifies one important boundary in the sample. It doesn't certify an entire application's authorization design.

DECLARE @Hostile nvarchar(80) = N''' OR 1=1 --';
EXEC sys.sp_executesql
    N'SELECT ItemId, ItemName, Price FROM dbo.DynamicSearchDemo WHERE ItemName = @Name;',
    N'@Name nvarchar(80)', @Name = @Hostile;
SELECT COUNT_BIG(*) AS ExistingItems FROM dbo.DynamicSearchDemo;

Keep Authorization Outside Safe Dynamic SQL

Safe dynamic SQL also needs appropriate permissions. A caller permitted to execute a broad dynamic query still has broad access. Restrict the exposed tables and columns deliberately.

Dynamic statements affect ownership-chain behavior. A module's ability to access a table doesn't automatically settle dynamic access. Use a reviewed execution context or module signing when the permission design requires it.

Don't elevate the caller to solve a failed permission test. First establish which object the statement legitimately needs. Then grant the smallest supported access through the chosen module boundary.

What can the same caller query outside this search operation? Test that directly. A well-parameterized search cannot compensate for an unrestricted login.

Check Less Obvious Inputs to Safe Dynamic SQL

LIKE parameters remain SQL values, but wildcard semantics still apply. Escape wildcards when the application promises literal text searching. Parameterization prevents injection without changing pattern matching rules.

Also test empty strings, nulls and boundary lengths. Decide whether missing input means no filter or no matching row. Those choices belong in the application contract.

I review the completed statement shape and its parameter declaration together. A type mismatch creates performance trouble even in secure code. A valid identifier outside the allowed scope creates authorization trouble.

Safe dynamic SQL uses different controls for different inputs. Keep that separation visible in the code review. A giant pile of replaced quotes isn't a design document.

A schema name needs its own policy too. Allowing a familiar table name in any schema exposes unintended objects. Validate the pair rather than quoting whichever schema arrives.

Don't accept a comma-separated column list as one identifier. It is a SQL fragment with several possible meanings. Map requested fields individually to approved columns and assemble only those known choices.

Input length checks must happen before narrowing assignments. Assigning a broad user string to sysname can discard extra characters. Validate the original string so rejection remains based on what the caller supplied.

For paging, keep the ordering deterministic across tied values. A safe sort column still needs a stable tiebreaker. Otherwise repeated requests can return inconsistent slices even without hostile input.

Related reading on this blog: Input and Output Parameter for Dynamic SQL: Simple Example and Finding Dynamic SQL Open to Injection.

A dynamic query is not a license to paste input, it is a statement with controlled values and identifiers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.