Najlepsze praktyki bezpieczeństwa dla aplikacji mssql-python

Zabezpiecz swoje aplikacje mssql-python, stosując się do najlepszych praktyk uwierzytelniania, parametryzowanych zapytań i ochrony danych.

Zacznij od uwierzytelniania bez hasła, kiedy tylko możesz. Lokalne pliki .env i hasła SQL traktuj jako tymczasowe rozwiązania pomocnicze na etapie programowania, a przed przeniesieniem kodu do środowiska współdzielonego przenieś wpisy tajne do zarządzanych tożsamości lub magazynu wpisów tajnych.

Zabezpieczenia uwierzytelniania

Użyj uwierzytelniania Microsoft Entra zamiast uwierzytelniania SQL

Uwierzytelnianie Microsoft Entra eliminuje przechowywane hasła i obsługuje zarządzane tożsamości. Wolę to od uwierzytelniania SQL we wszystkich środowiskach.

Dla obciążeń hostowanych w Azure używaj tożsamości zarządzanej z ActiveDirectoryMSI. Nie potrzebuje żadnych przechowywanych sekretów i łączy się bez konieczności przechodzenia przez łańcuch uwierzytelniający:

import mssql_python

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

Do lokalnego rozwoju użyj ActiveDirectoryDefault, który automatycznie odbiera Twoje dane uwierzytelniające Azure CLI lub inne poświadczenia deweloperskie. Unikaj tego w środowisku produkcyjnym, ponieważ DefaultAzureCredential przy pierwszym połączeniu po kolei wypróbowuje każdego dostawcę poświadczeń, co wprowadza dodatkowe opóźnienie, którego środowisko produkcyjne nie potrzebuje:

import mssql_python

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

Unikaj: Uwierzytelnianie SQL przechowuje dane uwierzytelniające w kodzie/konfiguracji i jest podatne na wycieki.

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

Nigdy nie koduj na stałe danych uwierzytelniających

Używaj zmiennych środowiskowych tylko do lokalnego rozwoju. W środowiskach współdzielonych preferuj uwierzytelnianie bezhasłowe. Gdy dziedziczny przepływ uwierzytelniania SQL jest nieunikniony, pobierz sekret w czasie działania z magazynu sekretów zamiast sprawdzać pełny ciąg parametry połączenia w kontroli źródeł.

Następująca metoda polega na twardym kodowaniu danych uwierzytelniających i nigdy nie powinna być używana:

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

Na potrzeby lokalnego programowania odczytuj dane uwierzytelniające ze zmiennych środowiskowych:

import os

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

W środowiskach współdzielonych, które nadal wymagają sekretu, pobierz go w czasie działania z 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

Zapobieganie wstrzykiwaniu SQL

Zawsze używaj zapytań parametryzowanych

Zapytania parametryzowane zapobiegają wstrzykiwaniu SQL, oddzielając wejście użytkownika od struktury zapytania. Zawsze parametryzuj dane wejściowe użytkownika.

Poniższe zapytanie sformatowane jako ciąg znaków jest podatne na atak typu SQL injection. Nigdy nie buduj zapytań w ten sposób:

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

Następujące parametryzowane zapytanie jest bezpieczne, ponieważ sterownik wysyła wartość oddzielnie od tekstu zapytania:

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

Parametryzuj wszystkie komponenty zapytań

Nie da się bezpośrednio parametryzować nazw tabel i kolumn. Interpolacja ich z danych wejściowych od użytkownika sprawia, że twoja aplikacja jest podatna na zastrzyk SQL:

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

Zamiast tego zweryfikuj identyfikatory dynamiczne względem listy dozwolonych wartości i parametryzuj pozostałe wartości.

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

Walidacja i oczyszczanie danych wejściowych

Budując dynamiczne SQL z identyfikatorami, waliduj każdą wartość według ścisłego wzorca przed jej użyciem:

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

Używaj procedur przechowywanych do złożonych operacji

Procedury przechowywane zmniejszają powierzchnię SQL wystawioną na kod aplikacyjny:

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

Zabezpieczenia połączeń

Wymagane szyfrowanie

Zawsze szyfruj połączenia. Azure SQL domyślnie wymusza szyfrowanie. W przypadku lokalnego programu SQL Server ustaw Encrypt=yes jawnie:

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

Użyj trybu TDS 8.0 strict mode dla najwyższego bezpieczeństwa

TDS 8.0 zapewnia:

• TLS 1.3 od początku połączenia
• Wymagana weryfikacja certyfikatu
• Nie ma powrotu do starszych protokołów

conn = mssql_python.connect(
    "Server=tcp:<server>.database.windows.net,1433;"
    "Database=<database>;"
    "Encrypt=strict"
)

Weryfikacja certyfikatów serwera

Zawsze weryfikuj certyfikat serwera w środowisku produkcyjnym, aby zapobiec atakom typu man-in-the-middle. Nie ustawaj TrustServerCertificate=yes, bo omija to walidację. Zamiast tego ustaw TrustServerCertificate=no, aby weryfikować względem certyfikatów CA, a HostNameInCertificate, aby weryfikować nazwę hosta:

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

Ochrona danych

Chroń wrażliwe dane na poziomie serwera

Always Encrypted nie można obecnie skonfigurować za pomocą słów kluczowych parametrów połączenia mssql-python. Jeśli potrzebujesz Always Encrypted, użyj pyodbc ze sterownikiem ODBC dla SQL Server, który go obsługuje. Chociaż nie zapewniają takiego samego poziomu ochrony, możesz wykorzystać funkcje SQL Server, takie jak dynamiczne maskowanie danych i bezpieczeństwo na poziomie wiersza, aby chronić wrażliwe kolumny.

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

row = cursor.fetchone()

Ochrona danych podczas przesyłania

• Zastosowanie Encrypt=yes w łańcuchach połączeń.
• Używaj VPN lub prywatnych punktów końcowych do połączeń lokalnych.
• Użyj Azure Private Link for Azure SQL.

Zasada najmniejszych uprawnień

Używaj minimalnych uprawnień do bazy danych

Konta aplikacji powinny mieć minimalne uprawnienia. Nie używaj sa ani db_owner do połączeń aplikacji.

• Raportowanie tylko do odczytu: GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
• Dostęp do konkretnych tabel:GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
• Tylko procedura przechowywana: GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Używaj różnych kont do różnych operacji

Przypisz każdemu poziomowi uprawnień własną tożsamość, aby ścieżka odczytu nie mogła wykonywać operacji zapisu. Ten przykład wykorzystuje dwie zarządzane tożsamości przypisane przez użytkownika: jedną z dostępem tylko do odczytu, a drugą z uprawnieniami do zapisu, wybieraną według identyfikatora klienta:

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

Zachowaj sekrety ról aplikacji z dala od kontroli wersji

Hasła do ról aplikacji nadal są tajemnicą. Przechowuj je w sejfie haseł lub w zmiennej środowiskowej zasilanej sekretami i rotuj je z taką samą ostrożnością jak wszelkie inne dane uwierzytelniające.

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

Inspekcja i rejestrowanie

Zdarzenia bezpieczeństwa w logach

Nie rejestruj wrażliwych danych, ale rejestruj zdarzenia istotne dla bezpieczeństwa, takie jak nieudane połączenia, błędy uprawnień i podejrzane zapytania.

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

Operacje wrażliwe na audyt

Konfiguruj audyt baz danych dla wrażliwych tabel i operacji. Możesz także wdrożyć audyt na poziomie aplikacji dla kluczowych działań.

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

Nigdy nie rejestruj wrażliwych danych

Nie włączaj wartości parametrów do komunikatów logowych. Rejestruj operację, nie dane.

Unikaj – przecieka wartość do logu:

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

Polecam – loguje intencję bez ujawniania wartości:

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

Obsługa błędów

Nie ujawniaj szczegółów wewnętrznych

Rejestruj szczegółowe błędy wewnętrznie, ale zwracaj użytkownikom ogólne komunikaty, aby uniknąć wycieku struktury bazy danych:

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

Oczyszcz komunikaty błędów

Mapuj wyjątki bazy danych na komunikaty przyjazne dla użytkownika, które nie ujawniają szczegółów implementacji:

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

Lista kontrolna zabezpieczeń

Lista kontrolna bezpieczeństwa połączenia

• [ ] Używaj uwierzytelniania Microsoft Entra, gdy to możliwe.
• [ ] Włącz szyfrowanie (Encrypt=yes).
• [ ] Zweryfikować certyfikaty serwera.
• [ ] Przechowywanie danych w bezpiecznym sejfie.
• [ ] Przechowywać .env pliki tylko lokalnie i wstrzykywać sekrety przez docelową platformę w środowiskach współdzielonych.
• [ ] Użyj trybu ścisłego TDS 8.0 dla Azure SQL.

Bezpieczeństwo zapytań

• [ ] Zawsze używaj parametryzowanych zapytań.
• [ ] Waliduj dynamiczne identyfikatory.
• [ ] Używaj procedur przechowywanych do złożonej logiki.
• [ ] Ogranicz rozmiar wyników zapytań.

Bezpieczeństwo danych

• [ ] Stosuj ochronę danych na poziomie serwera (maskowanie, bezpieczeństwo na poziomie wiersza).
• [ ] Stosuj zabezpieczenia na poziomie rzędów tam, gdzie to stosowne.
• [ ] Maskuj wrażliwe dane w logach.

Kontrola dostępu

• [ ] Stosuj zasadę najmniejszych przywilejów.
• [ ] Oddzielne konta do odczytu/zapisu.
• [ ] Regularnie audytuj uprawnienia.
• [ ] Zaimplementuj limity czasu połączeń.

Nadzorowanie

• [ ] Rejestruj zdarzenia bezpieczeństwa.
• [ ] Monitoruj pod kątem anomalii.
• [ ] Skonfiguruj alerty o awariach.
• [ ] Przeprowadzaj regularne kontrole bezpieczeństwa.

Treści powiązane

• Szyfrowanie i certyfikaty
• Uwierzytelnianie Microsoft Entra
• Tworzenie kontenerów i lokalne tworzenie
• Obsługa błędów
• Dokumentacja bezpieczeństwa SQL Server