SQL Security Checks Your Linter Doesn't Run

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_ADMIN') THEN val
    WHEN CURRENT_ROLE() IN ('SUPPORT_TIER2') THEN val
    ELSE val
  END;

Every branch of this policy returns val: both role checks and the ELSE. So the column comes back in the clear for everyone, not just the privileged roles. It still compiles, attaches to its columns, and registers in the governance inventory as a masking policy that exists, which is all an existence-check audit confirms. The one thing it doesn't do is mask.

A linter won't catch this, and it shouldn't be expected to. SQLFluff and TSQLLint are looking at how the SQL is written (layout, naming, dialect hygiene), and they're good at it. Keep them. They have no opinion about what the SQL does once it runs: who can read which columns afterward, whether a control that's supposed to be load-bearing actually carries any weight.

That second question splits into six rough classes. A linter has nothing to say about any of them. At most it flags how the example is indented.

A masking policy that masks nothing

Confidentiality — exposure

Finding the bug above means evaluating what the policy body returns on each path:

[CRITICAL] MASK-ALLOW-ALL — Masking Policy body passes through the original value
without masking. Policy is effectively a no-op and sensitive data may be exposed.

Every finding block in this post is real output, showing everything at Medium severity and above: paste any example into the Lexega Playground and the same signals fire. No install, no account.

There's a second angle on the same exposure. Columns tagged as PII get traced through lineage to wherever they surface in an output (Q-FLOW-TAINT), so the finding lands at the point data leaves, even when nobody touched a policy at all.

SQL injection through dynamic SQL

Control — injection

CREATE OR REPLACE PROCEDURE purge_user_sessions(user_name VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
AS
$
BEGIN
  EXECUTE IMMEDIATE
    'DELETE FROM user_sessions WHERE user_name = ''' || user_name || '''';
  RETURN 'ok';
END;
$;

The parameter goes straight into the statement text through ||, so whoever controls user_name controls the statement that runs. It's textbook injection, just inside a stored procedure instead of an app. The safe version passes the value through EXECUTE IMMEDIATE ... USING so it's bound at execution rather than spliced into the string.

[CRITICAL] DYNSQL-CONCAT — Dynamic SQL argument is built via string concatenation
or FORMAT(...) interpolation. SQL injection risk — switch to a parameterized
query (USING clause / sp_executesql parameter binding).
[HIGH]     DYNSQL — Dynamic SQL execution detected. SQL injection risk if input
parameters are not validated. Consider parameterized queries (USING clause /
sp_executesql parameters).
[HIGH]     PROC-DYNSQL — Stored procedure executes dynamic SQL with potential
injection vector. SQL injection risk if inputs are not validated. Consider
parameterized queries (e.g. USING clause / sp_executesql parameters).
[MEDIUM]   DYNSQL-NO-PARAM — Dynamic SQL executed without parameter binding when
the surface supports it (Snowflake USING, sp_executesql @params, EXECUTE … USING).
Bind runtime values rather than interpolating.

A SAST tool that taint-tracks SQL built up inside application code will catch the app-layer version of this. The blind spot is the SQL that ships as SQL (stored procedures, migrations, dbt models), where there's no host language for it to follow. Lexega tracks the same taint at the SQL level: across a procedure that calls another, and into sp_executesql calls where the parameter binding is there for appearances and the real value is still concatenated in. The Procedure That Looked Parameterized follows one of those chains end to end.

The LEFT JOIN that quietly becomes an INNER JOIN

Integrity — misstatement

SELECT
    t.transaction_id,
    t.amount,
    a.risk_rating
FROM transactions AS t
LEFT JOIN accounts AS a
    ON t.account_id = a.account_id
WHERE a.risk_rating = 'high';

This query keeps every transaction with a LEFT JOIN, then filters on a.risk_rating in the WHERE. For any transaction with no matching account, a.risk_rating is NULL, and NULL = 'high' is never true, so those rows drop and the LEFT JOIN collapses into the INNER JOIN nobody wrote. The rows that disappear are the transactions with no known account, which in a fraud or AML control are usually the ones that matter most. Nothing errors; the result set is just short.

SQLFluff parses this query, finds the style clean, and reports nothing. It has no rule that reasons about values or nullability. Lexega flags it because it follows the nullable side of the join: it knows a.risk_rating can be NULL specifically because of the LEFT JOIN, and that an equality test against a nullable column drops those rows. That's a property of what the query computes, not of how it's written.

[CRITICAL] Q-JOIN-LEFT-FILT — LEFT JOIN nullable side filtered in WHERE clause.
This effectively converts the LEFT JOIN to an INNER JOIN, likely a bug.

The other NULL hazard on this axis is the control that fails open:

DELETE FROM api_tokens
WHERE user_id NOT IN (
    SELECT user_id FROM active_employees
);

This DELETE revokes tokens for users no longer in active_employees. If that subquery ever returns a single NULL, NOT IN evaluates to UNKNOWN for every row, and the statement deletes nothing. No error, the run completes. A token-cleanup job written this way stops revoking the day a NULL appears in the employee list, and nothing about the run looks different.

The same shape breaks access reviews: a "who has access but isn't on the approved list" query written with NOT IN returns empty when the list contains a NULL, and empty is indistinguishable from a clean certification.

[HIGH] Q-NULL-NOTIN — NULL-logic hazard: NOT IN with subquery on column
'USER_ID'. If subquery returns any NULL, entire predicate evaluates to UNKNOWN
and filters all rows. Use NOT EXISTS or ensure subquery has WHERE ... IS NOT NULL.

Both findings turn on the analyzer knowing a column can be NULL and what one NULL does to a NOT IN. That's also what keeps it quiet on the safe versions: add WHERE user_id IS NOT NULL to the subquery, or handle the NULL side of the join, and neither signal fires.

GRANT ALL to PUBLIC, and the removal side of privilege

Control — privilege

GRANT ALL PRIVILEGES ON DATABASE analytics TO ROLE PUBLIC;

This hands every user in the account every privilege on the database, in one line. Nothing tests a GRANT, and as a one-line migration diff it reads like plumbing, so it clears review.

[CRITICAL] GRT-TO-PUBLIC — Avoid granting privileges to PUBLIC. The PUBLIC role
includes all users in the account, which may expose data unintentionally.
[HIGH]     GRT-ALL-PRIV — Avoid GRANT ALL PRIVILEGES. Use specific privilege
grants to follow the principle of least privilege.

You could nearly grep for this exact statement. The privilege surface stops being greppable one step out, though: ownership transfers (GRT-OWNER-XFER), broad role-scoped grants (GRT-BROAD-PRIV), and the removal side. DROP MASKING POLICY pii_email_mask; unmasks every column the policy governed, for every role that could already query the table (MASK-DROP, Critical). When an AI Agent Removes a Masking Policy walks through that shape, where an agent removed a policy while trying to fix a broken pipeline.

Cloud credentials hardcoded in DDL

Confidentiality — credentials

CREATE STAGE finance_export
    URL = 's3://acme-finance-exports/'
    CREDENTIALS = (AWS_KEY_ID = 'AKIAIOSFODNN7EXAMPLE'
                   AWS_SECRET_KEY = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY');

This CREATE STAGE hardcodes an AWS key pair in its CREDENTIALS clause. A secret scanner usually catches the AKIA access-key ID by its prefix. The secret key beside it has no prefix to match on, so prefix-based scanning misses it. Lexega has parsed the statement and knows that literal sits in the AWS_SECRET_KEY position of a stage's credentials, so the position alone is enough to flag it. The same covers credential slots in COPY INTO, ALTER STAGE, and Redshift COPY/UNLOAD:

[CRITICAL] CRED-AWS-LEAK — Hardcoded AWS access key detected (AKIA.../ASIA...).
Never commit credentials to source code. Use secure parameter passing, secrets
managers, or storage integrations instead.
[CRITICAL] CRED-PWD-LEAK — Hardcoded password detected in SQL. Use secure
parameter passing (e.g., :password_param) or secrets management instead of
literal passwords.

The DELETE with a WHERE clause that still wipes the table

Integrity — destructive writes

DELETE FROM login_audit
WHERE retention_class = 'expired'
   OR retention_class <> 'expired'
   OR retention_class IS NULL;

This DELETE has three OR branches: = 'expired', <> 'expired', and IS NULL. Between them they cover every possible value of retention_class, so the WHERE matches every row and the table is emptied. A bare DELETE with no WHERE is a known enough footgun that some linters check for it, and WHERE 1 = 1 is the known way past that check. But the check is really for whether a WHERE node is present, and this statement has one, built from real column names and three conditions that each look reasonable on their own.

Lexega evaluates what the predicate actually matches:

[CRITICAL] DML-WRITE-UNBOUNDED — Unbounded write operation detected - the
statement has no WHERE clause or its WHERE clause matches every row. This
affects ALL rows in the target table(s).
[MEDIUM]   Q-PRED-TAUTOLOGY — Tautological predicate: condition is always true
for non-NULL values (e.g., WHERE x=1 OR x<>1). The WHERE clause filters at most
NULL rows.

Same NULL discipline as everywhere else here: delete the IS NULL branch and the Critical goes away, because rows where retention_class is NULL would now survive, so the predicate no longer covers every row, leaving just the Medium. It's the same three-valued logic that made the NOT IN delete nothing, pointed the other way.

Unguarded DROP TABLE (TBL-DROP) and TRUNCATE (TBL-TRUNCATE) sit in the same bucket. On an audit or log table, a stray "cleanup" that empties it is what a regulator would call spoliation.

Run both

Run SQLFluff across every example above and it does its job: an unindented line here, an uppercase identifier there, nothing about the risk in any of them. Catching that takes analysis built on what the SQL means: taint into dynamic SQL, what a policy body returns, how NULLs move through a predicate, who a grant actually reaches. Lexega runs 600+ rules on that across Snowflake, PostgreSQL, BigQuery, MSSQL, Databricks, and Redshift. It doesn't replace the linter; run it next to one as a separate gate:

- run: sqlfluff lint models/                                # style gate
- run: lexega-sql review main..HEAD models/ -r --pr-comment # risk gate

Try it, including the SQL you think is safe

Paste any example above into the Lexega Playground and the signals fire. The better test is making them stop: add WHERE user_id IS NOT NULL to the NOT IN subquery, handle the NULL side of the LEFT JOIN, bind the dynamic SQL through USING. When the SQL is actually safe, the findings go away. Then try one of your own migrations.

To run it locally:

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

Get a free trial key. Instant activation, no account required. For how signals, rules, and policy decisions fit together, see How Lexega Turns SQL Into Signals.