review-sql

Skill: Review SQL

Purpose

Review only SQL the language and query conventionsof query-related code. Covers injection and parameterization, index and execution plan issues, transactions and isolation, NULL and unique constraints, dialect portability, large table and pagination patterns, and sensitive columns and permissions. Emit in standard formatresult listfor aggregation. Does not define scope or perform comprehensive security/architecture review; injection here is a SQL-specific issue, but broader security is targeted at review-security。

Core Objective

Primary objective: Generate a SQL-centric findings list covering injection/parameterization, indexes, transactions, NULL/constraints, dialect portability, pagination patterns, and sensitive column access, for the given code scope.

Success criteria(all requirements must be met):

✅ SQL scope only: Review only SQL and query conventions; no scope selection, full security, or architecture analysis performed
✅ Covers all seven SQL dimensions: injection/parameterization, indexes/execution plans, transactions/isolation, NULL/unique constraints, dialect/portability, large table/pagination patterns, and related sensitive columns/permissions
✅ Result format compatible: Each result includes location, category (language-sql), severity, title, description, and optional recommendation
✅ Critical injection issues flagged: SQL injection patterns (string concatenation, user input interpolation) are flagged with "critical" severity
✅ Precise location references: All results reference specific file:line or query identifier locations

Acceptancetest: Does the output contain a SQL result list covering all relevant query dimensions, with injection risks flagged as "critical" and specific location references for each result?

Scope boundaries (scope boundaries)

This skill is responsible for：

SQL injection via string concatenation or interpolation — parameterized queries and prepared statements
Index gaps for WHERE/JOIN/ORDER BY columns
Transaction boundaries, isolation levels, deadlock risks, long-running transactions
NULL handling in comparisons/aggregations, unique constraints, NOT NULL correctness
Database dialect portability (LIMIT/OFFSET/FETCH, date functions, vendor-specific syntax)
Large table full scans, pagination strategies (keyset vs OFFSET)
Sensitive column exposure in SELECT, least-privilege role usage

This skill is not responsible for：

Scope selection — scope is provided by the caller
Broader security analysis (beyond SQL injection) — use "review-security"
Architecture analysis — use "review-architecture"
Full orchestrated review — use "review-code"

Handoff point: After all SQL results are emitted, hand them to orchestrate-code-review for aggregation. For broader security issues (authentication, encryption, configuration), redirect to "review-security".

Use cases (use cases)

Orchestrated review: Used as a language step when orchestrate-code-review runs for projects containing SQL (.sql files, embedded SQL, or ORM-generated SQL).
SQL-only review: When the user only wants to check the correctness, performance, and security of a query.
Migration or portability: Check dialect-specific constructs and cross-database portability.

When to use: When the code under review contains SQL (raw .sql, embedded in code, or ORM-generated). The scope (diff vs path) is determined by the caller or user.

Behavior (Behavior)

Scope of this skill

Analyze：the SQL and query logic in the given scope(files, snippets, or diffs). Accepts .sql files, embedded SQL in application code, or ORM-generated SQL (if visible).
Do not: decide the scope (diff vs codebase); do not perform a full application security or architecture review. Focus on the SQL/query dimension.

Review checklist (SQL dimension only)

Injection and parameterization: user input in SQL without string concatenation or interpolation; use parameterized queries or prepared statements; avoid dynamic SQL from untrusted input.
Indexes and execution plans: queries filtering or joining on unindexed columns; SELECT * on large tables; missing indexes for WHERE/JOIN/ORDER BY.
Transactions and isolation: appropriate transaction boundaries; isolation levels and locking; avoid long-running transactions; deadlock risk.
NULL and unique constraints: handling of NULL in comparisons and aggregations; unique constraints and duplicate handling; NOT NULL where appropriate.
Dialect and portability: database-specific syntax (e.g., LIMIT vs OFFSET/FETCH, date functions) and portability (if multi-database support is needed).
Large tables and pagination: full scans on large tables; pagination (keyset vs OFFSET) and scalability.
Sensitive columns and permissions: sensitive data in SELECT; least privilege and role usage in SQL (if visible).

Tone and references

Professional and technical: reference specific locations (file:line or query identifier). Emit results containing location, category, severity, title, description, recommendation.

Input & Output (Input & Output)

Input (Input)

Code scope: files or snippets containing SQL (e.g., .sql files, code with embedded SQL, or ORM-generated SQL (if available)). Provided by the user or the scope skill.

Output (Output)

Emit zero or moreresultsin the format defined inAppendix: Output Contract。
The category of this skill islanguage-sql。

Limits (Limits)

Hard Boundaries (Hard Boundaries)

Do notperform scope selection or a comprehensive security/architecture review. Follow SQL and query conventions.
Do notgive conclusions without specific locations or actionable recommendations.
Do notassume a specific database vendor unless otherwise stated; note the dialect when relevant.

Skill Boundaries (Skill Boundaries)

Do not do these(other skills can handle them):

Do not select or define the code scope - the scope is determined by the caller or "review-code"
Do not perform broad security analysis beyond SQL injection - use "review-security"
Do not perform architecture analysis - use "review-architecture"
Do not review SQL conventions in non-SQL code (SQL injection in application code should be flagged by "review-security")

When to stop and hand off：

Once all SQL results are emitted, hand them off to "orchestrate-code-review" for aggregation
When the user needs broader security analysis (authentication, encryption, configuration), redirect to "review-security"
When the user needs a comprehensive review (scope+language+cognitive), redirect to "review-code"

Self-Check

Core success criteria

SQL-only scope: only SQL and query conventions are reviewed; no scope selection, full security, or architecture analysis is performed
Covers all seven SQL dimensions: injection/parameterization, indexes/execution plans, transactions/isolation, NULL/unique constraints, dialect/portability, large table/pagination patterns, and related sensitive columns/permissions
Result format compatible: each result includes location, category (language-sql), severity, title, description, and optional recommendation
Critical injection issues flagged: SQL injection patterns (string concatenation, user input interpolation) are flagged with "critical" severity
Precise location references: all results reference a specific file:line or query identifier location

Process quality checks

Were only SQL/query dimensions reviewed (no scope/architecture beyond query design)?
Are the relevant parameterization, indexes, transactions, NULL/constraints, and portability covered?
Does each finding include location, category=language-sql, severity, title, description, and optional recommendation?
Are issues referenced via file:line or query identifier?

Acceptance tests

Does the output contain a list of SQL results covering all relevant query dimensions, with injection risks flagged as "critical" and a specific location reference for each result?

Examples

Example 1: String concatenation in a query

Input: A query built using string concatenation (including user input).
Expected: Emit a critical finding for SQL injection; recommend parameterized queries or prepared statements. Category=language-sql.

Example 2: Large table without pagination

Input: SELECT * FROM large_table ORDER BY id, with no LIMIT or pagination.
Expected: Emit a finding for performance and scalability; recommend pagination (e.g., keyset or OFFSET/FETCH), and avoid SELECT * when not needed. Category=language-sql.

Edge case: ORM-generated SQL

Input: Application code that only uses an ORM; the generated SQL is not visible.
Expected: Check for any raw SQL or query builder in the code; if no SQL is visible, state so and skip or report "no SQL in scope to review." Do not invent SQL.