Mejores prácticas de seguridad para aplicaciones mssql-python

Protege tus aplicaciones mssql-python siguiendo estas mejores prácticas de autenticación, consultas parametrizadas y protección de datos.

Empieza con autenticación sin contraseña siempre que puedas. Trata los archivos locales .env y las contraseñas SQL como ayudas temporales al desarrollo, y traslada los secretos a identidades gestionadas o a un almacén secreto antes de que el código llegue a un entorno compartido.

Seguridad de autenticación

Utiliza la autenticación de Microsoft Entra en lugar de la autenticación SQL

La autenticación Microsoft Entra elimina las contraseñas almacenadas y soporta identidades gestionadas. Prefiero esto a la autenticación SQL en todos los entornos.

Para cargas de trabajo alojadas en Azure, utiliza una identidad gestionada con ActiveDirectoryMSI. No necesita secretos almacenados y conecta sin recorrer una cadena de credenciales:

import mssql_python

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

Para desarrollo local, usa ActiveDirectoryDefault, que recoge automáticamente tu CLI de Azure u otras credenciales de desarrollador. Evítalo en producción, porque DefaultAzureCredential prueba cada proveedor de credenciales en orden en la primera conexión, lo que añade latencia que las cargas de trabajo de producción no necesitan:

import mssql_python

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

Evitar: La autenticación SQL almacena las credenciales en código/configuración y es vulnerable a filtraciones.

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

Nunca codear las credenciales de forma fija

Utiliza variables de entorno solo para el desarrollo local. En entornos compartidos, prefiero la autenticación sin contraseña. Cuando sea inevitable utilizar un flujo heredado de autenticación SQL, recupera el secreto en tiempo de ejecución de un almacén de secretos en lugar de guardar una cadena de conexión completa en el control de versiones.

El siguiente enfoque codifica las credenciales de forma rígida y nunca debe utilizarse:

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

Para el desarrollo local, obtén las credenciales de las variables de entorno:

import os

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

Para entornos compartidos que aún necesitan un secreto, recupéralo en tiempo de ejecución desde 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

Prevención de inyección SQL

Utiliza siempre consultas parametrizadas

Las consultas parametrizadas impiden la inyección SQL separando la entrada del usuario de la estructura de la consulta. Siempre parametriza la entrada del usuario.

La siguiente consulta con formato de cadena es vulnerable a la inyección SQL. Nunca construyas consultas de esta manera:

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

La siguiente consulta parametrizada es segura, porque el controlador envía el valor por separado del texto de la consulta:

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

Parametrizar todos los componentes de consulta

No puedes parametrizar directamente los nombres de tablas y columnas. Interpolarlos a partir de la entrada del usuario hace que tu aplicación sea vulnerable a la inyección SQL:

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

En su lugar, validar identificadores dinámicos frente a una lista de valores permitidos y parametrizar los 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 y desinfectar la entrada

Cuando construyas SQL dinámico con identificadores, valida cada valor con un patrón estricto antes de usarlo:

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 procedimientos almacenados para operaciones complejas

Los procedimientos almacenados reducen el área superficial de SQL expuesta al código de la aplicación:

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

Seguridad de la conexión

Requieren cifrado

Siempre cifra las conexiones. Azure SQL aplica el cifrado por defecto. Para SQL Server local, establezca Encrypt=yes explícitamente:

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

Utiliza el modo estricto TDS 8.0 para la máxima seguridad

TDS 8.0 ofrece:

• TLS 1.3 desde el inicio de la conexión
• Se requiere validación de certificados
• No recurrir a protocolos antiguos

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

Validar certificados de servidor

Valida siempre el certificado del servidor en producción para evitar ataques de intermediario. No pongas TrustServerCertificate=yes, porque pasa por alto la validación. En su lugar, configura TrustServerCertificate=no para validar con los certificados de la CA y configura HostNameInCertificate para verificar el nombre de host:

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

Protección de los datos

Proteger los datos sensibles a nivel de servidor

Always Encrypted no se puede configurar actualmente mediante las palabras clave de la cadena de conexión de mssql-python. Si necesitas Always Encrypted, usa pyodbc con el controlador ODBC para SQL Server, que lo soporta. Aunque no ofrecen el mismo nivel de protección, puedes usar funciones de SQL Server como el enmascaramiento dinámico de datos y la seguridad a nivel de fila para proteger columnas sensibles.

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

row = cursor.fetchone()

Protección de los datos en tránsito

• Usa Encrypt=yes en cadenas de conexión.
• Usa VPN o endpoints privados para conexiones locales.
• Usa Azure Private Link for Azure SQL.

Principio de privilegio mínimo

Utiliza permisos mínimos de base de datos

Las cuentas de aplicación deberían tener permisos mínimos. No uses sa ni db_owner para las conexiones de aplicaciones.

• Reportajes de solo lectura: GRANT SELECT ON SCHEMA::dbo TO ReportingApp;
• Acceso a una tabla específica: GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
• Solo procedimiento almacenado: GRANT EXECUTE ON ProcessOrder TO OrderProcessor;

Utiliza diferentes cuentas para distintas operaciones

Respalda cada nivel de privilegio con su propia identidad para que una ruta de lectura no pueda realizar escrituras. Este ejemplo utiliza dos identidades gestionadas asignadas por el usuario, una con acceso de solo lectura y otra con acceso de escritura, seleccionadas por ID de 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;"
    )

Mantén las claves secretas de los roles de aplicación fuera del control de código fuente

Las contraseñas de los roles de aplicación siguen siendo secretos. Guárdalas en una bóveda o en una variable de entorno con inyección secreta, y gíralas con el mismo cuidado que cualquier otra 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")

Auditoría y registro

Registrar eventos de seguridad

No registres datos sensibles, pero sí registra eventos relevantes para la seguridad como fallos de conexión, errores de permisos y consultas sospechosas.

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

Operaciones sensibles a la auditoría

Configura la auditoría de bases de datos para tablas y operaciones sensibles. También puedes implementar auditorías a nivel de aplicación para acciones 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 registres datos sensibles

No incluyas los valores de los parámetros en los mensajes de registro. Registra la operación, no los datos.

Evite: expone el valor en el registro:

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

Recomendado : registra la intención sin exponer valores:

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

Gestión de errores

No expongas detalles internos

Registrar errores detallados internamente pero devolver mensajes genéricos a los usuarios para evitar filtraciones de la estructura de la base de datos:

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 mensajes de error

Mapear excepciones de base de datos a mensajes fáciles de usar que no revelen detalles de implementación:

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 verificación de seguridad

Lista de verificación de seguridad de conexión

• [ ] Utiliza la autenticación Microsoft Entra siempre que sea posible.
• [ ] Activar el cifrado (Encrypt=yes).
• [ ] Validar los certificados del servidor.
• [ ] Guardar credenciales en una caja fuerte segura.
• [ ] Mantén los archivos .env solo en local e inyecta los secretos a través de la plataforma de destino en entornos compartidos.
• [ ] Utiliza el modo estricto TDS 8.0 para Azure SQL.

Seguridad de consultas

• [ ] Siempre usa consultas parametrizadas.
• [ ] Validar identificadores dinámicos.
• [ ] Utiliza procedimientos almacenados para lógica compleja.
• [ ] Limitar el tamaño de los resultados de las consultas.

Seguridad de datos

• [ ] Utiliza protección de datos a nivel de servidor (enmascaramiento, seguridad a nivel de fila).
• [ ] Utiliza seguridad a nivel de fila cuando sea apropiado.
• [ ] Oculta datos sensibles en los registros.

Control de acceso

• [ ] Utilice el principio del mínimo privilegio.
• [ ] Cuentas separadas de lectura/escritura.
• [ ] Revise periódicamente los permisos.
• [ ] Implementar tiempos de espera de conexión.

Monitoring

• [ ] Registrar eventos de seguridad.
• [ ] Vigila las anomalías.
• [ ] Configura alertas de fallos.
• [ ] Realizad revisiones de seguridad periódicas.

Contenido relacionado

• Cifrado y certificados
• Autenticación de Microsoft Entra
• Contenedor y desarrollo local
• Control de errores
• Documentación de seguridad de SQL Server