ankane/pghero · error · ActiveRecord::StatementInvalid

Unsafe statement

Error message

Unsafe statement

What it means

explain() runs user-supplied SQL inside a rolled-back transaction with a statement timeout. As an injection guard, it rejects any statement that - after trimming one trailing ";" - still contains ";" or whose uppercased text contains "COMMIT", unless explain_safe? proves the connection itself rejects multi-statement SQL (it probes SELECT 1; SELECT 1). When the statement trips the check on a connection that would happily execute multiple statements, PgHero raises ActiveRecord::StatementInvalid "Unsafe statement".

Source

Thrown at lib/pghero/methods/explain.rb:24

        options = []
        add_explain_option(options, "ANALYZE", analyze)
        add_explain_option(options, "VERBOSE", verbose)
        add_explain_option(options, "SETTINGS", settings)
        add_explain_option(options, "GENERIC_PLAN", generic_plan)
        add_explain_option(options, "COSTS", costs)
        add_explain_option(options, "BUFFERS", buffers)
        add_explain_option(options, "WAL", wal)
        add_explain_option(options, "TIMING", timing)
        add_explain_option(options, "SUMMARY", summary)
        options << "FORMAT #{explain_format(format)}"

        sql = "(#{options.join(", ")}) #{sql}"
        explanation = nil

        # use transaction for safety
        with_transaction(statement_timeout: (explain_timeout_sec * 1000).round, rollback: true) do
          if (sql.delete_suffix(";").include?(";") || sql.upcase.include?("COMMIT")) && !explain_safe?
            raise ActiveRecord::StatementInvalid, "Unsafe statement"
          end
          explanation = execute("EXPLAIN #{sql}").map { |v| v["QUERY PLAN"] }.join("\n")
        end

        explanation
      end

      private

      def explain_safe?
        select_all("SELECT 1; SELECT 1")
        false
      rescue ActiveRecord::StatementInvalid
        true
      end

      def add_explain_option(options, name, value)
        unless value.nil?

View on GitHub (pinned to 7edb57986f)

Solutions

1. Explain one statement at a time: strip everything after the first ";" before calling explain
2. For the COMMIT false positive, remove or alias the offending token (e.g. select the column under a different expression) or wait for/use a version with a tighter check
3. If you control the call site, rescue ActiveRecord::StatementInvalid and show a clear message instead of letting it bubble as a 500

Example fix

# before - two statements in one call
database.explain("SELECT 1; SELECT 2")

# after - one statement per call
database.explain("SELECT 1")
database.explain("SELECT 2")

# reject what pghero's guard rejects, before calling explain
cleaned = sql.strip.delete_suffix(";")
if cleaned.include?(";") || cleaned.upcase.include?("COMMIT")
  raise ArgumentError, "refusing multi-statement / COMMIT SQL"
end
database.explain(cleaned)

begin
  database.explain(sql)
rescue ActiveRecord::StatementInvalid => e
  if e.message.include?("Unsafe statement")
    # tell the user to submit a single statement instead of surfacing a 500
    render_error("Explain accepts a single SQL statement without COMMIT")
  else
    raise
  end
end

When it happens

Trigger: database.explain("SELECT 1; DROP TABLE x") - a genuine multi-statement payload; pasting two statements into the Explain tab; false positives: any query mentioning a column or literal containing the letters "commit" (e.g. SELECT committed_at FROM orders) because the check is a plain case-insensitive substring match on the whole SQL string.

Common situations: Users pasting multi-statement SQL from psql into the pghero Explain tab; models with commit/committed_at columns whose queries get explained via the query stats page; drivers or poolers that allow multiple statements in one call, which makes explain_safe? return false.

Related errors

• Invalid config file
• Spec not found: #{config["spec"]}
• Unknown format
• Historical query stats not enabled
• Space stats not enabled

AI-assisted analysis of ankane/pghero@7edb57986f (2026-08-21). Data as JSON: /api/errors/7a9b6e44d2ceca77. Report an issue: GitHub.