sqlaudit

SQL Query Auditor & Optimizer — analyze SQL for performance, security, and best practices

---

Why This Exists

Every SQL query you write is a potential performance bottleneck, security vulnerability, or maintenance headache. Code linters catch bugs in Python, Go, and TypeScript — but who catches bugs in your SQL?

sqlaudit is a zero-dependency SQL static analyzer that catches:

• Performance killers: SELECT *, missing WHERE on DELETE/UPDATE, leading wildcards in LIKE, excessive JOINs
• Security risks: SQL injection patterns, GRANT ALL, always-true conditions (1=1), comment injection
• Correctness bugs: = NULL instead of IS NULL, HAVING without GROUP BY, implicit column lists
• Style issues: inconsistent keyword casing, implicit joins, unnecessary parentheses

No database connection required. No configuration needed. Just point it at SQL and get actionable feedback.

---

Installation

pip install sqlaudit

Or install from source:

git clone https://github.com/JSLEEKR/sqlaudit.git
cd sqlaudit
pip install -e .

---

Quick Start

CLI Usage

# Audit a SQL file
sqlaudit audit -f queries.sql

# Audit a single query
sqlaudit audit -q "SELECT * FROM users WHERE name = NULL"

# Audit from stdin
cat migration.sql | sqlaudit audit --stdin

# JSON output for CI/CD integration
sqlaudit audit -f queries.sql --format json

# Only check security rules
sqlaudit audit -f queries.sql --category security

# Fail CI pipeline on any error or warning
sqlaudit audit -f queries.sql --fail-on warning

# Show fix suggestions
sqlaudit audit -q "DELETE FROM users" --verbose

# List all available rules
sqlaudit rules

Python API

from sqlaudit import Analyzer

analyzer = Analyzer()
report = analyzer.analyze("""
    SELECT * FROM users WHERE name = NULL;
    DELETE FROM audit_logs;
    INSERT INTO users VALUES (1, 'test');
""")

print(f"Score: {report.score}/100")
print(f"Errors: {report.total_errors}")
print(f"Warnings: {report.total_warnings}")

for query in report.queries:
    for finding in query.findings:
        print(f"  [{finding.severity.value}] {finding.rule_id}: {finding.message}")
        print(f"    Fix: {finding.suggestion}")

Quick Check

from sqlaudit import Analyzer

analyzer = Analyzer()
findings = analyzer.check_query("DELETE FROM important_table")

for f in findings:
    print(f"{f.rule_id}: {f.message}")
# P002: DELETE without WHERE clause will affect all rows

---

Rules Reference

sqlaudit ships with 27 built-in rules across 5 categories.

Performance (P001-P010)

Rule	Severity	Description
P001	WARNING	SELECT * retrieves all columns
P002	ERROR	UPDATE/DELETE without WHERE clause
P003	WARNING	Leading wildcard in LIKE prevents index usage
P004	WARNING	Too many JOINs (>5 tables)
P005	WARNING	NOT IN with subquery (NULL handling issues)
P006	INFO	Multiple OR on same column (use IN instead)
P007	HINT	SELECT without LIMIT
P008	WARNING	Function on column in WHERE prevents index usage
P009	HINT	SELECT DISTINCT with JOINs may indicate bad join
P010	INFO	ORDER BY without LIMIT sorts entire result set

Security (S001-S007)

Rule	Severity	Description
S001	ERROR	String concatenation (SQL injection risk)
S002	ERROR	GRANT ALL PRIVILEGES (excessive permissions)
S003	WARNING	DROP without IF EXISTS
S004	WARNING	TRUNCATE detected (no row-level logging)
S005	ERROR	Comment patterns inside string literals
S006	WARNING	UNION injection pattern
S007	WARNING	Always-true condition (1=1, 'a'='a')

Best Practice (B001-B005)

Rule	Severity	Description
B001	WARNING	INSERT without column list
B002	INFO	SELECT INTO (non-standard)
B003	HINT	Multi-table query without aliases
B004	WARNING	Deeply nested subqueries (depth >= 3)
B005	INFO	Implicit join (comma-separated FROM)

Correctness (C001-C003)

Rule	Severity	Description
C001	ERROR	= NULL instead of IS NULL
C002	WARNING	SELECT * with GROUP BY
C003	ERROR	HAVING without GROUP BY

Style (T001-T002)

Rule	Severity	Description
T001	HINT	Inconsistent keyword casing
T002	HINT	Unnecessary parentheses in simple conditions

---

Advanced Usage

Category Filtering

from sqlaudit import Analyzer
from sqlaudit.rules import Category

# Only security checks
analyzer = Analyzer(enable_categories=[Category.SECURITY])
report = analyzer.analyze("SELECT * FROM users")

Severity Filtering

from sqlaudit import Analyzer
from sqlaudit.rules import Severity

# Only errors and warnings (skip info/hint)
analyzer = Analyzer(min_severity=Severity.WARNING)

Disable Specific Rules

analyzer = Analyzer(disable_rules=["P001", "P007", "T001"])

Custom Rules

from sqlaudit import Analyzer, Rule, Severity, Category

def check_table_prefix(info):
    """All tables should use 'app_' prefix."""
    for table in info.tables:
        if not table.startswith("app_"):
            return f"Table '{table}' missing 'app_' prefix"
    return None

analyzer = Analyzer()
analyzer.register_rule(Rule(
    id="CUSTOM001",
    name="table-prefix",
    description="Tables must use app_ prefix",
    severity=Severity.WARNING,
    category=Category.BEST_PRACTICE,
    check=check_table_prefix,
    suggestion="Rename table to use app_ prefix",
))

report = analyzer.analyze("SELECT * FROM users")
# Finds: Table 'users' missing 'app_' prefix

Output Formats

# Human-readable text (default)
sqlaudit audit -q "SELECT * FROM t" --format text

# JSON for programmatic consumption
sqlaudit audit -q "SELECT * FROM t" --format json

# CSV for spreadsheet import
sqlaudit audit -q "SELECT * FROM t" --format csv

# Summary only (score + counts)
sqlaudit audit -q "SELECT * FROM t" --format summary

CI/CD Integration

# GitHub Actions example
- name: SQL Audit
  run: |
    pip install sqlaudit
    sqlaudit audit -f migrations/*.sql --format json --fail-on warning

# Pre-commit hook
#!/bin/bash
SQL_FILES=$(git diff --cached --name-only --diff-filter=ACM | grep '\.sql$')
if [ -n "$SQL_FILES" ]; then
    sqlaudit audit -f $SQL_FILES --fail-on error --no-color
fi

---

Scoring

sqlaudit assigns a score from 0-100 based on findings:

Severity	Point Deduction
ERROR	-10 per finding
WARNING	-5 per finding
INFO	-2 per finding
HINT	-1 per finding

A pass rate is also computed: percentage of queries with zero errors or warnings.

---

Architecture

sqlaudit/
  parser.py     # SQL tokenizer + query parser (zero dependencies)
  rules.py      # Rule engine with 27 built-in rules
  analyzer.py   # Main analyzer (parser + rules + report)
  report.py     # Report data model (Finding, QueryResult, Report)
  formatter.py  # Output formatters (text, JSON, CSV, summary)
  cli.py        # Command-line interface

The architecture is designed for extensibility:

• Parser produces a QueryInfo object with structured query metadata
• Rules are pure functions: (QueryInfo) -> Optional[str]
• Custom rules can be registered at runtime
• Formatters are pluggable for any output format

---

License

MIT