Sécurité et intégrité avec PRAGMA

La fiabilité d’une base de données repose sur sa capacité à maintenir l’intégrité des données et à offrir un niveau de sécurité approprié. SQLite fournit plusieurs PRAGMA qui vous aident à vérifier et à renforcer ces aspects critiques. Dans cette section, nous explorons ces outils essentiels pour protéger vos données.

Vérification de l’intégrité

PRAGMA integrity_check

Ce PRAGMA est l’un des plus importants pour vérifier l’intégrité de votre base de données. Il effectue une analyse complète pour détecter les problèmes structurels.

-- Vérification complète
PRAGMA integrity_check;

-- Vérification limitée aux N premières erreurs
PRAGMA integrity_check(10);

Si tout va bien, SQLite retourne une ligne unique contenant “ok”. Sinon, il retourne une description pour chaque problème détecté.

PRAGMA quick_check

Une version plus rapide mais moins complète d’integrity_check :

-- Vérification rapide
PRAGMA quick_check;

-- Vérification rapide limitée aux N premières erreurs
PRAGMA quick_check(5);

Utilisez ce PRAGMA pour des vérifications régulières où le temps est un facteur important.

Contraintes et clés étrangères

PRAGMA foreign_keys

Les contraintes de clé étrangère ne sont pas activées par défaut dans SQLite. Ce PRAGMA les active ou les désactive :

-- Vérifier si les clés étrangères sont activées
PRAGMA foreign_keys;

-- Activer les contraintes de clé étrangère
PRAGMA foreign_keys = ON;

-- Désactiver les contraintes de clé étrangère
PRAGMA foreign_keys = OFF;

Note importante : Ce paramètre doit être défini séparément pour chaque connexion à la base de données.

PRAGMA defer_foreign_keys

Ce PRAGMA permet de différer la vérification des contraintes de clé étrangère à la fin de la transaction :

-- Activer les clés étrangères différées
PRAGMA defer_foreign_keys = ON;

Utile lorsque vous devez insérer ou mettre à jour des données dans un ordre qui violerait temporairement les contraintes.

PRAGMA foreign_key_check

Vérifie si des contraintes de clé étrangère sont actuellement violées :

-- Vérifier toutes les contraintes de clé étrangère
PRAGMA foreign_key_check;

-- Vérifier une table spécifique
PRAGMA foreign_key_check(ma_table);

PRAGMA foreign_key_list

Affiche toutes les contraintes de clé étrangère définies pour une table :

-- Lister les contraintes de clé étrangère d'une table
PRAGMA foreign_key_list(ma_table);

Sécurité des données sensibles

PRAGMA secure_delete

Quand des données sont supprimées de la base, SQLite peut écraser l’espace libéré avec des zéros pour éviter la récupération des données sensibles :

-- Vérifier le statut actuel
PRAGMA secure_delete;

-- Activer la suppression sécurisée
PRAGMA secure_delete = ON;

-- Désactiver la suppression sécurisée
PRAGMA secure_delete = OFF;

Activez cette option pour les bases de données contenant des informations sensibles, bien que cela puisse réduire légèrement les performances.

Protection contre les modifications involontaires

PRAGMA query_only

Ce PRAGMA permet de mettre une base de données en mode lecture seule au niveau de la connexion :

-- Activer le mode lecture seule
PRAGMA query_only = ON;

-- Désactiver le mode lecture seule
PRAGMA query_only = OFF;

Utile pour éviter les modifications accidentelles lorsque vous n’avez besoin que de lire des données.

PRAGMA trusted_schema

Contrôle si les fonctions SQL et les tables virtuelles définies dans le schéma sont considérées comme fiables :

-- Vérifier l'état actuel
PRAGMA trusted_schema;

-- Considérer le schéma comme non fiable
PRAGMA trusted_schema = OFF;

Il est recommandé de définir cette valeur à OFF pour les applications qui ne créent pas dynamiquement de schéma, afin de se protéger contre certaines attaques.

Intégrité des contraintes

PRAGMA ignore_check_constraints

Détermine si les contraintes CHECK sont ignorées :

-- Ignorer temporairement les contraintes CHECK
PRAGMA ignore_check_constraints = ON;

-- Réactiver les contraintes CHECK
PRAGMA ignore_check_constraints = OFF;

Ce PRAGMA est principalement utile pour les opérations de maintenance ou de récupération de données.

Diagnostics et information

PRAGMA database_list

Affiche la liste des bases de données attachées à la connexion actuelle :

PRAGMA database_list;

Utile pour comprendre la structure des bases de données dans une connexion multi-bases.

PRAGMA table_info

Fournit des informations détaillées sur les colonnes d’une table :

PRAGMA table_info(ma_table);

Les informations incluent les noms de colonnes, les types, les contraintes NOT NULL et les valeurs par défaut.

PRAGMA index_list et index_info

Affiche les index d’une table et les détails d’un index spécifique :

-- Lister tous les index d'une table
PRAGMA index_list(ma_table);

-- Afficher les détails d'un index spécifique
PRAGMA index_info(nom_index);

Ces informations sont précieuses pour comprendre et optimiser la structure de votre base de données.

Points de contrôle WAL

PRAGMA wal_checkpoint

Lorsque vous utilisez le mode journal WAL, ce PRAGMA permet de contrôler les points de contrôle :

-- Point de contrôle passif (sans bloquer)
PRAGMA wal_checkpoint(PASSIVE);

-- Point de contrôle complet (peut bloquer)
PRAGMA wal_checkpoint(FULL);

-- Point de contrôle et redémarrage du fichier WAL
PRAGMA wal_checkpoint(RESTART);

-- Point de contrôle et troncature du fichier WAL
PRAGMA wal_checkpoint(TRUNCATE);

Les points de contrôle sont essentiels pour maintenir les performances et l’intégrité en mode WAL.

Cas pratique : Configuration sécurisée pour données sensibles

Voici une configuration qui met l’accent sur la sécurité et l’intégrité des données :

-- Activer les contraintes de clé étrangère
PRAGMA foreign_keys = ON;

-- Utiliser le mode WAL pour réduire le risque de corruption
PRAGMA journal_mode = WAL;

-- Synchronisation complète pour éviter la perte de données
PRAGMA synchronous = FULL;

-- Suppression sécurisée des données sensibles
PRAGMA secure_delete = ON;

-- Considérer le schéma comme non fiable
PRAGMA trusted_schema = OFF;

-- Vérification régulière de l'intégrité (à exécuter périodiquement)
-- PRAGMA integrity_check;

-- Point de contrôle WAL périodique
-- PRAGMA wal_checkpoint(FULL);

Bonnes pratiques pour la sécurité et l’intégrité

Vérifiez régulièrement l’intégrité : Exécutez integrity_check ou quick_check périodiquement.
Activez les contraintes de clé étrangère : Elles sont essentielles pour l’intégrité référentielle.
Utilisez secure_delete pour les données sensibles : Empêche la récupération des données supprimées.
Soyez prudent avec les privilèges : Utilisez query_only lorsque seule la lecture est nécessaire.
Effectuez des sauvegardes régulières : Aucun PRAGMA ne remplace une bonne stratégie de sauvegarde.
Utilisez WAL avec des points de contrôle appropriés : Réduisez les risques de corruption.
Vérifiez la cohérence après des pannes : Utilisez integrity_check après des arrêts anormaux.

Vérification et récupération après une panne

Si votre application s’arrête de façon inattendue ou si vous suspectez une corruption, suivez ces étapes :

Effectuez une vérification d’intégrité :
```
PRAGMA integrity_check;
```
Si des problèmes sont détectés, essayez de restaurer à partir d’une sauvegarde.
Si aucune sauvegarde n’est disponible, vous pouvez tenter une récupération partielle des données en:
- Attachant la base de données en lecture seule
- Créant une nouvelle base de données
- Copiant les données table par table
Vérifiez les contraintes de clé étrangère après la récupération :
```
PRAGMA foreign_key_check;
```