A string built from user input can become a SQL command with user-chosen syntax. Finding dynamic SQL open to injection starts with code search, then a careful review of how each value enters the statement.
Search for Modules With Dynamic SQL Open to Injection
Dynamic SQL is legitimate for tasks such as optional predicates and variable object names. Injection risk appears when untrusted values are concatenated into executable text. Search stored procedures, functions, triggers, and application code for EXEC, sp_executesql, and string concatenation. A text search finds candidates, not proven vulnerabilities. Review the data flow for each one.
I start with modules that accept search terms, sort columns, filters, or identifiers from users. Those inputs reach SQL text frequently. The catalog query below locates definitions containing common execution terms. Encrypted modules and application-side SQL will not appear, so include source and application review too.
SELECT s.name AS schema_name,
o.name AS object_name,
o.type_desc,
m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o
ON o.object_id = m.object_id
JOIN sys.schemas AS s
ON s.schema_id = o.schema_id
WHERE m.definition LIKE N'%sp_executesql%'
OR m.definition LIKE N'%EXEC(%'
ORDER BY s.name, o.name;Trace the Value Into the Text
For each candidate, identify the source of every concatenated piece. Constants and carefully controlled server-generated fragments differ from request parameters. Follow values through wrappers, defaults, and validation functions. A value can be unsafe even if it is numeric in the interface when it arrives as text in the database. The key question is whether the value can alter command syntax.
I do not label every EXEC call as dynamic SQL open to injection from one search result. Some modules execute a fixed command. Others build a table name from an approved list. Read the whole procedure and its callers. A false positive wastes time; a missed data path is worse. The review needs both context and a test.
Recognize Dynamic SQL Open to Injection
The classic failure is building a WHERE clause by appending an input value inside quotes. A quote supplied by the caller can change where the string ends. Even when a filter appears to block obvious punctuation, alternate encodings and code paths can defeat fragile checks. Parameterization removes the value from the SQL syntax.
I explain this to developers using a small example rather than a dramatic exploit string. The issue is the boundary between code and data. If the input becomes part of the executable text, the database cannot reliably distinguish the intended value from new SQL. Fix that boundary, then test normal and hostile inputs.
Parameterize Data Values
sp_executesql accepts a parameter definition and separate parameter values. Build the statement with parameter markers and pass values through the argument list. This also helps plan reuse when statement text remains stable. Match parameter types and lengths to the underlying columns. A mismatched length can truncate or change behavior.
The example uses a literal sample value and a parameterized predicate. Replace the table with one in your database. The important part is that the search value never becomes SQL syntax. I prefer this pattern over escaping quotes by hand. Escaping is easy to get wrong as a procedure evolves.
DECLARE @sql nvarchar(max) =
N'SELECT CustomerID FROM dbo.Customers WHERE EmailAddress = @Email';
DECLARE @email nvarchar(320) = N'sample@example.com';
EXEC sys.sp_executesql
@sql,
N'@Email nvarchar(320)',
@Email = @email;
Handle Identifiers Separately
Table names, column names, and sort directions cannot be passed as ordinary value parameters. Map user choices to a fixed approved list, then use QUOTENAME for an identifier after validation. QUOTENAME handles delimiter escaping; it does not decide whether the chosen object should be allowed. Do not accept an arbitrary table name merely because it was quoted safely.
I prefer a CASE or allowlist for sort choices. A small number of approved options is easier to review than free-form SQL. If the application truly needs dynamic object names, document who can choose them and what permissions the execution identity has. Least privilege limits damage even when code validation fails.
Check Truncation and Types
A parameter declared shorter than the input or target column can truncate silently in some paths and change the intended filter. A string buffer too short for the assembled command can cut off a protective clause. Review lengths of variables, parameters, and columns together. Prefer nvarchar(max) for the command text when the statement length is not tightly bounded.
I have seen a parameterized procedure still behave oddly because the parameter length was guessed. Parameterization fixes the code-data boundary, but correct data types remain part of the implementation. Test long, empty, Unicode, and quoted values. The application should handle them as data, not as control characters.
Test Safely on a Copy
Use a nonproduction copy with synthetic data for adverse-input tests. Try quotes, comment markers, unusual Unicode, long values, and unexpected sort choices. Observe whether the query returns an error, an appropriate empty result, or data outside the user’s scope. Do not run destructive payloads against production. Preserve the test input and result for review.
I test the application path as well as the stored procedure directly. A safe procedure can still receive unsafe dynamic SQL from application code. Conversely, an application filter should not be the only defense for a database procedure exposed through another route. Both layers need clear boundaries.
Inspect Permissions Too
Dynamic SQL open to injection executed with a highly privileged login has a larger failure radius. Review the runtime account’s rights while fixing the SQL. Use procedures and custom roles to limit what the application identity can reach. Do not treat least privilege as a substitute for parameterization; it is a second control.
I ask what the account could read or change if the query text were altered. That makes the risk concrete without inventing an incident. The answer guides prioritization. Fix the code path, then reduce rights that were granted only to make the old code work.
Keep the Search for Dynamic SQL Open to Injection Repeatable
Add the module search and application code review to security maintenance. New features introduce new dynamic predicates. A code review checklist should ask whether every untrusted value is parameterized or mapped through a narrow allowlist. Record exceptions with a reason and a test.
Which module builds SQL text from a value supplied by a user today? Find it before an attacker does. The catalog search gives you a starting list. The real result is a documented, tested boundary between user data and executable SQL.
Related reading on this blog: SQL Injection: How It Works and How to Stop It and One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?.
Dynamic SQL is not the danger, it is the user input you let write the command.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
