Capturing SQL Statements Without Leaking Data

Record the parameterized statement — placeholders intact — at the driver, add a Collector redaction pass for the libraries you do not control, cap the length, and prove it with a canary value that fails the build if it ever reaches trace storage.

Context and when it matters

db.query.text is one of the highest-value attributes in a trace and one of the two or three most common sources of accidental data exposure. The value comes from grouping: a thousand spans carrying SELECT * FROM orders WHERE tenant_id = $1 collapse into one row in a slow-query analysis. The risk comes from the same field carrying SELECT * FROM users WHERE email = '[email protected]' — which groups into nothing useful and puts a personal identifier into a store that is retained for weeks, replicated, backed up, and readable by everyone with access to the tracing UI.

The distinction is entirely about whether the driver was given a parameterized statement or a pre-built string. That makes it an instrumentation problem with a code-level cause, and one worth testing rather than trusting.

What each capture mode produces

Parameterized, interpolated, and redacted capture Three rows. Parameterized capture stores one statement shape with dollar-one placeholders, groups perfectly and contains no user data. Interpolated capture stores a distinct string per request containing an email address, groups into nothing and leaks. Redacted capture rewrites literals to question marks in the Collector, restoring grouping and removing the data, but only after it left the process. Same query, three very different stored values parameterized · correct SELECT id, plan FROM users WHERE email = $1 1 distinct value · no data · groups perfectly interpolated · leaks SELECT id, plan FROM users WHERE email = '[email protected]' 1 value per user · PII stored · no grouping redacted in collector · backstop SELECT id, plan FROM users WHERE email = '?' grouping restored · data already left the process

The third row is worth dwelling on. Collector redaction is essential — it is the only control that covers a third-party library you did not write — but it fires after the value has been serialized and sent over the network. For genuinely sensitive data that ordering matters, which is why the driver-level control is the primary one and redaction is the safety net.

Implementation

Get the parameterization right at the call site

# Correct: the driver receives a template and a parameter tuple. The
# instrumentation records the template; the values never touch the span.
cursor.execute(
    "SELECT id, plan FROM users WHERE email = %s AND status = %s",
    (email, "active"),
)

# Wrong: the driver receives one pre-built string. Nothing downstream can
# tell which parts were data, so the whole string ends up on the span.
cursor.execute(
    f"SELECT id, plan FROM users WHERE email = '{email}' AND status = 'active'"
)

This is the same discipline that prevents SQL injection, which is a useful framing in a code review: any statement that is safe from injection is also safe to record on a span, and any statement that is not is both a security bug and a telemetry bug.

ORMs mostly do the right thing, with two exceptions worth checking: raw-SQL escape hatches (session.execute(text(...))), and dynamic IN clauses built by string joining. Both produce interpolated statements from code that otherwise looks parameterized.

Redact in the Collector

# collector-config.yaml — the backstop for everything you do not control
processors:
  transform/sql_redact:
    error_mode: ignore
    trace_statements:
      - context: span
        statements:
          # Quoted string literals — the main PII carrier in a WHERE clause.
          - replace_pattern(attributes["db.query.text"], "'[^']*'", "'?'")
              where attributes["db.query.text"] != nil
          # Long numeric literals: ids, card-like sequences, phone numbers.
          - replace_pattern(attributes["db.query.text"], "\\b\\d{4,}\\b", "?")
              where attributes["db.query.text"] != nil
          # IN lists collapse to a single placeholder so they still group.
          - replace_pattern(attributes["db.query.text"],
                            "IN \\s*\\([^)]*\\)", "IN (?)")
              where attributes["db.query.text"] != nil
          # Hard length cap: an ORM-generated monster must not dominate storage.
          - set(attributes["db.query.text"],
                Substring(attributes["db.query.text"], 0, 1024))
              where Len(attributes["db.query.text"]) > 1024

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [memory_limiter, transform/sql_redact, batch]
      exporters: [otlp/tempo]

Order matters in that pipeline: redaction must run before batch, and ideally before any exporter that writes to a second destination. A debug exporter placed earlier in the chain will happily print the unredacted value.

Prove it with a canary

The only trustworthy verification is a value that must never appear, checked automatically:

# tests/test_no_sql_leak.py — run against a real exporter in CI
CANARY = "[email protected]"

def test_email_never_reaches_span(otel_span_exporter, client):
    client.get(f"/users/by-email?email={CANARY}")
    spans = otel_span_exporter.get_finished_spans()
    assert spans, "no spans produced — instrumentation is not running"
    for span in spans:
        for key, value in span.attributes.items():
            assert CANARY not in str(value), (
                f"canary leaked into {key} on span {span.name}")
    # And the statement should still be present and useful:
    db_spans = [s for s in spans if s.attributes.get("db.system.name")]
    assert any("$1" in s.attributes.get("db.query.text", "") or
               "%s" in s.attributes.get("db.query.text", "")
               for s in db_spans), "statement was captured without placeholders"

That test asserts both halves: nothing leaked, and the attribute is still there. A test that only checks for absence passes trivially when instrumentation is broken, which is the failure mode you least want to ship.

Four controls, each catching what the previous one misses Four stacked controls. Parameterized call sites catch your own code. Driver-level capture catches anything using the instrumented driver. Collector redaction catches third-party libraries and services you do not own. The CI canary test catches regressions before they ship. Each control is labelled with what it cannot catch on its own. No single control is sufficient 1 · parameterized call sites catches: your own code — misses: every dependency 2 · driver-level capture catches: anything using this driver — misses: other drivers, other languages 3 · collector redaction catches: the whole fleet — misses: nothing, but acts after the value left the process 4 · CI canary test catches: regressions before they ship — the only control that runs before production

The same problem in other query languages

SQL gets the attention, but every data store your services talk to has an equivalent field and an equivalent risk, and a SQL-shaped redaction rule matches none of them.

MongoDB filters are documents, and instrumentation typically records the filter as JSON. {"email": "[email protected]"} is the direct analogue of an interpolated WHERE clause. The convention attribute is db.query.text here too, so a redaction pass keyed on that attribute can cover it — but the pattern must handle JSON string values rather than SQL quoting.

Elasticsearch query bodies are larger still and frequently contain the user’s literal search terms, which are among the most sensitive things an application handles. The pragmatic policy is to record the index and operation but not the body, or to record only the query’s structural shape.

GraphQL documents are usually safe — the query is a template and the values live in a separate variables map — but instrumentation that records the variables alongside the document reintroduces exactly the problem. Record the operation name and the document; never the variables.

Cache keys carry identifiers by construction. A key template is safe, the literal key is not, and the same reasoning applies as in instrumenting Redis clients.

The general rule that covers all of them: record the shape of the operation, never the values bound into it. That formulation is easier to apply in a review than a per-technology checklist, and it generalises to whatever store you adopt next.

Verification

Beyond the CI test, two production checks are worth having permanently:

# Sample stored statements and look for anything that is not a placeholder
curl -s "http://tempo:3200/api/search?q=%7Bspan.db.system.name%3D%22postgresql%22%7D&limit=50" \
  | jq -r '.traces[].traceID' \
  | while read tid; do
      curl -s "http://tempo:3200/api/traces/${tid}" \
        | jq -r '.. | .value? // empty | select(type=="string") | select(test("'\''[^?]"))'
    done | sort -u | head
# Distinct statement count — a healthy service has tens, not thousands
{ resource.service.name = "checkout-api" && span.db.system.name = "postgresql" }
  | by(span.db.query.text) | count()

A statement count in the thousands is the strongest signal of interpolation, because parameterized statements are a small fixed set by construction.

Distinct statement values stored per day, by capture mode Distinct statement values stored per day, by capture mode. parameterized ≈40 — groups perfectly; redacted in the collector ≈60; interpolated, numbers only ≈120,000; fully interpolated ≈2.1M — and leaking Distinct statement values stored per day, by capture mode parameterized ≈40 — groups perfectly redacted in the collector ≈60 interpolated, numbers only ≈120,000 fully interpolated ≈2.1M — and leaking The distinct-value count is the fastest test for whether parameterization is actually working.

Common pitfalls

  • Assuming the ORM always parameterizes. Raw-SQL escape hatches and hand-built IN lists are the two exceptions, and both appear in code that otherwise looks safe.
  • Redacting with a pattern that misses a quoting style. Double-quoted identifiers, dollar-quoted strings in PostgreSQL, and backtick-quoted values in MySQL all defeat a single-quote-only pattern.
  • Recording the statement on the wrong span. Some instrumentation puts it on the connection span, where it is neither grouped nor obviously present, so a redaction rule keyed on the query span misses it entirely.
  • Truncating before redacting. A cap applied first can leave a partial literal that the redaction pattern no longer matches.
  • Forgetting other query languages. MongoDB filters, Elasticsearch query bodies, and GraphQL documents carry the same risk, and none of them match a SQL-shaped redaction rule.

Whatever the store, decide the policy once and apply it in the Collector as well as in the client, so that a new service written by a team that has not read this page still cannot leak by default.

Related

↑ Back to Instrumenting Databases and Cache Clients