Capítulo 9 — Dados e bancos de dados

Mapa da aula

Aula 09 publicada. Mapa, exemplo, EX017.

flowchart TD
  N0["9.1 Por que dados relacionais"]
  N1["9.2 SQL em quatro cláusulas"]
  N2["9.3 Injeção"]
  N3["9.5 Atividade prática"]
  N4["Síntese do capítulo"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Exemplo resolvido"]
  EX --> Q["Exercício EX017"]

Desenho lógico

flowchart LR
  N0["9.1 Por que dados relacionais"]
  N1["9.2 SQL em quatro cláusulas"]
  N2["9.3 Injeção"]
  N3["9.5 Atividade prática"]
  N4["Síntese do capítulo"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX017"]

[9.1 Por que dados relacionais]
    |
    v
[9.2 SQL em quatro cláusulas]
    |
    v
[9.3 Injeção]
    |
    v
[9.5 Atividade prática]
    |
    v
[Síntese do capítulo]
    |
    v
[Exemplo resolvido] --> [Exercício EX017]

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). 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). 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.

# 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:

('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.