sql-code-review

仓库: github/awesome-copilot
安装量: 7.4K
排名: #362

安装

npx skills add https://github.com/github/awesome-copilot --skill 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.
🔒 Security Analysis
SQL Injection Prevention
-- ❌ 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
;
Access Control & Permissions
Principle of Least Privilege
Grant minimum required permissions
Role-Based Access
Use database roles instead of direct user permissions
Schema Security
Proper schema ownership and access controls
Function/Procedure Security
Review DEFINER vs INVOKER rights
Data Protection
Sensitive Data Exposure
Avoid SELECT * on tables with sensitive columns
Audit Logging
Ensure sensitive operations are logged
Data Masking
Use views or functions to mask sensitive data
Encryption
Verify encrypted storage for sensitive data
⚡ Performance Optimization
Query Structure Analysis
-- ❌ 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'
;
Index Strategy Review
Missing Indexes
Identify columns that need indexing
Over-Indexing
Find unused or redundant indexes
Composite Indexes
Multi-column indexes for complex queries
Index Maintenance
Check for fragmented or outdated indexes
Join Optimization
Join Types
Verify appropriate join types (INNER vs LEFT vs EXISTS)
Join Order
Optimize for smaller result sets first
Cartesian Products
Identify and fix missing join conditions
Subquery vs JOIN
Choose the most efficient approach
Aggregate and Window Functions
-- ❌ 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
;
🛠️ Code Quality & Maintainability
SQL Style & Formatting
-- ❌ 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'
;
Naming Conventions
Consistent Naming
Tables, columns, constraints follow consistent patterns
Descriptive Names
Clear, meaningful names for database objects
Reserved Words
Avoid using database reserved words as identifiers
Case Sensitivity
Consistent case usage across schema
Schema Design Review
Normalization
Appropriate normalization level (avoid over/under-normalization)
Data Types
Optimal data type choices for storage and performance
Constraints
Proper use of PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
Default Values
Appropriate default values for columns
🗄️ Database-Specific Best Practices
PostgreSQL
-- 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
[
]
)
;
MySQL
-- 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
)
;
SQL Server
-- 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
;
Oracle
-- 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
)
;
🧪 Testing & Validation
Data Integrity Checks
-- 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
;
Performance Testing
Execution Plans
Review query execution plans
Load Testing
Test queries with realistic data volumes
Stress Testing
Verify performance under concurrent load
Regression Testing
Ensure optimizations don't break functionality 📊 Common Anti-Patterns 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 optimized query SELECT u . * , o . * FROM users u LEFT JOIN orders o ON u . id = o . user_id ; Overuse of DISTINCT -- ❌ 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 ; Function Misuse in WHERE Clauses -- ❌ 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' ; 📋 SQL Review Checklist Security All user inputs are parameterized No dynamic SQL construction with string concatenation Appropriate access controls and permissions Sensitive data is properly protected SQL injection attack vectors are eliminated Performance Indexes exist for frequently queried columns No unnecessary SELECT * statements JOINs are optimized and use appropriate types WHERE clauses are selective and use indexes Subqueries are optimized or converted to JOINs Code Quality Consistent naming conventions Proper formatting and indentation Meaningful comments for complex logic Appropriate data types are used Error handling is implemented Schema Design Tables are properly normalized Constraints enforce data integrity Indexes support query patterns Foreign key relationships are defined Default values are appropriate 🎯 Review Output Format Issue Template

[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.
← 返回排行榜