Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
Generate secure SQL queries with validation, pagination helpers, risk analysis, and audit-focused safeguards.
version
0.3.0
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
# 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
# Copy sql_query_generator.py to your projectcp 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:
Read this SKILL.md file completely before generating queries
Follow all security guidelines strictly
Always use parameterized queries
Validate all inputs before query generation
Include security warnings in responses
Optional Database Drivers
Install only the drivers you need:
# 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
-- Example structureSELECT
column1,
column2,
aggregate_function(column3) AS alias
FROM
table1
JOIN
table2 ON table1.id = table2.foreign_id
WHERE
condition1 = value1
AND condition2 > value2
GROUPBY
column1, column2
HAVING
aggregate_condition
ORDERBY
column1 DESC
LIMIT 100;
Apply Security Measures
Use parameterized queries
Validate all inputs
Escape special characters
Query Patterns
Pattern 1: Simple SELECT
-- Natural language: "Get all users who registered after January 1, 2024"SELECT
id,
username,
email,
registration_date
FROM
users
WHERE
registration_date > $1-- ParameterizedORDERBY
registration_date DESC;
Pattern 2: JOIN with Aggregation
-- Natural language: "Show total orders by customer in 2024"SELECT
c.customer_name,
c.email,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS total_spent
FROM
customers c
INNERJOIN
orders o ON c.customer_id = o.customer_id
WHEREEXTRACT(YEARFROM o.order_date) = $1GROUPBY
c.customer_id,
c.customer_name,
c.email
HAVINGCOUNT(o.order_id) >5ORDERBY
total_spent DESC;
Pattern 3: Subquery
-- Natural language: "Find products with above-average prices"SELECT
product_name,
price,
category
FROM
products
WHERE
price > (
SELECTAVG(price)
FROM products
)
ORDERBY
price DESC;
Pattern 4: CTE (Common Table Expression)
-- Natural language: "Get top 3 products per category by sales"WITH product_sales AS (
SELECT
p.product_id,
p.product_name,
p.category_id,
c.category_name,
SUM(oi.quantity * oi.unit_price) AS total_sales,
ROW_NUMBER() OVER (
PARTITIONBY p.category_id
ORDERBYSUM(oi.quantity * oi.unit_price) DESC
) AS rank_in_category
FROM
products p
JOIN
order_items oi ON p.product_id = oi.product_id
JOIN
categories c ON p.category_id = c.category_id
GROUPBY
p.product_id,
p.product_name,
p.category_id,
c.category_name
)
SELECT
category_name,
product_name,
total_sales,
rank_in_category
FROM
product_sales
WHERE
rank_in_category <=3ORDERBY
category_name,
rank_in_category;
Pattern 5: Window Functions
-- Natural language: "Show running total of sales per day"SELECT
sale_date,
daily_total,
SUM(daily_total) OVER (
ORDERBY sale_date
ROWSBETWEEN UNBOUNDED PRECEDING ANDCURRENTROW
) AS running_total,
AVG(daily_total) OVER (
ORDERBY sale_date
ROWSBETWEEN6 PRECEDING ANDCURRENTROW
) AS moving_average_7days
FROM (
SELECTDATE(order_date) AS sale_date,
SUM(total_amount) AS daily_total
FROM
orders
GROUPBYDATE(order_date)
) daily_sales
ORDERBY
sale_date;
Best Practices
1. Query Structure
Always use explicit column names (avoid SELECT *)
Use meaningful table aliases
Indent for readability
Comment complex logic
2. Performance
Create appropriate indexes
Avoid SELECT DISTINCT when possible (use GROUP BY instead)
Use EXISTS instead of IN for large datasets
Limit result sets when appropriate
Use EXPLAIN to analyze query plans
3. Security (CRITICAL)
3.1 MANDATORY Security Rules
THESE RULES ARE NON-NEGOTIABLE AND MUST ALWAYS BE FOLLOWED:
NEVER CONCATENATE USER INPUT INTO SQL
# WRONG - CRITICAL SECURITY VULNERABILITY
query = f"SELECT * FROM users WHERE username = '{user_input}'"# CORRECT - Always use parameters
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (user_input,))
ALL VALUES MUST BE PARAMETERIZED
Even seemingly "safe" values like numbers
Even values from "trusted" sources
Even internal application values
NO EXCEPTIONS
VALIDATE AND SANITIZE ALL INPUTS
# Whitelist validation
VALID_STATUSES = ['active', 'inactive', 'pending']
if status notin VALID_STATUSES:
raise ValueError("Invalid status")
# Type validationifnotisinstance(user_id, int):
raise TypeError("user_id must be integer")
# Length validationiflen(username) > 50:
raise ValueError("username too long")
ESCAPE DYNAMIC IDENTIFIERS PROPERLY
from psycopg2 import sql
# For table/column names that must be dynamic
query = sql.SQL("SELECT * FROM {} WHERE id = %s").format(
sql.Identifier(table_name)
)
cursor.execute(query, (user_id,))
3.2 Input Validation Framework
import re
from typing importAny, List, OptionalclassSQLInputValidator:
"""Comprehensive input validation for SQL queries""" @staticmethoddefvalidate_identifier(identifier: str, max_length: int = 63) -> str:
"""Validate table/column names"""# Check lengthiflen(identifier) > max_length:
raise ValueError(f"Identifier too long: {len(identifier)} > {max_length}")
# Only alphanumeric and underscoreifnot re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', identifier):
raise ValueError(f"Invalid identifier: {identifier}")
# Prevent SQL keywords as identifiers
SQL_KEYWORDS = {
'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE',
'ALTER', 'TRUNCATE', 'UNION', 'JOIN', 'WHERE', 'FROM'
}
if identifier.upper() in SQL_KEYWORDS:
raise ValueError(f"SQL keyword not allowed: {identifier}")
return identifier
@staticmethoddefvalidate_integer(value: Any, min_val: Optional[int] = None,
max_val: Optional[int] = None) -> int:
"""Validate integer values"""try:
int_value = int(value)
except (ValueError, TypeError):
raise ValueError(f"Invalid integer: {value}")
if min_val isnotNoneand int_value < min_val:
raise ValueError(f"Value {int_value} below minimum {min_val}")
if max_val isnotNoneand int_value > max_val:
raise ValueError(f"Value {int_value} above maximum {max_val}")
return int_value
@staticmethoddefvalidate_string(value: str, max_length: int = 255,
allow_empty: bool = False) -> str:
"""Validate string values"""ifnotisinstance(value, str):
raise TypeError("Value must be string")
ifnot allow_empty andlen(value) == 0:
raise ValueError("Empty string not allowed")
iflen(value) > max_length:
raise ValueError(f"String too long: {len(value)} > {max_length}")
# Check for null bytesif'\x00'in value:
raise ValueError("Null bytes not allowed in string")
return value
@staticmethoddefvalidate_email(email: str) -> str:
"""Validate email format"""
email = SQLInputValidator.validate_string(email, max_length=254)
# Basic email validationifnot re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', email):
raise ValueError(f"Invalid email format: {email}")
return email
@staticmethoddefvalidate_date(date_str: str) -> str:
"""Validate date format (YYYY-MM-DD)"""ifnot re.match(r'^\d{4}-\d{2}-\d{2}$', date_str):
raise ValueError(f"Invalid date format: {date_str}")
return date_str
@staticmethoddefvalidate_enum(value: str, allowed_values: List[str]) -> str:
"""Validate value against whitelist"""if value notin allowed_values:
raise ValueError(f"Invalid value: {value}. Allowed: {allowed_values}")
return value
When generating queries, include error handling recommendations:
import psycopg2
from psycopg2 import sql
try:
cursor.execute(
sql.SQL("SELECT * FROM {} WHERE id = %s").format(
sql.Identifier('users')
),
(user_id,)
)
results = cursor.fetchall()
except psycopg2.Error as e:
print(f"Database error: {e}")
# Log error, return appropriate responsefinally:
cursor.close()
Query Validation Checklist
Before providing a query, verify:
All table and column names are valid
JOIN conditions are correct
WHERE clause logic is accurate
Parameters are used (not string concatenation)
Appropriate indexes exist or are recommended
Query is optimized for the expected dataset size
Results will be properly limited if needed
Error handling is included in implementation code
Response Format
When responding to a query request, provide:
The SQL Query (properly formatted and commented)
Explanation of what the query does
Parameters that need to be passed
Expected Result structure
Performance Notes (if applicable)
Security Warnings (if applicable)
Implementation Example in the requested language
Example Response Structure
### SQL Query```sql
-- Get active users with their order counts
SELECT
u.user_id,
u.username,
u.email,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM
users u
LEFT JOIN
orders o ON u.user_id = o.user_id
WHERE
u.status = $1
AND u.created_at >= $2
GROUP BY
u.user_id,
u.username,
u.email
HAVING
COUNT(o.order_id) >= $3
ORDER BY
lifetime_value DESC
LIMIT $4;
Parameters
$1: status (string, e.g., 'active')
$2: created_at (date, e.g., '2024-01-01')
$3: min_orders (integer, e.g., 5)
$4: limit (integer, e.g., 100)
Explanation
This query retrieves active users who joined after a specified date and have placed a minimum number of orders. It calculates their total order count and lifetime value, sorted by highest spending customers first.
## Advanced Topics
### 1. Query Optimization Techniques
- Use EXPLAIN ANALYZE to understand query plans
- Create covering indexes
- Partition large tables
- Use materialized views for complex aggregations
- Implement query result caching
### 2. Complex Scenarios
- Recursive CTEs for hierarchical data
- Pivot/Unpivot operations
- Full-text search
- Geospatial queries
- Time-series analysis
### 3. Migration Support
- Generate queries for data migration
- Schema comparison queries
- Data validation queries
- Backup and restore scripts
## Testing Recommendations
Always suggest testing generated queries with:
1. Small dataset first
2. EXPLAIN or EXPLAIN ANALYZE
3. Various edge cases (NULL values, empty sets)
4. Performance benchmarks
5. Security scanning tools
## Common Pitfalls to Avoid
1. **N+1 Query Problem**: Use JOINs instead of multiple queries
2. **SELECT ***: Specify needed columns explicitly
3. **Missing Indexes**: Recommend indexes on filter/join columns
4. **Cartesian Products**: Ensure proper JOIN conditions
5. **Implicit Type Conversions**: Cast explicitly when needed
6. **Timezone Issues**: Always use timezone-aware timestamps
## Integration Examples
### REST API
```python
from flask import Flask, request, jsonify
import psycopg2
@app.route('/api/users', methods=['GET'])
def get_users():
status = request.args.get('status', 'active')
# Validate input
if status not in ['active', 'inactive', 'suspended']:
return jsonify({'error': 'Invalid status'}), 400
try:
cursor.execute(
"SELECT id, username, email FROM users WHERE status = %s",
(status,)
)
users = cursor.fetchall()
return jsonify(users)
except Exception as e:
return jsonify({'error': str(e)}), 500
GraphQL Resolver
const resolvers = {
Query: {
users: async (_, { status, limit }, { db }) => {
const result = await db.query(
'SELECT * FROM users WHERE status = $1 LIMIT $2',
[status, limit]
);
return result.rows;
}
}
};
Conclusion
This skill provides comprehensive SQL query generation capabilities with a focus on security, performance, and best practices. Always prioritize parameterized queries and provide clear documentation with generated SQL.
