Capítulo 9 — Datos y bases de datos
Mapa de la lección
Lección 09 publicada. Mapa, ejemplo, EX017.
flowchart TD
N0["Resultados de aprendizaje y conexión"]
N1["9.1 Por qué datos relacionales"]
N2["9.2 SQL en cuatro cláusulas"]
N3["9.3 Inyección"]
N4["9.5 Actividad práctica"]
N5["Síntesis del capítulo"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5
N5 --> EX["Ejemplo resuelto"]
EX --> Q["Ejercicio EX017"]
Dibujo lógico
flowchart LR
N0["Resultados de aprendizaje y conexión"]
N1["9.1 Por qué datos relacionales"]
N2["9.2 SQL en cuatro cláusulas"]
N3["9.3 Inyección"]
N4["9.5 Actividad práctica"]
N5["Síntesis del capítulo"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX017"]
[Resultados de aprendizaje y conexión]
|
v
[9.1 Por qué datos relacionales]
|
v
[9.2 SQL en cuatro cláusulas]
|
v
[9.3 Inyección]
|
v
[9.5 Actividad práctica]
|
v
[Síntesis del capítulo]
|
v
[Ejemplo resuelto] --> [Ejercicio EX017]
Resultados de aprendizaje y conexión
Después de este capítulo usted podrá (1) diseñar un esquema relacional pequeño con claves; (2) escribir consultas SQL que filtran, combinan y ordenan; (3) explicar por qué importan los tipos de datos, con los números de versión como ejemplo; y (4) prevenir la inyección SQL con consultas parametrizadas. Se apoya en los capítulos 3 y 8 y prepara el capítulo 14 (privacidad), el capítulo 15 (medición) y el capítulo 22 (dependencias).
9.1 Por qué datos relacionales
Las preguntas de seguridad combinan hechos guardados en lugares distintos: qué activos existen, qué software ejecutan, qué avisos afectan a ese software, quién es responsable de cada activo, cuáles están expuestos a internet. Una base de datos relacional almacena cada tipo de hecho una sola vez, en una tabla de filas y columnas, y conecta las tablas mediante claves (Codd, 1970). Una clave primaria identifica una fila de forma única (el ID de un activo); una clave foránea hace referencia a una fila de otra tabla (el nombre de producto de un activo, que coincide con el producto de un aviso). Una combinación (join) reúne las filas que coinciden. Almacenar cada hecho una sola vez evita la falla conocida de las hojas de cálculo: dos copias que no concuerdan.
9.2 SQL en cuatro cláusulas
SELECT nombra las columnas que quiere; FROM ... JOIN ... ON nombra las tablas y cómo coinciden; WHERE filtra las filas; ORDER BY ordena. GROUP BY con COUNT resume. La biblioteca estándar de Python incluye sqlite3, una base de datos integrada completa, de modo que puede practicar sin servidor. Dos advertencias. Primera, una combinación descarta en silencio las filas sin coincidencia: un activo cuyo nombre de producto está mal escrito simplemente desaparece de un informe de vulnerabilidades. Use un LEFT JOIN y busque valores nulos cuando importe la completitud. Segunda, los tipos deciden las comparaciones. Las cadenas de versión comparadas como texto ponen "3.10" antes que "3.4", porque el carácter "1" se ordena antes que "4".
9.3 Inyección
Si un programa construye SQL pegando la entrada del usuario en una cadena ("... WHERE id = '" + user_input + "'"), una entrada como x' OR '1'='1 cambia el significado de la consulta. Es la inyección SQL, que figura de forma persistente entre las debilidades de software más peligrosas (CWE Top 25). La corrección es estructural: las consultas parametrizadas (db.execute("... WHERE id = ?", (user_input,))) envían los datos separados del código, de modo que la entrada nunca puede convertirse en SQL. En el Lab 5, toda consulta con una entrada, como buscar un activo por su ID, debe usar un marcador ?.
9.4 Ejemplo resuelto y Lab 5: correspondencia de avisos en CPN (sintético)
Este caso es sintético. Calder Power Networks mantiene una tabla de activos e importa los avisos de los fabricantes. La pregunta: ¿qué activos ejecutan una versión anterior a la versión corregida, ordenados de modo que primero aparezcan los elementos con explotación conocida y expuestos a internet? Una pequeña función propia compara las versiones numéricamente.
# Lab 5 - asset and vulnerability joins in SQLite (synthetic Calder Power data, offline)
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE asset(id TEXT PRIMARY KEY, site TEXT, role TEXT, product TEXT, version TEXT, internet_exposed INTEGER);
CREATE TABLE advisory(product TEXT, fixed_in TEXT, severity TEXT, known_exploited INTEGER);
INSERT INTO asset VALUES
('RTU-11','Substation North','feeder RTU','RTU-K','3.2',0),
('RTU-12','Substation North','feeder RTU','RTU-K','3.4',0),
('HMI-01','Control room','operator HMI','HMI-S','7.0',0),
('VPN-01','Head office','remote access gateway','VPN-G','9.1',1),
('HIS-01','DMZ','historian replica','HIST-P','5.5',0);
INSERT INTO advisory VALUES
('RTU-K','3.4','high',0), ('VPN-G','9.3','critical',1), ('HIST-P','5.1','medium',0);
""")
def vkey(v): return tuple(int(x) for x in v.split("."))
db.create_function("vlt", 2, lambda a, b: int(vkey(a) < vkey(b)))
q = """SELECT a.id, a.role, a.version, v.fixed_in, v.severity, v.known_exploited, a.internet_exposed
FROM asset a JOIN advisory v ON a.product = v.product
WHERE vlt(a.version, v.fixed_in)
ORDER BY v.known_exploited DESC, a.internet_exposed DESC, v.severity"""
for row in db.execute(q):
print(row)
print("string comparison '3.10' < '3.4':", "3.10" < "3.4", "| numeric:", vkey("3.10") < vkey("3.4"))
Salida esperada:
('VPN-01', 'remote access gateway', '9.1', '9.3', 'critical', 1, 1)
('RTU-11', 'feeder RTU', '3.2', '3.4', 'high', 0, 0)
string comparison '3.10' < '3.4': True | numeric: False
Dos activos necesitan actuación, primero la pasarela (es el aviso del capítulo 5). HMI-01 no tiene aviso y no aparece; HIS-01 ya ha superado la corrección. Observe que ORDER BY v.severity ordena las palabras alfabéticamente ("critical" < "high" < "medium"), lo que aquí resulta correcto por casualidad y sería erróneo con "low". Una columna con el rango de gravedad es el diseño robusto.
9.5 Actividad práctica
(1) Cambie la combinación por un LEFT JOIN y enumere los activos sin registro de aviso: ¿es «sin aviso» lo mismo que «sin vulnerabilidad»? (2) Escriba una consulta parametrizada que devuelva un activo por su ID; después, muestre con una consulta construida a propósito de forma insegura mediante cadenas, solo sobre su base de datos en memoria, cómo ' OR '1'='1 devuelve todas las filas. El LEFT JOIN muestra HMI-01 con nulos en las columnas del aviso. «Sin aviso» significa que no hay aviso en esta fuente para el producto HMI-S, no que la HMI no tenga defectos; puede tener un boletín del fabricante fuera de la fuente o no tener ninguno publicado todavía. En la parte (2), la consulta insegura solo debe ejecutarse contra su base de datos en memoria, nunca contra una real.
9.6 Errores comunes y límites
- Comparar versiones, fechas o números almacenados como texto.
- Combinaciones internas que descartan en silencio las filas sin coincidencia.
- Construir SQL concatenando cadenas.
- Confiar en que los nombres de producto coincidan exactamente entre las fuentes de los fabricantes y los inventarios locales.
El Lab 5 muestra dónde morderían tres de estos errores. La comparación de versiones trataría "3.10" como anterior a "3.4" si se hiciera como texto, y marcaría erróneamente como vulnerable una RTU actualizada. La combinación interna descarta HMI-01 en silencio. Y los nombres de producto "RTU-K" y "VPN-G" solo se combinan porque la muestra se escribió de forma coherente; las fuentes reales de los fabricantes escriben los productos de forma distinta y necesitan una tabla de correspondencias.
Ejercicios — capítulo 9
EX017 — Recuento por sitio. Objetivo: resumir con SQL. Entradas: el Lab 5. Pasos: escriba una consulta que devuelva, por sitio, el número de activos que necesitan una actualización. Evidencia esperada: la consulta y el resultado. Rúbrica: 3 = GROUP BY correcto con el filtro de versión, resultado Head office 1, Substation North 1; 2 = cuenta todos los activos; 1 = solo la sintaxis; 0 = nada.
EX018 — Orden por gravedad. Objetivo: corregir una ordenación frágil. Entradas: el Lab 5 con un aviso adicional de gravedad "low". Pasos: explique por qué ORDER BY severity colocaría mal "low" y dé dos correcciones. Evidencia esperada: la explicación y las correcciones. Rúbrica: 3 = el orden alfabético pone "low" antes que "medium"; correcciones: una columna de rango numérico o una tabla de consulta o una expresión CASE; 2 = una corrección; 1 = solo la explicación; 0 = nada.
Síntesis del capítulo
Los datos relacionales permiten responder con una sola consulta a preguntas de seguridad transversales, si las claves, los tipos y las combinaciones son correctos. Compare las versiones numéricamente, busque lo que descartan las combinaciones, ordene las gravedades con números y nunca construya SQL a partir de cadenas no confiables.
Siga estudiando — capítulo 09 de 40
← Capítulo 08 · Índice de capítulos del curso · Capítulo 10 →
Continúe con la siguiente lección escrita.