sql-review

/sql-review - SQL Code Review

Perform a thorough SQL code review of the provided code (or selection) focusing on security, performance, maintainability, and database best practices.

Input

SQL code to review: {{input}}

If no SQL provided, scan the workspace for .sql files, stored procedures, or inline queries.

---

Review Categories

1. Security Analysis

SQL Injection Prevention

-- BAD: SQL Injection vulnerability
query = "SELECT * FROM users WHERE id = " + userInput;

-- GOOD: Parameterized queries
-- PostgreSQL/MySQL
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
-- SQL Server
EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;

Access Control

• Principle of Least Privilege: Grant minimum required permissions
• Use database roles instead of direct user permissions
• Review DEFINER vs INVOKER rights on functions/procedures
• Avoid SELECT * on tables with sensitive columns

---

2. Performance Optimization

Common Anti-Patterns

Functions in WHERE clauses (prevents index usage):

-- BAD
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- GOOD
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

N+1 Query Problem:

-- BAD: N+1 queries in application code
for user in users:
    orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)
-- GOOD: Single query
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id;

Overuse of DISTINCT (masking join issues):

-- BAD
SELECT DISTINCT u.name FROM users u, orders o WHERE u.id = o.user_id;
-- GOOD
SELECT u.name FROM users u INNER JOIN orders o ON u.id = o.user_id GROUP BY u.name;

Index Strategy

• Missing indexes on WHERE/JOIN/ORDER BY columns
• Over-indexing (unused or redundant indexes)
• Composite index column order
• Covering indexes for frequently queried column sets

Join Optimization

• Verify appropriate join types (INNER vs LEFT vs EXISTS)
• Optimize join order for smaller result sets first
• Identify accidental cartesian products (missing join conditions)