Protezione contro SQL-injection e altri attacchi: PREPARE, EXECUTE, parameterized queries

Allora, prima capiamo cosa stiamo proteggendo, o meglio — da chi. All'inizio di questo livello abbiamo già menzionato le SQL-injection, uno degli attacchi più popolari e distruttivi ai database. L'attacco funziona così: un malintenzionato invia codice SQL dannoso nella tua query per "fregarla" e ottenere accesso ai dati a cui non dovrebbe accedere. Ora vediamo meglio come funziona.

Esempio di SQL-injection

Supponiamo che tu abbia una web app con un semplice input per username e password. Il backend esegue una query SQL per verificare se l'utente esiste nel database:

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

Questa query sembra perfetta... finché l'utente inserisce dati corretti. Ma cosa succede se qualcuno inserisce:

• Username: admin' --
• Password: (lasciamo questo campo vuoto)

Alla fine la query diventa qualcosa tipo:

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

Nota i caratteri --, che in SQL indicano l'inizio di un commento. Tutto quello che viene dopo viene ignorato. Così il controllo della password viene saltato e il malintenzionato entra come admin!

Conseguenze potenziali delle SQL-injection

Una SQL-injection può portare a conseguenze terribili:

1. Accesso non autorizzato ai dati. Per esempio, qualcuno può vedere dati sensibili come le password degli utenti.
2. Cancellazione o modifica dei dati. Qualcuno può cancellare tutta una tabella o fare casino nei dati.
3. Esecuzione di codice SQL arbitrario. Immagina che qualcuno lanci il comando DROP DATABASE... Sì, roba da incubo.

Ma noi non ci spaventiamo! PostgreSQL ci dà un sacco di strumenti per difenderci da questi attacchi.

Come difendersi? Metodi per prevenire le SQL-injection

1. Uso di query preparate (PREPARE e EXECUTE)

Le query preparate (prepared statements) sono come una ricetta collaudata per il tuo codice SQL. Funzionano così: "prepari" la query una volta, poi ci passi i dati separatamente. Così è impossibile infilare codice malevolo.

Ecco un esempio di come si fa bene:

Prepara la query usando PREPARE.

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

La query con due parametri $1 e $2 aspetta che i valori veri vengano passati dopo.

Usa EXECUTE per eseguire la query.

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

In questo modo PostgreSQL fa l'escape automatico di tutti i dati dell'utente, bloccando qualsiasi tentativo di SQL-injection.

Vantaggi di questo approccio:

• Impossibile fare SQL-injection, perché i parametri sono trattati come dati semplici, non come parte del codice SQL.
• Le query sono più sicure e vanno anche più veloci grazie alla cache del piano di esecuzione.

2. Parameterized queries

Questo metodo è super usato nelle app scritte in linguaggi come Python o Java. Invece di lavorare a mano con PREPARE e EXECUTE, puoi usare librerie o ORM che gestiscono i parametri in automatico.

Esempio con Python e la libreria psycopg2:

import psycopg2

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

cursor = connection.cursor()

# Uso di una parameterized query
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# I tuoi dati sono totalmente al sicuro!
result = cursor.fetchall()
print(result)

Nota il %s nella query SQL — è il posto dove va il parametro. La libreria psycopg2 si occupa di passare i dati in modo sicuro.

3. Validazione dell'input

Se passi dati inseriti dall'utente, assicurati che siano come ti aspetti. Per esempio:

• Per i dati di testo usa le regex per controllare che non ci siano caratteri vietati.
• Per i dati numerici controlla che siano davvero numeri.

Esempio in Python:

import re

username = input("Inserisci il nome utente: ")

# Permettiamo solo lettere, numeri e underscore
if re.match(r"^\w+$", username):
    print("Nome utente valido")
else:
    print("Nome utente pericoloso!")

4. Usa privilegi minimi

Assicurati che i ruoli usati per eseguire le query abbiano solo i privilegi strettamente necessari. Per esempio, non dare accesso a DROP TABLE o ALTER TABLE se non serve.

5. Logga le attività sospette

Puoi usare i parametri di PostgreSQL per tracciare l'attività degli utenti:

• log_statement = 'all' — logga tutte le query.
• log_connections = on — logga tutte le connessioni al database.

Questi parametri aiutano a scoprire attività potenzialmente dannose.

Esempi pratici

Esempio 1: Uso di una query preparata in SQL

-- Creiamo una query preparata
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- Eseguiamo la query con parametri sicuri
EXECUTE check_credentials('admin', 'password123');

Esempio 2: Parameterized query in Python

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

Consigli di sicurezza

Controlla sempre l'input dell'utente. Non fidarti mai dei dati che arrivano dall'utente.

Usa solo query preparate o parameterized queries. Questi metodi sono la tua barriera principale contro le SQL-injection.

Dai ai ruoli solo i privilegi strettamente necessari. Così, anche se qualcuno entra, il danno è minimo.

Configura il logging e controlla spesso i log. Tieni sempre d'occhio cosa succede nel tuo database.

Le SQL-injection fanno paura, ma usando query preparate, parameterized queries e seguendo le buone pratiche, puoi proteggere il tuo database e dormire tranquillo la notte, senza paura che qualcuno ti cancelli tutte le tabelle "per sbaglio". PostgreSQL ti dà tutti gli strumenti, quindi vai tranquillo!