Sois el equipo de inteligencia de una corporación tipo Weyland-Yutani / Tyrell / Cyberdyne. Necesitáis un arsenal organizado de herramientas online de ciberseguridad para elegir la herramienta adecuada en cada misión. Vuestra tarea es diseñar y explotar la base de datos de ese arsenal.

Crear una base de datos llamada, por ejemplo, ciber_arsenal, que almacene herramientas web de ciberseguridad / OSINT / análisis:

• SELECT básicos
• filtros (WHERE)
• ordenaciones (ORDER BY)
• agregaciones (COUNT, AVG…)
• GROUP BY / HAVING
• JOIN entre varias tablas
• subconsultas sencillas

1. Diseño de la base de datos

Tablas

3 tablas principales:

1. categorias – Tipo de herramienta (OSINT, análisis malware, escaneo, forense…).
2. herramientas – Cada herramienta web concreta.
3. tags y herramienta_tag – Muchos-a-muchos y JOINs.

1.1. Tabla categorias

CREATE DATABASE ciber_arsenal;
USE ciber_arsenal;

CREATE TABLE categorias (
    id_categoria      INT AUTO_INCREMENT PRIMARY KEY,
    nombre            VARCHAR(50) NOT NULL,
    descripcion       VARCHAR(255)
);

Ejemplos de categorías:

• OSINT
• Escaneo de puertos / servicios
• Análisis de malware
• Análisis de dominios / DNS
• Forense
• Threat Intelligence

1.2. Tabla herramientas

CREATE TABLE herramientas (
    id_herramienta        INT AUTO_INCREMENT PRIMARY KEY,
    nombre                VARCHAR(100) NOT NULL,
    url                   VARCHAR(255) NOT NULL,
    id_categoria          INT,
    tipo_acceso           ENUM('web','api','desktop','mixto') DEFAULT 'web',
    licencia              ENUM('gratuita','freemium','comercial','código abierto') DEFAULT 'gratuita',
    requiere_registro     BOOLEAN DEFAULT 0,
    nivel_criticidad      TINYINT,      -- 1 a 5: 1 = muy suave, 5 = muy delicada
    pais_servidor         VARCHAR(50),  -- País principal (aprox)
    anyo_lanzamiento      INT,
    uso_principal         VARCHAR(100), -- en una frase corta
    requiere_vpn          BOOLEAN DEFAULT 0,
    notas                 VARCHAR(255),
    CONSTRAINT fk_cat
        FOREIGN KEY (id_categoria) REFERENCES categorias(id_categoria)
);

Con estos campos puedes preguntar por:

• Herramientas por país
• Por tipo de licencia
• Por nivel de criticidad
• Por año de lanzamiento
• etc.

1.3. Tablas de tags

CREATE TABLE tags (
    id_tag      INT AUTO_INCREMENT PRIMARY KEY,
    nombre      VARCHAR(50) NOT NULL
);

CREATE TABLE herramienta_tag (
    id_herramienta INT,
    id_tag         INT,
    PRIMARY KEY (id_herramienta, id_tag),
    FOREIGN KEY (id_herramienta) REFERENCES herramientas(id_herramienta),
    FOREIGN KEY (id_tag) REFERENCES tags(id_tag)
);

Ejemplos de tags: shodan-like, buscador_ips, malware_sandbox, dns, breach, wifi, mobile, etc.

Datos de EJEMPLO

Categorías

INSERT INTO categorias (nombre, descripcion) VALUES
('OSINT', 'Recolección de información en fuentes abiertas'),
('Escaneo', 'Descubrimiento de puertos, servicios y hosts'),
('Analisis Malware', 'Análisis de ejecutables sospechosos'),
('Analisis Dominios/DNS', 'Información sobre dominios, DNS y certificados'),
('Forense', 'Análisis forense de sistemas y archivos'),
('Threat Intelligence', 'Reputación de IPs, dominios y campañas');

Herramientas

INSERT INTO herramientas 
(nombre, url, id_categoria, tipo_acceso, licencia, requiere_registro, nivel_criticidad, pais_servidor, anyo_lanzamiento, uso_principal, requiere_vpn, notas)
VALUES
('Shodan', 'https://www.shodan.io', 1, 'web', 'freemium', 1, 5, 'Estados Unidos', 2009, 'Buscador de dispositivos conectados a Internet', 0, 'API muy utilizada en pentesting'),
('Censys', 'https://search.censys.io', 1, 'web', 'freemium', 1, 5, 'Estados Unidos', 2015, 'Escaneo y búsqueda de hosts y certificados', 0, 'Similar a Shodan'),
('ZoomEye', 'https://www.zoomeye.org', 1, 'web', 'freemium', 1, 5, 'China', 2013, 'Buscador de servicios y dispositivos expuestos', 1, 'A veces bloqueado en algunos países'),
('VirusTotal', 'https://www.virustotal.com', 3, 'web', 'freemium', 1, 4, 'España', 2004, 'Análisis de ficheros y URLs con múltiples antivirus', 0, 'Muy usado para análisis rápido'),
('Hybrid Analysis', 'https://www.hybrid-analysis.com', 3, 'web', 'gratuita', 1, 4, 'Estados Unidos', 2014, 'Sandbox para analizar malware', 0, 'Requiere cuenta gratuita'),
('Any.Run', 'https://any.run', 3, 'web', 'freemium', 1, 4, 'Emiratos Árabes Unidos', 2016, 'Sandbox interactiva para malware', 0, 'Permite ver la ejecución en tiempo real'),
('Urlscan.io', 'https://urlscan.io', 4, 'web', 'gratuita', 0, 3, 'Alemania', 2017, 'Análisis de URLs y recursos cargados', 0, 'Muy útil para phishing'),
('crt.sh', 'https://crt.sh', 4, 'web', 'gratuita', 0, 2, 'Estados Unidos', 2014, 'Búsqueda de certificados TLS emitidos', 0, 'Muy útil para descubrir subdominios'),
('SecurityTrails', 'https://securitytrails.com', 4, 'web', 'freemium', 1, 3, 'Estados Unidos', 2017, 'Inteligencia sobre dominios, DNS e IPs', 0, 'Buen complemento a otras OSINT'),
('Have I Been Pwned', 'https://haveibeenpwned.com', 6, 'web', 'gratuita', 0, 3, 'Australia', 2013, 'Comprobación de correos en brechas de datos', 0, 'Tiene API limitada'),
('Dehashed', 'https://www.dehashed.com', 6, 'web', 'comercial', 1, 5, 'Estados Unidos', 2015, 'Búsqueda avanzada en bases de datos filtradas', 1, 'Muy potente pero de pago'),
('MalwareBazaar', 'https://bazaar.abuse.ch', 3, 'web', 'gratuita', 0, 4, 'Suiza', 2019, 'Repositorio de muestras de malware', 1, 'Uso muy delicado'),
('AbuseIPDB', 'https://www.abuseipdb.com', 6, 'web', 'freemium', 1, 3, 'Irlanda', 2016, 'Reputación de IPs reportadas por abuso', 0, 'Útil para filtrar tráfico malicioso'),
('Wigle', 'https://wigle.net', 1, 'web', 'gratuita', 1, 3, 'Estados Unidos', 2001, 'Base de datos de redes WiFi geolocalizadas', 1, 'Muy útil para geolocalización'),
('Archive.org', 'https://web.archive.org', 1, 'web', 'gratuita', 0, 1, 'Estados Unidos', 1996, 'Archivo histórico de páginas web', 0, 'Wayback Machine'),
('MetaDefender Cloud', 'https://metadefender.opswat.com', 3, 'web', 'gratuita', 0, 3, 'Estados Unidos', 2017, 'Análisis de ficheros y URLs', 0, 'Alternativa a VirusTotal');

Tags

INSERT INTO tags (nombre) VALUES
('buscador_ips'),
('buscador_dispositivos'),
('malware_sandbox'),
('analisis_url'),
('breach'),
('dns'),
('dominios'),
('certificados'),
('threat_intel'),
('wifi'),
('historial_web');

Relación herramienta-tag

-- Shodan
INSERT INTO herramienta_tag VALUES (1, 1), (1, 2), (1, 9);
-- Censys
INSERT INTO herramienta_tag VALUES (2, 1), (2, 2), (2, 8), (2, 9);
-- ZoomEye
INSERT INTO herramienta_tag VALUES (3, 1), (3, 2);
-- VirusTotal
INSERT INTO herramienta_tag VALUES (4, 3), (4, 4), (4, 9);
-- Hybrid Analysis
INSERT INTO herramienta_tag VALUES (5, 3), (5, 9);
-- Any.Run
INSERT INTO herramienta_tag VALUES (6, 3);
-- Urlscan.io
INSERT INTO herramienta_tag VALUES (7, 4), (7, 9);
-- crt.sh
INSERT INTO herramienta_tag VALUES (8, 6), (8, 7), (8, 8);
-- SecurityTrails
INSERT INTO herramienta_tag VALUES (9, 6), (9, 7), (9, 9);
-- HIBP
INSERT INTO herramienta_tag VALUES (10, 5), (10, 9);
-- Dehashed
INSERT INTO herramienta_tag VALUES (11, 5), (11, 9);
-- MalwareBazaar
INSERT INTO herramienta_tag VALUES (12, 3), (12, 9);
-- AbuseIPDB
INSERT INTO herramienta_tag VALUES (13, 1), (13, 9);
-- Wigle
INSERT INTO herramienta_tag VALUES (14, 10);
-- Archive.org
INSERT INTO herramienta_tag VALUES (15, 11);
-- MetaDefender Cloud
INSERT INTO herramienta_tag VALUES (16, 3), (16, 4);

MISION

Vuestra misión es diseñar, crear y explotar una base de datos que almacene un arsenal de herramientas web utilizadas en ciberseguridad (OSINT, análisis de malware, dominios, threat intel, etc.).

1. Crear la base de datos ciber_arsenal.
2. Crear las tablas categorias, herramientas, tags y herramienta_tag siguiendo el modelo proporcionado.
3. Insertar al menos 15 herramientas con datos realistas (podéis completar o modificar los ejemplos).
4. Diseñar al menos 10 tags y relacionarlos con las herramientas.
5. Realizar una colección de consultas SQL (SELECT) para responder a preguntas típicas de un analista de ciberseguridad.

Propuesta de ejercicios de consultas SELECT

1. Mostrar todas las herramientas con su nombre y URL.
2. Listar todas las herramientas ordenadas por año de lanzamiento (más antiguas primero).
3. Mostrar nombre, licencia y categoría de todas las herramientas que sean gratuitas.
4. Mostrar las herramientas cuya licencia sea “freemium” o “comercial”.
5. Listar herramientas que no requieran registro (requiere_registro = 0).
6. Mostrar las herramientas con nivel de criticidad mayor o igual que 4.
7. Contar cuántas herramientas hay por cada categoría (usar GROUP BY).
8. Obtener el número total de herramientas por tipo de licencia.
9. Calcular el año medio de lanzamiento de las herramientas de la categoría “OSINT”.
10. Listar todas las herramientas cuyo país del servidor sea “Estados Unidos”.
11. Listar las herramientas que requieren VPN (requiere_vpn = 1).
12. Mostrar todas las herramientas cuya URL contenga la palabra "virus" o "malware".
13. Listar las herramientas lanzadas después de 2015 y que sean de tipo acceso web.
14. Mostrar el nombre de la categoría y el número de herramientas asociadas, ordenado de mayor a menor número.
15. Listar todas las herramientas junto con el nombre de su categoría (JOIN entre herramientas y categorias).
16. Listar las herramientas que tengan el tag "malware_sandbox" (JOIN con tags y herramienta_tag).
17. Mostrar todas las herramientas que tengan el tag "breach", indicando nombre de la herramienta, URL y licencia.
18. Listar las herramientas que tengan más de 2 tags asociados (JOIN + GROUP BY + HAVING).
19. Encontrar las herramientas con nivel de criticidad 5 que además tengan algún tag relacionado con amenazas ('threat_intel' o 'breach').
20. Usar una subconsulta para mostrar todas las herramientas cuya categoría tenga más de 3 herramientas asociadas (subconsulta sobre categorias o herramientas).
21. Mostrar las herramientas cuya licencia sea distinta de la mayoría (por ejemplo, todas las que NO sean gratuitas si la mayoría lo son).
22. Mostrar, por cada país, cuántas herramientas tiene y el nivel de criticidad medio.
23. Crear una consulta que responda a: “Quiero herramientas para investigar dominios o DNS que sean gratuitas y no requieran VPN”.
24. Crear una consulta que responda a: “Necesito un sustituto de Shodan que no esté alojado en Estados Unidos”.
25. Crear una consulta que devuelva una “lista recomendada”: herramientas con criticidad entre 2 y 4, gratuitas o freemium, y con al menos un tag.