sql-code-review

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.

Review Categories

🔒 Security

• SQL Injection Prevention — all user inputs must be parameterized; no string concatenation in queries
• Access Control — principle of least privilege, role-based permissions, schema security
• Data Protection — avoid SELECT * on sensitive tables, enforce audit logging and data masking

⚡ Performance

• Query Structure — eliminate SELECT DISTINCT *, prefer explicit JOINs over comma-separated FROM
• Index Strategy — verify indexes for WHERE/JOIN columns, flag over-indexing and unused indexes
• Join Optimization — correct join types, optimal join order, no accidental Cartesian products
• Aggregation — replace correlated subqueries with JOIN/GROUP BY when possible

🛠️ Code Quality

• Formatting — consistent uppercase keywords, aligned columns, proper indentation
• Naming — descriptive table/column names, no reserved words as identifiers, consistent casing
• Schema Design — appropriate normalization, optimal data types, proper constraints and defaults

🗄️ Database Compatibility

• ANSI SQL first — use COALESCE, CASE WHEN, ANSI JOINs, FETCH FIRST for portability
• Database-specific idioms — leverage engine-specific features (JSONB, COLUMNSTORE, sequences) where appropriate

Protheus note: Use ChangeQuery() to automatically translate SQL syntax to the active database dialect. BeginSQL/EndSQL calls ChangeQuery() automatically.

---

Bundled Reference Files

This skill uses progressive disclosure. The SKILL.md body covers the review workflow, category definitions, checklist, and output format. Detailed code examples, anti-patterns, and database-specific best practices are in the references/ directory — read them on demand based on the scenario:

Reference File	When to Read	Content
references/sql-security-patterns.md	Reviewing security concerns — SQL injection, access control, data protection, or dynamic SQL	Injection examples, parameterization patterns, access control checklist, data masking examples
references/sql-performance-and-quality-patterns.md	Reviewing query performance, code style, anti-patterns, or testing strategies	Query optimization examples, JOIN/aggregation patterns, N+1 and DISTINCT anti-patterns, formatting standards, data integrity checks
references/database-specific-best-practices.md	Reviewing SQL targeting a specific database (PostgreSQL, SQL Server, Oracle) or cross-database portability	ANSI SQL cross-database patterns, PostgreSQL (JSONB, GIN), SQL Server (NVARCHAR, COLUMNSTORE), Oracle (sequences, VARCHAR2)