Boas práticas de segurança para aplicações mssql-python

Proteja as suas aplicações mssql-python seguindo estas melhores práticas para autenticação, consultas parametrizadas e proteção de dados.

Começa sempre que puderes com autenticação sem palavra-passe. Trate ficheiros locais .env e palavras-passe SQL como auxílios temporários de desenvolvimento e mova segredos para identidades geridas ou um armazenamento secreto antes que o código chegue a um ambiente partilhado.

Segurança de autenticação

Use autenticação Microsoft Entra em vez de autenticação SQL

A autenticação Microsoft Entra elimina palavras-passe armazenadas e suporta identidades geridas. Prefiro-o à autenticação SQL em todos os ambientes.

Para cargas de trabalho alojadas no Azure, use uma identidade gerida com ActiveDirectoryMSI. Não precisa de segredos armazenados e liga-se sem percorrer uma cadeia de credenciais:

import mssql_python

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

Para desenvolvimento local, use ActiveDirectoryDefault, que recolhe automaticamente a sua CLI do Azure ou outras credenciais de programador. Evite-o em produção, porque DefaultAzureCredential tenta cada fornecedor de credenciais por ordem ao estabelecer a primeira ligação, o que acrescenta latência de que as cargas de trabalho de produção não precisam:

import mssql_python

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

Evitar: A autenticação SQL armazena as credenciais em código/configuração e é vulnerável a fugas de informação.

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

Nunca codifiquem as credenciais de forma fixa

Use variáveis ambientais apenas para desenvolvimento local. Em ambientes partilhados, prefira autenticação sem palavra-passe. Quando um fluxo de autenticação SQL legado for inevitável, obtenha o segredo em tempo de execução a partir de um cofre de segredos, em vez de submeter uma cadeia de ligação completa ao controlo de versões.

A seguinte abordagem codifica as credenciais de forma rígida e nunca deve ser utilizada:

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

Para desenvolvimento local, leia as credenciais das variáveis de ambiente:

import os

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

Para ambientes partilhados que ainda precisam de um segredo, recupere-o em tempo de execução no 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

Prevenção da injeção SQL

Use sempre consultas parametrizadas

As consultas parametrizadas impedem a injeção SQL ao separar a entrada do utilizador da estrutura da consulta. Parametrize sempre a entrada do utilizador.

A seguinte consulta formatada por strings é vulnerável à injeção SQL. Nunca construas consultas desta forma:

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

A seguinte consulta parametrizada é segura, porque o driver envia o valor separadamente do texto da consulta:

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

Parametrizar todos os componentes da consulta

Não podes parametrizar diretamente nomes de tabelas e colunas. Interpolá-los a partir da entrada do utilizador torna a sua aplicação vulnerável à injeção SQL:

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

Em vez disso, validar identificadores dinâmicos contra uma lista de valores permitidos e parametrizar os valores 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()

Validar e higienizar a entrada

Quando constróis SQL dinâmico com identificadores, valida cada valor contra um padrão rigoroso antes de o usar:

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

Utilizar procedimentos armazenados para operações complexas

Os procedimentos armazenados reduzem a área de superfície SQL exposta ao código da aplicação:

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

Segurança da ligação

Exigir encriptação

Encripta sempre as ligações. O SQL do Azure impõe encriptação por defeito. Para o SQL Server on-premiss, defina Encrypt=yes explicitamente:

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

Use o modo estrito TDS 8.0 para máxima segurança

O TDS 8.0 fornece:

• TLS 1.3 desde o início da ligação
• Validação de certificados necessária
• Sem recorrer a protocolos antigos

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

Validar certificados de servidor

Valide sempre o certificado do servidor em produção para evitar ataques adversários no meio. Não definas TrustServerCertificate=yes, porque isso ignora a validação. Em vez disso, defina TrustServerCertificate=no para validar em relação aos certificados da AC e defina HostNameInCertificate para verificar o nome do host:

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

Proteção de dados

Proteger dados sensíveis ao nível do servidor

Always Encrypted não é atualmente possível configurar através das palavras‑chave da cadeia de ligação do mssql-python. Se precisares de Always Encrypted, usa pyodbc com o ODBC Driver for SQL Server, que o suporta. Embora não ofereçam o mesmo nível de proteção, pode usar funcionalidades do SQL Server como mascaramento dinâmico de dados e segurança ao nível da linha para proteger colunas sensíveis.

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

row = cursor.fetchone()

Proteger os dados em trânsito

• Use Encrypt=yes em cadeias de ligação.
• Utilize VPN ou pontos finais privados para ligações no local.
• Utilize o Azure Private Link para o SQL do Azure.

Princípio do menor privilégio

Use permissões mínimas de base de dados

As contas de aplicação devem ter permissões mínimas. Não uses sa nem db_owner para ligações de aplicações.

• Reportagem apenas de leitura: GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
• Acesso específico a tabelas: GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
• Apenas procedimento armazenado: GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Use contas diferentes para diferentes operações

Associe cada nível de privilégio a uma identidade própria, para que uma via de leitura não possa efetuar escritas. Este exemplo utiliza duas identidades geridas atribuídas pelo utilizador, uma com acesso só de leitura e outra com acesso de escrita concedida, selecionadas pelo ID do cliente:

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

Mantenha os segredos das funções da aplicação fora do controlo de versões

As palavras-passe das funções da aplicação continuam a ser informações secretas. Guarde-as num cofre ou numa variável de ambiente onde o segredo é injetado e faça a sua rotação com o mesmo cuidado que dedica a qualquer outra credencial.

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

Auditoria e registo

Registar eventos de segurança

Não regista dados sensíveis, mas regista eventos relevantes para a segurança, como falhas em ligações, erros de permissão e consultas suspeitas.

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

Auditoria de operações sensíveis

Configurar auditoria de bases de dados para tabelas e operações sensíveis. Também pode implementar auditoria ao nível da aplicação para ações críticas.

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

Nunca registar dados sensíveis

Não inclua valores de parâmetros nas mensagens de log. Registe a operação, não os dados.

Evitar - expõe o valor no registo:

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

Recomendado - regista a intenção sem expor valores:

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

Tratamento de erros

Não exponhas detalhes internos

Registar erros detalhados internamente, mas devolver mensagens genéricas aos utilizadores para evitar fugas na estrutura da base de dados:

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

Sanitizar mensagens de erro

Mapear exceções de base de dados para mensagens fáceis de usar que não revelam detalhes de implementação:

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 de verificação de segurança

Lista de verificação de segurança de ligação

• [ ] Use autenticação Microsoft Entra sempre que possível.
• [ ] Ativar encriptação (Encrypt=yes).
• [ ] Validar os certificados do servidor.
• [ ] Guardar credenciais num cofre seguro.
• [ ] Mantém os ficheiros .env apenas locais e injeta segredos através da plataforma-alvo em ambientes partilhados.
• [ ] Utilizar o modo estrito TDS 8.0 para SQL do Azure.

Segurança de consultas

• [ ] Usa sempre consultas parametrizadas.
• [ ] Validar identificadores dinâmicos.
• [ ] Usar procedimentos armazenados para lógica complexa.
• [ ] Limitar o tamanho dos resultados das consultas.

Segurança de dados

• [ ] Use proteção de dados ao nível do servidor (mascaramento, segurança ao nível da linha).
• [ ] Use segurança ao nível das filas quando apropriado.
• [ ] Mascarar dados sensíveis nos registos.

Controlo de acesso

• [ ] Use o princípio do menor privilégio.
• [ ] Contas separadas de leitura/escrita.
• [ ] Auditar regularmente permissões.
• [ ] Implementar tempos limite de ligação.

Monitorização

• [ ] Registar eventos de segurança.
• [ ] Monitorizar anomalias.
• [ ] Configurar alertas para falhas.
• [ ] Realizar revisões regulares de segurança.

Conteúdo relacionado

• Encriptação e certificados
• Autenticação do Microsoft Entra
• Contentores e desenvolvimento local
• Tratamento de erros
• Documentação de segurança do SQL Server