May 2026

The Procedure That Looked Parameterized

How Lexega detects SQL injection risks across statement boundaries

A "search by name" stored procedure lands in the internal admin tool. It uses sp_executesql, the parameterized form. The PR review takes thirty seconds.

CREATE PROCEDURE dbo.usp_search_customers
    @name NVARCHAR(200)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT * FROM customers WHERE name LIKE ''%' + @name + '%''';
    EXEC sp_executesql @sql;
END

Read the DECLARE slowly. @name is concatenated into the SQL text on that line, then handed to sp_executesql as the statement, not as a parameter. There are no parameters at all. The sp_executesql is decorative. A search term of '; DROP TABLE customers; -- runs as a second statement.

This is the failure mode dynamic SQL specializes in: the syntax of safety without the substance. It survives review because the dangerous part isn't on the line that calls EXEC. It's three lines above, in a DECLARE initializer that looks like setup.

The whole problem in one sentence

Dynamic SQL collapses the boundary between code and data. Once a runtime value is concatenated into the SQL text, the database has no way to tell which characters were yours and which the user supplied. sp_executesql @sql, N'@p NVARCHAR(200)', @p = @name is the form that's actually parameterized: the value is bound through the second and third arguments. The shorter form, the one in the procedure above, is sp_executesql in name only.

Why review keeps missing it

The dangerous flow is rarely on a single line:

  1. An @sql template variable is declared on one line.
  2. User input is concatenated in on a later line, often inside an IF branch or a loop.
  3. EXEC(@sql) or EXEC sp_executesql @sql runs at the bottom of the procedure.

Reviewers see a DECLARE, a couple of SETs, and an EXEC. The EXEC looks parameterized. The review moves on.

It gets worse when the taint crosses a procedure boundary:

CREATE PROCEDURE dbo.usp_build_filter
    @user_input NVARCHAR(200),
    @sql NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET @sql = N'SELECT * FROM orders WHERE notes LIKE ''%' + @user_input + '%''';
END

CREATE PROCEDURE dbo.usp_run_filter
    @user_input NVARCHAR(200)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    EXEC dbo.usp_build_filter @user_input, @sql OUTPUT;
    EXEC sp_executesql @sql;
END

usp_run_filter looks clean: no concatenation, no string building. The concatenation happened in usp_build_filter, which is "just a helper". Each procedure, read on its own, looks fine. The injection is the chain.

The same gap shows up in tooling. Static analysis that stops at the SQL string boundary (parsing the syntax without tracking what runtime values flow through it) can't see this category. The DECLARE and the EXEC are syntactically separate; only taint flow across statements connects them.

What Lexega does about it

In v1.0, the procedure at the top of this post would have slipped through. The DYNSQL rules existed, but the concatenation lived in the DECLARE initializer and the EXEC ran three lines below, and the engine didn't follow taint across statement boundaries. The string handed to sp_executesql looked like an opaque variable with no upstream history.

v1.1 closes that gap. When Lexega analyzes the procedure, it walks the body and records where each variable's value came from. The @sql initializer is a concatenation involving the procedure's @name parameter, so @sql is marked tainted, with provenance pointing back to the concat expression. When EXEC sp_executesql @sql runs, Lexega consults the taint map for @sql, sees the concat origin, and fires three signals at the call site: DYNSQL-CONCAT (Critical) for the concatenated argument, DYNSQL (High) as the catch-all, and DYNSQL-NO-PARAM (Medium) for using a parameterizable surface without binding any parameters. Each signal carries the upstream concat as its evidence span.

The same machinery handles the two-procedure example. @user_input enters usp_build_filter as a parameter, gets concatenated into @sql, and flows back out through the OUTPUT argument. When usp_run_filter calls usp_build_filter and then EXEC sp_executesql on the returned @sql, Lexega has already recorded that @sql carries the taint from the upstream concat. The call boundary doesn't reset the map. Calling the helper usp_build_filter doesn't launder anything.

Dialect coverage is parallel. Snowflake :var, MSSQL @var, PostgreSQL and Redshift PL/pgSQL locals, BigQuery and Databricks bare names. The taint walker follows each through whatever assignment forms the dialect supports (SET, DECLARE = expr, SELECT @x = col FROM ..., FETCH cursor INTO @v), and fires the same signals when a tainted value reaches a dynamic-SQL call that didn't bind parameters.

The same shape in Snowflake Scripting:

CREATE OR REPLACE PROCEDURE search_customers(name VARCHAR)
  RETURNS TABLE (id NUMBER, name VARCHAR)
  LANGUAGE SQL
AS
$
DECLARE
  stmt VARCHAR;
  res RESULTSET;
BEGIN
  stmt := 'SELECT id, name FROM customers WHERE name LIKE ''%' || :name || '%''';
  res := (EXECUTE IMMEDIATE :stmt);
  RETURN TABLE(res);
END;
$;

|| instead of +, :name instead of @name, EXECUTE IMMEDIATE instead of sp_executesql. Same DYNSQL-CONCAT and DYNSQL signals fire, same taint trace from the DECLARE / stmt := assignment through to the EXECUTE IMMEDIATE call site. The safe form is EXECUTE IMMEDIATE :stmt USING (:name) with a ? placeholder in the statement string; without USING, DYNSQL-NO-PARAM also fires.

The OS-shell escalation

Dynamic SQL is the on-ramp. The cliff is xp_cmdshell. Once an injection lands, the three-line escalation:

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC xp_cmdshell 'whoami';

v1.1 ships two separate critical rules:

  • MSSQL-SPCONFIG-XPCMDSHELL (Critical): fires on the sp_configure 'xp_cmdshell' call, the prerequisite that enables xp_cmdshell at the server level. Caught regardless of whether the EXEC lands in the same change.
  • MSSQL-XP-CMDSHELL (Critical): fires on EXEC xp_cmdshell, or on a wrapper procedure (CREATE PROCEDURE usp_admin_helper AS EXEC xp_cmdshell @cmd) that gets called downstream. A generic procedure name doesn't move it past detection.

Try it

Paste the search procedure at the top of this post into the Lexega Playground. You'll see DYNSQL-CONCAT (Critical), DYNSQL (High), and DYNSQL-NO-PARAM (Medium) fire on the sp_executesql @sql call (plus an INFO-MSSQL-EXEC-PROC audit signal), with the taint traced back to the DECLARE initializer.

To run it locally:

curl -fsSL https://lexega.com/install.sh | sh
export LEXEGA_LICENSE_KEY=<your-key>
lexega-sql analyze --dialect mssql usp_search_customers.sql --min-severity medium

To gate it in CI:

lexega-sql review main..HEAD migrations/ -r --pr-comment

Get a free trial key. Instant activation, no account required. For how Lexega's signal model fits together, see How Lexega Turns SQL Into Signals. For AI-generated SQL flowing through normal PR paths, see Code Review Can't Keep Up with AI-Generated SQL.