Protection contre les injections SQL et autres attaques : PREPARE, EXECUTE, parameterized queries

Bon, voyons d'abord ce qu'on protège, ou plutôt — contre qui. Au début de ce niveau, on a déjà parlé des injections SQL, qui sont parmi les attaques les plus populaires et destructrices sur les bases de données. L'attaque fonctionne comme ça : un hacker envoie du code SQL malveillant dans ta requête pour la "berner" et accéder à des données auxquelles il ne devrait pas avoir accès. Maintenant, on va creuser un peu plus.

Exemple d'injection SQL

Imaginons que tu as une appli web avec un champ simple pour le nom d'utilisateur et le mot de passe. Le backend exécute une requête SQL pour vérifier si l'utilisateur existe dans la base :

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

Cette requête a l'air nickel... tant que l'utilisateur entre des données correctes. Mais que se passe-t-il si quelqu'un tape :

• Nom d'utilisateur : admin' --
• Mot de passe : (on laisse ce champ vide)

Au final, la requête va ressembler à ça :

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

Mate bien les caractères --, qui en SQL marquent le début d'un commentaire. Tout ce qui suit est ignoré. Résultat : la vérif du mot de passe saute, et le hacker se connecte comme admin !

Conséquences potentielles des injections SQL

L'injection SQL peut avoir des conséquences horribles :

1. Accès non autorisé aux données. Par exemple, un hacker peut choper des infos sensibles, genre les mots de passe des utilisateurs.
2. Suppression ou modification des données. Quelqu'un peut supprimer toute une table ou foutre le bazar dedans.
3. Exécution de code SQL arbitraire. Imagine qu'un hacker lance la commande DROP DATABASE... Ouais, c'est le cauchemar.

Mais on n'est pas du genre à flipper ! PostgreSQL te file tout un tas d'outils pour te défendre contre ce genre d'attaques.

Comment se protéger ? Méthodes pour éviter les injections SQL

1. Utilisation des requêtes préparées (PREPARE et EXECUTE)

Les requêtes préparées (prepared statements), c'est comme une recette fiable pour ton code SQL. Ça marche comme ça : tu "prépares" la requête SQL une fois, puis tu passes les données à part. Impossible d'injecter du code malveillant.

Voilà comment faire ça proprement :

Prépare la requête avec PREPARE.

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

La requête avec deux paramètres $1 et $2 attend que tu passes les vraies valeurs plus tard.

Utilise EXECUTE pour lancer la requête.

EXECUTE user_login('admin', 'password123');

Avec ça, PostgreSQL s'occupe d'échapper toutes les données utilisateur, donc pas moyen d'injecter du code SQL foireux.

Les avantages de cette méthode :

• Impossible de faire une injection SQL, car les paramètres sont traités comme de simples données, pas comme du code SQL.
• Les requêtes sont plus sûres et s'exécutent plus vite grâce au cache du plan d'exécution.

2. Requêtes paramétrées (Parameterized Queries)

Cette méthode est super populaire dans les applis codées en Python ou Java. Au lieu de gérer PREPARE et EXECUTE à la main, tu peux utiliser des libs ou des ORM qui gèrent les paramètres pour toi.

Exemple avec Python et la lib psycopg2 :

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# Utilisation d'une requête paramétrée
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# Tes données sont totalement safe !
result = cursor.fetchall()
print(result)

Regarde le %s dans la requête SQL — c'est là que le paramètre va être inséré. La lib psycopg2 s'occupe de transmettre les données en toute sécurité.

3. Validation des données d'entrée

Si tu passes des données saisies par l'utilisateur, assure-toi qu'elles correspondent à ce que tu attends. Par exemple :

• Pour les données texte, utilise des regex pour vérifier qu'il n'y a pas de caractères interdits.
• Pour les données numériques, vérifie que c'est bien des nombres.

Exemple en Python :

import re

username = input("Entrez le nom d'utilisateur : ")

# On autorise que les lettres, chiffres et underscores
if re.match(r"^\w+$", username):
    print("Nom d'utilisateur correct")
else:
    print("Nom d'utilisateur dangereux !")

4. Utilisation des privilèges minimaux

Assure-toi que les rôles utilisés pour exécuter les requêtes n'ont que les privilèges minimum nécessaires. Par exemple, ne donne pas accès aux commandes DROP TABLE ou ALTER TABLE si c'est pas utile.

5. Logger les actions suspectes

Tu peux utiliser les paramètres PostgreSQL pour suivre l'activité des utilisateurs :

• log_statement = 'all' — log de toutes les requêtes.
• log_connections = on — log de toutes les connexions à la base.

Ces paramètres t'aident à repérer les actions potentiellement dangereuses.

Exemples d'implémentation

Exemple 1 : Utilisation d'une requête préparée en SQL

-- On crée une requête préparée
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- On exécute la requête avec des paramètres safe
EXECUTE check_credentials('admin', 'password123');

Exemple 2 : Requête paramétrée en Python

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

Recommandations de sécurité

Valide toujours les données d'entrée. Ne fais jamais confiance aux données qui viennent de l'utilisateur.

Utilise uniquement des requêtes préparées ou paramétrées. Ces méthodes sont ton meilleur rempart contre les injections SQL.

Donne aux rôles uniquement les privilèges minimum nécessaires. Comme ça, même si un hacker chope un accès, il pourra pas faire trop de dégâts.

Configure le logging et vérifie régulièrement les logs. Reste au courant de ce qui se passe dans ta base.

Les injections SQL, c'est flippant, mais en utilisant les requêtes préparées, les requêtes paramétrées et en suivant les bonnes pratiques, tu protèges ta base et tu peux dormir tranquille, sans craindre qu'un mec "par hasard" efface toutes tes tables. PostgreSQL te file tous les outils pour ça, alors fonce !