Abfragen von UTXO-Daten sowie EVM-Logs und -Traces

Das meiste, was ein Ermittler aus einer Blockchain benötigt, liegt nicht als fertige Antwort vor. Auf Bitcoin und anderen UTXO-Blockchains gibt es keine direkt abfragbaren Kontostände: Jeder Kontostand, jede Gebühr und jeder Geldfluss muss rekonstruiert werden, indem Outputs mit den Inputs verknüpft werden, die sie später ausgeben. Auf EVM-Blockchains finden sich Token-Bewegungen, Freigaben und Auszahlungen durch Smart Contracts in Ereignis-Logs und internen Aufrufen statt in der Transaktion selbst. Dekodierte Tabellen decken verbreitete Smart Contracts ab, doch früher oder später betrifft ein Fall einen neuen Token, einen nicht verifizierten Smart Contract oder ein Muster, das noch niemand dekodiert hat. Diese Lektion zeigt, wie Sie solche Joins und Dekodierungen in SQL korrekt schreiben, sodass jede Zahl in einem Bericht auf einer Abfrage beruht, die ein anderer Prüfer erneut ausführen und dabei dasselbe Ergebnis erhalten kann. Sie verwendet das generische Schema aus Lektion 2.7 und baut auf den Join-, Fensterfunktions- und CTE-Mustern aus Lektion 2.8 auf.

UTXO-Tabellen und der Outpoint-Join

UTXO-Datensätze teilen jede Transaktion in Zeilen auf zwei Granularitätsebenen auf (Werte in Satoshis):

Ein Output wird durch seinen Outpoint identifiziert: Transaktionshash plus Output-Index. Ein Input benennt den Outpoint, den er ausgibt, über spent_transaction_hash und spent_output_index. In diesem Schema haben Coinbase-Transaktionen keine Zeilen in inputs; manche Datensätze enthalten einen Coinbase-Input mit leerem Outpoint. Prüfen Sie dies daher vor dem Zählen.

Ein roher Input enthält nur den Outpoint, ein Skript und eine Sequenznummer, jedoch weder Adresse noch Betrag (Lektion 2.2). Die Spalten address, value und script_type in inputs existieren, weil die Pipeline jeden ausgegebenen Output nachgeschlagen und dessen Felder übernommen hat. Diesen Schritt bezeichnen die blockchain-etl-Werkzeuge als Anreicherung. Sie ist eine Erleichterung und zugleich eine Abhängigkeit: Wenn die Anreicherung für einige Blöcke fehlgeschlagen ist, haben diese Inputs NULL-Werte, und jede darauf aufbauende Gebühr und jeder Kontostand ist falsch. Zählen Sie die Inputs mit NULL in value in Ihrem Bereich, bevor Sie sich darauf verlassen.

Zwei weitere Fallstricke beim Datenlayout:

• Adressarrays. Einige Datensätze, darunter der Bitcoin-Datensatz von BigQuery, speichern addresses als Array, weil reine Multisig-Skripte mehrere Schlüssel und nicht standardkonforme Skripte keine enthalten. Um eine Zeile pro Adresse zu erhalten, lösen Sie das Array auf: CROSS JOIN UNNEST(addresses) AS address in BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) in Trino und DuckDB, unnest() in PostgreSQL, LATERAL FLATTEN in Snowflake, explode() in Spark SQL. OP_RETURN-Outputs behalten NULL oder ein leeres Array.
• Doppelte txids. Zwei Coinbase-Transaktionen aus dem Jahr 2010 haben dieselbe txid wie frühere Coinbase-Transaktionen, ein Problem, das BIP 30 später unterband. Wenn Genauigkeit entscheidend ist, verwenden Sie Hash und Blocknummer als Schlüssel.

Die Kante im UTXO-Ausgabegraphen entsteht durch den Join über beide Teile des Outpoints:

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

Ein Join allein über den Hash verknüpft jeden Input mit jedem Output der früheren Transaktion.

Unausgegebene Outputs und stichtagsbezogene Kontostände

Das Finden von UTXOs ist ein Anti-Join: Verknüpfen Sie Outputs per Left Join mit Inputs und behalten Sie die Zeilen, für die kein passender Input gefunden wurde.

-- Unausgegebene Outputs einer Adresse zum Stand von Block 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     -- nur Ausgaben bis zum Stichtag
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- kein ausgebender Input gefunden

Die Summe von value ergibt den Kontostand der Adresse auf dieser Blockhöhe. Im Beispiel empfing die Adresse 0,05 und 0,03 BTC; der erste Output wurde in Block 870.000 ausgegeben, der zweite in Block 870.005. Der Kontostand bei Block 870.000 beträgt daher 0,03 BTC.

Die Stichtagsbedingung für i.block_number muss in der ON-Klausel stehen. Wird sie nach WHERE verschoben, wird sie nach dem Join ausgewertet, wenn i.block_number für jeden Output ohne Zuordnung NULL ist. Die Abfrage liefert dann keine Zeilen statt 0,03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) drückt denselben Anti-Join ohne diesen Fallstrick aus. Für einen Kontostandsverlauf führen Sie Outputs an die Adresse als positive Beträge und angereicherte Inputs von ihr als negative Beträge mit UNION ALL zusammen und berechnen anschließend eine Fenstersumme in Blockreihenfolge (Lektion 2.8).

Gebühren ohne Join-Vervielfachung

Eine Gebühr ist die Summe der Inputs abzüglich der Summe der Outputs. Inputs und Outputs über den Transaktionshash zu verknüpfen und beide zu summieren, ist falsch: Eine Transaktion mit 3 Inputs und 4 Outputs erzeugt 12 verknüpfte Zeilen und vervielfacht beide Summen. Aggregieren Sie zuerst jede Seite und verknüpfen Sie anschließend die Summen.

-- Gebühr pro Transaktion: jede Seite aggregieren, dann verknüpfen
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          -- Coinbase hat keine Inputs: entfällt durch den Join
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

Im Beispielblock gibt eine Transaktion 5.000.000 und 2.000.000 sat aus und erzeugt Outputs von 6.000.000 und 991.000, sodass ihre Gebühr 9.000 sat beträgt. Die Coinbase entfällt beim Inner Join, was korrekt ist: Sie zahlt keine Gebühr. Dividieren Sie durch virtual_size, um einen Gebührensatz zu erhalten. Viele Datensätze berechnen fee vorab; eine erneute Berechnung anhand einer Stichprobe ist ein guter Test ihrer Anreicherung.

Heuristiken als gespeicherte Kennzeichen

Heuristiken aus Modul 4 werden zu Kennzeichen: Spalten, die Kandidaten markieren, keine Schlussfolgerungen. Ein stärkeres Wechselgeldsignal (Lektion 4.2) ist eine Übereinstimmung des Skripttyps: In einer Transaktion mit zwei Outputs, deren Inputs alle denselben Skripttyp haben, ist der einzige Output dieses Typs ein Wechselgeldkandidat.

-- Wechselgeldkandidaten: der einzige Output, dessen Skripttyp mit den Inputs übereinstimmt
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      -- alle Inputs haben denselben Typ
),
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;

Die äußere Abfrage ergänzt ein schwächeres Signal: einen Betrag, der kein rundes Vielfaches von 100.000 sat ist. Im Beispiel zahlen native SegWit-Inputs 6.000.000 sat an eine P2SH-Adresse und 991.000 an eine neue native SegWit-Adresse; der Output über 991.000 wird nach beiden Kriterien gekennzeichnet. Speichern Sie Kennzeichen mit dem Namen und der Version der Heuristik und kombinieren Sie diese in der Analyse zu einem Konfidenzniveau, niemals in einem fest codierten Join. Der Abgleich des Skripttyps versagt, wenn der Empfänger denselben Wallet-Typ verwendet, was mit der Verbreitung von Taproot immer häufiger vorkommt, und wenn Wallets die Wechselgeldtypen bewusst variieren.

Die Heuristik gemeinsamer Input-Inhaberschaft (Lektion 4.1) wird zu einer Kantenliste von Adressen, deren Inputs gemeinsam ausgegeben wurden:

-- Kanten gemeinsamer Inputs, ohne CoinJoin-ähnliche Transaktionen
WITH equal_outs AS (                    -- größte Gruppe gleich hoher Outputs
  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                 -- CoinJoin-ähnliche Transaktionen überspringen
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- eine Kante pro zusätzlicher Input-Adresse

Drei oder mehr gleich hohe Outputs sind ein grobes CoinJoin-Signal, daher werden diese Transaktionen ausgeschlossen. Die Verknüpfung jeder Input-Adresse mit einem Anker (der kleinsten Adresse in der Transaktion) ergibt n − 1 Kanten für n Adressen statt aller möglichen Paare. Der Transaktionshash an jeder Kante bewahrt dabei die Beweisgrundlage. Kanten in Cluster umzuwandeln bedeutet, Zusammenhangskomponenten zu finden. Dies ist ein iterativer Vorgang: Analysten exportieren die Kanten üblicherweise in eine Graphbibliothek mit einem Union-Find-Algorithmus. In SQL lässt sich Label Propagation (wiederholtes Ersetzen der ID jeder Adresse durch die kleinste ID ihrer Nachbarn, bis sich nichts mehr ändert) mit WITH RECURSIVE schreiben, ist bei Daten der gesamten Blockchain jedoch langsam. Dokumentieren Sie Ausschlussregeln und deren Version und achten Sie auf Supercluster (Lektion 4.4).

EVM-Logs: Signaturen, Topics und Daten

Ein Log enthält die ausgebende Contract-Adresse, bis zu vier Topics und ein Datenfeld. Bei einem normalen Solidity-Ereignis ist topic0 der Keccak-256-Hash der kanonischen Signatur (Name und Parametertypen, ohne Leerzeichen oder Parameternamen). Als indexed deklarierte Parameter stehen in topic1 bis topic3; die übrigen werden ABI-kodiert als 32-Byte-Wörter in data gespeichert.

Ein Filter auf topic0 wählt einen Ereignistyp über alle Smart Contracts hinweg aus. Ergänzen Sie contract_address, wenn Sie einen bestimmten Token oder ein bestimmtes Protokoll meinen: Jeder kann einen Smart Contract bereitstellen, der ein formal korrektes Transfer-Ereignis ausgibt, und betrügerische Token tun genau dies, um gefälschte Transfers in den Historien der Opfer zu platzieren. Ein Ereignis ist nur so vertrauenswürdig wie der Smart Contract, der es ausgegeben hat. ERC-20 und ERC-721 verwenden dieselbe Transfer-Signatur, doch ERC-721 indiziert die Token-ID, sodass vier Topics entstehen; die Prüfung topic3 IS NULL behält ERC-20-Transfers bei. Wenn Topics als Array gespeichert werden, zählt BigQuery ab null (topics[OFFSET(0)] oder SAFE_OFFSET(0)), während PostgreSQL, Trino und DuckDB ab eins zählen (topics[1]). Ein Fehler hierbei verschiebt unbemerkt jedes Topic.

Topics und Datenwörter bestehen aus 64 Hexadezimalzeichen nach 0x. Eine Adresse belegt die letzten 40 und wird links mit Nullen aufgefüllt; sie beginnt daher bei Zeichen 27. Datenwort k (ab null gezählt) beginnt bei Zeichen 3 + 64k.

-- ERC-20-Transfer-Ereignisse eines Tokens aus rohen Logs dekodieren
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    -- erstes 32-Byte-Wort
FROM logs
WHERE contract_address = '0x7a11…0001'
  AND topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL                   -- ERC-721 ergänzt ein 4. Topic (tokenId)
  AND block_number BETWEEN 21000000 AND 21000999;

In einer Beispielzeile endet data auf 77359400, was 2.000.000.000 entspricht; bei den 6 Dezimalstellen des Tokens sind dies 2.000 Token. Bei der Umwandlung von Hexadezimalwerten in Zahlen unterscheiden sich die Engines: DuckDB konvertiert in einen 128-Bit-HUGEINT; Trinos from_base(h, 16) und BigQuerys Konvertierung in INT64 sind auf 64 Bit begrenzt; PostgreSQL benötigt eine benutzerdefinierte Funktion. Die 64-Bit-Grenze von etwa 9,22 × 10^18 Rohwerteinheiten entspricht bei 18 Dezimalstellen nur 9,22 Token. Konvertierungen, die bei Testdaten funktionieren, laufen daher bei realen Transfers über. Dekodieren Sie Beträge im ETL-Schritt oder verwenden Sie eine dekodierte Tabelle, speichern Sie sie als Dezimalzahlen und überlassen Sie dynamische Typen (Zeichenketten, Arrays) einer ABI-Bibliothek.

Einige Werte müssen nicht konvertiert werden. Eine „unbegrenzte“ Freigabe setzt den freigegebenen Betrag auf 2^256 − 1, also 64 f-Zeichen:

-- Unbegrenzte Token-Freigaben: freigegebener Betrag = 2^256 - 1 (64 Hex-Zeichen 'f')
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;

Listen von Inhabern, die einem bestimmten Ausgabeberechtigten unbegrenzte Freigaben erteilt haben, sind ein Standardschritt bei der Ermittlung des Ausmaßes einer Wallet-Drainer-Kampagne oder eines kompromittierten Protokolls (Lektion 5.5).

Interne Transfers aus Traces

ETH, die ein Smart Contract versendet, etwa bei einer Pool-Auszahlung oder einer Multisig-Auszahlung, erscheinen nicht in transactions.value, sondern in Traces, mit einer Zeile pro Aufrufrahmen. trace_address bezeichnet die Position des Rahmens im Aufrufbaum (hier als Text: '' für den Aufruf auf oberster Ebene, '0' für dessen ersten Unteraufruf, '0,1' für den zweiten Aufruf darin; manche Datensätze verwenden Arrays). trace_type ist call, create, suicide (die historische Bezeichnung für selfdestruct) oder reward; call_type ist call, delegatecall oder staticcall; status und tx_status halten fest, ob der Rahmen und die gesamte Transaktion erfolgreich waren.

-- Interne ETH-Transfers (von Smart Contracts gesendet) an eine Adresse
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 und staticcall übertragen keine ETH
  AND trace_address <> ''         -- '' ist der Aufruf auf oberster Ebene selbst
  AND status = 1                  -- dieser Aufrufrahmen war erfolgreich
  AND tx_status = 1               -- ebenso die Transaktion
  AND block_number BETWEEN 21000000 AND 21050000;

Jeder Filter entfernt einen bekannten falsch positiven Treffer. Manche Trace-Formate zeigen bei einem delegatecall einen Wert an, der nie übertragen wurde. Der Ausschluss des Rahmens auf oberster Ebene verhindert Doppelzählungen, wenn Sie auch transactions abfragen. Um alle empfangenen ETH zu erfassen, verwenden Sie ausschließlich Traces einschließlich des Rahmens auf oberster Ebene und bedenken Sie, dass auch create-Rahmen mit Wert und selfdestruct-Rahmen ETH übertragen. Der Status ist bei Traces wichtiger als bei Logs: Eine zurückgesetzte Transaktion gibt keine Logs aus, ihre Traces werden jedoch weiterhin aufgezeichnet, und ein Rahmen kann innerhalb einer erfolgreichen Transaktion zurückgesetzt werden. Die Prüfung beider Statuswerte deckt die üblichen Fälle ab; ein innerhalb des Aufrufbaums abgefangenes Zurücksetzen kann jedoch unentdeckt bleiben. Prüfen Sie ungewöhnliche Transaktionen daher einzeln.

Gas-Analysen

Seit EIP-1559 (August 2021) gilt: gezahlte Gebühr = verbrauchtes Gas × effektiver Gaspreis; verbrannter Betrag = verbrauchtes Gas × Basisgebühr pro Gas; Prioritätsgebühr = verbrauchtes Gas × (effektiver Gaspreis − Basisgebühr). Transaktionen mit Blobs (EIP-4844) zahlen zusätzlich eine verbrannte Blob-Gebühr, verbrauchtes Blob-Gas × Blob-Gaspreis, die in separaten Receipt-Feldern erfasst wird.

-- Tägliche Gebühren, verbrannte Beträge und Prioritätsgebühren 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                -- auch fehlgeschlagene Transaktionen zahlen Gebühren
JOIN blocks AS b ON b.hash = t.block_hash    -- Hash, nicht Höhe: überspringt ersetzte Blöcke
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;

Anders als Abfragen zu übertragenen Werten behält diese Abfrage fehlgeschlagene Transaktionen bewusst bei, da sie Gas verbrauchen und dafür bezahlen. Gas multipliziert mit einem Preis in wei überschreitet leicht 64 Bit (10 Millionen Gas zu 1.000 gwei ergeben 10^19 wei), ein weiterer Grund dafür, diese Spalten als Dezimalzahlen zu speichern. In einer kleinen Stichprobe weist ein Tag 0,0239 ETH an Gebühren auf, von denen nur 0,0030 ETH verbrannt wurden: Eine einzelne Transaktion, die 1.000 gwei pro Gas zahlt (980 gwei über der Basisgebühr), dominiert das Ergebnis. Ein solcher Ausreißer verdient eine nähere Betrachtung. Gas-Daten zeigen, wer die Gas-Kosten eines Kontos finanziert hat (eine starke Verknüpfung, Lektion 4.3), welche Absender ungewöhnlich hohe Prioritätsgebühren zahlen (häufig Bots oder Angreifer unter Zeitdruck) und was ein Angriff gekostet hat. Bei Rollups enthalten Receipts zusätzliche Felder für L1-Datengebühren, deren Namen je nach Blockchain variieren.

Prüfungsschwerpunkt: Rechnen Sie damit, Fehler in einer Abfrage erkennen zu müssen: Join über beide Teile des Outpoints; Anreicherung und NULL-Werte bei Inputs; Finden von UTXOs mit einem Anti-Join und der Stichtagsbedingung in ON; getrennte Aggregation von Inputs und Outputs zur Vermeidung einer Vervielfachung; Coinbase und BIP-30-Duplikate; Heuristiken als versionierte Kennzeichen und Kantenlisten gemeinsamer Inputs mit CoinJoin-Ausschlüssen; topic0 als Keccak-256 der kanonischen Signatur; Filtern nach Contract-Adresse zum Schutz vor gefälschten Ereignissen; topic3 IS NULL für ERC-20; SUBSTR(topic, 27, 40) und Datenwörter bei 3 + 64k; null- gegenüber einsbasierten Topic-Arrays; 64-Bit-Überlauf; unbegrenzte Freigaben; Trace-Filter nach Aufruftyp, Trace-Adresse und beiden Statuswerten; EIP-1559-Formeln für Gebühren, verbrannte Beträge und Prioritätsgebühren sowie das Beibehalten fehlgeschlagener Transaktionen in den Gebührensummen.