Examen du Code SQL

Effectuez un examen approfondi du code SQL ${selection} (ou du projet entier s'il n'y a pas de sélection) en mettant l'accent sur la sécurité, les performances, la maintenabilité et les bonnes pratiques de base de données.

🔒 Analyse de Sécurité

Prévention des Injections SQL

-- ❌ CRITIQUE : Vulnérabilité d'injection SQL
query = "SELECT * FROM users WHERE id = " + userInput;
query = f"DELETE FROM orders WHERE user_id = {user_id}";

-- ✅ SÉCURisé : Requêtes paramétrées
-- 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;

Contrôle d'Accès et Permissions

Principe du Moindre Privilège : Accorder les permissions minimales requises
Contrôle d'Accès Basé sur les Rôles : Utiliser les rôles de base de données plutôt que des permissions directes
Sécurité du Schéma : Propriété et contrôles d'accès appropriés du schéma
Sécurité des Fonctions/Procédures : Examiner les droits DEFINER vs INVOKER

Protection des Données

Exposition de Données Sensibles : Éviter SELECT * sur les tables contenant des colonnes sensibles
Journalisation d'Audit : S'assurer que les opérations sensibles sont enregistrées
Masquage des Données : Utiliser des vues ou des fonctions pour masquer les données sensibles
Chiffrement : Vérifier le chiffrage du stockage pour les données sensibles

⚡ Optimisation des Performances

Analyse de la Structure des Requêtes

-- ❌ MAUVAIS : Patterns de requête inefficaces
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;

-- ✅ BON : Structure optimisée
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';

Examen de la Stratégie d'Indexation

Index Manquants : Identifier les colonnes qui ont besoin d'indexation
Sur-Indexation : Trouver les index inutilisés ou redondants
Index Composites : Index multi-colonnes pour les requêtes complexes
Maintenance des Index : Vérifier la fragmentation ou les index obsolètes

Optimisation des Jointures

Types de Jointures : Vérifier les types de jointures appropriés (INNER vs LEFT vs EXISTS)
Ordre des Jointures : Optimiser pour les ensembles de résultats plus petits en premier
Produits Cartésiens : Identifier et corriger les conditions de jointure manquantes
Sous-Requête vs JOIN : Choisir l'approche la plus efficace

Fonctions d'Agrégation et de Fenêtrage

-- ❌ MAUVAIS : Agrégation inefficace
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;

-- ✅ BON : Agrégation efficace
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;

🛠️ Qualité du Code et Maintenabilité

Style et Formatage SQL

-- ❌ MAUVAIS : Formatage et style médiocres
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';

-- ✅ BON : Formatage propre et lisible
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';

Conventions de Nommage

Nommage Cohérent : Les tables, colonnes et contraintes suivent des patterns cohérents
Noms Descriptifs : Noms clairs et significatifs pour les objets de la base de données
Mots Réservés : Éviter d'utiliser les mots réservés de la base de données comme identifiants
Sensibilité à la Casse : Utilisation cohérente de la casse sur le schéma

Examen de la Conception du Schéma

Normalisation : Niveau de normalisation approprié (éviter la sur/sous-normalisation)
Types de Données : Choix de types de données optimaux pour le stockage et les performances
Contraintes : Utilisation appropriée de PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL
Valeurs par Défaut : Valeurs par défaut appropriées pour les colonnes

🗄️ Bonnes Pratiques Spécifiques à la Base de Données

PostgreSQL

-- Utiliser JSONB pour les données JSON
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index GIN pour les requêtes JSONB
CREATE INDEX idx_events_data ON events USING gin(data);

-- Types tableau pour les colonnes multi-valeurs
CREATE TABLE tags (
    post_id INT,
    tag_names TEXT[]
);

MySQL

-- Utiliser les moteurs de stockage appropriés
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    data TEXT,
    expires TIMESTAMP
) ENGINE=InnoDB;

-- Optimiser pour InnoDB
ALTER TABLE large_table 
ADD INDEX idx_covering (status, created_at, id);

SQL Server

-- Utiliser les types de données appropriés
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()
);

-- Index columnstore pour l'analytique
CREATE COLUMNSTORE INDEX idx_sales_cs ON sales;

Oracle

-- Utiliser des séquences pour l'auto-incrémentation
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
);

🧪 Test et Validation

Vérifications de l'Intégrité des Données

-- Vérifier l'intégrité référentielle
SELECT o.user_id 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;

-- Vérifier la cohérence des données
SELECT COUNT(*) as inconsistent_records
FROM products 
WHERE price < 0 OR stock_quantity < 0;

Test de Performance

Plans d'Exécution : Examiner les plans d'exécution des requêtes
Test de Charge : Tester les requêtes avec des volumes de données réalistes
Test de Stress : Vérifier les performances sous charge concurrente
Test de Régression : S'assurer que les optimisations ne cassent pas la fonctionnalité

📊 Anti-Patterns Courants

Problème N+1

-- ❌ MAUVAIS : Requêtes N+1 dans le code applicatif
for user in users:
    orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- ✅ BON : Requête unique optimisée
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Suruse de DISTINCT

-- ❌ MAUVAIS : DISTINCT masquant les problèmes de jointure
SELECT DISTINCT u.name 
FROM users u, orders o 
WHERE u.id = o.user_id;

-- ✅ BON : Jointure appropriée sans DISTINCT
SELECT u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Mésusage de Fonctions dans les Clauses WHERE

-- ❌ MAUVAIS : Les fonctions empêchent l'utilisation des index
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;

-- ✅ BON : Les conditions de plage utilisent les index
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

📋 Checklist d'Examen SQL

Sécurité

[ ] Toutes les entrées utilisateur sont paramétrées
[ ] Aucune construction SQL dynamique avec concaténation de chaînes
[ ] Contrôles d'accès et permissions appropriés
[ ] Les données sensibles sont correctement protégées
[ ] Les vecteurs d'attaque par injection SQL sont éliminés

Performance

[ ] Des index existent pour les colonnes fréquemment interrogées
[ ] Aucun statement SELECT * inutile
[ ] Les JOINs sont optimisés et utilisent les types appropriés
[ ] Les clauses WHERE sont sélectives et utilisent les index
[ ] Les sous-requêtes sont optimisées ou converties en JOINs

Qualité du Code

[ ] Conventions de nommage cohérentes
[ ] Formatage et indentation appropriés
[ ] Commentaires significatifs pour la logique complexe
[ ] Types de données appropriés utilisés
[ ] Gestion d'erreurs implémentée

Conception du Schéma

[ ] Les tables sont correctement normalisées
[ ] Les contraintes appliquent l'intégrité des données
[ ] Les index supportent les patterns de requête
[ ] Les relations de clé étrangère sont définies
[ ] Les valeurs par défaut sont appropriées

🎯 Format de Sortie de l'Examen

Modèle de Problème

## [PRIORITÉ] [CATÉGORIE] : [Brève Description]

**Localisation** : [Nom de la table/vue/procédure et numéro de ligne si applicable]
**Problème** : [Explication détaillée du problème]
**Risque de Sécurité** : [Si applicable - risque d'injection, exposition de données, etc.]
**Impact sur les Performances** : [Coût de requête, impact sur le temps d'exécution]
**Recommandation** : [Correction spécifique avec exemple de code]

**Avant** :
```sql
-- SQL problématique

Après :

-- SQL amélioré

Amélioration Attendue : [Gain de performance, bénéfice de sécurité]



### Évaluation Récapitulative
- **Score de Sécurité** : [1-10] - Protection contre l'injection SQL, contrôles d'accès
- **Score de Performance** : [1-10] - Efficacité des requêtes, utilisation des index
- **Score de Maintenabilité** : [1-10] - Qualité du code, documentation
- **Score de Qualité du Schéma** : [1-10] - Patterns de conception, normalisation

### 3 Actions Prioritaires Principales
1. **[Correction de Sécurité Critique]** : Résoudre les vulnérabilités d'injection SQL
2. **[Optimisation de Performance]** : Ajouter des index manquants ou optimiser les requêtes
3. **[Qualité du Code]** : Améliorer les conventions de nommage et la documentation

Concentrez-vous sur la fourniture de recommandations exploitables et indépendantes de la base de données tout en mettant en évidence les optimisations spécifiques à la plateforme et les bonnes pratiques.

sql-code-review