Back to Details

review-sql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Skill: Review SQL

技能：SQL审查

Purpose

目的

Review SQL and query-related code for language and query conventions only. Cover injection and parameterization, indexing and execution-plan concerns, transactions and isolation, NULL and unique constraints, dialect portability, large-table and paging patterns, and sensitive columns and permissions. Emit a findings list in the standard format for aggregation. Do not define scope or perform full security/architecture review; injection is in scope here as an SQL-specific concern, but broader security is for review-security.
仅针对SQL及查询相关代码的语言与查询规范进行审查。涵盖注入与参数化、索引与执行计划相关问题、事务与隔离级别、NULL值与唯一约束、方言可移植性、大表与分页模式，以及敏感列与权限。按照标准格式输出问题发现列表以进行汇总。请勿定义审查范围或执行全面的安全/架构审查；注入问题作为SQL特有关注点在此范围内，但更广泛的安全审查需参考review-security。

Use Cases

适用场景

  • Orchestrated review: Used as the language step when review-code runs for projects that include SQL (.sql files, embedded SQL, or ORM-generated SQL).
  • SQL-only review: When the user wants only query correctness, performance, and safety checked.
  • Migration or portability: Check dialect-specific constructs and portability across databases.
When to use: When the code under review includes SQL (raw .sql, embedded in code, or ORM-generated). Scope (diff vs paths) is determined by the caller or user.
  • 编排式审查：当review-code针对包含SQL的项目（.sql文件、嵌入式SQL或ORM生成的SQL）运行时，作为语言层面的审查步骤使用。
  • 仅SQL审查：当用户仅需检查查询的正确性、性能与安全性时使用。
  • 迁移或可移植性检查：检查特定方言的语法结构，以及跨数据库的可移植性。
使用时机：当待审查代码包含SQL时（原生.sql文件、嵌入在代码中的SQL或ORM生成的SQL）。审查范围（差异对比 vs 指定路径）由调用者或用户决定。

Behavior

行为规范

Scope of this skill

本技能的审查范围

  • Analyze: SQL and query logic in the given scope (files, snippets, or diff). Accept .sql files, embedded SQL in application code, or ORM-generated SQL when visible.
  • Do not: Decide scope (diff vs codebase); do not perform full application security or architecture review. Focus on SQL/query dimension.
  • 分析内容：指定范围内（文件、代码片段或差异）的SQL与查询逻辑。支持.sql文件、应用代码中的嵌入式SQL，以及可见的ORM生成SQL。
  • 禁止操作：不得决定审查范围（差异对比 vs 整个代码库）；不得执行全面的应用安全或架构审查。仅聚焦于SQL/查询维度。

Review checklist (SQL dimension only)

审查检查清单（仅SQL维度）

  1. Injection and parameterization: No string concatenation or interpolation for user input in SQL; use parameterized queries or prepared statements; avoid dynamic SQL from untrusted input.
  2. Indexing and execution plan: Queries that filter or join on unindexed columns; SELECT * on large tables; missing indexes for WHERE/JOIN/ORDER BY.
  3. Transactions and isolation: Appropriate transaction boundaries; isolation level and locking; avoid long-running transactions; deadlock risk.
  4. NULL and unique constraints: Handling of NULL in comparisons and aggregates; unique constraints and duplicate handling; NOT NULL where appropriate.
  5. Dialect and portability: Database-specific syntax (e.g. LIMIT vs OFFSET/FETCH, date functions) and portability if multi-DB support is required.
  6. Large tables and paging: Full scans on large tables; paging (keyset vs OFFSET) and scalability.
  7. Sensitive columns and permissions: Sensitive data in SELECT; least-privilege and role usage in SQL (where visible).
  1. 注入与参数化：SQL中不得使用字符串拼接或插值处理用户输入；需使用参数化查询或预编译语句；避免来自不可信输入的动态SQL。
  2. 索引与执行计划：针对未索引列进行过滤或关联的查询；在大表上使用SELECT *；WHERE/JOIN/ORDER BY语句缺失对应索引。
  3. 事务与隔离级别：事务边界是否合理；隔离级别与锁机制；避免长事务；死锁风险。
  4. NULL值与唯一约束：比较与聚合操作中对NULL值的处理；唯一约束与重复值处理；在合适的场景使用NOT NULL。
  5. 方言与可移植性：数据库特定语法（如LIMIT与OFFSET/FETCH的差异、日期函数），以及多数据库支持场景下的可移植性。
  6. 大表与分页：对大表执行全表扫描；分页方式（键集分页 vs OFFSET）与可扩展性。
  7. 敏感列与权限：SELECT语句中包含敏感数据；SQL中使用最小权限原则与角色（若可见）。

Tone and references

语气与参考规范

  • Professional and technical: Reference specific locations (file:line or query identifier). Emit findings with Location, Category, Severity, Title, Description, Suggestion.
  • 专业且技术化：标注具体位置（文件:行号或查询标识符）。输出的问题发现需包含位置、类别、严重程度、标题、描述、建议。

Input & Output

输入与输出

Input

输入

  • Code scope: Files or snippets containing SQL (e.g. .sql files, code with embedded SQL, or ORM-generated SQL when available). Provided by the user or scope skill.
  • 代码范围：包含SQL的文件或代码片段（例如.sql文件、含嵌入式SQL的代码，或可获取的ORM生成SQL）。由用户或范围指定技能提供。

Output

输出

  • Emit zero or more findings in the format defined in Appendix: Output contract.
  • Category for this skill is language-sql.
  • 输出零个或多个问题发现，格式需符合附录：输出约定中的定义。
  • 本技能对应的类别为language-sql。

Restrictions

限制条件

  • Do not perform scope selection or full security/architecture review. Stay within SQL and query conventions.
  • Do not give conclusions without specific locations or actionable suggestions.
  • Do not assume a specific database vendor unless stated; note dialect when relevant.
  • 禁止选择审查范围或执行全面的安全/架构审查。仅聚焦于SQL与查询规范。
  • 禁止在无具体位置或可操作建议的情况下给出结论。
  • 禁止假设特定数据库厂商，除非明确说明；相关方言需标注说明。

Self-Check

自我检查

  • Was only the SQL/query dimension reviewed (no scope/architecture beyond query design)?
  • Are parameterization, indexing, transactions, NULL/constraints, and portability covered where relevant?
  • Is each finding emitted with Location, Category=language-sql, Severity, Title, Description, and optional Suggestion?
  • Are issues referenced with file:line or query identifier?
  • 是否仅审查了SQL/查询维度（未涉及查询设计之外的范围/架构）？
  • 是否涵盖了相关的参数化、索引、事务、NULL值/约束及可移植性问题？
  • 每个问题发现是否都包含位置、类别=language-sql、严重程度、标题、描述及可选的建议？
  • 问题是否标注了文件:行号或查询标识符？

Examples

示例

Example 1: String concatenation in query

示例1：查询中使用字符串拼接

  • Input: Query built with string concatenation including user input.
  • Expected: Emit a critical finding for SQL injection; suggest parameterized query or prepared statement. Category = language-sql.
  • 输入：通过字符串拼接构建包含用户输入的查询。
  • 预期输出：输出一个SQL注入相关的严重问题发现；建议使用参数化查询或预编译语句。类别=language-sql。

Example 2: Large table without paging

示例2：大表未使用分页

  • Input: SELECT * FROM large_table ORDER BY id without LIMIT or paging.
  • Expected: Emit finding for performance and scalability; suggest paging (e.g. keyset or OFFSET/FETCH) and avoid SELECT * if not needed. Category = language-sql.
  • 输入：在大表上执行SELECT * FROM large_table ORDER BY id且未使用LIMIT或分页。
  • 预期输出：输出一个性能与可扩展性相关的问题发现；建议使用分页（如键集分页或OFFSET/FETCH），若无需全部字段则避免使用SELECT *。类别=language-sql。

Edge case: ORM-generated SQL

边缘案例：ORM生成的SQL

  • Input: Only application code using an ORM; generated SQL not visible.
  • Expected: Review any raw SQL or query builders in the code; if no SQL is visible, state that and skip or report "No SQL to review in scope." Do not invent SQL.
  • 输入：仅包含使用ORM的应用代码；生成的SQL不可见。
  • 预期输出：审查代码中的原生SQL或查询构建器；若无可视化SQL，则说明情况并跳过，或报告“当前范围内无SQL可审查”。不得虚构SQL内容。

Appendix: Output contract

附录：输出约定

Each finding MUST follow the standard findings format:
ElementRequirement
Location
path/to/file.ext
(optional line or range) or query identifier.
Category
language-sql
.
Severity
critical
| 
major
| 
minor
| 
suggestion
.
TitleShort one-line summary.
Description1–3 sentences.
SuggestionConcrete fix or improvement (optional).
Example:
markdown
- **Location**: `scripts/orders.sql:12`
- **Category**: language-sql
- **Severity**: critical
- **Title**: Query built with string concatenation; injection risk
- **Description**: User-controlled input is concatenated into the WHERE clause.
- **Suggestion**: Use parameterized query or prepared statement with placeholders.
每个问题发现必须遵循标准格式：
元素要求
位置
path/to/file.ext
（可选行号或范围）或查询标识符。
类别
language-sql
。
严重程度
critical
| 
major
| 
minor
| 
suggestion
。
标题简短的单行摘要。
描述1-3句话。
建议具体的修复或改进方案（可选）。
示例：
markdown
- **Location**: `scripts/orders.sql:12`
- **Category**: language-sql
- **Severity**: critical
- **Title**: Query built with string concatenation; injection risk
- **Description**: User-controlled input is concatenated into the WHERE clause.
- **Suggestion**: Use parameterized query or prepared statement with placeholders.