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

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

Comece com autenticação sem senha sempre que puder. Trate arquivos locais .env e senhas SQL como auxílios temporários de desenvolvimento, e mova segredos para identidades gerenciadas ou um armazenamento secreto antes que o código chegue a um ambiente compartilhado.

Segurança de autenticação

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

A autenticação Microsoft Entra elimina senhas armazenadas e suporta identidades gerenciadas. Prefiro isso à autenticação SQL em todos os ambientes.

Para cargas de trabalho hospedadas no Azure, use uma identidade gerenciada com ActiveDirectoryMSI. Não precisa de segredos armazenados e se conecta sem passar por 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 detecta automaticamente sua credencial do CLI do Azure ou outras credenciais de desenvolvedor. Evite isso em produção, porque DefaultAzureCredential tenta cada provedor de credenciais em ordem na primeira conexão, o que acrescenta latência da qual 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;"
    )

Evite: A autenticação SQL armazena credenciais em código/configuração e é vulnerável a vazamentos.

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

Nunca code credenciais fixamente

Use variáveis ambientais apenas para desenvolvimento local. Em ambientes compartilhados, prefira autenticação sem senha. Quando um fluxo legado de autenticação SQL for inevitável, recupere o segredo em tempo de execução de um armazenamento secreto em vez de verificar uma cadeia de conexão completa no controle de versão.

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

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

Para desenvolvimento local, leia credenciais a partir das variáveis do ambiente:

import os

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

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

Sempre use consultas parametrizadas

Consultas parametrizadas impedem a injeção SQL ao separar a entrada do usuário da estrutura da consulta. Sempre parametrize a entrada do usuário.

A consulta formatada em strings a seguir é vulnerável à injeção SQL. Nunca construa consultas dessa forma:

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

A consulta parametrizada a seguir é 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}
)

Parametrize todos os componentes da consulta

Você não pode parametrizar diretamente nomes de tabelas e colunas. Interpolá-los a partir da entrada do usuário torna seu app vulnerável à injeção de SQL:

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

Em vez disso, valide identificadores dinâmicos em relação a uma lista de valores permitidos e parametrize 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()

Valide e higienize a entrada

Quando você constrói SQL dinâmico com identificadores, valide cada valor contra um padrão rigoroso antes de usá-lo:

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

Use procedimentos armazenados para operações complexas

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 de conexão

Exigir criptografia

Sempre criptografe conexões. O SQL do Azure aplica criptografia por padrão. Para SQL Server on-local, 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 oferece:

• TLS 1.3 desde o início da conexão
• Validação de certificado 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

Sempre valide o certificado do servidor em produção para evitar ataques adversários no meio. Não defina TrustServerCertificate=yes, porque isso ignora a validação. Em vez disso, defina TrustServerCertificate=no para validar com os 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 no nível do servidor

Always Encrypted atualmente não pode ser configurado por meio de palavras-chave da cadeia de conexão do mssql-python. Se você precisar de Always Encrypted, use o pyodbc com o driver ODBC para SQL Server, que oferece suporte a esse recurso. Embora não ofereçam o mesmo nível de proteção, você pode usar recursos do SQL Server como mascaramento dinâmico de dados e segurança em nível de 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 dados em trânsito

• Use Encrypt=yes em cadeias de conexão.
• Use VPN ou endpoints privados para conexões locais.
• Use Link Privado do Azure for SQL do Azure.

Princípio do privilégio mínimo

Use permissões mínimas de banco de dados

Contas de aplicação devem ter permissões mínimas. Não use sa ou db_owner para conexões de aplicativos.

• Reportagem somente 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 operações diferentes

Associe cada nível de privilégio à sua própria identidade para que um caminho de leitura não possa efetuar gravações. Este exemplo usa duas identidades gerenciadas atribuídas pelo usuário, uma concedida ao acesso somente leitura e outra concedida ao acesso à escrita, 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 de aplicativo fora do controle de versão

As senhas da função de aplicativo ainda são informações confidenciais. Armazene-as em um cofre ou em uma variável de ambiente com segredo injetado e faça a rotação delas com o mesmo cuidado dedicado 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 registro em log

Registrar eventos de segurança

Não registre dados sensíveis, mas registre eventos relevantes para segurança, como falhas em conexõ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

Operações sensíveis à auditoria

Configure auditoria de banco de dados para tabelas e operações sensíveis. Você também pode implementar auditoria em nível de 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 registre dados sensíveis

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

Evite - expõe o valor no log:

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

Recomendado - registra 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 exponha detalhes internos

Registre erros detalhados internamente, mas retorne mensagens genéricas aos usuários para evitar vazamentos na estrutura do banco 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

Associar exceções de banco de dados a mensagens amigáveis ao usuário que não revelem detalhes da 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

Checklist de segurança da conexão

• [ ] Use autenticação Microsoft Entra sempre que possível.
• [ ] Habilitar criptografia (Encrypt=yes).
• [ ] Validar certificados de servidor.
• [ ] Armazene credenciais em um cofre seguro.
• [ ] Mantenha os arquivos .env apenas localmente e injete segredos por meio da plataforma de destino em ambientes compartilhados.
• [ ] Usar o modo estrito do TDS 8.0 para o SQL do Azure.

Segurança de consulta

• [ ] Sempre use consultas parametrizadas.
• [ ] Validar identificadores dinâmicos.
• [ ] Use procedimentos armazenados para lógica complexa.
• [ ] Limite o tamanho dos resultados das consultas.

Segurança de dados

• [ ] Use proteção de dados em nível de servidor (mascaramento, segurança em nível de linha).
• [ ] Use segurança em nível de linha quando apropriado.
• [ ] Mascarar dados sensíveis nos logs.

Controle de acesso

• [ ] Use o princípio do menor privilégio.
• [ ] Contas separadas de leitura/escrita.
• [ ] Auditar permissões regularmente.
• [ ] Implemente tempos de conexão (timeouts).

Monitorização

• [ ] Registrar eventos de segurança.
• [ ] Monitore anomalias.
• [ ] Configurar alertas para falhas.
• [ ] Realize revisões regulares de segurança.

Conteúdo relacionado

• Criptografia e certificados
• Autenticação do Microsoft Entra
• Contêiner e desenvolvimento local
• Tratamento de erros
• Documentação de segurança do SQL Server