Osvědčené postupy bezpečnosti pro aplikace mssql-python

Chraňte své mssql-python aplikace dodržováním těchto osvědčených postupů pro autentizaci, parametrizované dotazy a ochranu dat.

Začněte s autentizací bez hesla, kdykoli můžete. Lokální .env soubory a SQL hesla považujte za dočasné pomůcky pro vývoj a přesouvejte tajemství do spravovaných identit nebo tajného úložiště dříve, než kód dorazí do sdíleného prostředí.

Zabezpečení ověřování

Použijte autentizaci Microsoft Entra místo SQL autentizace

Autentizace Microsoft Entra eliminuje uložená hesla a podporuje spravované identity. Preferuji to před SQL autentizací ve všech prostředích.

Pro pracovní zátěže hostované v Azure použijte managed identity s ActiveDirectoryMSI. Nepotřebuje žádná uložená tajemství a propojuje se bez nutnosti procházet řetězcem oprávnění:

import mssql_python

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

Pro lokální vývoj použijte ActiveDirectoryDefault, který automaticky zachytí vaše Azure CLI nebo jiné vývojářské přihlašovací údaje. Vyhněte se tomu v produkci, protože DefaultAzureCredential při prvním připojení zkouší každého poskytovatele přihlašovacích údajů v pořadí, což přidává latenci, kterou produkční zátěže nepotřebují:

import mssql_python

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

Vyhněte se: SQL autentizace ukládá přihlašovací údaje v kódu/konfiguraci a je zranitelná vůči únikům informací.

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

Nikdy nenatvrdo kódujte přihlašovací údaje

Proměnné prostředí používejte pouze pro lokální vývoj. Ve sdílených prostředích preferuji autentizaci bez hesla. Pokud se nelze vyhnout staršímu způsobu ověřování SQL, načítejte tajný klíč za běhu z úložiště tajných klíčů, místo abyste celý připojovací řetězec ukládali do správy zdrojového kódu.

Následující přístup má napevno zakódované přihlašovací údaje a nikdy by se neměl používat:

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

Pro lokální vývoj načítejte pověřovací údaje z proměnných prostředí:

import os

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

Pro sdílená prostředí, která stále tajemství potřebují, jej získejte za běhu 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

Prevence injekcí SQL

Vždy používejte parametrizované dotazy

Parametrizované dotazy zabraňují SQL injekci tím, že oddělují uživatelský vstup od struktury dotazu. Vždy parametrizujte uživatelský vstup.

Následující dotaz ve formátu řetězce je zranitelný vůči SQL injection. Nikdy nevytvářejte dotazy tímto způsobem:

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

Následující parametrizovaný dotaz je bezpečný, protože ovladač odesílá hodnotu odděleně od textu dotazu:

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

Parametrizujte všechny komponenty dotazu

Nemůžete přímo parametrizovat názvy tabulek a sloupců. Interpolace z uživatelského vstupu činí vaši aplikaci zranitelnou vůči SQL injection:

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

Místo toho ověřte dynamické identifikátory vůči seznamu povolených hodnot a parametrizujte zbývající hodnoty.

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

Validace a sanitace vstupu

Když vytváříte dynamické SQL s identifikátory, ověřte každou hodnotu podle přísného vzoru před použitím:

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

Používejte uložené procedury pro složité operace

Uložené procedury snižují plochu SQL vystavenou aplikačnímu kódu:

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

Zabezpečení připojení

Vyžaduje šifrování

Vždy šifrujte spojení. Azure SQL vynucuje šifrování ve výchozím nastavení. Pro on-premises SQL Server nastavte Encrypt=yes explicitně:

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

Pro nejvyšší bezpečnost použijte přísný režim TDS 8.0

TDS 8.0 poskytuje:

• TLS 1.3 od začátku připojení
• Vyžadováno ověření certifikátu
• Žádná záložní možnost ke starším protokolům

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

Validovat serverové certifikáty

Vždy v produkčním prostředí ověřujte certifikát serveru, abyste zabránili útokům typu man-in-the-middle. Nenastavujte TrustServerCertificate=yes, protože to obchází validaci. Místo toho nastavte TrustServerCertificate=no na validaci proti CA certifikátům a nastavte HostNameInCertificate na ověření názvu hostitele:

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

Ochrana dat

Chraňte citlivá data na úrovni serveru

Always Encrypted momentálně není konfigurovatelný pomocí klíčových slov mssql-python připojovací řetězec. Pokud potřebujete Always Encrypted, použijte pyodbc s ODBC ovladačem pro SQL Server, který to podporuje. I když neposkytují stejnou úroveň ochrany, můžete použít funkce SQL Server, jako je dynamické maskování dat a bezpečnost na úrovni řádků, k ochraně citlivých sloupců.

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

row = cursor.fetchone()

Ochrana dat během přenosů

• Použití Encrypt=yes v spojovacích řetězcích.
• Pro připojení na místě používejte VPN nebo privátní endpointy.
• Use Azure Private Link for Azure SQL.

Princip nejmenšího oprávnění

Používejte minimální databázová oprávnění

Aplikační účty by měly mít minimální oprávnění. Nepoužívejte sadb_owner ani pro připojení k aplikacím.

• Pouze pro čtení: GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
• Přístup ke konkrétním stolům: GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
• Pouze uložené procedury: GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Používejte různé účty pro různé operace

Každou úroveň oprávnění podpořte vlastní identitou, aby cesta pro čtení nemohla provádět zápisy. Tento příklad používá dvě uživatelem přiřazené spravované identity, jednu s povoleným přístupem pouze pro čtení a jednu s povoleným zápisem, vybrané podle ID 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;"
    )

Uchovávejte tajné klíče role aplikace mimo systém správy zdrojového kódu

Hesla pro role aplikací jsou stále tajemstvím. Ukládejte je do trezoru nebo do prostředí s injekcí tajemství a otáčejte je se stejnou opatrností jako s jakýmkoli jiným přihlašovacím údajem.

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 a protokolování

Záznamy bezpečnostních událostí

Nezaznamenávejte citlivá data, ale zaznamenávejte bezpečnostní události, jako jsou neúspěšná připojení, chyby oprávnění a podezřelé dotazy.

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

Auditně citlivé operace

Konfigurujte audit databáze pro citlivé tabulky a operace. Můžete také implementovat audity na úrovni aplikací pro kritické akce.

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

Nikdy nezaznamenávejte citlivá data

Nezahrňujte hodnoty parametrů do log zpráv. Zaznamenávejte operaci, ne data.

Vyhněte se tomu - zapíše hodnotu do protokolu:

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

Doporučeno – zaznamenávat záměr bez zveřejňování hodnot:

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

Zpracování chyb

Neodhalujte vnitřní detaily

Interně zaznamenávejte podrobné chyby, ale vraťte uživatelům obecné zprávy, abyste předešli úniku databázové struktury:

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

Sanitizace chybových zpráv

Přiřaďte databázové výjimky k uživatelsky přívětivým zprávám, které neodhalují podrobnosti implementace:

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

Kontrolní seznam zabezpečení

Kontrolní seznam bezpečnosti spojení

• [ ] Používejte autentizaci Microsoft Entra, pokud je to možné.
• [ ] Povolit šifrování (Encrypt=yes).
• [ ] Ověřte serverové certifikáty.
• [ ] Ukládejte přihlašovací údaje do zabezpečeného trezoru.
• [ ] Soubory .env ponechávejte pouze lokálně a tajné údaje vkládejte ve sdílených prostředích prostřednictvím cílové platformy.
• [ ] Použijte TDS 8.0 striktní režim pro Azure SQL.

Bezpečnost dotazů

• [ ] Vždy používejte parametrizované dotazy.
• [ ] Ověřte dynamické identifikátory.
• [ ] Používejte uložené procedury pro složitou logiku.
• [ ] Omezte velikost výsledků dotazu.

Zabezpečení dat

• [ ] Používejte ochranu dat na úrovni serveru (maskování, bezpečnost na úrovni řádků).
• [ ] Používejte bezpečnost na úrovni řádků, kde je to vhodné.
• [ ] Maskujte citlivá data v logech.

Řízení přístupu

• [ ] Použijte princip nejmenšího privilegia.
• [ ] Oddělené účty pro čtení/zápis.
• [ ] Pravidelně auditujte oprávnění.
• [ ] Zaveďte časové limity spojení.

Monitoring

• [ ] Zaznamenávej bezpečnostní události.
• [ ] Monitorujte anomálie.
• [ ] Nastavte upozornění na selhání.
• [ ] Provádějte pravidelné bezpečnostní kontroly.

Související obsah

• Šifrování a certifikáty
• Ověřování Microsoft Entra
• Kontejner a místní vývoj
• Zpracování chyb
• Bezpečnostní dokumentace SQL Server