sql-code-review

Name: sql-code-review
Author: github

12kinstalls4.6kforks

How to install sql-code-review?

github/awesome-copilot/sql-code-review

$npx -y skills add github/awesome-copilot --skill sql-code-review

Installs into the current project.

Summary

Universal SQL code review assistant that performs comprehensive security, maintainability, and code quality analysis across all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Focuses on SQL injection prevention, access control, code standards, and anti-pattern detection.

Files · 1

View on GitHub

SKILL.md

1	# SQL Code Review
2
3	Perform a thorough SQL code review of ${selection} (or entire project if no selection) focusing on security, performance, maintainability, and database best practices.
4
5	## 🔒 Security Analysis
6
7	### SQL Injection Prevention
8	```sql
9	-- ❌ CRITICAL: SQL Injection vulnerability
10	query = "SELECT * FROM users WHERE id = " + userInput;
11	query = f"DELETE FROM orders WHERE user_id = {user_id}";
12
13	-- ✅ SECURE: Parameterized queries
14	-- PostgreSQL/MySQL
15	PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
16	EXECUTE stmt USING @user_id;
17
18	-- SQL Server
19	EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;
20	```
21
22	### Access Control & Permissions
23	- Principle of Least Privilege: Grant minimum required permissions
24	- Role-Based Access: Use database roles instead of direct user permissions
25	- Schema Security: Proper schema ownership and access controls
26	- Function/Procedure Security: Review DEFINER vs INVOKER rights
27
28	### Data Protection
29	- Sensitive Data Exposure: Avoid SELECT * on tables with sensitive columns
30	- Audit Logging: Ensure sensitive operations are logged
31	- Data Masking: Use views or functions to mask sensitive data
32	- Encryption: Verify encrypted storage for sensitive data
33
34	## ⚡ Performance Optimization
35
36	### Query Structure Analysis
37	```sql
38	-- ❌ BAD: Inefficient query patterns
39	SELECT DISTINCT u.*
40	FROM users u, orders o, products p
41	WHERE u.id = o.user_id
42	AND o.product_id = p.id
43	AND YEAR(o.order_date) = 2024;
44
45	-- ✅ GOOD: Optimized structure
46	SELECT u.id, u.name, u.email
47	FROM users u
48	INNER JOIN orders o ON u.id = o.user_id
49	WHERE o.order_date >= '2024-01-01'
50	AND o.order_date < '2025-01-01';
51	```
52
53	### Index Strategy Review
54	- Missing Indexes: Identify columns that need indexing
55	- Over-Indexing: Find unused or redundant indexes
56	- Composite Indexes: Multi-column indexes for complex queries
57	- Index Maintenance: Check for fragmented or outdated indexes
58
59	### Join Optimization
60	- Join Types: Verify appropriate join types (INNER vs LEFT vs EXISTS)
61	- Join Order: Optimize for smaller result sets first
62	- Cartesian Products: Identify and fix missing join conditions
63	- Subquery vs JOIN: Choose the most efficient approach
64
65	### Aggregate and Window Functions
66	```sql
67	-- ❌ BAD: Inefficient aggregation
68	SELECT user_id,
69	(SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
70	FROM orders o1
71	GROUP BY user_id;
72
73	-- ✅ GOOD: Efficient aggregation
74	SELECT user_id, COUNT(*) as order_count
75	FROM orders
76	GROUP BY user_id;
77	```
78
79	## 🛠️ Code Quality & Maintainability
80
81	### SQL Style & Formatting
82	```sql
83	-- ❌ BAD: Poor formatting and style
84	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';
85
86	-- ✅ GOOD: Clean, readable formatting
87	SELECT u.id,
88	u.name,
89	o.total
90	FROM users u
91	LEFT JOIN orders o ON u.id = o.user_id
92	WHERE u.status = 'active'
93	AND o.order_date >= '2024-01-01';
94	```
95
96	### Naming Conventions
97	- Consistent Naming: Tables, columns, constraints follow consistent patterns
98	- Descriptive Names: Clear, meaningful names for database objects
99	- Reserved Words: Avoid using database reserved words as identifiers
100	- Case Sensitivity: Consistent case usage across schema
101
102	### Schema Design Review
103	- Normalization: Appropriate normalization level (avoid over/under-normalization)
104	- Data Types: Optimal data type choices for storage and performance
105	- Constraints: Proper use of PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
106	- Default Values: Appropriate default values for columns
107
108	## 🗄️ Database-Specific Best Practices
109
110	### PostgreSQL
111	```sql
112	-- Use JSONB for JSON data
113	CREATE TABLE events (
114	id SERIAL PRIMARY KEY,
115	data JSONB NOT NULL,
116	created_at TIMESTAMPTZ DEFAULT NOW()
117	);
118
119	-- GIN index for JSONB queries
120	CREATE INDEX idx_events_data ON events USING gin(data);
121
122	-- Array types for multi-value columns
123	CREATE TABLE tags (
124	post_id INT,
125	tag_names TEXT[]
126	);
127	```
128
129	### MySQL
130	```sql
131	-- Use appropriate storage engines
132	CREATE TABLE sessions (
133	id VARCHAR(128) PRIMARY KEY,
134	data TEXT,
135	expires TIMESTAMP
136	) ENGINE=InnoDB;
137
138	-- Optimize for InnoDB
139	ALTER TABLE large_table
140	ADD INDEX idx_covering (status, created_at, id);
141	```
142
143	### SQL Server
144	```sql
145	-- Use appropriate data types
146	CREATE TABLE products (
147	id BIGINT IDENTITY(1,1) PRIMARY KEY,
148	name NVARCHAR(255) NOT NULL,
149	price DECIMAL(10,2) NOT NULL,
150	created_at DATETIME2 DEFAULT GETUTCDATE()
151	);
152
153	-- Columnstore indexes for analytics
154	CREATE COLUMNSTORE INDEX idx_sales_cs

People who liked this also viewed

6 picks

Type

sql-code-review

12kinstalls4.6kforks

View on GitHub

How to install sql-code-review?

github/awesome-copilot/sql-code-review

$npx -y skills add github/awesome-copilot --skill sql-code-review

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# SQL Code Review
2
3	Perform a thorough SQL code review of ${selection} (or entire project if no selection) focusing on security, performance, maintainability, and database best practices.
4
5	## 🔒 Security Analysis
6
7	### SQL Injection Prevention
8	```sql
9	-- ❌ CRITICAL: SQL Injection vulnerability
10	query = "SELECT * FROM users WHERE id = " + userInput;
11	query = f"DELETE FROM orders WHERE user_id = {user_id}";
12
13	-- ✅ SECURE: Parameterized queries
14	-- PostgreSQL/MySQL
15	PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
16	EXECUTE stmt USING @user_id;
17
18	-- SQL Server
19	EXEC sp_executesql N'SELECT * FROM users WHERE id = @id', N'@id INT', @id = @user_id;
20	```
21
22	### Access Control & Permissions
23	- Principle of Least Privilege: Grant minimum required permissions
24	- Role-Based Access: Use database roles instead of direct user permissions
25	- Schema Security: Proper schema ownership and access controls
26	- Function/Procedure Security: Review DEFINER vs INVOKER rights
27
28	### Data Protection
29	- Sensitive Data Exposure: Avoid SELECT * on tables with sensitive columns
30	- Audit Logging: Ensure sensitive operations are logged
31	- Data Masking: Use views or functions to mask sensitive data
32	- Encryption: Verify encrypted storage for sensitive data
33
34	## ⚡ Performance Optimization
35
36	### Query Structure Analysis
37	```sql
38	-- ❌ BAD: Inefficient query patterns
39	SELECT DISTINCT u.*
40	FROM users u, orders o, products p
41	WHERE u.id = o.user_id
42	AND o.product_id = p.id
43	AND YEAR(o.order_date) = 2024;
44
45	-- ✅ GOOD: Optimized structure
46	SELECT u.id, u.name, u.email
47	FROM users u
48	INNER JOIN orders o ON u.id = o.user_id
49	WHERE o.order_date >= '2024-01-01'
50	AND o.order_date < '2025-01-01';
51	```
52
53	### Index Strategy Review
54	- Missing Indexes: Identify columns that need indexing
55	- Over-Indexing: Find unused or redundant indexes
56	- Composite Indexes: Multi-column indexes for complex queries
57	- Index Maintenance: Check for fragmented or outdated indexes
58
59	### Join Optimization
60	- Join Types: Verify appropriate join types (INNER vs LEFT vs EXISTS)
61	- Join Order: Optimize for smaller result sets first
62	- Cartesian Products: Identify and fix missing join conditions
63	- Subquery vs JOIN: Choose the most efficient approach
64
65	### Aggregate and Window Functions
66	```sql
67	-- ❌ BAD: Inefficient aggregation
68	SELECT user_id,
69	(SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) as order_count
70	FROM orders o1
71	GROUP BY user_id;
72
73	-- ✅ GOOD: Efficient aggregation
74	SELECT user_id, COUNT(*) as order_count
75	FROM orders
76	GROUP BY user_id;
77	```
78
79	## 🛠️ Code Quality & Maintainability
80
81	### SQL Style & Formatting
82	```sql
83	-- ❌ BAD: Poor formatting and style
84	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';
85
86	-- ✅ GOOD: Clean, readable formatting
87	SELECT u.id,
88	u.name,
89	o.total
90	FROM users u
91	LEFT JOIN orders o ON u.id = o.user_id
92	WHERE u.status = 'active'
93	AND o.order_date >= '2024-01-01';
94	```
95
96	### Naming Conventions
97	- Consistent Naming: Tables, columns, constraints follow consistent patterns
98	- Descriptive Names: Clear, meaningful names for database objects
99	- Reserved Words: Avoid using database reserved words as identifiers
100	- Case Sensitivity: Consistent case usage across schema
101
102	### Schema Design Review
103	- Normalization: Appropriate normalization level (avoid over/under-normalization)
104	- Data Types: Optimal data type choices for storage and performance
105	- Constraints: Proper use of PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
106	- Default Values: Appropriate default values for columns
107
108	## 🗄️ Database-Specific Best Practices
109
110	### PostgreSQL
111	```sql
112	-- Use JSONB for JSON data
113	CREATE TABLE events (
114	id SERIAL PRIMARY KEY,
115	data JSONB NOT NULL,
116	created_at TIMESTAMPTZ DEFAULT NOW()
117	);
118
119	-- GIN index for JSONB queries
120	CREATE INDEX idx_events_data ON events USING gin(data);
121
122	-- Array types for multi-value columns
123	CREATE TABLE tags (
124	post_id INT,
125	tag_names TEXT[]
126	);
127	```
128
129	### MySQL
130	```sql
131	-- Use appropriate storage engines
132	CREATE TABLE sessions (
133	id VARCHAR(128) PRIMARY KEY,
134	data TEXT,
135	expires TIMESTAMP
136	) ENGINE=InnoDB;
137
138	-- Optimize for InnoDB
139	ALTER TABLE large_table
140	ADD INDEX idx_covering (status, created_at, id);
141	```
142
143	### SQL Server
144	```sql
145	-- Use appropriate data types
146	CREATE TABLE products (
147	id BIGINT IDENTITY(1,1) PRIMARY KEY,
148	name NVARCHAR(255) NOT NULL,
149	price DECIMAL(10,2) NOT NULL,
150	created_at DATETIME2 DEFAULT GETUTCDATE()
151	);
152
153	-- Columnstore indexes for analytics
154	CREATE COLUMNSTORE INDEX idx_sales_cs

People who liked this also viewed

6 picks

Type