Metadata-Version: 2.5
Name: sqlguard-ninja
Version: 0.1.1
Summary: Security testing, policy enforcement, agent integration, privacy analysis and CI benchmarking for LLM-generated SQL.
Project-URL: Homepage, https://github.com/Pratik653/SQLGuard_Ninjapy
Project-URL: Documentation, https://github.com/Pratik653/SQLGuard_Ninjapy/tree/main/docs
Project-URL: Issues, https://github.com/Pratik653/SQLGuard_Ninjapy/issues
Author: SQLGuard Ninja contributors
License-Expression: Apache-2.0
License-File: LICENSE
License-File: THIRD_PARTY_NOTICES.md
Keywords: benchmark,guardrail,langchain,langgraph,llm,privacy,security,sql,sqlglot,text-to-sql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Security
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: pydantic<3.0,>=2.6
Requires-Dist: pyyaml>=6.0
Requires-Dist: sqlglot<31.0,>=30.0
Provides-Extra: all
Requires-Dist: fastapi>=0.110; extra == 'all'
Requires-Dist: langchain-core>=0.3; extra == 'all'
Requires-Dist: langgraph>=0.2; extra == 'all'
Requires-Dist: psycopg[binary]>=3.1; extra == 'all'
Provides-Extra: dev
Requires-Dist: build>=1.2; extra == 'dev'
Requires-Dist: fastapi>=0.110; extra == 'dev'
Requires-Dist: httpx>=0.27; extra == 'dev'
Requires-Dist: hypothesis>=6.100; extra == 'dev'
Requires-Dist: langchain-core>=0.3; extra == 'dev'
Requires-Dist: langgraph>=0.2; extra == 'dev'
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: psycopg[binary]>=3.1; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Requires-Dist: types-pyyaml; extra == 'dev'
Provides-Extra: fastapi
Requires-Dist: fastapi>=0.110; extra == 'fastapi'
Provides-Extra: langchain
Requires-Dist: langchain-core>=0.3; extra == 'langchain'
Provides-Extra: langgraph
Requires-Dist: langchain-core>=0.3; extra == 'langgraph'
Requires-Dist: langgraph>=0.2; extra == 'langgraph'
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3.1; extra == 'postgres'
Description-Content-Type: text/markdown

# SQLGuard Ninja

**Security testing, policy enforcement, agent integration, privacy analysis and CI
benchmarking for LLM-generated SQL.**

[![CI](https://github.com/Pratik653/SQLGuard_Ninjapy/actions/workflows/ci.yml/badge.svg)](https://github.com/Pratik653/SQLGuard_Ninjapy/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/sqlguard-ninja)](https://pypi.org/project/sqlguard-ninja/)
![Python](https://img.shields.io/badge/python-3.11%20%7C%203.12%20%7C%203.13-blue)
![License](https://img.shields.io/badge/license-Apache--2.0-green)

When an LLM writes SQL, the system prompt is not a security boundary. SQLGuard Ninja sits
between the model and the database. It checks every statement against a declared policy for
the *authenticated* caller: which tables and columns a role may use, which data classes may
appear only in aggregates or only after human approval, and which tenant the caller belongs
to. It records an audit trail and gives the agent an explanation it can act on.

It ships with a labelled benchmark corpus (530 cases, 1,002 dialect evaluations) and a
regression gate for CI. The same benchmark harness can evaluate other open-source guards
side by side.

> **One layer, not the only one.** Static SQL inspection cannot guarantee database security.
> Use a read-only least-privilege role, row-level security and statement timeouts as well.
> See the [threat model](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/security/THREAT_MODEL.md).

## What it does

| Workstream | What you get | Status |
|---|---|---|
| **Policy engine** | Read-only enforcement, table/column grants, tenant isolation, function and catalog rules, structural limits. Built on [sqlglot](https://github.com/tobymao/sqlglot). Fails closed when analysis is incomplete. | PostgreSQL + SQLite tested |
| **Privacy pack** | Explicit classification labels, `deny` / `aggregate_only` / `review` per role, JSON-path labels, small-cohort review, minimisation hints and alternatives | implemented |
| **Agent integrations** | `SQLCheckpoint` with block / review / audit modes, hash-bound single-use approvals, retry lock-out, redacted audit events. LangChain tool, LangGraph nodes, FastAPI router | implemented |
| **Benchmark engine** | Versioned corpus, metrics (recall, precision, FPR, FNR, exact match, per category/dialect, latency percentiles), adapters for native, callable and isolated external validators, JSON and Markdown reports | implemented |
| **Query cost** | Explainable static score; optional PostgreSQL `EXPLAIN (FORMAT JSON)` inspection, gated and read-only | implemented |
| **CI** | Composite GitHub Action and CLI gate: security, usability, label-change and error regressions against an approved baseline | implemented |

Not yet included: governed writes (all writes are denied in v0.1); benchmark cases for
restricted functions and system catalogs (the rules exist, but the corpus does not cover
them); dialects beyond PostgreSQL and SQLite (they parse, but are untested). Full list:
[KNOWN_LIMITATIONS.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/KNOWN_LIMITATIONS.md).

## Architecture

```mermaid
flowchart LR
    subgraph App["Your application"]
      Auth["Auth layer"] --> Ctx["RequestContext<br/>(principal, role, tenant)"]
      LLM["LLM / agent"] -->|SQL text| CP
      Ctx --> CP["SQLCheckpoint<br/>block / review / audit"]
    end
    CP --> G["Guard"]
    G --> AN["Analyser<br/>sqlglot qualify + scopes"]
    AN --> R1["Statement / catalog / function rules"]
    AN --> R2["Table and column grants"]
    AN --> R3["Privacy labels"]
    AN --> R4["Tenant pins"]
    AN --> R5["Limits + static cost"]
    CP -->|authorized only| EX["Read-only executor"] --> DB[("Database<br/>grants, RLS, timeouts")]
    CP --> AU["Audit sink<br/>hash + redacted SQL"]
    CP -.review.-> AP["ApprovalQueue (human)"]
    G -.ALLOW only.-> PI["PostgreSQL EXPLAIN inspector"]
    subgraph Bench["Benchmark + CI"]
      DS["Corpus v1"] --> RUN["Runner"] --> ADP["Adapters: native, callable,<br/>isolated external workers"]
      RUN --> RES["Result JSON / Markdown"] --> CMP["Baseline compare"] --> GATE["GitHub Action gate"]
    end
```

Design decisions and their trade-offs are recorded in
[ARCHITECTURE_DECISIONS.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/architecture/ARCHITECTURE_DECISIONS.md).

## Five-minute quick start

```bash
python -m pip install sqlguard-ninja            # core: sqlglot, pydantic, PyYAML
python -m pip install "sqlguard-ninja[all]"     # + LangChain, LangGraph, FastAPI, psycopg
```

Extras: `langchain`, `langgraph`, `fastapi`, `postgres`, `all`. Python 3.11+.

Validate a query against the bundled reference policy (`builtin:retail_saas`, a synthetic
multi-tenant retail schema):

```bash
sqlninja validate --policy builtin:retail_saas --role support_analyst --tenant 1   --sql "SELECT o.id FROM orders o WHERE o.tenant_id = 1 OR 1 = 1"
```

```
DENY:
  [deny] tenant.missing_predicate: Every read of table 'orders' must filter on the caller's tenant. Suggestion: Add `o.tenant_id = :tenant_id` to the WHERE clause of the scope that reads this table.
```

Exit codes: 0 allow, 1 deny, 3 review, 2 usage error.

Run the bundled benchmark corpus, then start your own project:

```bash
sqlninja benchmark run --dataset builtin:v1 --output results/native.json
sqlninja init my-project && cd my-project
sqlninja benchmark run --config sqlninja.yaml
```

The runnable demos live in the repository (deterministic; no API key needed):

```bash
git clone https://github.com/Pratik653/SQLGuard_Ninjapy.git && cd SQLGuard_Ninjapy
python -m pip install -e ".[all]"
python examples/sdk/quickstart.py
python examples/sqlite/text_to_sql_demo.py
python examples/langgraph/sql_agent_demo.py
```

## Python SDK

```python
from sqlguard_ninja import Guard, Policy

from sqlguard_ninja.benchmarking.builtin import resolve

guard = Guard(Policy.from_file(resolve("builtin:retail_saas", "policy")))
result = guard.validate(
    "SELECT department, AVG(salary) FROM employees GROUP BY department",
    role="hr_analyst",
    dialect="postgres",
)
result.allowed      # True
result.findings     # () - structured Finding objects with rule_id, action, message, remediation

guard.validate("SELECT full_name, salary FROM employees", role="hr_analyst").explain()
# DENY:
#   [deny] privacy.aggregate_only: The column 'employees.salary' may only be used inside
#   statistical aggregates (COUNT, SUM, AVG, ...), not as individual values. ...
```

## LangGraph

```python
from sqlguard_ninja.integrations import RequestContext, SQLCheckpoint
from sqlguard_ninja.integrations.langgraph import (
    CONTEXT_KEY, make_execute_node, make_guard_node, route_after_guard)

checkpoint = SQLCheckpoint(guard, mode="block", max_denied_attempts=3)
graph.add_node("guard", make_guard_node(checkpoint))
graph.add_node("execute", make_execute_node(checkpoint, executor))   # re-validates
graph.add_conditional_edges("guard", route_after_guard(3),
                            {"execute": "execute", "revise": "generate", "end": END})

ctx = RequestContext(principal=user.id, role=user.role, tenant_id=user.tenant_id)
app.invoke(state, config={"configurable": {CONTEXT_KEY: ctx}})       # nodes cannot alter config
```

More in [AGENT_INTEGRATION.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/AGENT_INTEGRATION.md): the LangChain tool (its
schema accepts only `query`), the FastAPI router, review-mode approvals and audit sinks.

## Privacy policies

Sensitivity comes from labels **you** assign in the policy, never from column names, and
access is resolved per role:

```yaml
labels:
  payment_card: {sensitivity: critical, masking: use card_last4 and card_brand}
  financial:    {sensitivity: high}
catalog:
  tables:
    payments:
      tenant_column: tenant_id
      columns:
        card_number: {labels: [payment_card], alternatives: [card_last4]}
        card_last4: text
roles:
  support_analyst:
    tables: {payments: [id, tenant_id, card_last4, card_brand, amount, status]}
    labels: {payment_card: deny, financial: deny}
  hr_analyst:
    tables: {employees: "*"}
    labels: {hr: allow, financial: aggregate_only}   # salary only inside COUNT/SUM/AVG
```

```text
support_analyst> SELECT p.card_number FROM payments p WHERE p.tenant_id = 1
DENY:
  [deny] privacy.label_denied: The column 'payments.card_number' contains classified data (payment_card) that this role may not access. Suggestion: Use column 'payments.card_last4' instead. ...

hr_analyst> SELECT AVG(salary) FROM employees WHERE id = 7
REVIEW:
  [review] privacy.small_cohort: An aggregate of restricted data is filtered or grouped by an identifying column, which can reveal individual values. ...
```

Labels also apply to paths inside JSON columns (`profile->'health'->>'allergies'`).
`sqlninja policy suggest` proposes labels from column names for a human to review; those
suggestions are never enforced. This is a technical control, not a certification of GDPR
compliance. Full semantics: [POLICY_REFERENCE.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/POLICY_REFERENCE.md).

## Query cost analysis

Every validation includes an explainable static score that uses the catalog's
`estimated_rows`:

```text
platform_admin> SELECT o.id, i.id FROM orders o CROSS JOIN order_items i
DENY:
  [deny] cost.static_score: Estimated query cost risk is 100/100: join with i has no join condition (Cartesian product); o has an estimated 10,000,000 rows and is read without a filter; ...
```

Optionally, compare it with PostgreSQL's planner. This runs `EXPLAIN (FORMAT JSON)` only,
never `ANALYZE`, and only on SQL the guard allowed, inside a read-only, time-limited,
rolled-back transaction:

```python
from sqlguard_ninja.query_cost.postgres import PostgresPlanInspector
report = PostgresPlanInspector(guard, lambda: psycopg.connect(READER_DSN)).inspect(
    sql, role="support_analyst", context={"tenant_id": 1})
report.total_cost, report.plan_rows, report.static.score, report.findings
```

See [QUERY_COST.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/QUERY_COST.md) and `examples/postgresql/plan_inspection_demo.py`.

## Benchmark

```bash
sqlninja benchmark run --dataset builtin:v1 --adapter native
python scripts/setup_external_adapters.py          # (repository) isolated venvs for other tools
sqlninja benchmark run --dataset builtin:v1 --adapter external:sql-guardrail
sqlninja benchmark compare --baseline benchmarks/baselines/native.json --candidate results/latest.json
```

Results for corpus v1.0.0 (Windows 11, Python 3.11, 1,002 evaluations). Details and raw files:
[RESULTS_v1.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/benchmarks/RESULTS_v1.md).

| Validator | Recall | Precision | FPR | Recall / FPR within declared capability | p95 ms |
|---|---:|---:|---:|---:|---:|
| SQLGuard Ninja 0.1.0 | 100.0% | 100.0% | 0.0% | 100.0% / 0.0% | 2.15 |
| sql-guardrail 0.5.2 | 98.6% | 86.6% | 21.2% | 98.3% / 19.3% | 1.72 |
| agent-sql-guard 0.2.0 | 67.9% | 86.6% | 14.5% | 89.5% / 17.2% | 1.26 |
| krish2105/sqlguard 0.1.0 | 65.8% | 91.4% | 8.6% | 83.5% / 5.2% | 1.22 |

**Read the native row with care.** The corpus and the engine were developed together, and
every disagreement was triaged by fixing the engine or correcting a label (both are logged).
100% shows consistency with the documented semantics, not independent detection quality. The
other tools have smaller policy models (no roles, labels, aggregate-only access or review), so
the "within capability" column is the fairer comparison. Methodology and threats to validity:
[BENCHMARK_METHODOLOGY.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/BENCHMARK_METHODOLOGY.md).

## How it relates to existing tools

SQLGuard Ninja uses **sqlglot** for parsing, dialects and scope analysis, and does not
re-implement them. [sql-guardrail](https://github.com/nickusevich/sql-guardrail) is a
thorough read-only validator with a tautology catch-all, which this project does not
duplicate. [agent-sql-guard](https://github.com/sakura-sky/sql-guard) has a careful
schema-less PII denylist. What SQLGuard Ninja adds: role- and label-based policies, the agent
checkpoint contract, a shared benchmark with isolated adapters, planner inspection, and the
CI gate. Full comparison: [COMPETITIVE_ANALYSIS.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/research/COMPETITIVE_ANALYSIS.md).

## Security limitations

* Static inspection can be wrong where sqlglot and the real database parse SQL differently.
  Only PostgreSQL and SQLite are tested.
* It cannot see inside views or user-defined functions, and it does not stop inference
  across many permitted queries.
* Cost scores and planner estimates are indicators, not guarantees.
* v0.1 denies all writes.
* The benchmark has no restricted-function or system-catalog cases yet. The rules exist,
  but those two categories are not measured.

Use database grants, row-level security and timeouts as the authoritative controls. See the
[threat model](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/security/THREAT_MODEL.md), [limits of static analysis](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/security/STATIC_ANALYSIS_LIMITS.md),
the [benchmark bias review](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/BENCHMARK_BIAS_REVIEW.md) and [KNOWN_LIMITATIONS.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/KNOWN_LIMITATIONS.md).

## Roadmap

Next: restricted-function and system-catalog benchmark cases, an independent held-out
corpus, lineage-aware aggregate checks, and tenant predicate push-down. Later: governed
writes, more tested dialects, and SARIF output. Details in [ROADMAP.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/ROADMAP.md).

## Links

* Repository: https://github.com/Pratik653/SQLGuard_Ninjapy
* Releases: https://github.com/Pratik653/SQLGuard_Ninjapy/releases
* PyPI: https://pypi.org/project/sqlguard-ninja/
* Issues: https://github.com/Pratik653/SQLGuard_Ninjapy/issues
* Security policy: [SECURITY.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/SECURITY.md) · Contributing: [CONTRIBUTING.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/CONTRIBUTING.md)

## Documentation

* [Policy reference](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/POLICY_REFERENCE.md)
* [Agent integration](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/AGENT_INTEGRATION.md)
* [Query cost](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/QUERY_COST.md)
* [CI integration](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/CI_INTEGRATION.md)
* [Benchmark methodology](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/BENCHMARK_METHODOLOGY.md)
* [Threat model](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/security/THREAT_MODEL.md)
* [Limits of static SQL analysis and dialect support](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/security/STATIC_ANALYSIS_LIMITS.md)
* [Benchmark bias review](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/guides/BENCHMARK_BIAS_REVIEW.md)
* [Architecture decisions](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/docs/architecture/ARCHITECTURE_DECISIONS.md)
* [Known limitations](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/KNOWN_LIMITATIONS.md) · [Roadmap](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/ROADMAP.md) · [Changelog](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/CHANGELOG.md)

## Supported versions

* Python 3.11, 3.12, 3.13 (CI matrix on Linux and Windows).
* SQL dialects: **PostgreSQL** (tested against PostgreSQL 16) and **SQLite**. Other sqlglot
  dialects parse but are experimental and untested.

## Development

```bash
python -m pip install -e ".[dev]"
ruff check src tests scripts examples && ruff format --check src tests scripts examples
mypy
pytest                          # PostgreSQL tests run if SQLNINJA_PG_DSN is set or pgserver is installed
python scripts/generate_corpus.py --check
```

See [CONTRIBUTING.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/CONTRIBUTING.md). Report vulnerabilities privately per
[SECURITY.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/SECURITY.md).

## Licence

Apache-2.0. No third-party code is vendored; dependencies and their licences are listed in
[THIRD_PARTY_NOTICES.md](https://github.com/Pratik653/SQLGuard_Ninjapy/blob/main/THIRD_PARTY_NOTICES.md). All benchmark data is synthetic.
