Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Universal SQL code review assistant that performs comprehensive security, maintainability, and code quality analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Focuses on SQL injection prevention, access control, code standards, and anti-pattern detection. Complements SQL optimization prompt for complete development coverage.
SQL Code Review
Perform a thorough SQL code review of ${selection} (or entire project if no selection) focusing on security, performance, maintainability, and database best practices.
🔒 Security Analysis
SQL Injection Prevention
-- ❌ CRITICAL: SQL Injection vulnerability
query = "SELECT * FROM users WHERE id = " + userInput;
query = f"DELETE FROM orders WHERE user_id = {user_id}";
-- ✅ SECURE: Parameterized queries-- PostgreSQL/MySQLPREPARE stmt FROM'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING@user_id;
-- SQL ServerEXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id=@user_id;
Access Control & Permissions
Principle of Least Privilege: Grant minimum required permissions
Role-Based Access: Use database roles instead of direct user permissions
Schema Security: Proper schema ownership and access controls
Function/Procedure Security: Review DEFINER vs INVOKER rights
Data Protection
Sensitive Data Exposure: Avoid SELECT * on tables with sensitive columns
Audit Logging: Ensure sensitive operations are logged
Data Masking: Use views or functions to mask sensitive data
Encryption: Verify encrypted storage for sensitive data
⚡ Performance Optimization
Query Structure Analysis
-- ❌ BAD: Inefficient query patternsSELECTDISTINCT u.*FROM users u, orders o, products p
WHERE u.id = o.user_id
AND o.product_id = p.id
ANDYEAR(o.order_date) =2024;
-- ✅ GOOD: Optimized structureSELECT u.id, u.name, u.email
FROM users u
INNERJOIN orders o ON u.id = o.user_id
WHERE o.order_date >='2024-01-01'AND o.order_date <'2025-01-01';
Index Strategy Review
Missing Indexes: Identify columns that need indexing
Over-Indexing: Find unused or redundant indexes
Composite Indexes: Multi-column indexes for complex queries
Index Maintenance: Check for fragmented or outdated indexes
Join Optimization
Join Types: Verify appropriate join types (INNER vs LEFT vs EXISTS)
Join Order: Optimize for smaller result sets first
Cartesian Products: Identify and fix missing join conditions
Subquery vs JOIN: Choose the most efficient approach
Aggregate and Window Functions
-- ❌ BAD: Inefficient aggregationSELECT user_id,
(SELECTCOUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
FROM orders o1
GROUPBY user_id;
-- ✅ GOOD: Efficient aggregationSELECT user_id, COUNT(*) as order_count
FROM orders
GROUPBY user_id;
🛠️ Code Quality & Maintainability
SQL Style & Formatting
-- ❌ BAD: Poor formatting and styleselect u.id,u.name,o.total from users u leftjoin orders o on u.id=o.user_id where u.status='active'and o.order_date>='2024-01-01';
-- ✅ GOOD: Clean, readable formattingSELECT u.id,
u.name,
o.total
FROM users u
LEFTJOIN orders o ON u.id = o.user_id
WHERE u.status ='active'AND o.order_date >='2024-01-01';
Data Types: Optimal data type choices for storage and performance
Constraints: Proper use of PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
Default Values: Appropriate default values for columns
🗄️ Database-Specific Best Practices
PostgreSQL
-- Use JSONB for JSON dataCREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- GIN index for JSONB queriesCREATE INDEX idx_events_data ON events USING gin(data);
-- Array types for multi-value columnsCREATE TABLE tags (
post_id INT,
tag_names TEXT[]
);
MySQL
-- Use appropriate storage enginesCREATE TABLE sessions (
id VARCHAR(128) PRIMARY KEY,
data TEXT,
expires TIMESTAMP
) ENGINE=InnoDB;
-- Optimize for InnoDBALTER TABLE large_table
ADD INDEX idx_covering (status, created_at, id);
SQL Server
-- Use appropriate data typesCREATE TABLE products (
id BIGINTIDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at DATETIME2 DEFAULT GETUTCDATE()
);
-- Columnstore indexes for analyticsCREATE COLUMNSTORE INDEX idx_sales_cs ON sales;
Oracle
-- Use sequences for auto-incrementCREATE SEQUENCE user_id_seq STARTWITH1 INCREMENT BY1;
CREATE TABLE users (
id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,
name VARCHAR2(255) NOT NULL
);
🧪 Testing & Validation
Data Integrity Checks
-- Verify referential integritySELECT o.user_id
FROM orders o
LEFTJOIN users u ON o.user_id = u.id
WHERE u.id ISNULL;
-- Check for data consistencySELECTCOUNT(*) as inconsistent_records
FROM products
WHERE price <0OR stock_quantity <0;
Performance Testing
Execution Plans: Review query execution plans
Load Testing: Test queries with realistic data volumes
Stress Testing: Verify performance under concurrent load
-- ❌ BAD: N+1 queries in application codeforuserin users:
orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)
-- ✅ GOOD: Single optimized querySELECT u.*, o.*FROM users u
LEFTJOIN orders o ON u.id = o.user_id;
Overuse of DISTINCT
-- ❌ BAD: DISTINCT masking join issuesSELECTDISTINCT u.name
FROM users u, orders o
WHERE u.id = o.user_id;
-- ✅ GOOD: Proper join without DISTINCTSELECT u.name
FROM users u
INNERJOIN orders o ON u.id = o.user_id
GROUPBY u.name;
Function Misuse in WHERE Clauses
-- ❌ BAD: Functions prevent index usageSELECT*FROM orders
WHEREYEAR(order_date) =2024;
-- ✅ GOOD: Range conditions use indexesSELECT*FROM orders
WHERE order_date >='2024-01-01'AND order_date <'2025-01-01';
📋 SQL Review Checklist
Security
All user inputs are parameterized
No dynamic SQL construction with string concatenation
Appropriate access controls and permissions
Sensitive data is properly protected
SQL injection attack vectors are eliminated
Performance
Indexes exist for frequently queried columns
No unnecessary SELECT * statements
JOINs are optimized and use appropriate types
WHERE clauses are selective and use indexes
Subqueries are optimized or converted to JOINs
Code Quality
Consistent naming conventions
Proper formatting and indentation
Meaningful comments for complex logic
Appropriate data types are used
Error handling is implemented
Schema Design
Tables are properly normalized
Constraints enforce data integrity
Indexes support query patterns
Foreign key relationships are defined
Default values are appropriate
🎯 Review Output Format
Issue Template
## [PRIORITY] [CATEGORY]: [Brief Description]
**Location**: [Table/View/Procedure name and line number if applicable]
**Issue**: [Detailed explanation of the problem]
**Security Risk**: [If applicable - injection risk, data exposure, etc.]
**Performance Impact**: [Query cost, execution time impact]
**Recommendation**: [Specific fix with code example]
**Before**:
```sql
-- Problematic SQL
After:
-- Improved SQL
Expected Improvement: [Performance gain, security benefit]
