sql-code-review by github/awesome-copilot
npx skills add https://github.com/github/awesome-copilot --skill sql-code-review对 ${selection}(如未选择则对整个项目)执行全面的 SQL 代码审查,重点关注安全性、性能、可维护性和数据库最佳实践。
-- ❌ 严重:SQL 注入漏洞
query = "SELECT * FROM users WHERE id = " + userInput;
query = f"DELETE FROM orders WHERE user_id = {user_id}";
-- ✅ 安全:参数化查询
-- PostgreSQL/MySQL
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;
-- SQL Server
EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;
-- ❌ 不佳:低效的查询模式
SELECT DISTINCT u.*
FROM users u, orders o, products p
WHERE u.id = o.user_id
AND o.product_id = p.id
AND YEAR(o.order_date) = 2024;
-- ✅ 良好:优化后的结构
SELECT u.id, u.name, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01';
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
-- ❌ 不佳:低效的聚合
SELECT user_id,
(SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
FROM orders o1
GROUP BY user_id;
-- ✅ 良好:高效的聚合
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
-- ❌ 不佳:糟糕的格式化和风格
select u.id,u.name,o.total from users u left join orders o on u.id=o.user_id where u.status='active' and o.order_date>='2024-01-01';
-- ✅ 良好:清晰、可读的格式化
SELECT u.id,
u.name,
o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND o.order_date >= '2024-01-01';
-- 对 JSON 数据使用 JSONB
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- 为 JSONB 查询创建 GIN 索引
CREATE INDEX idx_events_data ON events USING gin(data);
-- 为多值列使用数组类型
CREATE TABLE tags (
post_id INT,
tag_names TEXT[]
);
-- 使用适当的存储引擎
CREATE TABLE sessions (
id VARCHAR(128) PRIMARY KEY,
data TEXT,
expires TIMESTAMP
) ENGINE=InnoDB;
-- 为 InnoDB 优化
ALTER TABLE large_table
ADD INDEX idx_covering (status, created_at, id);
-- 使用适当的数据类型
CREATE TABLE products (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at DATETIME2 DEFAULT GETUTCDATE()
);
-- 为分析查询创建列存储索引
CREATE COLUMNSTORE INDEX idx_sales_cs ON sales;
-- 使用序列实现自增
CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE users (
id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,
name VARCHAR2(255) NOT NULL
);
-- 验证参照完整性
SELECT o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 检查数据一致性
SELECT COUNT(*) as inconsistent_records
FROM products
WHERE price < 0 OR stock_quantity < 0;
-- ❌ 不佳:应用程序代码中的 N+1 查询
for user in users:
orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)
-- ✅ 良好:单一优化查询
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- ❌ 不佳:使用 DISTINCT 掩盖连接问题
SELECT DISTINCT u.name
FROM users u, orders o
WHERE u.id = o.user_id;
-- ✅ 良好:不使用 DISTINCT 的正确连接
SELECT u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- ❌ 不佳:函数阻止索引使用
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
-- ✅ 良好:范围条件使用索引
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
## [优先级] [类别]: [简要描述]
**位置**: [表/视图/过程名称及行号(如适用)]
**问题**: [问题的详细解释]
**安全风险**: [如适用 - 注入风险、数据暴露等]
**性能影响**: [查询成本、执行时间影响]
**建议**: [包含代码示例的具体修复方案]
**修复前**:
```sql
-- 有问题的 SQL
修复后 :
-- 改进后的 SQL
预期改进 : [性能提升、安全收益]
### 总结评估
- **安全评分**: [1-10] - SQL 注入防护、访问控制
- **性能评分**: [1-10] - 查询效率、索引使用
- **可维护性评分**: [1-10] - 代码质量、文档
- **模式质量评分**: [1-10] - 设计模式、规范化
### 前三项优先行动
1. **[关键安全修复]**: 解决 SQL 注入漏洞
2. **[性能优化]**: 添加缺失索引或优化查询
3. **[代码质量]**: 改进命名规范和文档
重点提供可操作的、与数据库无关的建议,同时突出平台特定的优化和最佳实践。
周安装量
7.7K
仓库
GitHub 星标数
26.9K
首次出现
2026年2月25日
安全审计
安装于
codex7.7K
gemini-cli7.6K
opencode7.6K
cursor7.6K
github-copilot7.6K
kimi-cli7.6K
Perform a thorough SQL code review of ${selection} (or entire project if no selection) focusing on security, performance, maintainability, and database best practices.
-- ❌ 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/MySQL
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
EXECUTE stmt USING @user_id;
-- SQL Server
EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;
-- ❌ BAD: Inefficient query patterns
SELECT DISTINCT u.*
FROM users u, orders o, products p
WHERE u.id = o.user_id
AND o.product_id = p.id
AND YEAR(o.order_date) = 2024;
-- ✅ GOOD: Optimized structure
SELECT u.id, u.name, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01';
-- ❌ BAD: Inefficient aggregation
SELECT user_id,
(SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
FROM orders o1
GROUP BY user_id;
-- ✅ GOOD: Efficient aggregation
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;
-- ❌ BAD: Poor formatting and style
select u.id,u.name,o.total from users u left join orders o on u.id=o.user_id where u.status='active' and o.order_date>='2024-01-01';
-- ✅ GOOD: Clean, readable formatting
SELECT u.id,
u.name,
o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND o.order_date >= '2024-01-01';
-- Use JSONB for JSON data
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- GIN index for JSONB queries
CREATE INDEX idx_events_data ON events USING gin(data);
-- Array types for multi-value columns
CREATE TABLE tags (
post_id INT,
tag_names TEXT[]
);
-- Use appropriate storage engines
CREATE TABLE sessions (
id VARCHAR(128) PRIMARY KEY,
data TEXT,
expires TIMESTAMP
) ENGINE=InnoDB;
-- Optimize for InnoDB
ALTER TABLE large_table
ADD INDEX idx_covering (status, created_at, id);
-- Use appropriate data types
CREATE TABLE products (
id BIGINT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at DATETIME2 DEFAULT GETUTCDATE()
);
-- Columnstore indexes for analytics
CREATE COLUMNSTORE INDEX idx_sales_cs ON sales;
-- Use sequences for auto-increment
CREATE SEQUENCE user_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE users (
id NUMBER DEFAULT user_id_seq.NEXTVAL PRIMARY KEY,
name VARCHAR2(255) NOT NULL
);
-- Verify referential integrity
SELECT o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- Check for data consistency
SELECT COUNT(*) as inconsistent_records
FROM products
WHERE price < 0 OR stock_quantity < 0;
-- ❌ BAD: N+1 queries in application code
for user in users:
orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)
-- ✅ GOOD: Single optimized query
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- ❌ BAD: DISTINCT masking join issues
SELECT DISTINCT u.name
FROM users u, orders o
WHERE u.id = o.user_id;
-- ✅ GOOD: Proper join without DISTINCT
SELECT u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- ❌ BAD: Functions prevent index usage
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
-- ✅ GOOD: Range conditions use indexes
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
## [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]
### Summary Assessment
- **Security Score**: [1-10] - SQL injection protection, access controls
- **Performance Score**: [1-10] - Query efficiency, index usage
- **Maintainability Score**: [1-10] - Code quality, documentation
- **Schema Quality Score**: [1-10] - Design patterns, normalization
### Top 3 Priority Actions
1. **[Critical Security Fix]**: Address SQL injection vulnerabilities
2. **[Performance Optimization]**: Add missing indexes or optimize queries
3. **[Code Quality]**: Improve naming conventions and documentation
Focus on providing actionable, database-agnostic recommendations while highlighting platform-specific optimizations and best practices.
Weekly Installs
7.7K
Repository
GitHub Stars
26.9K
First Seen
Feb 25, 2026
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex7.7K
gemini-cli7.6K
opencode7.6K
cursor7.6K
github-copilot7.6K
kimi-cli7.6K
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
102,200 周安装