June 2026
SQL Security Checks Your Linter Doesn't Run
Six classes of SQL risk that SQLFluff and TSQLLint aren't looking for
Here's a masking policy that passes every inventory check and still hands every email address to everyone:
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 returns val. The policy is attached, it has a name, and an audit will happily report the column as masked, when in fact it masks nothing: every row's real value goes to every role.
A linter won't catch this, and I wouldn't ask it to. SQLFluff and TSQLLint check how the SQL is written (layout, naming, dialect hygiene), and they're good at it, so keep them. They do a bit more than layout, too. They'll catch an inconsistent operator, an unused CTE, even a column you've referenced from a table that isn't in your FROM. But all of that is still about how the SQL reads, not what it does once it runs. They can't tell you:
- whether a masking policy actually masks
- whether dynamic SQL is injectable
- whether a nullable join is quietly dropping rows
- whether a migration just deleted a control
A linter is really answering one question: is this SQL shaped correctly? That's worth answering, but it's a different question from the one I care about here. Lexega asks whether the SQL can expose data, weaken a control, misstate a result, or destroy records. Those aren't competing checks. You want both.
Catching the second kind takes a different category of tool, not a stricter linter. A linter reads the SQL as text. Lexega reads what it means, and flags the risk before the statement ever runs. That takes more than one kind of analysis. Sometimes it means following a value, into the string a procedure builds at runtime or through the rows a join keeps or drops. Other times it means reading a policy's body to see whether it actually masks, working out who a grant really reaches, or figuring out what a WHERE really matches. Below are five of those failures, grouped on three axes (control, confidentiality, integrity). Each one is SQL a linter is happy with, next to what Lexega catches.
A masking policy that masks nothing
Confidentiality — exposure
The intro already showed the policy. Lexega flags it because it reads what each branch of the CASE actually returns, finds that every path hands back the raw val, and concludes the policy never masks anything. A linter sees a well-formed policy and moves on.
[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.
Paste it into the Lexega Playground and the signal fires. The same exposure shows up from the data side, too: give Lexega a catalog and Q-FLOW-TAINT follows the column through lineage and fires wherever it lands in an output.
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 user_name parameter gets concatenated straight into the statement text with ||, so whoever controls user_name controls the DELETE that runs. It's textbook injection that happens to live inside a stored procedure instead of an app. The fix is to pass the value through EXECUTE IMMEDIATE ... USING, so it's bound at execution instead of 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).
[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.
Two more signals fire here at High: DYNSQL and PROC-DYNSQL, the statement-level and procedure-level view of the same vector. The two above are the ones carrying the specific guidance.
A SAST tool that taint-tracks SQL assembled inside application code will catch the app-layer version of this. But SQL that ships as SQL (stored procedures, migrations, dbt models) is mostly a blind spot for those tools, since there's no host language 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 just cosmetic 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';
The LEFT JOIN is there to keep every transaction, including the ones with no matching account. Then the WHERE filters on a.risk_rating. For any row with no match, a.risk_rating is NULL, so NULL = 'high' is never true and those rows drop. The LEFT JOIN quietly collapses into the INNER JOIN nobody wrote. The rows that vanish are exactly the ones with no known account, which in a fraud or AML control are often the ones that matter most. There's no error here, just silent data loss.
And to be fair, SQLFluff gets closer here than you'd think. It has a NULL rule (CV05) that turns = NULL into IS NULL, and a join rule (AM05) that wants an explicit INNER JOIN instead of a bare JOIN. Neither one fires, though. CV05 is looking for a literal = NULL in the text, and this code says a.risk_rating = 'high', so there's nothing for it to rewrite. AM05 has no complaint either, since the join type is already spelled out. The problem isn't anything you can spot in any one token. It's that a.risk_rating is nullable in the first place because of the LEFT JOIN, and comparing a nullable column with = quietly drops the NULL rows. That's what Lexega follows: it knows the column can be NULL because of the LEFT JOIN, and that the WHERE then filters those rows back out.
[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, again with no error but the wrong outcome. A token-cleanup job written this way stops revoking the day a NULL shows up in the employee list, and stays broken until someone goes looking.
The same shape breaks access reviews: a query meant to list users "who have access but shouldn't," written with NOT IN, comes back empty the moment the list contains a NULL. An empty result looks exactly like "no one has improper access," when really the check has stopped working.
[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.
Lexega fires Q-NULL-NOTIN because it knows a column can be NULL and what one NULL does inside 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 privilege on the database to every user in the account (PUBLIC includes everyone, automatically). It's a single line in a migration diff, easy to wave through in review, and the day it lands in production the whole database is open.
[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 almost grep for this exact statement. But the less obvious shapes slip right past a grep: ownership transfers (GRT-OWNER-XFER) and broad role-scoped grants (GRT-BROAD-PRIV) widen access just as far without ever naming PUBLIC.
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 value of retention_class, so the WHERE matches every row and the table is emptied. A bare DELETE with no WHERE is a familiar enough footgun that some linters check for it, and WHERE 1 = 1 is the classic way around that check. But all the check really asks is whether a WHERE clause is present. And this one is, 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 in this post: drop the IS NULL branch and the Critical goes away. Rows where retention_class is NULL now survive, so the predicate no longer covers every row, and you're left with just the Medium. It's the same 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.
A signal isn't a verdict
Every signal in this post is raised unconditionally. The analyzer reports what's true about the SQL (the same result every run) and it doesn't decide your merge. That decision is a separate layer, and it's entirely yours.
Linters come by their false-positive reputation honestly. Style is judgment calls all the way down, and the standard escape hatch is an inline -- noqa on the line under review. That's fine for a linter. For risk analysis, that escape hatch is exactly the thing you need to keep control of.
Lexega keeps those two decisions apart. A policy maps each rule to block, warn, or allow, and it can vary by environment, so the same signal blocks a production migration and just annotates a feature branch:
policies:
- rule_id: DML-WRITE-UNBOUNDED
action: block
envs: ["prod"]
requires_exception: true # blocked unless a reviewed exception exists
- rule_id: GRT-TO-PUBLIC
action: warn # surfaced everywhere, blocks nothing
When a block is a genuine false positive (or a real risk someone has reviewed and accepted), the override is an exception, not a comment buried in the SQL. It carries the rule, a reason, a ticket, who approved it, an optional expiry, and the scope it applies to:
exceptions:
- exception_id: EX-2026-014
policy_id: prod-data-safety
rule_id: DML-WRITE-UNBOUNDED
approved_by: data-governance@acme.com
approved_at: "2026-06-05T00:00:00Z"
ticket: DATA-1234
reason: "One-time backfill, reviewed in DATA-1234"
scope:
scoped:
path_prefixes: ["migrations/2026_06_backfill/"]
expires_at: "2026-07-01T00:00:00Z"
Suppression still exists, because every gate needs it. But it lives in a reviewed artifact with an owner, a scope, and an expiry, not an inline comment dropped by whoever's trying to clear CI. And if you do want a blanket exception, you have to write it as one deliberately, instead of letting it slip in by accident. Every decision gets recorded, too: which signals fired, which exceptions were used, against which bundle. An exception becomes a line in the audit trail instead of a silent edit. So you get the controls you need to manage the noise, without leaving anyone a quiet way around the gate.
Run both
Run SQLFluff across every example above and it does exactly what it should: tidies the layout, maybe flags an inconsistent operator or a column that isn't in your FROM, and says nothing about the risk in any of them, because that was never its job. The risk gate is Lexega's semantic analysis plus your definition of risk. Run both, side by side in CI:
- run: sqlfluff lint models/ # style gate
- run: lexega-sql review main..HEAD models/ -r --pr-comment # risk gate
For how signals, rules, and policy decisions fit together behind that second gate, see How Lexega Turns SQL Into Signals.
Try it, including the SQL you think is safe
1. Try the examples in the playground. 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 genuinely safe, the signals go quiet. Then point it at one of your own migrations.
2. Get a free trial key. 30 days, full functionality, no credit card, no account. Instant activation.
3. Run it locally and in CI.
curl -fsSL https://lexega.com/install.sh | sh
export LEXEGA_LICENSE_KEY=<your-key>
lexega-sql analyze --dialect snowflake migration.sql --min-severity medium