SQL Security Scan

Optimize every query. Eliminate SQL injection risks, enforce schema best practices, and safeguard PII/PHI data with real-time analysis before deployment.

Purpose

The SQL Security Scan & Auto-Fix Expert is a specialized AI agent designed for Databricks environments. It combines deep security scanning with automated remediation to protect sensitive data, prevent injection attacks, and ensure regulatory compliance.

Target Personas

• Data Engineers: To automatically secure SQL pipelines and implement PII masking functions.

• Security Architects: To validate Databricks workspace configurations against SOC2 and GDPR standards.

• Database Administrators (DBAs): To perform privilege analysis and enforce the principle of least privilege.

• Compliance Officers: To generate standardized security reports for auditing purposes.

Key Business Values

• Automated Remediation: Instantly transforms vulnerable code (e.g., SQL Injection) into secure, parameterized queries.

• PII Protection: Automatically detects and masks sensitive data like SSNs, emails, and phone numbers with 98-99% confidence.

• Risk Mitigation: Identifies hardcoded credentials and replaces them with secure Databricks secrets management.

• Operational Efficiency: Replaces manual security reviews with a 7-step automated "Scan-and-Fix" process.

Security Capabilities & Vulnerability Categories

The agent categorizes risks by severity and provides automated fixes for most common threats.

Category	Severity	CWE	Auto-Fix
SQL Injection	Critical	CWE-89	✅ Supported
Hardcoded Credentials	Critical	CWE-798	✅ Supported
PII Exposure	High	CWE-359	✅ Supported
Missing Encryption	High	CWE-311	✅ Supported
Insecure Joins	Medium	CWE-1286	✅ Supported
Over-Privileged Access	High	CWE-269	⚠️ Manual

Execution Flow

The "Scan-and-Fix" Process

When the command sql-security scan-and-fix --file queries.sql is invoked, the agent follows this workflow:

1. Vulnerability Scan: Analyzes SQL files for patterns like string concatenation in WHERE clauses.

   
2. AI Fix Generation: Utilizes Claude Sonnet 4 to generate secure code alternatives.

   
3. Safety Verification: Internally verifies that the fix does not break query logic.

   
4. Diff Presentation: Shows the user a "Vulnerable vs. Secure" rationale.

   
5. User Approval: The user reviews and approves or rejects the suggested changes.

6. Apply Fix: Generates a new secured file (e.g., sample_fixed_queries.sql).

7. Re-scan: Performs a final check to confirm all issues are resolved.

Key Agent Commands & Inputs

The agent operates via specific CLI-style commands:

• scan-and-fix --file [filename]: Initiates the primary remediation workflow.

• detect-pii --table [tablename]: Scans specific tables for sensitive data types.

• compliance --standard [SOC2/GDPR]: Runs high-level checks for encryption and logging.

• analyze-privileges --user [username]: Detects over-privileged accounts (e.g., users with ADMIN on production.*).

Agent Outputs

The agent provides three distinct types of output to ensure full visibility:

• SQL Security Report: A summarized dashboard showing critical/high/medium issues, impact analysis, and compliance percentages.

  
• Auto-Generated Masking Functions: DDL scripts (e.g., mask_email, mask_ssn) that use conditional logic to hide data from non-production users.

  
• Fixed SQL Files: A complete, ready-to-deploy SQL file with all security enhancements applied.

5. Execution Guidelines (Guardrails)

To maintain security integrity, the agent adheres to these strict rules:

• Hide Complexity: Bash commands and intermediate processing steps are hidden from the final output.

• No Command Exposure: Does NOT display raw bash commands being executed.