Metadata-Version: 2.5
Name: sql-sop
Version: 0.11.0
Summary: Fast rule-based SQL linter. Pre-commit hook + GitHub Action + CLI.
Project-URL: Homepage, https://github.com/Pawansingh3889/sql-sop
Project-URL: Source, https://github.com/Pawansingh3889/sql-sop
Project-URL: Issues, https://github.com/Pawansingh3889/sql-sop/issues
Project-URL: Changelog, https://github.com/Pawansingh3889/sql-sop/blob/main/CHANGELOG.md
Project-URL: Documentation, https://github.com/Pawansingh3889/sql-sop#readme
Author: Pawan Singh Kapkoti
License-Expression: MIT
License-File: LICENSE
License-File: NOTICE
Keywords: dbt,devtools,github-action,linter,pre-commit,sarif,sql,sql-server,static-analysis
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Quality Assurance
Requires-Python: >=3.10
Requires-Dist: pyyaml>=6.0
Requires-Dist: rich>=13.0
Requires-Dist: sqlparse>=0.5.0
Requires-Dist: typer>=0.12
Provides-Extra: dev
Requires-Dist: libcst>=1.1.0; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.4; extra == 'dev'
Requires-Dist: sqlalchemy>=2.0; extra == 'dev'
Provides-Extra: python
Requires-Dist: libcst>=1.1.0; extra == 'python'
Provides-Extra: snapshot
Requires-Dist: sqlalchemy>=2.0; extra == 'snapshot'
Provides-Extra: structural
Description-Content-Type: text/markdown

# sql-sop

[![CI](https://github.com/Pawansingh3889/sql-sop/actions/workflows/ci.yml/badge.svg)](https://github.com/Pawansingh3889/sql-sop/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/sql-sop)](https://pypi.org/project/sql-sop/)
[![Python](https://img.shields.io/pypi/pyversions/sql-sop)](https://pypi.org/project/sql-sop/)
[![License](https://img.shields.io/badge/license-MIT-blue)](LICENSE)
[![Discord](https://img.shields.io/badge/discord-join-5865F2?logo=discord&logoColor=white)](https://discord.gg/gBr77yYPkD)
[![Marketplace](https://img.shields.io/badge/GitHub%20Marketplace-sql--sop-a07aff?logo=githubactions&logoColor=white)](https://github.com/marketplace/actions/sql-sop)
[![Playground](https://img.shields.io/badge/playground-try%20online-a07aff)](https://pawansingh3889.github.io/sql-sop/)
[![Downloads](https://img.shields.io/pypi/dm/sql-sop?color=a07aff)](https://pypi.org/project/sql-sop/)
[![codecov](https://codecov.io/gh/Pawansingh3889/sql-sop/branch/main/graph/badge.svg)](https://codecov.io/gh/Pawansingh3889/sql-sop)

**Contributing:** [`CONTRIBUTING.md`](CONTRIBUTING.md) · [`ROADMAP.md`](ROADMAP.md) · [`GOVERNANCE.md`](GOVERNANCE.md) · [`CODE_OF_CONDUCT.md`](CODE_OF_CONDUCT.md) · [`SECURITY.md`](SECURITY.md) · [`NOTICE`](NOTICE)

## Related projects

- [Governed Agent Stack](https://github.com/Pawansingh3889/governed-agent-stack) brings together on-prem tools for auditable database agents; sql-sop is a standalone linter that can complement that workflow.
- [sql-steward](https://github.com/Pawansingh3889/sql-steward) governs SQL generated by agents through a semantic layer and audit trail, complementing sql-sop's checks on SQL written by people.
- [sql-explorer-mcp](https://github.com/Pawansingh3889/sql-explorer-mcp) provides read-only database exploration over MCP and uses sql-sop to reject dangerous queries before execution.

The dbt-aware rule pack (DBT001+) extends sql-sop into dbt projects. See the [ADR](https://github.com/Pawansingh3889/sql-sop/issues?q=is%3Aissue+label%3AADR) for the broader roadmap.

## Why Does This Exist?

One bad SQL query can delete production data, expose customer records, or bring down a database. Most teams only find out after the damage is done. sql-sop catches dangerous patterns automatically, before the query ever runs, in 0.08 seconds.

### Key Numbers

| | |
|---|---|
| Rules | 48 (9 errors, 25 warnings, 3 structural, 6 T-SQL, 5 Python-source); 53 with `--contract`, 55 with `--dbt` |
| Tests | 445 |
| Scan speed | 0.08s across 200 files |
| PyPI installs | 2,000+ (mirrors excluded) |
| Version | 0.11.0 |

### Fluent API (v0.2.0)

```python
from sql_guard import SqlGuard

result = SqlGuard().enable("E001", "W001").scan("DELETE FROM users")
print(result.passed)    # False
print(result.summary()) # "1 error, 0 warnings in 1 statement"
```

---

48 rules, plus an optional Contracts pack (5 schema-aware rules) under `--contract path.yml` and a dbt pack (7 rules) under `--dbt`. Inline disable, project config, git-changed-only mode, and SARIF output for GitHub Code Scanning. Runs as a **CLI tool**, **pre-commit hook**, and **GitHub Action**.

Want the same rules inside your AI assistant? [sql-sop-mcp](https://github.com/Pawansingh3889/sql-sop-mcp) exposes them over MCP, so the model is told a query is unsafe before it suggests it.

---

## Quick start

**If sql-sop catches a real bug for you, a GitHub star is the easiest way
to help. It makes the project more discoverable for people with the same
problem.**

```bash
pip install sql-sop
sql-sop check .

# Also scan .py files for SQL hazards in execute()/read_sql() calls:
pip install "sql-sop[python]"
sql-sop check . --include-python
```

```
queries/create_orders.sql
  L3:  ERROR [E001] DELETE without WHERE clause -- this will delete all rows
         -> Add a WHERE clause to limit affected rows
  L7:  WARN  [W001] SELECT * -- specify columns explicitly
         -> Replace with: SELECT col1, col2, col3 FROM ...

Found 2 issues (1 error, 1 warning) in 1 file (0.001s)
```

## Where sql-sop runs

Most teams have **no SQL review process** at all. sql-sop gives you one in two places, both driven by the same rules:

- **Pre-commit** -- runs in under 0.2s on the files you changed, so a bad `DELETE FROM users` is blocked before it is ever committed.
- **CI** -- runs on every pull request and uploads SARIF, so findings appear inline in the GitHub "Files changed" view through Code Scanning.

Same engine in both, no AI, no API keys, nothing to host. Fast enough to sit in the commit path, strict enough to gate a PR.

---

## Set up (5 minutes)

### Step 1: Pre-commit hook

```yaml
# .pre-commit-config.yaml
repos:
  - repo: https://github.com/Pawansingh3889/sql-sop
    rev: v0.11.0
    hooks:
      - id: sql-sop
        args: [--severity, error]  # only block on errors locally
```

```bash
pip install pre-commit
pre-commit install
```

Now every `git commit` with `.sql` changes runs sql-sop automatically. Errors block the commit. Warnings are shown but don't block.

### Step 2: GitHub Actions

```yaml
# .github/workflows/sql-quality.yml
name: SQL Quality
on:
  pull_request:
    paths: ['**/*.sql']

permissions:
  contents: read
  pull-requests: write

jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: Pawansingh3889/sql-sop@v1
        with:
          severity: warning
```

That's it. Every SQL change gets an instant, rule-based lint on the PR.

### Step 3 (optional): CLI for manual checks

`sql-sop list-rules` prints every registered rule. See [Configuration](#configuration) for the flags that change what is reported.

---

## Rules

### Errors (block commit by default)

| ID | Name | What it catches |
|---|---|---|
| E001 | `delete-without-where` | `DELETE FROM orders;` -- deletes all rows |
| E002 | `drop-without-if-exists` | `DROP TABLE users;` -- fails if table missing |
| E003 | `grant-revoke` | `GRANT SELECT ON users TO public;` -- privilege escalation |
| E004 | `string-concat-in-where` | `WHERE id = '' + @input` -- SQL injection |
| E005 | `insert-without-columns` | `INSERT INTO t VALUES (...)` -- breaks on schema change |
| E006 | `update-without-where` | `UPDATE orders SET status = 'x';` -- overwrites every row |
| E007 | `alter-add-not-null-no-default` | `ALTER TABLE t ADD c INT NOT NULL;` -- locks table for full rewrite |
| E008 | `drop-column` | `ALTER TABLE t DROP COLUMN c;` -- irreversible, breaks subscribers |
| E009 | `update-from-without-join` | `UPDATE a SET x = b.x FROM a, b` -- comma-separated FROM silently makes a Cartesian product |

### Warnings (advisory by default)

| ID | Name | What it catches |
|---|---|---|
| W001 | `select-star` | `SELECT * FROM users` -- pulls unnecessary columns |
| W002 | `missing-limit` | Unbounded SELECT -- could return millions of rows |
| W003 | `function-on-column` | `WHERE YEAR(date) = 2024` -- kills index usage |
| W004 | `missing-alias` | JOIN without table aliases -- hard to read |
| W005 | `subquery-in-where` | `WHERE x IN (SELECT ...)` -- often slower than JOIN |
| W006 | `orderby-without-limit` | ORDER BY without LIMIT -- sorts entire result |
| W007 | `hardcoded-values` | `WHERE amount > 10000` -- use parameters |
| W008 | `mixed-case-keywords` | `select ... FROM` -- inconsistent casing |
| W009 | `missing-semicolon` | Statement not terminated with `;` |
| W010 | `commented-out-code` | `-- SELECT * FROM old_table` -- use version control |
| W011 | `union-without-all` | `UNION` between disjoint sets -- forces a deduplication sort, `UNION ALL` is faster when uniqueness is guaranteed |
| W012 | `group-by-ordinal` | `GROUP BY 1, 2` -- fragile to SELECT-list reorders |
| W013 | `window-missing-partition` | `OVER ()` -- unpredictable results and unclear intent |
| W014 | `case-without-else` | `CASE WHEN ... THEN ... END` -- unmatched rows return NULL |
| W015 | `join-function-on-column` | `JOIN customers c ON UPPER(o.email) = UPPER(c.email)` -- kills index seek |
| W016 | `not-in-with-subquery` | `WHERE id NOT IN (SELECT ...)` -- silently returns 0 rows on NULL |
| W017 | `leading-wildcard-like` | `WHERE name LIKE '%smith'` -- non-SARGable, full scan |
| W018 | `or-across-columns` | `WHERE a = 1 OR b = 2` -- defeats single-column indexes |
| W019 | `count-distinct-unbounded` | `COUNT(DISTINCT col)` with no WHERE / GROUP BY / LIMIT -- full sort + distinct over the whole table |
| W020 | `truncate-table` | `TRUNCATE TABLE staging;` -- bypasses triggers, resets identity |
| W021 | `having-without-group-by` | `HAVING status = 'x'` with no GROUP BY -- legal, but usually a misplaced WHERE |
| W022 | `cross-join-explicit` | `FROM products CROSS JOIN regions` -- Cartesian product, confirm intent |
| W023 | `scalar-udf-in-where` | `WHERE dbo.fn_X(col) = 1` -- row-by-row predicate evaluation |
| W024 | `select-distinct-suspicious` | `SELECT DISTINCT a, b FROM x JOIN y ON ...` -- DISTINCT often masks a missing join condition or GROUP BY |
| W025 | `assertion-malformed` | `-- @assert: <predicate>` comment whose predicate does not match the sql-sop grammar (`row_count <op> <int>`, `unique(<col>)`, `not_null(<col>)`, `<col> <op> <literal>`) |


### Structural (v0.3.0+, sqlparse-based)

Rules that need an AST view of the statement, parsed via sqlparse. Catch
issues that single-line regex matching cannot reliably see.

| ID | Name | What it catches |
|---|---|---|
| S001 | `implicit-cross-join` | `JOIN customers` with no `ON` / `USING` -- accidental Cartesian product |
| S002 | `deeply-nested-subquery` | Subqueries more than 2 levels deep -- typically a refactor opportunity |
| S003 | `unused-cte` | `WITH x AS (...)` defined but never referenced |

### T-SQL (v0.5.0+)

Rules targeting SQL Server anti-patterns common in legacy stored procs
and SSRS datasets. Fire on text patterns that do not appear in BigQuery
or Postgres code, so they run unconditionally with near-zero false
positives on non-T-SQL input.

| ID | Name | What it catches |
|---|---|---|
| T001 | `with-nolock` | `SELECT * FROM t WITH (NOLOCK)` -- dirty reads |
| T002 | `xp-cmdshell` | `EXEC xp_cmdshell ...` -- shell-exec surface |
| T003 | `cursor-declaration` | `DECLARE c CURSOR FOR ...` -- row-by-row processing |
| T004 | `deprecated-outer-join` | `WHERE a.x *= b.y` -- removed in SQL Server 2012+ |
| T005 | `create-index-without-online` | `CREATE INDEX ix ON t (...)` -- locks table; add `WITH (ONLINE = ON)` |
| T006 | `select-into-without-typed-fields` | `SELECT * INTO target FROM source` -- destination schema is inferred at runtime |

### Contracts (opt-in via `--contract`)

Pass `--contract path/to/contract.yml` (or set `contract:` in
`.sql-guard.yml`) to lint queries against the expected schema. Without a
contract these rules are silent. Format is a thin subset of the open
data-contract space; see `tests/fixtures/contract_sample.yml` for a
working example.

| ID | Name | What it catches |
|---|---|---|
| C001 | `column-not-in-contract` | `SELECT o.bogus FROM orders o` -- column not declared for that table |
| C002 | `table-not-in-contract` | `SELECT * FROM ghost_table` -- table absent from the contract |
| C003 | `not-null-violation` | `INSERT INTO orders (id) VALUES (1)` -- omits a NOT NULL column |
| C004 | `primary-key-missing-on-insert` | INSERT omits a PK column with no default |
| C005 | `unmapped-fk` | `JOIN ... ON o.id = c.id` -- columns have no FK relationship in the contract |

Two helper subcommands round out the workflow:

```bash
# Bootstrap a contract from an existing database (requires sql-sop[snapshot]):
sql-sop schema-snapshot \
  --dsn "mssql+pyodbc://user:pass@host/db?driver=ODBC+Driver+18+for+SQL+Server" \
  --output contract.yml

# Validate a contract YAML structure before running rules (CI-friendly):
sql-sop validate-contract --contract contract.yml
```

### Python scanning (v0.4.0+, opt-in)

Enable with `pip install "sql-sop[python]"` and `--include-python`. Uses
libCST to walk Python source and extract SQL strings from `.execute()`,
`.read_sql()`, `sqlalchemy.text(...)` calls and `sql =`/`query =` style
assignments. Then applies every rule above, plus five that only make
sense at the Python level:

| ID | Name | What it catches |
|---|---|---|
| P001 | `fstring-in-execute` | `cursor.execute(f"... {user_input}")` -- SQL injection |
| P002 | `concat-in-execute` | `cursor.execute("..." + user_input)` -- SQL injection |
| P003 | `format-in-execute` | `.format()` or `%` interpolation into an execute call |
| P004 | `bare-variable-in-execute` | `cursor.execute(query)` where `query` is an unchecked variable |
| P005 | `sqlalchemy-text-fstring` | `sqlalchemy.text(f"... {var}")` -- SQL injection on the SQLAlchemy text() surface |

### dbt (v0.10.0+, opt-in via `--dbt`)

Pass `--dbt` to activate the dbt-aware rule pack. sql-sop walks up from
the first checked path to find `dbt_project.yml` and reads `schema.yml`
at lint time; with no project found the pack stays silent, so existing
users see no behaviour change. Rules only fire on `.sql` files inside
the project's `model-paths` -- macros, analyses, seeds and snapshots are
skipped.

| ID | Name | What it catches |
|---|---|---|
| DBT001 | `model-without-test` | Model absent from `schema.yml`, or declared with neither `tests:` nor `data_tests:` |
| DBT002 | `direct-table-ref` | Raw table name in FROM/JOIN instead of `ref()` / `source()` -- breaks lineage |
| DBT003 | `incremental-without-unique-key` | `materialized='incremental'` with no `unique_key`. Error on `merge` / `delete+insert` (silent duplicate rows), warning when the strategy is unset, silent on `append` / `insert_overwrite` |
| DBT004 | `hook-with-ddl` | `pre_hook` / `post_hook` containing destructive or structural DDL |
| DBT005 | `select-star-in-mart` | `SELECT *` in a mart model -- downstream contracts drift silently |
| DBT006 | `model-without-description` | Model has no `description:` in `schema.yml` |
| DBT007 | `unquoted-var-interpolation` | `{{ var(...) }}` interpolated into SQL without surrounding quotes -- breaks the day the var holds whitespace or a quote |

```bash
sql-sop check models/ --dbt
```

---

## Configuration

### Disable specific rules

```bash
sql-sop check . --disable E002 W008 W010
```

### Project config file (`.sql-guard.yml`)

Drop a `.sql-guard.yml` (or `.sql-guard.yaml`) at the repo root. The loader walks up from the current directory; CLI flags merge with and override these settings.

```yaml
disable:
  - W005
  - T001
ignore:
  - migrations/legacy/
  - vendor/
include_python: true
severity: warning
```

### Inline disable comments

Silence a known false positive on a single line, no project-wide override needed:

```sql
SELECT * FROM lookups; -- sql-guard: disable=W001
SELECT * FROM users  -- sql-guard: disable=W001,W002
WHERE name LIKE '%smith';

-- sql-guard: disable-next-line=W017
SELECT * FROM events WHERE name LIKE '%checkout';
```

A bare `-- sql-guard: disable` (no equals sign) silences every rule on the line. The same directives work in Python with `#` instead of `--`.

### Lint only changed files

For pre-commit and CI on big repos:

```bash
sql-sop check . --changed-only                      # working tree
sql-sop check . --changed-only --changed-base main  # vs a branch ref
```

Falls back to a full scan with a warning when not in a git repo.

### Severity filtering

```bash
sql-sop check . --severity error    # only show errors
sql-sop check . --severity warning  # show everything (default)
```

### Fail fast

```bash
sql-sop check . --fail-fast  # stop after first error found
```

### SARIF output for GitHub Code Scanning

Render findings inline on PRs in the GitHub Files Changed view:

```bash
sql-sop check . --format sarif --output results.sarif
```

In a GitHub Actions workflow:

```yaml
- run: sql-sop check . --format sarif --output sql-guard.sarif
- uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: sql-guard.sarif
```

---

## Performance

sql-sop is designed to be fast:

- **Compiled regex** -- patterns compiled once at startup, reused per file
- **Two-pass scanning** -- single-line rules run first (16 of 43 SQL rules), multi-line parsing only when needed
- **Line-by-line streaming** -- files read line by line, not loaded entirely into memory
- **Early exit** -- `--fail-fast` stops on first error

```
Benchmark: 200 SQL files, 20 SQL rules
  sql-guard:  0.08 seconds
  sqlfluff:   45 seconds (560x slower)
```

---

## Production Use Case

In a regulated data environment, sql-sop runs as a pre-commit hook on all SQL that touches the database. Combined with read-only database users and container isolation, it forms part of a layered safety setup that prevents accidental writes to production.

---

## How it compares

| | sql-sop | sqlfluff | sql-lint |
|---|---|---|---|
| Rules | 48 (focused) | 800+ (comprehensive) | ~20 |
| Speed | <0.1s for 200 files | 45s for 200 files | ~2s |
| Config needed | Zero | Extensive | Minimal |
| Language | Python | Python | JavaScript |
| Pre-commit | Yes | Yes | No |
| GitHub Action | Yes | Community | No |
| AI integration | MCP server (sql-sop-mcp) | No | No |

sql-sop is not a replacement for sqlfluff. It's a fast first pass that catches 80% of real issues with zero setup. If you need dialect-specific formatting and 800 rules, use sqlfluff. If you want instant feedback on dangerous SQL, use sql-sop.

---

## Contributing

```bash
git clone https://github.com/Pawansingh3889/sql-sop.git
cd sql-sop
pip install -e ".[dev]"
pytest
```

### Adding a new rule

1. Create a class in `sql_guard/rules/errors.py` or `warnings.py`
2. Inherit from `Rule`, set `id`, `name`, `severity`, `description`
3. Override `check_line()` for single-line rules or `check_statement()` for multi-line
4. Add to `ALL_RULES` in `sql_guard/rules/__init__.py`
5. Add a test in `tests/test_rules.py`
6. Add a trigger case in `tests/fixtures/`

```python
class MyNewRule(Rule):
    id = "W011"
    name = "my-rule"
    severity = "warning"
    description = "What this rule catches"
    multiline = False

    _pattern = Rule._compile(r"your regex here")

    def check_line(self, line, line_number, file):
        if self._pattern.search(line):
            return Finding(
                rule_id=self.id,
                severity=self.severity,
                file=file,
                line=line_number,
                message="What went wrong",
                suggestion="How to fix it",
            )
        return None
```

PRs welcome. Keep rules simple, keep patterns fast.

---

## Contributors

Thank you to the people who have shipped rules and code to sql-sop.

| Contributor | Contribution |
|---|---|
| [@tmchow](https://github.com/tmchow) | [W011 `union-without-all`](https://github.com/Pawansingh3889/sql-sop/pull/12). Flags `UNION` where `UNION ALL` would be safe and faster. |
| [@tmchow](https://github.com/tmchow) | [P005 `sqlalchemy-text-fstring`](https://github.com/Pawansingh3889/sql-sop/pull/25). Catches `sqlalchemy.text(f"...{var}")` patterns that defeat parameter binding. |
| [@mvanhorn](https://github.com/mvanhorn) | [W019 `count-distinct-unbounded`](https://github.com/Pawansingh3889/sql-sop/pull/29). Flags `COUNT(DISTINCT col)` without WHERE, GROUP BY, or LIMIT. |
| [@mvanhorn](https://github.com/mvanhorn) | [W015 `join-function-on-column`](https://github.com/Pawansingh3889/sql-sop/pull/33). JOIN-side companion to W003. Flags function calls wrapping columns inside `JOIN ... ON` predicates. |
| [@mvanhorn](https://github.com/mvanhorn) | [W023 `scalar-udf-in-where`](https://github.com/Pawansingh3889/sql-sop/pull/34). Flags schema-qualified scalar UDF calls inside WHERE, HAVING, and ON predicates. |
| [@Prabhu-1409](https://github.com/Prabhu-1409) | [W013 `window-without-partition`](https://github.com/Pawansingh3889/sql-sop/pull/21). Flags `OVER ()` without `PARTITION BY`, dialect-aware messaging for Postgres and Redshift. |
| [@hellozzm](https://github.com/hellozzm) | [W014 `case-without-else`](https://github.com/Pawansingh3889/sql-sop/pull/32). Walks `CASE`/`END` token-by-token; catches outer `CASE` without `ELSE` even when an inner `CASE` does have one. |
| [@vibeyclaw](https://github.com/vibeyclaw) | [W022 `cross-join-explicit`](https://github.com/Pawansingh3889/sql-sop/pull/31). Flags explicit `CROSS JOIN`. Strips trailing line comments before matching to avoid false positives on commentary. |

See [the full contributors graph](https://github.com/Pawansingh3889/sql-sop/graphs/contributors) on GitHub.

Want to add your name here? Pick a [`good first issue`](https://github.com/Pawansingh3889/sql-sop/labels/good%20first%20issue), follow [`CONTRIBUTING.md`](CONTRIBUTING.md), and check the [roadmap](ROADMAP.md) for the next batch of rules.

---

## License

MIT
