SQL Scanner

other
SQL Scanner

🛡️ SQL Scanner

A PowerShell CLI tool that scans your .sql files for destructive, risky, and security-sensitive commands — before they hit production.

Catch DROP TABLE, TRUNCATE, unguarded DELETE, permission changes, and more with a single command. Color-coded terminal output makes it instantly clear which files need a closer look.

Preview

  ✔ 001_create_users.sql
  ✘ 002_cleanup_staging.sql
      [!!] TRUNCATE (x1)
      [!!] DROP TABLE (x1)
      [!!] DELETE (no WHERE) (x1)
  ✘ 003_setup_permissions.sql
      [! ] GRANT (x2)
      [! ] CREATE USER (x1)
  ✔ 004_add_columns.sql

🔴 Red for destructive commands · 🟡 Yellow for permission changes · 🟠 Amber for schema modifications

Quick Start

# Scan current directory
.\Scan-SqlFiles.ps1

# Scan a specific folder
.\Scan-SqlFiles.ps1 -Path "C:\projects\migrations"

# Include subfolders
.\Scan-SqlFiles.ps1 -Path "C:\deploy\sql" -Recurse

No dependencies. No modules to install. Just download and run.

What It Catches

🔴 HIGH — Destructive & Irreversible

Pattern Example
DROP TABLE DROP TABLE users;
DROP DATABASE DROP DATABASE staging;
DROP SCHEMA DROP SCHEMA legacy;
TRUNCATE TRUNCATE TABLE audit_logs;
DELETE (no WHERE) DELETE FROM sessions;
ALTER TABLE … DROP ALTER TABLE users DROP COLUMN email;
DROP USER / LOGIN DROP USER readonly_svc;
xp_cmdshell EXEC xp_cmdshell 'dir';

🟡 MEDIUM — Security & Permissions

Pattern Example
GRANT GRANT SELECT ON dbo.users TO app_role;
REVOKE REVOKE INSERT ON dbo.logs FROM dev;
CREATE USER CREATE USER report_reader FOR LOGIN …;
ALTER USER ALTER USER svc WITH DEFAULT_SCHEMA = dbo;
DROP INDEX / VIEW DROP INDEX idx_email ON users;
DROP PROCEDURE DROP PROCEDURE sp_cleanup;
OPENROWSET SELECT * FROM OPENROWSET(…);

🟠 LOW — Worth Reviewing

Pattern Example
ALTER TABLE ALTER TABLE users ADD is_active BIT;
EXEC / EXECUTE EXEC sp_update_stats;
BULK INSERT BULK INSERT staging FROM '…';

Comment-Aware

The scanner strips SQL comments before analysis, so commented-out code won't trigger false positives.

-- DROP TABLE users;              ← ignored
/* TRUNCATE TABLE old_data; */    ← ignored
DROP TABLE temp_cache;            ← flagged

CI/CD Integration

The script returns meaningful exit codes, making it easy to plug into any pipeline as a safety gate.

Exit Code Meaning
0 ✅ All clear — no findings
1 ⚠️ Medium severity findings
2 🚨 High severity findings

GitHub Actions

- name: Scan SQL migrations
  shell: pwsh
  run: |
    .\Scan-SqlFiles.ps1 -Path ./migrations -Recurse
    if ($LASTEXITCODE -ge 2) { exit 1 }

Azure DevOps

- task: PowerShell@2
  inputs:
    filePath: Scan-SqlFiles.ps1
    arguments: '-Path $(Build.SourcesDirectory)/sql -Recurse'
  failOnStderr: true

Parameters

Parameter Type Default Description
-Path string . (current dir) Folder containing .sql files
-Recurse switch $false Scan subfolders recursively

Requirements

  • PowerShell 5.1+ (Windows PowerShell) or PowerShell 7+ (cross-platform)
  • No external modules or dependencies

License

MIT — use it, fork it, adapt it to your workflow.

View Source