Migliori pratiche di sicurezza per applicazioni mssql-python

Proteggi le tue applicazioni mssql-python seguendo queste migliori pratiche per autenticazione, query parametrizzate e protezione dei dati.

Inizia con l'autenticazione senza password ogni volta che puoi. Considera i file locali .env e le password SQL come aiuti temporanei allo sviluppo, e sposta i segreti in identità gestite o in uno store segreto prima che il codice raggiunga un ambiente condiviso.

Sicurezza dell'autenticazione

Usa l'autenticazione Microsoft Entra invece dell'autenticazione SQL

L'autenticazione Microsoft Entra elimina le password memorizzate e supporta le identità gestite. Preferisco questa all'autenticazione SQL in tutti gli ambienti.

Per i carichi di lavoro ospitati in Azure, usa un'identità gestita con ActiveDirectoryMSI. Non ha bisogno di segreti e si collega senza percorrere una catena di credenziali:

import mssql_python

def connect_with_managed_identity():
    return mssql_python.connect(
        "Server=<server>.database.windows.net;"
        "Database=<database>;"
        "Authentication=ActiveDirectoryMSI;"
        "Encrypt=yes;"
    )

Per lo sviluppo locale, usa ActiveDirectoryDefault, che rileva automaticamente la tua interfaccia della riga di comando di Azure o altre credenziali di sviluppatore. Evitalo in produzione, perché DefaultAzureCredential prova ogni provider di credenziali in ordine al primo tentativo di connessione, aggiungendo una latenza di cui i carichi di lavoro in produzione non hanno bisogno:

import mssql_python

def connect_with_default_credential():
    return mssql_python.connect(
        "Server=<server>.database.windows.net;"
        "Database=<database>;"
        "Authentication=ActiveDirectoryDefault;"
        "Encrypt=yes;"
    )

Evitare: L'autenticazione SQL memorizza le credenziali in codice/configurazione ed è vulnerabile a fughe di notizie.


conn = mssql_python.connect("Server=...;UID=user;PWD=password")

Mai codificare le credenziali in modo rigido

Usa variabili ambientali solo per lo sviluppo locale. Negli ambienti condivisi, preferisci l'autenticazione senza password. Quando un flusso legacy di autenticazione SQL è inevitabile, recupera il segreto in fase di esecuzione da un archivio sicuro dei segreti invece di inserire una stringa di connessione completa nel sistema di controllo del codice sorgente.

Il seguente approccio codifica rigidamente le credenziali e non dovrebbe mai essere utilizzato:

conn_str = "Server=<server>;UID=<login>;PWD=<password>"

Per lo sviluppo locale, leggi le credenziali dalle variabili ambientali:

import os

conn_str = (
    f"Server={os.environ['DB_SERVER']};"
    f"Database={os.environ['DB_NAME']};"
)

Per ambienti condivisi che necessitano ancora di un segreto, recuperalo in runtime da Azure Key Vault:

from azure.keyvault.secrets import SecretClient
from azure.identity import DefaultAzureCredential

def get_connection_string():
    credential = DefaultAzureCredential()
    secret_client = SecretClient(
        vault_url="https://myvault.vault.azure.net/",
        credential=credential
    )
    return secret_client.get_secret("db-connection-string").value

Prevenzione dell'iniezione SQL

Usa sempre query parametrizzate

Le query parametrizzate impediscono l'iniezione SQL separando l'input dell'utente dalla struttura della query. Parametrizza sempre l'input dell'utente.

La seguente query formattata a stringhe è vulnerabile all'iniezione SQL. Non costruire mai query in questo modo:

user_input = "'; DROP TABLE Person.Person; --"
cursor.execute(f"SELECT * FROM Person.Person WHERE FirstName = '{user_input}'")

La seguente query parametrizzata è sicura, perché il driver invia il valore separatamente dal testo della query:

user_input = "'; DROP TABLE Person.Person; --"
cursor.execute(
    "SELECT * FROM Person.Person WHERE FirstName = %(name)s",
    {"name": user_input}
)

Parametrizza tutti i componenti della query

Non puoi parametrizzare direttamente i nomi di tabelle e colonne. Interpolarli dall'input dell'utente rende la tua app vulnerabile all'iniezione SQL:

table = user_input
cursor.execute(f"SELECT * FROM {table}")

Invece, convalida gli identificatori dinamici rispetto a una lista di permessi di valori permessi e parametrizza i valori rimanenti.

ALLOWED_TABLES = {"Person.Person", "Production.Product", "Sales.SalesOrderHeader"}

def query_table(cursor, table_name: str, conditions: dict):
    """Query with validated table name."""
    if table_name not in ALLOWED_TABLES:
        raise ValueError(f"Invalid table: {table_name}")
    
    # Table name is safe, parameters are parameterized
    where_clauses = [f"{k} = %({k})s" for k in conditions.keys()]
    query = f"SELECT * FROM {table_name} WHERE {' AND '.join(where_clauses)}"
    
    cursor.execute(query, conditions)
    return cursor.fetchall()

Valida e sanifica l'input

Quando costruisci SQL dinamico con identificatori, valida ogni valore rispetto a un pattern rigoroso prima di usarlo:

import re

def validate_identifier(value: str) -> bool:
    """Validate SQL identifier (table/column name)."""
    # Only allow alphanumeric and underscore
    return bool(re.match(r'^[a-zA-Z_][a-zA-Z0-9_]*$', value))

def safe_order_by(cursor, table: str, order_column: str, direction: str):
    """Order by with validation."""
    if not validate_identifier(order_column):
        raise ValueError(f"Invalid column name: {order_column}")
    
    if direction.upper() not in ("ASC", "DESC"):
        raise ValueError(f"Invalid direction: {direction}")
    
    cursor.execute(f"""
        SELECT * FROM {table}
        ORDER BY {order_column} {direction.upper()}
    """)

Utilizzare le stored procedure per le operazioni complesse

Le procedure memorizzate riducono la superficie di esposizione SQL esposta al codice dell'applicazione:

employee_id = 5
cursor.execute("""
    EXECUTE dbo.uspGetEmployeeManagers @BusinessEntityID = %(id)s
""", {"id": employee_id})

Sicurezza delle connessioni

Richiedi crittografia

Cripta sempre le connessioni. Azure SQL applica la crittografia di default. Per SQL Server locale, imposta Encrypt=yes esplicitamente:

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Encrypt=yes;"
    "TrustServerCertificate=no"
)

Usa la modalità rigorosa TDS 8.0 per la massima sicurezza

TDS 8.0 fornisce:

  • TLS 1.3 dall'inizio della connessione
  • Convalida certificato obbligatoria
  • Nessun ritorno ai protocolli più vecchi
conn = mssql_python.connect(
    "Server=tcp:<server>.database.windows.net,1433;"
    "Database=<database>;"
    "Encrypt=strict"
)

Valida i certificati del server

Valida sempre il certificato server in produzione per prevenire attacchi avversari nel mezzo. Non impostare TrustServerCertificate=yes, perché bypassa la validazione. Invece, imposta TrustServerCertificate=no per convalidare rispetto ai certificati della CA e imposta HostNameInCertificate per verificare il nome host:

conn = mssql_python.connect(
    "Server=<server>;"
    "Database=<database>;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
    "HostNameInCertificate=<server>.domain.com"
)

Protezione dei dati

Proteggere i dati sensibili a livello di server

Always Encrypted attualmente non è configurabile tramite le parole chiave di stringa di connessione mssql-python. Se hai bisogno di Always Encrypted, usa pyodbc con il driver ODBC per SQL Server, che lo supporta. Anche se non offrono lo stesso livello di protezione, puoi utilizzare funzionalità di SQL Server come il mascheramento dinamico dei dati e la sicurezza a livello di riga per proteggere colonne sensibili.

employee_id = 1
cursor.execute("""
    SELECT NationalIDNumber, LoginID
    FROM HumanResources.Employee
    WHERE BusinessEntityID = %(id)s
""", {"id": employee_id})

row = cursor.fetchone()

Proteggere i dati in transito

  • Usa Encrypt=yes nelle stringhe di connessione.
  • Usa VPN o endpoint privati per le connessioni on-premises.
  • Usa collegamento privato di Azure per Azure SQL.

Principio del privilegio minimo

Usa permessi minimi del database

Gli account applicativi dovrebbero avere permessi minimi. Non usare sa o db_owner per le connessioni dell'applicazione.

  • Reportage di sola lettura: GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
  • Accesso specifico ai tavoli:GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
  • Solo procedura memorizzata: GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Usa account diversi per operazioni diverse

Associa a ogni livello di privilegio una propria identità, in modo che un percorso di lettura non possa eseguire operazioni di scrittura. Questo esempio utilizza due identità gestite assegnate dall'utente, una concessa per accesso in sola lettura e una per scrittura, selezionate per ID client:

readonly_client_id = os.environ["READONLY_IDENTITY_CLIENT_ID"]
readwrite_client_id = os.environ["READWRITE_IDENTITY_CLIENT_ID"]

def get_readonly_connection():
    """Connection for read-only operations."""
    return mssql_python.connect(
        f"Server={server};Database={db};"
        f"Authentication=ActiveDirectoryMSI;UID={readonly_client_id};"
        f"Encrypt=yes;ApplicationIntent=ReadOnly;"
    )

def get_readwrite_connection():
    """Connection for write operations."""
    return mssql_python.connect(
        f"Server={server};Database={db};"
        f"Authentication=ActiveDirectoryMSI;UID={readwrite_client_id};"
        f"Encrypt=yes;"
    )

Mantieni i segreti dei ruoli dell'applicazione fuori dal controllo del codice sorgente

Le password dei ruoli applicativi sono ancora segrete. Conservali in un vault o in una variabile d’ambiente in cui il segreto viene iniettato, e ruotali con la stessa cura riservata a qualsiasi altra credenziale.

def execute_with_role(cursor, role: str, query: str, params: dict):
    """Execute query with specific application role."""
    # Activate application role
    cursor.execute(
        "EXECUTE sp_setapprole @rolename = %(role)s, @password = %(pwd)s",
        {"role": role, "pwd": os.environ[f"ROLE_{role.upper()}_PWD"]}
    )
    
    try:
        cursor.execute(query, params)
        return cursor.fetchall()
    finally:
        # Reset to original context
        cursor.execute("EXECUTE sp_unsetapprole")

Controllo e registrazione

Registra gli eventi di sicurezza

Non registrare dati sensibili, ma registra eventi rilevanti per la sicurezza come connessioni fallite, errori di permesso e query sospette.

import logging

logger = logging.getLogger("db_security")

def secure_connect(connection_string: str):
    """Connect with security logging."""
    logger.info("Attempting database connection")
    
    try:
        conn = mssql_python.connect(connection_string)
        logger.info("Database connection established")
        return conn
    except mssql_python.OperationalError as e:
        logger.warning(f"Database connection failed: {type(e).__name__}")
        raise

Operazioni sensibili alla revisione

Configurare l'audit del database per tabelle e operazioni sensibili. Puoi anche implementare audit a livello applicativo per le azioni critiche.

def audit_data_access(cursor, user_id: str, action: str, resource: str):
    """Log data access for audit trail."""
    cursor.execute("""
        INSERT INTO AuditLog (UserID, Action, Resource, Timestamp, IPAddress)
        VALUES (%(user)s, %(action)s, %(resource)s, GETUTCDATE(), %(ip)s)
    """, {
        "user": user_id,
        "action": action,
        "resource": resource,
        "ip": get_client_ip()
    })

Mai registrare dati sensibili

Non includere i valori dei parametri nei messaggi di log. Registra l'operazione, non i dati.

Evitare - espone il valore nel log:

nid = "295847284"
logger.debug(f"Query: SELECT * FROM HumanResources.Employee WHERE NationalIDNumber = '{nid}'")

Consigliato - loga l'intento senza esporre i valori:

logger.debug("Executing employee lookup query")
nid = "295847284"
cursor.execute("SELECT * FROM HumanResources.Employee WHERE NationalIDNumber = %(nid)s", {"nid": nid})

Gestione degli errori

Non esporre dettagli interni

Registra internamente errori dettagliati ma restituisci messaggi generici agli utenti per evitare trapelature della struttura del database:

def safe_query(cursor, query: str, params: dict):
    """Execute query with safe error handling."""
    try:
        cursor.execute(query, params)
        return cursor.fetchall()
    except mssql_python.ProgrammingError as e:
        # Log full error internally
        logging.error(f"Query error: {e}")
        # Return generic error to user
        raise UserFacingError("An error occurred processing your request")
    except mssql_python.IntegrityError:
        raise UserFacingError("Invalid data provided")

Sanitizza i messaggi di errore

Mappare le eccezioni del database per messaggi user-friendly che non rivelano dettagli di implementazione:

class UserFacingError(Exception):
    """Exception safe to show to users."""
    pass

def handle_database_error(error: Exception) -> str:
    """Convert database errors to safe user messages."""
    if isinstance(error, mssql_python.IntegrityError):
        if "UNIQUE" in str(error):
            return "A record with this value already exists"
        if "FOREIGN KEY" in str(error):
            return "Referenced item not found"
    
    return "An error occurred. Please try again later."

Elenco di controllo della sicurezza

Checklist per la sicurezza della connessione

  • [ ] Usa l'autenticazione Microsoft Entra quando possibile.
  • [ ] Abilita la crittografia (Encrypt=yes).
  • [ ] Valida i certificati del server.
  • [ ] Conserva le credenziali in una cassaforte sicura.
  • [ ] Mantenere i file .env solo locali e inserire i segreti tramite la piattaforma di destinazione negli ambienti condivisi.
  • [ ] Usa la modalità rigorosa TDS 8.0 per Azure SQL.

Sicurezza delle query

  • [ ] Usa sempre query parametrizzate.
  • [ ] Valida gli identificatori dinamici.
  • [ ] Usa procedure memorizzate per la logica complessa.
  • [ ] Limita le dimensioni dei risultati delle query.

Sicurezza dei dati

  • [ ] Usa la protezione dati a livello di server (mascheramento, sicurezza a livello di riga).
  • [ ] Usa la sicurezza a livello di riga dove appropriato.
  • [ ] Maschera i dati sensibili nei log.

Controllo di accesso

  • [ ] Usa il principio del privilegio minimo.
  • [ ] Account distinti per lettura e scrittura.
  • [ ] Controllare regolarmente i permessi.
  • [ ] Implementa i timeout di connessione.

Monitoring

  • [ ] Registra gli eventi di sicurezza.
  • [ ] Monitorare eventuali anomalie.
  • [ ] Imposta avvisi per i guasti.
  • [ ] Effettuate revisioni regolari di sicurezza.

Contenuti correlati

  • Last updated on