Skip to content

A platform for community discussion. Free, open, simple.

24.2 KB
Raw Blame
# frozen_string_literal: true

describe DiscourseDataExplorer::DataExplorer do
  describe ".strip_comments" do
    it "removes line comments, keeping the newline" do
      expect(described_class.strip_comments("SELECT 1 -- hi\nFROM t")).to eq("SELECT 1 \nFROM t")
    end

    it "removes nested block comments" do
      expect(described_class.strip_comments("SELECT /* a /* b */ c */ 1")).to eq("SELECT   1")
    end

    it "keeps comment markers inside quoted identifiers" do
      expect(described_class.strip_comments(%{SELECT 1 AS "-- a"})).to eq(%{SELECT 1 AS "-- a"})
    end

    it "keeps comment markers inside dollar-quoted strings" do
      expect(described_class.strip_comments("SELECT $q$-- a$q$")).to eq("SELECT $q$-- a$q$")
    end

    it "does not let an escaped quote in an E'' string swallow a comment" do
      expect(described_class.strip_comments("SELECT E'a\\'b' -- c")).to eq("SELECT E'a\\'b' ")
    end

    it "passes unterminated literals through" do
      expect(described_class.strip_comments("SELECT 'a -- b")).to eq("SELECT 'a -- b")
    end
  end

  describe ".rewrite_to_binds" do
    it "rewrites a code-position parameter to a positional placeholder" do
      sql, binds = described_class.rewrite_to_binds("SELECT :id", { "id" => 7 })

      expect(sql).to eq("SELECT $1")
      expect(binds).to eq([{ value: "7", type: 20 }])
    end

    it "reuses one placeholder and one bind for a repeated parameter" do
      sql, binds = described_class.rewrite_to_binds("SELECT :id, :id", { "id" => 7 })

      expect(sql).to eq("SELECT $1, $1")
      expect(binds.size).to eq(1)
    end

    it "expands a list parameter into a run of placeholders" do
      sql, binds = described_class.rewrite_to_binds("WHERE id IN (:ids)", { "ids" => [1, 2, 3] })

      expect(sql).to eq("WHERE id IN ($1, $2, $3)")
      expect(binds.map { |bind| bind[:value] }).to eq(%w[1 2 3])
    end

    it "emits NULL without consuming bind slots and reuses mixed list placeholders" do
      sql, binds =
        described_class.rewrite_to_binds(
          "SELECT :missing, :ids, :name, :ids, :missing",
          { "missing" => nil, "ids" => [1, nil, 2], "name" => "NULL" },
        )

      expect(sql).to eq("SELECT NULL, $1, NULL, $2, $3, $1, NULL, $2, NULL")
      expect(binds.map { |bind| bind[:value] }).to eq(%w[1 2 NULL])
    end

    it "leaves a marker inside a string literal untouched" do
      sql, binds = described_class.rewrite_to_binds("SELECT ':id'", { "id" => 7 })

      expect(sql).to eq("SELECT ':id'")
      expect(binds).to be_empty
    end

    it "ignores dollar-quote syntax that appears inside a string literal" do
      sql, binds = described_class.rewrite_to_binds("SELECT '$sql$:id$sql$'", { "id" => 7 })

      expect(sql).to eq("SELECT '$sql$:id$sql$'")
      expect(binds).to be_empty
    end

    it "rejects a parameter inside a dollar-quoted literal" do
      expect {
        described_class.rewrite_to_binds("SELECT $sql$:id$sql$", { "id" => 7 })
      }.to raise_error(
        DiscourseDataExplorer::ValidationError,
        "Parameters cannot be used inside dollar-quoted literals",
      )
    end
  end

  describe ".run_query" do
    fab!(:topic)

    it "runs a query that includes PostgreSQL template patterns" do
      sql = <<~SQL
      WITH query AS (
        SELECT TO_CHAR(created_at, 'yyyy:mm:dd') AS date FROM topics
      ) SELECT * FROM query
      SQL

      query = DiscourseDataExplorer::Query.create!(name: "some query", sql: sql)

      result = described_class.run_query(query)

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["date"]).to eq(topic.created_at.strftime("%Y:%m:%d"))
    end

    it "runs a query containing a question mark in a comment" do
      sql = <<~SQL
      WITH query AS (
        SELECT id FROM topics -- some SQL ? comment ?
      ) SELECT * FROM query
      SQL

      query = DiscourseDataExplorer::Query.create!(name: "some query", sql: sql)

      result = described_class.run_query(query)

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["id"]).to eq(topic.id)
    end

    it "can run a query with params interpolation" do
      topic2 = Fabricate(:topic)

      sql = <<~SQL
      -- [params]
      -- int :topic_id = 99999999
      WITH query AS (
        SELECT
          id,
          TO_CHAR(created_at, 'yyyy:mm:dd') AS date
        FROM topics
        WHERE topics.id = :topic_id
      ) SELECT * FROM query
      SQL

      query = DiscourseDataExplorer::Query.create!(name: "some query", sql: sql)

      result = described_class.run_query(query, { "topic_id" => topic2.id.to_s })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a.size).to eq(1)
      expect(result[:pg_result][0]["id"]).to eq(topic2.id)
    end

    it "runs a list parameter through an IN clause" do
      topic2 = Fabricate(:topic)
      topic3 = Fabricate(:topic)

      query = DiscourseDataExplorer::Query.create!(name: "list query", sql: <<~SQL)
            -- [params]
            -- int_list :ids
            SELECT id FROM topics WHERE id IN (:ids) ORDER BY id
          SQL

      result = described_class.run_query(query, { "ids" => "#{topic.id},#{topic3.id}" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a.map { |row| row["id"] }).to eq([topic.id, topic3.id].sort)
    end

    it "runs a query that checks a declared string parameter against IS NULL, with no cast needed" do
      query = DiscourseDataExplorer::Query.create!(name: "is null query", sql: <<~SQL)
            -- [params]
            -- string :site = hosted
            SELECT (:site) IS NULL AS is_null
          SQL

      result = described_class.run_query(query, { "site" => "hosted" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["is_null"]).to eq(false)
    end

    %w[text date datetime time boolean user_id int_list string_list].each do |type|
      it "uses an untyped NULL for a missing #{type} parameter" do
        query = DiscourseDataExplorer::Query.create!(name: "missing parameter", sql: <<~SQL)
              -- [params]
              -- null #{type} :value
              SELECT :value IS NULL AS is_null, COALESCE(:value, 'missing') AS label
            SQL

        result = described_class.run_query(query)

        expect(result[:error]).to eq(nil)
        expect(result[:pg_result].to_a).to eq([{ "is_null" => true, "label" => "missing" }])
      end
    end

    it "lets each occurrence of a null parameter infer its own type" do
      query = DiscourseDataExplorer::Query.create!(name: "null inference", sql: <<~SQL)
            -- [params]
            -- null int :value
            SELECT :value IS NULL AS is_null,
                   COALESCE(:value, 'all') AS label,
                   round(1.234::numeric, COALESCE(:value, 2)) AS rounded
          SQL

      result = described_class.run_query(query)

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a).to eq(
        [{ "is_null" => true, "label" => "all", "rounded" => BigDecimal("1.23") }],
      )
    end

    it "preserves numeric precision in a null double parameter's fallback" do
      query = DiscourseDataExplorer::Query.create!(name: "numeric fallback", sql: <<~SQL)
            -- [params]
            -- null double :value
            SELECT COALESCE(:value, 9007199254740993::numeric) AS value
          SQL

      result = described_class.run_query(query)

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["value"]).to eq(BigDecimal("9007199254740993"))
      expect(result[:pg_result].ftype(0)).to eq(1700)
    end

    it "preserves null elements in an integer list" do
      query = DiscourseDataExplorer::Query.create!(name: "nullable list", sql: <<~SQL)
            -- [params]
            -- int_list :ids
            SELECT unnest(ARRAY[:ids]) AS id
          SQL

      result = described_class.run_query(query, { "ids" => "1,#null,2" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a.map { |row| row["id"] }).to eq([1, nil, 2])
    end

    it "runs optional filters when a null integer is checked before its column comparison" do
      query = DiscourseDataExplorer::Query.create!(name: "optional filters", sql: <<~SQL)
            -- [params]
            -- null text :username
            -- null text :email
            -- null int :user_id
            -- null text :external_id
            SELECT id FROM users
            WHERE (:username IS NOT NULL OR :email IS NOT NULL OR :user_id IS NOT NULL OR :external_id IS NOT NULL)
              AND (username = :username OR :username IS NULL)
              AND (id = :user_id OR :user_id IS NULL)
          SQL

      result = described_class.run_query(query, { "username" => topic.user.username })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a.map { |row| row["id"] }).to eq([topic.user_id])
    end

    it "still compares a declared date parameter against a timestamp column with no cast" do
      query = DiscourseDataExplorer::Query.create!(name: "date compare query", sql: <<~SQL)
            -- [params]
            -- date :start_date
            SELECT id FROM topics WHERE created_at >= :start_date ORDER BY id
          SQL

      result = described_class.run_query(query, { "start_date" => "2020-01-01" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a.map { |row| row["id"] }).to include(topic.id)
    end

    it "rejects, without executing, a parameter inside a dollar-quoted literal" do
      query = DiscourseDataExplorer::Query.create!(name: "dollar query", sql: <<~SQL)
            -- [params]
            -- string :value
            SELECT $sql$:value$sql$ AS value
          SQL

      result = described_class.run_query(query, { "value" => "harmless$sql$) SELECT 1 --" })

      expect(result[:error]).to be_a(DiscourseDataExplorer::ValidationError)
      expect(result[:error].message).to eq(
        "Parameters cannot be used inside dollar-quoted literals",
      )
      expect(result[:pg_result]).to eq(nil)
    end

    it "adds query instrumentation after removing stored comments" do
      user = Fabricate(:user)
      query =
        DiscourseDataExplorer::Query.create!(
          name: "instrumented query",
          sql: "-- removable annotation\nSELECT current_query() AS sql",
        )

      result = described_class.run_query(query, {}, { current_user: user })
      executed_sql = result[:pg_result][0]["sql"]

      expect(result[:error]).to eq(nil)
      expect(executed_sql).to include(
        "DiscourseDataExplorer Query",
        "/admin/plugins/discourse-data-explorer/queries/#{query.id}",
        "Started by: #{user.username}",
      )
      expect(executed_sql).not_to include("removable annotation")
    end

    it "keeps comment markers that are part of a string literal" do
      query =
        DiscourseDataExplorer::Query.create!(
          name: "some query",
          sql: "SELECT '-- not /* a comment' AS value",
        )

      result = described_class.run_query(query)

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["value"]).to eq("-- not /* a comment")
    end

    it "interpolates each parameter once" do
      query = DiscourseDataExplorer::Query.create!(name: "parameterized query", sql: <<~SQL)
            -- [params]
            -- string :selected
            -- string :other
            SELECT :selected AS value
          SQL

      result =
        described_class.run_query(query, { "selected" => ":other", "other" => "other value" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["value"]).to eq(":other")
    end

    it "interpolates a parameter whose name is a prefix of another" do
      query = DiscourseDataExplorer::Query.create!(name: "parameterized query", sql: <<~SQL)
            -- [params]
            -- int :topic
            -- int :topic_id
            SELECT :topic AS a, :topic_id AS b
          SQL

      result = described_class.run_query(query, { "topic" => "1", "topic_id" => "2" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a).to eq([{ "a" => 1, "b" => 2 }])
    end

    it "leaves undeclared parameters alone" do
      query = DiscourseDataExplorer::Query.create!(name: "parameterized query", sql: <<~SQL)
            -- [params]
            -- string :declared
            SELECT :declared AS a, ':undeclared' AS b
          SQL

      result = described_class.run_query(query, { "declared" => "value" })

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result].to_a).to eq([{ "a" => "value", "b" => ":undeclared" }])
    end

    it "does not let a parameter break out of a string literal via another parameter" do
      query = DiscourseDataExplorer::Query.create!(name: "parameterized query", sql: <<~SQL)
            -- [params]
            -- string :selected
            -- string :other
            SELECT :selected AS value
          SQL

      result =
        described_class.run_query(
          query,
          {
            "selected" => "x:other",
            "other" => "'||(SELECT users.username FROM users LIMIT 1)||'",
          },
        )

      expect(result[:error]).to eq(nil)
      expect(result[:pg_result][0]["value"]).to eq("x:other")
    end

    describe "current_user_id parameter" do
      fab!(:user)

      it "injects the current user's id, ignoring user-provided values" do
        sql = <<~SQL
          -- [params]
          -- current_user_id :me
          SELECT id FROM users WHERE id = :me
        SQL

        query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)
        other_user = Fabricate(:user)

        result =
          described_class.run_query(query, { "me" => other_user.id.to_s }, { current_user: user })

        expect(result[:error]).to eq(nil)
        expect(result[:pg_result][0]["id"]).to eq(user.id)
      end

      it "returns an error when not nullable and no current user" do
        sql = <<~SQL
          -- [params]
          -- current_user_id :me
          SELECT id FROM users WHERE id = :me
        SQL

        query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)

        result = described_class.run_query(query, {}, {})

        expect(result[:error]).to be_a(DiscourseDataExplorer::ValidationError)
        expect(result[:error].message).to include("requires a logged in user")
      end

      it "allows null when nullable and no current user" do
        sql = <<~SQL
          -- [params]
          -- null current_user_id :me
          SELECT COALESCE(:me, -1) AS user_id
        SQL

        query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)

        result = described_class.run_query(query, {}, {})

        expect(result[:error]).to eq(nil)
        expect(result[:pg_result][0]["user_id"]).to eq(-1)
      end
    end

    describe ".add_extra_data" do
      it "treats any column with payload in the name as 'json'" do
        Fabricate(:reviewable_queued_post)
        sql = <<~SQL
          SELECT id, payload FROM reviewables LIMIT 10
        SQL
        query = DiscourseDataExplorer::Query.create!(name: "some query", sql: sql)
        result = described_class.run_query(query)
        _, colrender =
          DiscourseDataExplorer::DataExplorer.add_extra_data(result[:pg_result], guardian: nil)
        expect(colrender).to eq({ 1 => "json" })
      end

      it "treats columns with the actual json or jsonb data type as 'json'" do
        ApiKeyScope.create(
          resource: "topics",
          action: "update",
          api_key_id: Fabricate(:api_key).id,
          allowed_parameters: {
            "category_id" => ["#{topic.category_id}"],
          },
        )
        sql = <<~SQL
          SELECT id, allowed_parameters FROM api_key_scopes LIMIT 10
        SQL
        query = DiscourseDataExplorer::Query.create!(name: "some query", sql: sql)
        result = described_class.run_query(query)
        _, colrender =
          DiscourseDataExplorer::DataExplorer.add_extra_data(result[:pg_result], guardian: nil)
        expect(colrender).to eq({ 1 => "json" })
      end

      it "treats jsonb columns as 'json'" do
        query =
          DiscourseDataExplorer::Query.create!(
            name: "some query",
            sql: "SELECT '[{\"key\": \"value\"}]'::jsonb AS response_format",
          )
        result = described_class.run_query(query)

        _, colrender =
          DiscourseDataExplorer::DataExplorer.add_extra_data(result[:pg_result], guardian: nil)

        expect(colrender).to eq({ 0 => "json" })
      end

      it "treats json and jsonb array columns as 'json'" do
        query = DiscourseDataExplorer::Query.create!(name: "some query", sql: <<~SQL)
              SELECT
                ARRAY['{\"key\": \"value\"}'::json] AS json_values,
                ARRAY['{\"key\": \"value\"}'::jsonb] AS jsonb_values
            SQL
        result = described_class.run_query(query)

        _, colrender =
          DiscourseDataExplorer::DataExplorer.add_extra_data(result[:pg_result], guardian: nil)

        expect(colrender).to eq({ 0 => "json", 1 => "json" })
      end

      it "limits relation resolution to the query result limit per relation type" do
        SiteSetting.data_explorer_query_result_limit = 2
        topics = Fabricate.times(4, :topic)
        query = DiscourseDataExplorer::Query.create!(name: "some query", sql: <<~SQL)
              SELECT #{topics[0].id} AS topic_id, #{topics[2].id} AS related_topic_id
              UNION ALL
              SELECT #{topics[1].id} AS topic_id, #{topics[3].id} AS related_topic_id
            SQL

        pg_result = described_class.run_query(query)[:pg_result]
        relations, _ = DiscourseDataExplorer::DataExplorer.add_extra_data(pg_result, guardian: nil)

        expect(relations[:topic].as_json.size).to eq(2)
      end

      it "does not rewrite fancy_title when serializing topic relations" do
        Topic.where(id: topic.id).update_all(fancy_title: "sentinel")
        query = Fabricate(:query, sql: "SELECT #{topic.id} AS topic_id")

        relations, _ =
          described_class.add_extra_data(
            described_class.run_query(query)[:pg_result],
            guardian: nil,
          )
        relations[:topic].as_json

        expect(Topic.where(id: topic.id).pick(:fancy_title)).to eq("sentinel")
      end

      it "classifies id columns" do
        query = Fabricate(:query, sql: <<~SQL)
          SELECT
            1 AS "html$post_id",
            1 AS user_badge_id,
            1 AS group_user_id,
            1 AS topic_user_id,
            1 AS category_user_id,
            1 AS topic_allowed_user_id,
            1 AS topic_allowed_group_id,
            1 AS associated_group_id,
            1 AS watched_word_group_id,
            1 AS badge_id,
            1 AS card_image_badge_id,
            1 AS acting_user_id,
            1 AS topic_id,
            1 AS tag_group_id,
            1 AS quoted_post_id,
            1 AS deleted_by_id,
            1 AS last_editor_id,
            1 AS group_id,
            1 AS category_id,
            1 AS "author$user_id",
            1 AS reviewable_id,
            1 AS related_reviewable_id,
            1 AS "reviewable$item"
        SQL

        relations, colrender =
          described_class.add_extra_data(
            described_class.run_query(query)[:pg_result],
            guardian: nil,
          )

        expect(colrender).to eq(
          {
            0 => :html,
            9 => :badge,
            10 => :badge,
            11 => :user,
            12 => :topic,
            13 => :tag_group,
            14 => :post,
            15 => :user,
            16 => :user,
            17 => :group,
            18 => :category,
            19 => :user,
            20 => :reviewable,
            21 => :reviewable,
            22 => :reviewable,
          },
        )
        expect(relations.keys).not_to include(:group, :category, :reviewable)
      end

      it "reports relations hidden by the guardian" do
        SiteSetting.detailed_404 = true
        pm_post = Fabricate(:private_message_post)
        pm = pm_post.topic
        query =
          Fabricate(
            :query,
            sql:
              "SELECT #{pm.id} AS topic_id, #{pm_post.id} AS post_id, #{topic.id} AS other_topic_id",
          )

        relations, _, hidden =
          described_class.add_extra_data(
            described_class.run_query(query)[:pg_result],
            guardian: Fabricate(:user).guardian,
          )

        expect(hidden).to eq({ topic: [pm.id], post: [pm_post.id] })
        expect(relations[:topic].as_json.map { |t| t[:id] }).to eq([topic.id])
      end

      it "does not report hidden relations when detailed 404s are disabled" do
        SiteSetting.detailed_404 = false
        pm_post = Fabricate(:private_message_post)
        query =
          Fabricate(:query, sql: "SELECT #{pm_post.topic_id} AS topic_id, #{pm_post.id} AS post_id")

        relations, _, hidden =
          described_class.add_extra_data(
            described_class.run_query(query)[:pg_result],
            guardian: Fabricate(:user).guardian,
          )

        expect(hidden).to eq({})
        expect(relations[:post].as_json).to be_empty
      end

      it "resolves post visibility without a query per post" do
        query_counts =
          [2, 8].map do |post_count|
            posts = Fabricate.times(post_count, :private_message_post)
            query = Fabricate(:query, sql: "SELECT unnest(ARRAY#{posts.map(&:id)}) AS post_id")
            pg_result = described_class.run_query(query)[:pg_result]
            guardian = Fabricate(:user).guardian
            relations = nil

            queries =
              track_sql_queries do
                relations, _ = described_class.add_extra_data(pg_result, guardian:)
              end

            expect(relations[:post].as_json).to be_empty
            queries.size
          end

        expect(query_counts.first).to eq(query_counts.last)
      end

      describe "serializing models to serializer" do
        it "serializes correctly to BasicTopicSerializer for topic relations" do
          topic = Fabricate(:topic, locale: "ja")
          query = Fabricate(:query, sql: "SELECT id AS topic_id FROM topics WHERE id = #{topic.id}")

          pg_result = described_class.run_query(query)[:pg_result]
          relations, _ =
            DiscourseDataExplorer::DataExplorer.add_extra_data(pg_result, guardian: nil)

          expect {
            records = relations[:topic].object
            records.map { |t| BasicTopicSerializer.new(t, root: false).as_json }
          }.not_to raise_error

          json = relations[:topic].as_json
          expect(json).to include(BasicTopicSerializer.new(topic, root: false).as_json)
        end

        it "serializes badge relations with their type and image" do
          badge = Fabricate(:badge, image_upload: Fabricate(:upload))
          query = Fabricate(:query, sql: "SELECT #{badge.id} AS badge_id")

          pg_result = described_class.run_query(query)[:pg_result]
          relations, _ =
            DiscourseDataExplorer::DataExplorer.add_extra_data(pg_result, guardian: nil)

          expect(MultiJson.load(relations[:badge].to_json)).to include(
            include(
              "image_url" => badge.image_url,
              "badge_type" => include("id" => badge.badge_type_id, "name" => badge.badge_type.name),
            ),
          )
        end

        it "chooses the correct serializer for tag_group" do
          tag_group = Fabricate(:tag_group)
          tag1 = Fabricate(:tag)
          tag2 = Fabricate(:tag)
          tag_group.tags = [tag1, tag2]

          query = Fabricate(:query, sql: "SELECT tag_id, tag_group_id FROM tag_group_memberships")

          pg_result = described_class.run_query(query)[:pg_result]
          relations, colrender =
            DiscourseDataExplorer::DataExplorer.add_extra_data(pg_result, guardian: nil)

          expect(colrender).to eq({ 1 => :tag_group })
          expect(relations[:tag_group].as_json).to include(
            { "id" => tag_group.id, "name" => tag_group.name },
          )
        end
      end
    end
  end

  describe "schema hints" do
    it "only names existing columns" do
      schema_columns = DB.query_single(<<~SQL)
        SELECT table_name || '.' || column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = 'public'
      SQL

      expect(described_class.foreign_keys.keys - schema_columns).to be_empty
      expect(described_class.sensitive_column_names - schema_columns).to be_empty
    end
  end
end