sql-code-review

SQL コードレビュー

${selection} に対して徹底的な SQL コードレビューを実行します（選択範囲がない場合はプロジェクト全体）。セキュリティ、パフォーマンス、保守性、データベースのベストプラクティスに焦点を当てます。

🔒 セキュリティ分析

SQL インジェクションの防止

-- ❌ 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;

アクセス制御と権限

• 最小権限の原則: 必要最小限の権限のみを付与する
• ロールベースアクセス: ユーザーへの直接権限ではなくデータベースロールを使用する
• スキーマセキュリティ: 適切なスキーマの所有権とアクセス制御
• 関数・プロシージャのセキュリティ: DEFINER 権限と INVOKER 権限を確認する

データ保護

• 機密データの露出: 機密カラムを含むテーブルで SELECT * を避ける
• 監査ログ: 機密性の高い操作がログに記録されることを確認する
• データマスキング: ビューや関数を使用して機密データをマスクする
• 暗号化: 機密データの暗号化ストレージを検証する

⚡ パフォーマンス最適化

クエリ構造の分析

-- ❌ 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';

インデックス戦略のレビュー

• インデックスの欠落: インデックスが必要なカラムを特定する
• 過剰なインデックス: 未使用または冗長なインデックスを見つける
• 複合インデックス: 複雑なクエリ向けの複数カラムインデックス
• インデックスのメンテナンス: 断片化または古くなったインデックスを確認する

結合の最適化

• 結合の種類: 適切な結合の種類（INNER、LEFT、EXISTS）を検証する
• 結合の順序: 結果セットが小さいものから先に最適化する
• デカルト積: 結合条件の欠落を特定して修正する
• サブクエリと JOIN: 最も効率的なアプローチを選択する

集計関数とウィンドウ関数

-- ❌ 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;

🛠️ コード品質と保守性

SQL のスタイルとフォーマット

-- ❌ 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';

命名規則

• 一貫した命名: テーブル、カラム、制約が一貫したパターンに従っている
• 説明的な名前: データベースオブジェクトに明確で意味のある名前を付ける
• 予約語: データベースの予約語を識別子として使用しない
• 大文字と小文字の区別: スキーマ全体で一貫した大文字・小文字の使用

スキーマ設計のレビュー

• 正規化: 適切な正規化レベル（過剰・過少な正規化を避ける）
• データ型: ストレージとパフォーマンスに最適なデータ型の選択
• 制約: PRIMARY KEY、FOREIGN KEY、CHECK、NOT NULL の適切な使用
• デフォルト値: カラムに対する適切なデフォルト値

🗄️ データベース固有のベストプラクティス

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
);

🧪 テストと検証

データ整合性チェック

-- 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;

パフォーマンステスト

• 実行計画: クエリの実行計画を確認する
• 負荷テスト: 現実的なデータ量でクエリをテストする
• ストレステスト: 同時負荷がかかった状態でのパフォーマンスを検証する
• リグレッションテスト: 最適化が機能を損なわないことを確認する

📊 よくあるアンチパターン

N+1 クエリ問題

-- ❌ 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;

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;

WHERE 句での関数の誤用

-- ❌ 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 レビューチェックリスト

セキュリティ

• すべてのユーザー入力がパラメータ化されている
• 文字列連結による動的 SQL の構築がない
• 適切なアクセス制御と権限が設定されている
• 機密データが適切に保護されている
• SQL インジェクションの攻撃経路が排除されている

パフォーマンス

• 頻繁にクエリされるカラムにインデックスが存在する
• 不要な SELECT * ステートメントがない
• JOIN が最適化され、適切な種類を使用している
• WHERE 句が選択的でインデックスを使用している
• サブクエリが最適化されているか JOIN に変換されている

コード品質

• 一貫した命名規則
• 適切なフォーマットとインデント
• 複雑なロジックに対する意味のあるコメント
• 適切なデータ型が使用されている
• エラーハンドリングが実装されている

スキーマ設計

• テーブルが適切に正規化されている
• 制約がデータ整合性を強制している
• インデックスがクエリパターンをサポートしている
• 外部キー関係が定義されている
• デフォルト値が適切である

🎯 レビュー出力フォーマット

問題テンプレート

## [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**:
```sql
-- Improved SQL
```

**Expected Improvement**: [Performance gain, security benefit]

総合評価

• セキュリティスコア: [1-10] - SQL インジェクション対策、アクセス制御
• パフォーマンススコア: [1-10] - クエリ効率、インデックスの使用
• 保守性スコア: [1-10] - コード品質、ドキュメント
• スキーマ品質スコア: [1-10] - 設計パターン、正規化

優先度の高い上位 3 つのアクション

1. [重大なセキュリティ修正]: SQL インジェクションの脆弱性に対処する
2. [パフォーマンス最適化]: 不足しているインデックスを追加するか、クエリを最適化する
3. [コード品質]: 命名規則とドキュメントを改善する

プラットフォーム固有の最適化とベストプラクティスを強調しつつ、実行可能でデータベースに依存しない推奨事項を提供することに重点を置きます。