Block dangerous SQL before it runs.

-- The sink runs a bare variable. The concatenation that taints it is
-- three assignments back; a decoy copy is returned, never executed.
CREATE OR REPLACE PROCEDURE etl.add_audit_column(target_col VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$
DECLARE
  a VARCHAR;
  b VARCHAR;
  c VARCHAR;
  preview VARCHAR;
BEGIN
  a := 'ALTER TABLE fct_events ADD COLUMN ' || target_col || ' VARCHAR';
  b := a;
  preview := 'ddl: ' || b;
  c := b;
  EXECUTE IMMEDIATE c;
  RETURN preview;
END;
$;

-- Same three-hop relay, same bare-variable sink. But the traced value
-- is a constant template; the runtime value is bound with USING.
CREATE OR REPLACE PROCEDURE etl.count_tenant_events(tenant_id VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$
DECLARE
  a VARCHAR;
  b VARCHAR;
  c VARCHAR;
BEGIN
  a := 'SELECT COUNT(*) FROM fct_events WHERE tenant_id = ?';
  b := a;
  c := b;
  EXECUTE IMMEDIATE c USING (tenant_id);
  RETURN 'ok';
END;
$;

-- Untrusted @term crosses into build_clause, is wrapped in QUOTENAME()
-- (not a sanitizer in a string-literal context), returned via an OUTPUT
-- parameter, then concatenated and executed back in the caller.
CREATE PROCEDURE dbo.build_clause @raw NVARCHAR(200), @out NVARCHAR(400) OUTPUT
AS
BEGIN
  SET @out = N'WHERE name = ''' + QUOTENAME(@raw) + N'''';
END;
GO

CREATE PROCEDURE dbo.run_search @term NVARCHAR(200)
AS
BEGIN
  DECLARE @clause NVARCHAR(400);
  EXEC dbo.build_clause @term, @clause OUTPUT;
  DECLARE @sql NVARCHAR(800) = N'SELECT * FROM users ' + @clause;
  EXEC sp_executesql @sql;
END;
GO

-- Identical intent, but @term is bound via sp_executesql parameters
-- instead of being concatenated into the statement text.
CREATE PROCEDURE dbo.run_search @term NVARCHAR(200)
AS
BEGIN
  DECLARE @sql NVARCHAR(800) = N'SELECT * FROM users WHERE name = @p';
  EXEC sp_executesql @sql, N'@p NVARCHAR(200)', @p = @term;
END;
GO

-- The DELETE inside the literal has a WHERE clause — one that is
-- provably always true. The engine parses the embedded SQL and proves it.
CREATE PROCEDURE dbo.archive_stale
AS
BEGIN
  EXEC sp_executesql N'DELETE FROM dbo.audit_log
    WHERE batch_id = 42 OR retained = 0 OR batch_id <> 42 OR batch_id IS NULL';
END;

-- Same shape. The DELETE inside the literal is bounded for real, and
-- the cutoff arrives as a bound parameter.
CREATE PROCEDURE dbo.archive_stale @cutoff DATETIME2
AS
BEGIN
  EXEC sp_executesql
    N'DELETE FROM dbo.audit_log WHERE logged_at < @c',
    N'@c DATETIME2', @c = @cutoff;
END;

-- A revenue rollup that runs without error and returns the wrong number.
WITH active AS (
  SELECT account_id, region FROM accounts WHERE status = 'active'
),
billable AS (
  SELECT a.account_id, SUM(i.amount) AS revenue
  FROM active a
  LEFT JOIN invoices i ON i.account_id = a.account_id
  WHERE i.status = 'posted'
    AND (i.is_billable = TRUE OR i.is_billable = FALSE OR i.is_billable IS NULL)
  GROUP BY a.account_id
)
SELECT * FROM billable
WHERE account_id NOT IN (SELECT account_id FROM suspended_accounts);

-- The join filters live in the ON clause, so unmatched accounts survive;
-- NOT EXISTS is immune to a NULL in suspended_accounts.
WITH active AS (
  SELECT account_id, region FROM accounts WHERE status = 'active'
),
billable AS (
  SELECT a.account_id, SUM(i.amount) AS revenue
  FROM active a
  LEFT JOIN invoices i
    ON i.account_id = a.account_id
    AND i.status = 'posted'
    AND i.is_billable = TRUE
  GROUP BY a.account_id
)
SELECT * FROM billable b
WHERE NOT EXISTS (
  SELECT 1 FROM suspended_accounts s WHERE s.account_id = b.account_id
);

-- Looks conditional. Every branch returns the input untouched.
CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val ELSE val END;

-- Same policy. The non-admin branch actually masks.
CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val ELSE '***-**-****' END;