## Capítulo 9 — Dados e bancos de dados ### Objetivos de aprendizagem e conexão Depois deste capítulo, você consegue (1) projetar um pequeno esquema relacional com chaves; (2) escrever consultas SQL que filtram, juntam e ordenam; (3) explicar por que os tipos de dados importam, usando números de versão como exemplo; e (4) prevenir a injeção de SQL com consultas parametrizadas. O capítulo se apoia nos Capítulos 3 e 8 e prepara o Capítulo 14 (privacidade), o Capítulo 15 (medição) e o Capítulo 22 (dependências). ### 9.1 Por que dados relacionais As perguntas de segurança combinam fatos guardados em lugares diferentes: quais ativos existem, que software eles rodam, quais alertas afetam esse software, quem é o dono de cada ativo, quais estão expostos à internet. Um **banco de dados relacional** guarda cada tipo de fato uma única vez, numa **tabela** de linhas e colunas, e liga as tabelas por meio de **chaves** ([Codd, 1970](https://doi.org/10.1145/362384.362685)). Uma **chave primária** identifica uma linha de forma única (o ID de um ativo); uma **chave estrangeira** se refere a uma linha de outra tabela (o nome do produto de um ativo, que casa com o produto de um alerta). Uma **junção (join)** combina as linhas que casam. Guardar cada fato uma única vez evita a falha conhecida das planilhas: duas cópias que discordam. ### 9.2 SQL em quatro cláusulas `SELECT` nomeia as colunas que você quer; `FROM ... JOIN ... ON` nomeia as tabelas e como elas casam; `WHERE` filtra as linhas; `ORDER BY` ordena. `GROUP BY` com `COUNT` resume. A biblioteca padrão do Python inclui o `sqlite3`, um banco de dados embutido completo, para você praticar sem servidor. Dois cuidados. Primeiro, uma junção descarta em silêncio as linhas sem correspondência: um ativo cujo nome de produto está escrito errado simplesmente desaparece de um relatório de vulnerabilidades. Use um `LEFT JOIN` e procure nulos quando a completude importar. Segundo, os **tipos** decidem as comparações. Strings de versão comparadas como texto põem "3.10" antes de "3.4", porque o caractere "1" vem antes de "4" na ordenação. ### 9.3 Injeção Se um programa monta SQL colando a entrada do usuário numa string — `"... WHERE id = '" + user_input + "'"` —, uma entrada como `x' OR '1'='1` muda o significado da consulta. Isso é a **injeção de SQL**, persistentemente entre as fraquezas de software mais perigosas ([CWE Top 25](https://cwe.mitre.org/top25/)). A correção é estrutural: **consultas parametrizadas** (`db.execute("... WHERE id = ?", (user_input,))`) enviam os dados separados do código, de modo que a entrada nunca pode se tornar SQL. No Laboratório 5, toda consulta com uma entrada, como buscar um ativo pelo ID, deve usar um marcador `?`. ### 9.4 Exemplo resolvido e Laboratório 5: casar alertas da CPN (sintético) **Este caso é sintético.** A Calder Power Networks mantém uma tabela de ativos e importa alertas de fornecedores. A pergunta: quais ativos rodam uma versão mais antiga que a versão corrigida, ordenados de modo que os itens sabidamente explorados e expostos à internet venham primeiro? Uma pequena função personalizada compara as versões numericamente. ```python # 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")) ``` Saída esperada: ```text ('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 ``` Dois ativos precisam de ação, o gateway primeiro (é o alerta do Capítulo 5). O HMI-01 não tem alerta e não aparece; o HIS-01 já passou da correção. Note que `ORDER BY v.severity` ordena as *palavras* alfabeticamente ("critical" < "high" < "medium"), o que por acaso está certo aqui e estaria errado com "low". Uma coluna de posição de severidade é o projeto robusto. ### 9.5 Atividade prática (1) Mude a junção para um `LEFT JOIN` e liste os ativos sem registro de alerta — "nenhum alerta" é o mesmo que "nenhuma vulnerabilidade"? (2) Escreva uma consulta parametrizada que retorne um ativo pelo ID; depois mostre, com uma consulta montada por string deliberadamente insegura, apenas no seu banco de dados em memória, como `' OR '1'='1` retorna todas as linhas. O `LEFT JOIN` mostra o HMI-01 com nulos nas colunas do alerta. "Nenhum alerta" significa nenhum alerta neste feed para o produto HMI-S, e não que a IHM não tem falha; ela pode ter um boletim do fornecedor fora do feed ou nenhum publicado ainda. Na parte (2), a consulta insegura só pode rodar contra o seu banco de dados em memória, nunca contra um real. ### 9.6 Erros comuns e limites - Comparar versões, datas ou números guardados como texto. - Junções internas que descartam em silêncio as linhas sem correspondência. - Montar SQL por concatenação de strings. - Confiar que os nomes de produtos casam exatamente entre os feeds dos fornecedores e os inventários locais. O Laboratório 5 mostra onde três desses erros morderiam. A comparação de versões trataria "3.10" como mais antiga que "3.4" se fosse feita como texto, marcando erradamente como vulnerável uma RTU atualizada. A junção interna descarta o HMI-01 em silêncio. E os nomes de produto "RTU-K" e "VPN-G" só casam porque a amostra foi digitada de forma consistente; os feeds reais dos fornecedores escrevem os produtos de formas diferentes e precisam de uma tabela de mapeamento. ### Exercícios — capítulo 9 **EX017 — Conte por local.** *Objetivo:* resumir com SQL. *Entradas:* Laboratório 5. *Etapas:* escreva uma consulta que retorne, por local, o número de ativos que precisam de atualização. *Evidência esperada:* consulta e resultado. *Rubrica:* 3 = `GROUP BY` correto com o filtro de versão, resultado Head office 1, Substation North 1; 2 = conta todos os ativos; 1 = só a sintaxe; 0 = nada. **EX018 — Ordem de severidade.** *Objetivo:* corrigir uma ordenação frágil. *Entradas:* Laboratório 5 com uma severidade extra de alerta "low". *Etapas:* explique por que `ORDER BY severity` poria "low" no lugar errado e dê duas correções. *Evidência esperada:* explicação e correções. *Rubrica:* 3 = a ordem alfabética põe "low" antes de "medium"; correções: uma coluna de posição numérica ou uma tabela de consulta/expressão `CASE`; 2 = uma correção; 1 = só a explicação; 0 = nada. ### Síntese do capítulo Os dados relacionais permitem responder a perguntas de segurança transversais com uma única consulta, se as chaves, os tipos e as junções estiverem certos. Compare as versões numericamente, procure o que as junções descartam, ordene as severidades com números e nunca monte SQL a partir de strings não confiáveis. ### Continue estudando — capítulo 09 de 40 [← Capítulo 08](#ch-08) · [Índice de capítulos do curso](../SUBJECTS.md) · [Capítulo 10 →](#ch-10) Siga para a próxima lição escrita. ## Worked answers **EX017.** `SELECT a.site, COUNT(*) FROM asset a JOIN advisory v ON a.product = v.product WHERE vlt(a.version, v.fixed_in) GROUP BY a.site;` → Head office 1; Substation North 1. **EX018.** A ordenação de texto põe "critical" < "high" < "low" < "medium", então "low" fica antes de "medium" — errado. Correções: armazenar um `severity_rank` numérico (critical 4, high 3, medium 2, low 1) e ordenar por ele; ou ordenar com `CASE severity WHEN 'critical' THEN 4 ... END`, ou fazer junção com uma tabela de consulta.