Interrogazione dei dati UTXO e dei log e delle tracce EVM

La maggior parte di ciò che serve a un investigatore da una blockchain non è memorizzata come risposta già pronta. Su Bitcoin e sulle altre blockchain UTXO non esistono saldi da consultare: ogni saldo, commissione e flusso deve essere ricostruito collegando gli output agli input che successivamente li spendono. Sulle blockchain EVM, i movimenti di token, le approvazioni e i pagamenti dei contratti si trovano nei log degli eventi e nelle chiamate interne, anziché nella transazione stessa. Le tabelle decodificate coprono i contratti più diffusi, ma prima o poi un caso coinvolge un nuovo token, un contratto non verificato o uno schema che nessuno ha decodificato. Questa lezione mostra come scrivere correttamente tali join e decodifiche in SQL, affinché ogni cifra di una relazione si basi su una query che un altro esaminatore possa rieseguire ottenendo lo stesso risultato. Utilizza lo schema generico della lezione 2.7 e si basa sugli schemi di join, funzioni finestra e CTE della lezione 2.8.

Tabelle UTXO e join sull'outpoint

I dataset UTXO suddividono ogni transazione in righe a due livelli di granularità (valori in satoshi):

Un output è identificato dal suo outpoint: hash della transazione più indice dell'output. Un input indica l'outpoint che spende tramite spent_transaction_hash e spent_output_index. In questo schema le transazioni coinbase non hanno righe in inputs; alcuni dataset conservano un input coinbase con un outpoint vuoto, quindi occorre verificarlo prima di effettuare i conteggi.

Un input grezzo contiene soltanto l'outpoint, uno script e un numero di sequenza, senza indirizzo né importo (lezione 2.2). Le colonne address, value e script_type in inputs esistono perché la pipeline ha cercato ogni output speso e ne ha copiato i campi: un passaggio che gli strumenti blockchain-etl chiamano arricchimento. È una comodità, ma anche una dipendenza: se l'arricchimento non è riuscito per alcuni blocchi, quegli input hanno valori NULL e ogni commissione e saldo basati su di essi sono errati. Prima di farvi affidamento, contare gli input con value NULL nell'intervallo di interesse.

Altre due insidie nella struttura dei dati:

• Array di indirizzi. Alcuni dataset, incluso il dataset Bitcoin di BigQuery, memorizzano addresses come array, perché gli script multisig diretti contengono più chiavi e gli script non standard nessuna. Per ottenere una riga per indirizzo, espandere l'array: CROSS JOIN UNNEST(addresses) AS address in BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) in Trino e DuckDB, unnest() in PostgreSQL, LATERAL FLATTEN in Snowflake, explode() in Spark SQL. Gli output OP_RETURN mantengono un valore NULL o un array vuoto.
• txid duplicati. Due transazioni coinbase del 2010 condividono il txid di transazioni coinbase precedenti, un problema che BIP 30 ha successivamente vietato. Quando l'esattezza è importante, utilizzare come chiave l'hash e il numero di blocco.

L'arco del grafo di spesa UTXO corrisponde al join su entrambe le parti dell'outpoint:

inputs.spent_transaction_hash = outputs.transaction_hash AND inputs.spent_output_index = outputs.output_index

Un join sul solo hash associa ogni input a tutti gli output della transazione precedente.

Output non spesi e saldi a un determinato momento

L'individuazione degli UTXO è un anti-join: eseguire un left join degli output con gli input e mantenere le righe per le quali non è stato trovato alcun input corrispondente.

-- Output non spesi di un indirizzo al blocco 870.000
SELECT o.transaction_hash, o.output_index, o.value / 1e8 AS btc
FROM outputs AS o
LEFT JOIN inputs AS i
  ON  i.spent_transaction_hash = o.transaction_hash
  AND i.spent_output_index     = o.output_index
  AND i.block_number          <= 870000     -- solo spese fino all'istantanea
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- nessun input di spesa trovato

La somma di value fornisce il saldo dell'indirizzo a quell'altezza. Nell'esempio, l'indirizzo ha ricevuto 0,05 e 0,03 BTC; il primo output è stato speso al blocco 870.000 e il secondo al blocco 870.005, quindi il saldo al blocco 870.000 è 0,03 BTC.

La condizione dell'istantanea su i.block_number deve trovarsi nella clausola ON. Se viene spostata in WHERE, viene valutata dopo il join, quando i.block_number è NULL per ogni output senza corrispondenza, per cui la query restituisce zero righe anziché 0,03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) esprime lo stesso anti-join senza questa insidia. Per una cronologia del saldo, accodare con UNION ALL gli output destinati all'indirizzo come importi positivi e gli input arricchiti provenienti da esso come importi negativi, quindi eseguire una somma finestra in ordine di blocco (lezione 2.8).

Commissioni senza moltiplicazione delle righe nel join

Una commissione è la somma degli input meno la somma degli output. Eseguire un join tra input e output sull'hash della transazione e sommare entrambi è errato: una transazione con 3 input e 4 output produce 12 righe risultanti dal join e moltiplica entrambe le somme. Aggregare prima ciascun lato, quindi unire i totali.

-- Commissione per transazione: aggregare ciascun lato, quindi eseguire il join
WITH vin AS (
  SELECT transaction_hash, COUNT(*) AS n_in, SUM(value) AS in_sat
  FROM inputs WHERE block_number = 870000
  GROUP BY transaction_hash
),
vout AS (
  SELECT transaction_hash, COUNT(*) AS n_out, SUM(value) AS out_sat
  FROM outputs WHERE block_number = 870000
  GROUP BY transaction_hash
)
SELECT vout.transaction_hash, n_in, n_out,
       in_sat - out_sat AS fee_sat          -- la coinbase non ha input: esclusa dal join
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

Nel blocco di esempio, una transazione spende 5.000.000 e 2.000.000 sat e crea output da 6.000.000 e 991.000, quindi la sua commissione è 9.000 sat. La coinbase scompare nell'inner join, il che è corretto: non paga commissioni. Dividere per virtual_size per ottenere un tasso di commissione. Molti dataset precalcolano fee; ricalcolarlo su un campione è un buon test del loro arricchimento.

Euristiche come indicatori memorizzati

Le euristiche del modulo 4 diventano indicatori: colonne che contrassegnano candidati, non conclusioni. Un segnale più forte di resto (lezione 4.2) è la corrispondenza del tipo di script: in una transazione a due output i cui input condividono un unico tipo di script, l'unico output di quel tipo è un candidato a essere il resto.

-- Candidati a essere il resto: l'unico output il cui tipo di script corrisponde agli input
WITH tx_in AS (
  SELECT transaction_hash, MIN(script_type) AS in_type
  FROM inputs WHERE block_number BETWEEN 870000 AND 870099
  GROUP BY transaction_hash
  HAVING COUNT(DISTINCT script_type) = 1      -- tutti gli input condividono un unico tipo
),
outs AS (
  SELECT o.transaction_hash, o.output_index, o.address, o.value,
         CASE WHEN o.script_type = t.in_type THEN 1 ELSE 0 END AS type_match,
         COUNT(*) OVER (PARTITION BY o.transaction_hash) AS n_out
  FROM outputs AS o JOIN tx_in AS t ON t.transaction_hash = o.transaction_hash
  WHERE o.block_number BETWEEN 870000 AND 870099
)
SELECT transaction_hash, output_index, address, value,
       CASE WHEN MOD(value, 100000) <> 0 THEN 1 ELSE 0 END AS not_round
FROM (SELECT outs.*, SUM(type_match) OVER (PARTITION BY transaction_hash) AS n_match
      FROM outs) AS x
WHERE n_out = 2 AND n_match = 1 AND type_match = 1;

La query esterna aggiunge anche un segnale più debole: un importo che non è un multiplo tondo di 100.000 sat. Nell'esempio, input SegWit nativi pagano 6.000.000 sat a un indirizzo P2SH e 991.000 a un nuovo indirizzo SegWit nativo; l'output da 991.000 è contrassegnato per entrambi i motivi. Memorizzare gli indicatori con il nome e la versione dell'euristica e combinarli in un livello di confidenza nell'analisi, mai in un join codificato rigidamente. La corrispondenza del tipo di script non funziona quando il destinatario utilizza lo stesso tipo di portafoglio, situazione sempre più comune con la diffusione di Taproot, e quando i portafogli variano deliberatamente i tipi di resto.

L'euristica di proprietà comune degli input (lezione 4.1) diventa una lista di archi degli indirizzi che hanno speso congiuntamente:

-- Archi basati sugli input comuni, escludendo le transazioni simili a CoinJoin
WITH equal_outs AS (                    -- gruppo più numeroso di output di pari valore
  SELECT transaction_hash, MAX(n) AS max_equal
  FROM (SELECT transaction_hash, value, COUNT(*) AS n
        FROM outputs WHERE block_number BETWEEN 870000 AND 870099
        GROUP BY transaction_hash, value) AS g
  GROUP BY transaction_hash
),
ins AS (
  SELECT i.transaction_hash, i.address,
         MIN(i.address) OVER (PARTITION BY i.transaction_hash) AS anchor
  FROM inputs AS i JOIN equal_outs AS e ON e.transaction_hash = i.transaction_hash
  WHERE i.block_number BETWEEN 870000 AND 870099
    AND e.max_equal < 3                 -- escludere le transazioni simili a CoinJoin
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- un arco per ogni ulteriore indirizzo di input

Tre o più output uguali sono un segnale approssimativo di CoinJoin, quindi tali transazioni vengono escluse. Collegare ogni indirizzo di input a un unico indirizzo di ancoraggio (l'indirizzo minimo nella transazione) produce n − 1 archi per n indirizzi anziché tutte le coppie, e mantenere l'hash della transazione su ogni arco conserva l'evidenza. Trasformare gli archi in cluster significa individuare le componenti connesse, un processo iterativo: gli analisti solitamente esportano gli archi in una libreria per grafi con un algoritmo union-find. In SQL, la propagazione delle etichette (sostituire ripetutamente l'ID di ogni indirizzo con il più piccolo tra quelli dei suoi vicini finché non cambia più nulla) può essere scritta con WITH RECURSIVE, ma è lenta sui dati dell'intera blockchain. Registrare le regole di esclusione e la loro versione e prestare attenzione ai supercluster (lezione 4.4).

Log EVM: firme, topic e dati

Un log contiene l'indirizzo del contratto che lo emette, fino a quattro topic e un campo data. Per un normale evento Solidity, topic0 è l'hash Keccak-256 della firma canonica (nome e tipi dei parametri, senza spazi né nomi dei parametri). I parametri dichiarati indexed sono inseriti da topic1 a topic3; gli altri vengono codificati secondo l'ABI in data come parole da 32 byte.

Filtrare su topic0 seleziona un tipo di evento in tutti i contratti. Aggiungere contract_address ogni volta che si intende un singolo token o protocollo: chiunque può distribuire un contratto che emette un evento Transfer ben formato, ed è esattamente ciò che fanno i token truffaldini per inserire trasferimenti falsi nelle cronologie delle vittime. Un evento è affidabile solo quanto il contratto che lo ha emesso. ERC-20 ed ERC-721 condividono la firma Transfer, ma ERC-721 indicizza l'ID del token, producendo quattro topic; verificare topic3 IS NULL mantiene i trasferimenti ERC-20. Quando i topic sono memorizzati come array, BigQuery conta da zero (topics[OFFSET(0)] o SAFE_OFFSET(0)), mentre PostgreSQL, Trino e DuckDB contano da uno (topics[1]); sbagliare questo aspetto sposta ogni topic senza segnalare errori.

I topic e le parole di dati sono composti da 64 caratteri esadecimali dopo 0x. Un indirizzo occupa gli ultimi 40, con zeri di riempimento a sinistra, quindi inizia al carattere 27; la parola di dati k (a partire da zero) inizia al carattere 3 + 64k.

-- Decodificare gli eventi Transfer ERC-20 di un token dai log grezzi
SELECT block_number, log_index, transaction_hash,
       '0x' || SUBSTR(topic1, 27, 40)  AS from_address,
       '0x' || SUBSTR(topic2, 27, 40)  AS to_address,
       SUBSTR(data, 3, 64)             AS amount_hex    -- prima parola da 32 byte
FROM logs
WHERE contract_address = '0x7a11…0001'
  AND topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL                   -- ERC-721 aggiunge un 4° topic (tokenId)
  AND block_number BETWEEN 21000000 AND 21000999;

In una riga di esempio, data termina con 77359400, che corrisponde a 2.000.000.000; con i 6 decimali del token, equivale a 2.000 token. La conversione da esadecimale a numero è il punto in cui i motori differiscono: DuckDB converte in un HUGEINT a 128 bit; from_base(h, 16) di Trino e la conversione a INT64 di BigQuery sono a 64 bit; PostgreSQL richiede una funzione definita dall'utente. Il limite a 64 bit di circa 9,22 × 10^18 unità grezze corrisponde ad appena 9,22 token con 18 decimali, per cui conversioni che funzionano sui dati di prova vanno in overflow sui trasferimenti reali. Decodificare gli importi nella fase ETL oppure utilizzare una tabella decodificata, memorizzarli come decimali e lasciare i tipi dinamici (stringhe, array) a una libreria ABI.

Alcuni valori non richiedono conversione. Un'approvazione "illimitata" imposta il limite di spesa a 2^256 − 1, ossia 64 caratteri f:

-- Approvazioni illimitate di token: limite di spesa = 2^256 - 1 (64 'f' esadecimali)
SELECT block_number, transaction_hash,
       contract_address                AS token,
       '0x' || SUBSTR(topic1, 27, 40)  AS owner,
       '0x' || SUBSTR(topic2, 27, 40)  AS spender
FROM logs
WHERE topic0 = '0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925'
  AND LOWER(data) = '0xffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
  AND block_number BETWEEN 21000000 AND 21000999;

Gli elenchi dei titolari che hanno concesso approvazioni illimitate a un singolo soggetto autorizzato alla spesa costituiscono un passaggio standard per valutare la portata di una campagna di svuotamento dei portafogli o della compromissione di un protocollo (lezione 5.5).

Trasferimenti interni dalle tracce

Gli ETH inviati da un contratto, ad esempio un prelievo da un pool o un pagamento multisig, non compaiono in transactions.value, ma nelle tracce, con una riga per frame di chiamata. trace_address è la posizione del frame nell'albero delle chiamate (qui in formato testuale: '' per la chiamata di livello superiore, '0' per la sua prima sottochiamata, '0,1' per la seconda chiamata all'interno di quest'ultima; alcuni dataset utilizzano array). trace_type è call, create, suicide (il nome storico di selfdestruct) o reward; call_type è call, delegatecall o staticcall; status e tx_status registrano se il frame e l'intera transazione sono riusciti.

-- Trasferimenti interni di ETH (inviati da contratti) verso un indirizzo
SELECT block_number, transaction_hash, trace_address,
       from_address, value / 1e18 AS value_eth
FROM traces
WHERE to_address = '0x5e1f…c0de'
  AND value > 0
  AND trace_type = 'call'
  AND call_type = 'call'          -- delegatecall e staticcall non spostano ETH
  AND trace_address <> ''         -- '' è la chiamata di livello superiore stessa
  AND status = 1                  -- questo frame di chiamata è riuscito
  AND tx_status = 1               -- e anche la transazione
  AND block_number BETWEEN 21000000 AND 21050000;

Ogni filtro elimina un falso positivo noto. Alcuni formati di traccia mostrano un valore su una delegatecall che non è mai stato trasferito. Escludere il frame di livello superiore evita il doppio conteggio se si interroga anche transactions; per misurare tutti gli ETH ricevuti, utilizzare soltanto le tracce, includendo il frame di livello superiore, e ricordare che anche i frame create con valore e i frame selfdestruct spostano ETH. Lo stato è più importante per le tracce che per i log: una transazione annullata non emette log, ma le sue tracce vengono comunque registrate, e un frame può essere annullato all'interno di una transazione riuscita. Richiedere entrambi gli stati copre i casi usuali, ma un annullamento intercettato a un livello intermedio dell'albero può sfuggire, quindi verificare singolarmente le transazioni insolite.

Analisi del gas

Da EIP-1559 (agosto 2021): commissione pagata = gas utilizzato × prezzo effettivo del gas; importo bruciato = gas utilizzato × commissione base per unità di gas; commissione di priorità = gas utilizzato × (prezzo effettivo del gas − commissione base). Le transazioni che trasportano blob (EIP-4844) pagano anche una commissione blob che viene bruciata, pari al gas blob utilizzato × prezzo del gas blob, registrata in campi separati della ricevuta.

-- Commissioni giornaliere, importi bruciati e commissioni di priorità, in ETH
SELECT DATE_TRUNC('day', t.block_timestamp)                         AS day_utc,
       COUNT(*)                                                      AS txs,
       SUM(t.gas_used * t.effective_gas_price) / 1e18                AS fees_eth,
       SUM(t.gas_used * b.base_fee_per_gas) / 1e18                   AS burned_eth,
       SUM(t.gas_used * (t.effective_gas_price - b.base_fee_per_gas)) / 1e18
                                                                     AS tips_eth
FROM transactions AS t                -- anche le transazioni fallite pagano commissioni
JOIN blocks AS b ON b.hash = t.block_hash    -- hash, non altezza: esclude i blocchi sostituiti
WHERE t.block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND t.block_timestamp <  TIMESTAMP '2024-11-08 00:00:00'
GROUP BY DATE_TRUNC('day', t.block_timestamp)
ORDER BY day_utc;

A differenza delle query sui valori, questa mantiene deliberatamente le transazioni fallite, perché consumano gas e lo pagano. Il gas moltiplicato per un prezzo in wei supera facilmente i 64 bit (10 milioni di gas a 1.000 gwei equivalgono a 10^19 wei), un'altra ragione per cui queste colonne sono decimali. In un piccolo campione, un giorno mostra 0,0239 ETH di commissioni, di cui soltanto 0,0030 ETH sono stati bruciati: una singola transazione che paga 1.000 gwei per unità di gas (980 gwei sopra la commissione base) domina il totale, il tipo di valore anomalo che merita un esame più attento. I dati sul gas mostrano chi ha finanziato il gas di un account (un collegamento forte, lezione 4.3), quali mittenti pagano commissioni di priorità insolitamente elevate (spesso bot o attaccanti per i quali il fattore tempo è critico) e quanto è costato un attacco. Sui rollup, le ricevute contengono campi aggiuntivi per le commissioni dei dati L1, i cui nomi variano a seconda della blockchain.

Focus sull'esame: prepararsi a individuare l'errore in una query: join su entrambe le parti dell'outpoint; arricchimento e valori NULL degli input; individuazione degli UTXO con un anti-join, con la condizione dell'istantanea in ON; aggregazione separata di input e output per evitare la moltiplicazione delle righe; coinbase e duplicati BIP 30; euristiche come indicatori con versione e liste di archi basate sugli input comuni con esclusioni CoinJoin; topic0 come Keccak-256 della firma canonica; filtro per indirizzo del contratto contro gli eventi contraffatti; topic3 IS NULL per ERC-20; SUBSTR(topic, 27, 40) e parole di dati a 3 + 64k; array di topic con indice iniziale zero oppure uno; overflow a 64 bit; approvazioni illimitate; filtri delle tracce sul tipo di chiamata, sull'indirizzo della traccia e su entrambi gli stati; formule EIP-1559 per commissioni, importi bruciati e priorità, mantenendo le transazioni fallite nei totali delle commissioni.