sql-query-generator

# SQL Query Generator Skill

## Overview
This skill enables AI agents to generate accurate, optimized SQL queries from natural language descriptions. It supports multiple database systems and follows best practices for query construction, security, and performance.

## Installation

### Method 1: Direct Download
```bash
# Clone or download the repository
git clone https://github.com/yourusername/sql-query-generator.git
cd sql-query-generator

# No external dependencies required for core functionality
python sql_query_generator.py
```

### Method 2: Using as a Module
```bash
# Copy sql_query_generator.py to your project
cp sql_query_generator.py /path/to/your/project/

# Import in your code
from sql_query_generator import SQLQueryGenerator, DatabaseType
```

### Method 3: AI Agent Integration
For AI agents using this skill:
1. Read this SKILL.md file completely before generating queries
2. Follow all security guidelines strictly
3. Always use parameterized queries
4. Validate all inputs before query generation
5. Include security warnings in responses

### Optional Database Drivers
Install only the drivers you need:

```bash
# PostgreSQL
pip install psycopg2-binary

# MySQL
pip install mysql-connector-python

# SQL Server
pip install pyodbc

# Oracle
pip install cx_Oracle

# For testing and development
pip install pytest pytest-cov
```

### System Requirements
- Python 3.7 or higher
- No external dependencies for core query generation
- Database drivers only needed for actual query execution

## Supported Database Systems
- PostgreSQL
- MySQL
- SQLite
- Microsoft SQL Server
- Oracle Database
- MariaDB

## Core Capabilities

### 1. Query Generation
- **SELECT Queries**: Simple and complex data retrieval
- **JOIN Operations**: INNER, LEFT, RIGHT, FULL OUTER, CROSS
- **Aggregations**: GROUP BY, HAVING, aggregate functions
- **Subqueries**: Correlated and non-correlated
- **CTEs**: Common Table Expressions (WITH clause)
- **Window Functions**: OVER, PARTITION BY, ROW_NUM...

# SQL Query Generator - Ultra Secure Edition

## 🔒 Military-Grade Security for SQL Query Generation

AI-powered SQL query generator with **100x enhanced security** features, designed to prevent SQL injection, protect sensitive data, and ensure safe database operations.

## 🛡️ Security Features

### New in latest update (v0.3.0)
- Table allowlist now enforced on **JOIN tables** too (not just primary table).
- New `generate_paginated_select_query(...)` helper:
  - strict page/page_size validation
  - safe sort column + sort direction validation
  - automatic LIMIT/OFFSET generation
- New `query_fingerprint(query)` helper for deterministic cache/audit correlation.
- Stronger log sanitization: redacts `api_key`, `token`, and `secret` patterns.
- Structured query analysis (`analyze_query`) retained for CI/automation scoring.

### Core Security Mechanisms

1. **SQL Injection Prevention**
   - Pattern-based detection (18+ injection patterns)
   - Input validation on ALL identifiers
   - Mandatory parameterized queries
   - Null byte detection
   - Hex encoding detection

2. **Input Validation**
   - Type validation (integers, strings, dates, emails)
   - Length constraints
   - Format validation (regex-based)
   - Whitelist validation for enums
   - SQL keyword blocking

3. **Rate Limiting**
   - Per-user request throttling
   - Automatic penalty system for violations
   - Configurable time windows
   - Thread-safe implementation

4. **Audit Logging**
   - Complete query history
   - Security event tracking
   - Sanitized logging (PII protection)
   - JSON-formatted logs for analysis

5. **Error Sanitization**
   - No sensitive data exposure
   - Generic error messages for users
   - Detailed logging for admins
   - Stack trace protection

6. **Data Sanitization**
   - Credit card number redaction
   - SSN redaction
   - Password redaction
   - Sensitive pattern detection

## 📊 Security Levels

```python
SecurityLevel.STRICT      # Maximum validation (RECOMMENDED)
Securi...