Meilleures pratiques de sécurité pour les applications mssql-python

Sécurisez vos applications mssql-python en suivant ces meilleures pratiques pour l’authentification, les requêtes paramétrées et la protection des données.

Commencez par une authentification sans mot de passe dès que vous le pouvez. Traitez les fichiers locaux .env et les mots de passe SQL comme des aides temporaires au développement, et transférez les secrets dans des identités gérées ou un stockage secret avant que le code n’atteigne un environnement partagé.

Sécurité de l’authentification

Utilisez l’authentification Microsoft Entra plutôt que l’authentification SQL

L’authentification Microsoft Entra élimine les mots de passe stockés et prend en charge les identités gérées. Je la préfère à l’authentification SQL dans tous les environnements.

Pour les charges de travail hébergées dans Azure, utilisez une identité gérée avec ActiveDirectoryMSI. Il n’a pas besoin de secrets stockés et se connecte sans passer par une chaîne d’accréditation :

import mssql_python

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

Pour le développement local, utilisez ActiveDirectoryDefault, qui récupère automatiquement votre Azure CLI ou d’autres identifiants de développeur. Évitez-le en production, car DefaultAzureCredential essaie chaque fournisseur d’informations d’identification dans l’ordre lors de la première connexion, ce qui ajoute une latence dont les charges de travail de production n’ont pas besoin :

import mssql_python

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

À éviter : L’authentification SQL stocke les identifiants dans le code/la configuration et est vulnérable aux fuites.


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

Ne codez jamais les identifiants en dur

Utilisez uniquement les variables d’environnement pour le développement local. Dans les environnements partagés, il faut privilégier l’authentification sans mot de passe. Quand un flux d’authentification SQL hérité est inévitable, récupérez le secret à l’exécution depuis un stockage secret au lieu de vérifier une chaîne de connexion complète dans le contrôle de version.

L’approche suivante code durement les identifiants et ne doit jamais être utilisée :

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

Pour le développement local, lisez les identifiants à partir des variables de l’environnement :

import os

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

Pour les environnements partagés qui nécessitent encore un secret, récupérez-le à l’exécution depuis 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

Prévention de l’injection de code SQL

Utilisez toujours des requêtes paramétrées

Les requêtes paramétrées empêchent l’injection SQL en séparant l’entrée utilisateur de la structure de requête. Parametriser toujours les entrées de l’utilisateur.

La requête suivante formatée en chaîne de caractères est vulnérable à l’injection SQL. Ne construisez jamais de requêtes de cette façon :

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

La requête paramétrée suivante est sûre, car le pilote envoie la valeur séparément du texte de la requête :

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

Paramétrez tous les composants de requête

Vous ne pouvez pas paramétrer directement les noms des tables et des colonnes. Les interpoler à partir des saisies de l’utilisateur rend votre application vulnérable à l’injection SQL :

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

À la place, validez les identifiants dynamiques par rapport à une liste de permis de valeurs permises, et paramétrez les valeurs restantes.

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()

Valider et désinfecter les entrées

Lorsque vous construisez du SQL dynamique avec des identifiants, validez chaque valeur par rapport à un schéma strict avant de l’utiliser :

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()}
    """)

Utiliser des procédures stockées pour des opérations complexes

Les procédures stockées réduisent la surface SQL exposée au code applicatif :

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

Sécurité de la connexion

Nécessite un chiffrement

Chiffrez toujours les connexions. Azure SQL applique le chiffrement par défaut. Pour le SQL Server sur site, définissez Encrypt=yes explicitement :

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

Utilisez le mode strict TDS 8.0 pour la sécurité la plus élevée

TDS 8.0 propose :

  • TLS 1.3 depuis le début de la connexion
  • Validation de certificats requise
  • Pas de recours aux anciens protocoles
conn = mssql_python.connect(
    "Server=tcp:<server>.database.windows.net,1433;"
    "Database=<database>;"
    "Encrypt=strict"
)

Valider les certificats serveur

Validez toujours le certificat serveur en production pour prévenir les attaques adversaires intermédiaires. Ne définissez pas TrustServerCertificate=yes, car cela permet de contourner la validation. À la place, configurez TrustServerCertificate=no pour valider avec les certificats de l’AC, et HostNameInCertificate pour vérifier le nom d’hôte :

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

Protection de données

Protéger les données sensibles au niveau serveur

Always Encrypted n'est pas actuellement configurable via les mots-clés de chaîne de connexion mssql-python. Si vous avez besoin d’Ever Encrypted, utilisez pyodbc avec le pilote ODBC pour SQL Server, qui le supporte. Bien qu'ils n'offrent pas le même niveau de protection, vous pouvez utiliser des fonctionnalités de SQL Server comme le masquage dynamique des données et la sécurité au niveau des lignes pour protéger les colonnes sensibles.

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

row = cursor.fetchone()

Protection des données en transit

  • Utilisez Encrypt=yes dans les chaînes de connexion.
  • Utilisez un VPN ou des points de terminaison privés pour les connexions sur site.
  • Utilisez Azure Private Link pour Azure SQL.

Principe du privilège minimum

Utilisez des permissions minimales de base de données

Les comptes applications devraient avoir des permissions minimales. N’utilisez pas sa ou db_owner pour les connexions d’application.

  • Reportage en lecture seule : GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
  • Accès spécifique à la table : GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
  • Procédure stockée seulement : GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Utilisez différents comptes pour différentes opérations

Sauvegardez chaque niveau de privilège avec sa propre identité afin qu’un chemin de lecture ne puisse pas effectuer d’écritures. Cet exemple utilise deux identités managées attribuées par l’utilisateur, l’une accordée à l’accès en lecture seule et l’autre à l’écriture, sélectionnées par identifiant 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;"
    )

Gardez les secrets des rôles applicatifs hors du contrôle de version

Les mots de passe pour les postes d’application restent des secrets. Conservez-les dans un coffre-fort de secrets ou dans une variable d’environnement alimentée par un secret, et effectuez leur rotation avec le même soin que pour toute autre information d’authentification.

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")

Audit et journalisation

Enregistrer les événements de sécurité

Ne consignez pas les données sensibles, mais enregistrez les événements liés à la sécurité comme les connexions défaillantes, les erreurs d’autorisation et les requêtes suspectes.

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

Opérations sensibles à l’audit

Configurez l’audit de base de données pour les tables et opérations sensibles. Vous pouvez également mettre en place un audit au niveau de l’application pour les actions critiques.

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()
    })

Ne jamais enregistrer les données sensibles

N’incluez pas les valeurs des paramètres dans les messages du journal. Enregistrez l’opération, pas les données.

Éviter - fait fuir la valeur dans le journal :

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

Recommandé - enregistre l’intention sans exposer les valeurs :

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

Gestion des erreurs

Ne révèle pas les détails internes

Consignez les erreurs détaillées en interne mais renvoyez des messages génériques aux utilisateurs pour éviter la fuite de la structure de la base de données :

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")

Désinfecter les messages d’erreur

Associez les exceptions de base de données à des messages clairs pour l’utilisateur qui ne révèlent pas les détails d’implémentation :

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."

Liste de contrôle de sécurité

Liste de contrôle de la sécurité des connexions

  • [ ] Utilisez l’authentification Microsoft Entra lorsque c’est possible.
  • [ ] Activer le chiffrement (Encrypt=yes).
  • [ ] Validez les certificats du serveur.
  • [ ] Stockez les identifiants dans un coffre-fort sécurisé.
  • [ ] Conservez les fichiers .env uniquement en local, et injectez les secrets dans la plateforme cible dans des environnements partagés.
  • [ ] Utilisez le mode strict TDS 8.0 pour Azure SQL.

Sécurité des requêtes

  • [ ] Utilisez toujours des requêtes paramétrées.
  • [ ] Validez les identifiants dynamiques.
  • [ ] Utilisez des procédures stockées pour la logique complexe.
  • [ ] Limitez la taille des résultats des requêtes.

Sécurité des données

  • [ ] Utilisez la protection des données au niveau serveur (masquage, sécurité au niveau des lignes).
  • [ ] Utilisez la sécurité au niveau des rangées lorsque cela est approprié.
  • [ ] Masquer les données sensibles dans les logs.

Contrôle d’accès

  • [ ] Appliquez le principe du moindre privilège.
  • [ ] Comptes de lecture et d’écriture distincts.
  • [ ] Auditez régulièrement les autorisations.
  • [ ] Implémentez des délais d’expiration de connexion.

Monitoring

  • [ ] Enregistrez les événements de sécurité.
  • [ ] Surveillez les anomalies.
  • [ ] Mettez en place des alertes en cas de défaillance.
  • [ ] Effectuez des contrôles de sécurité réguliers.

Contenu connexe

  • Last updated on