Skip to content

Commit 72e55ca

Browse files
ZogStriPZogStriP
authored
FEATURE: Add current_user_id parameter type to Data Explorer (#36655)
Introduces a new `current_user_id` parameter type that automatically injects the ID of the user running the query. This enables secure "personal data" queries in group reports where non-admin users can run queries filtered to their own data. Why: - Members requested queries like "show my recent posts" for group reports, but there was no secure way to reference the current user - Passing user_id as a regular parameter would allow users to spoof other users' IDs How it works: - Parameter is injected server-side, ignoring any user-provided value - Frontend hides input fields for "internal" parameter types - Supports nullable option for queries that may run without auth Example usage: ```sql -- [params] -- current_user_id :me SELECT * FROM posts WHERE user_id = :me ``` Here are a few screenshots of how it looks like: The query from the admin PoV <img width="1471" height="1092" alt="CleanShot 2025-12-12 at 11 20 10" src="https://github.com/user-attachments/assets/bc6ce759-ebcb-4550-9035-dbaf7ae034da" /> How it looks like from a member of the report's allowed group <img width="1471" height="1092" alt="CleanShot 2025-12-12 at 11 19 48" src="https://github.com/user-attachments/assets/10eb1ddb-c93b-4608-988d-e4a8ca13d8ba" />
1 parent 76a22d8 commit 72e55ca

File tree

9 files changed

+178
-44
lines changed

9 files changed

+178
-44
lines changed

‎plugins/discourse-data-explorer/app/controllers/discourse_data_explorer/query_controller.rb‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -157,7 +157,7 @@ def run
157157
params[:params].is_a?(String) ? MultiJson.load(params[:params]) : params[:params]
158158
end
159159

160-
opts = { current_user: current_user&.username }
160+
opts = { current_user: current_user }
161161
opts[:explain] = true if params[:explain] == "true"
162162

163163
opts[:limit] = if params[:format] == "csv"

‎plugins/discourse-data-explorer/app/models/discourse_data_explorer/query.rb‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -34,10 +34,10 @@ def params
3434
@params ||= Parameter.create_from_sql(sql)
3535
end
3636

37-
def cast_params(input_params)
37+
def cast_params(input_params, opts = {})
3838
result = {}.with_indifferent_access
3939
self.params.each do |pobj|
40-
result[pobj.identifier] = pobj.cast_to_ruby input_params[pobj.identifier]
40+
result[pobj.identifier] = pobj.cast_to_ruby(input_params[pobj.identifier], opts)
4141
end
4242
result
4343
end

‎plugins/discourse-data-explorer/assets/javascripts/discourse/components/param-input-form.gjs‎

Lines changed: 38 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -155,6 +155,11 @@ export default class ParamInputForm extends Component {
155155

156156
initializeParams() {
157157
this.args.paramInfo.forEach((info) => {
158+
// Skip internal types - they are auto-injected server-side
159+
if (info.internal) {
160+
return;
161+
}
162+
158163
const identifier = info.identifier;
159164
const pinfo = this.createParamInfo(info);
160165

@@ -393,6 +398,10 @@ export default class ParamInputForm extends Component {
393398

394399
@action
395400
async submit() {
401+
// No visible params to validate - return empty object
402+
if (this.paramInfo.length === 0) {
403+
return {};
404+
}
396405
if (this.form == null) {
397406
throw "No form";
398407
}
@@ -422,32 +431,34 @@ export default class ParamInputForm extends Component {
422431
}
423432

424433
<template>
425-
<div class="query-params">
426-
<Form
427-
@data={{this.data}}
428-
@onRegisterApi={{this.onRegisterApi}}
429-
@onSubmit={{this.onSubmit}}
430-
class="params-form"
431-
as |form|
432-
>
433-
{{#each this.paramInfo as |info|}}
434-
<div class="param">
435-
<form.Field
436-
@name={{info.identifier}}
437-
@title={{info.identifier}}
438-
@validation={{info.validation}}
439-
@validate={{info.validate}}
440-
as |field|
441-
>
442-
<info.component @field={{field}} @info={{info}} />
443-
<ConditionalLoadingSpinner
444-
@condition={{info.loading}}
445-
@size="small"
446-
/>
447-
</form.Field>
448-
</div>
449-
{{/each}}
450-
</Form>
451-
</div>
434+
{{#if this.paramInfo.length}}
435+
<div class="query-params">
436+
<Form
437+
@data={{this.data}}
438+
@onRegisterApi={{this.onRegisterApi}}
439+
@onSubmit={{this.onSubmit}}
440+
class="params-form"
441+
as |form|
442+
>
443+
{{#each this.paramInfo as |info|}}
444+
<div class="param">
445+
<form.Field
446+
@name={{info.identifier}}
447+
@title={{info.identifier}}
448+
@validation={{info.validation}}
449+
@validate={{info.validate}}
450+
as |field|
451+
>
452+
<info.component @field={{field}} @info={{info}} />
453+
<ConditionalLoadingSpinner
454+
@condition={{info.loading}}
455+
@size="small"
456+
/>
457+
</form.Field>
458+
</div>
459+
{{/each}}
460+
</Form>
461+
</div>
462+
{{/if}}
452463
</template>
453464
}

‎plugins/discourse-data-explorer/config/locales/client.en.yml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@ en:
4343
<li><b>text</b> - variable-length character string</li>
4444
<li><b>boolean</b> – true/false</li>
4545
<li><b>date</b> - calendar date (year, month, day)</li>
46+
<li><b>current_user_id</b> - ID of the user running the query (auto-injected, useful for group reports)</li>
4647
</ul>
4748
<p>For more information on data types, visit
4849
<a href='http://www.postgresql.org/docs/9.3/static/datatype.html#DATATYPE-TABLE' target='_blank'>this website</a>.</p>"

‎plugins/discourse-data-explorer/lib/discourse_data_explorer/data_explorer.rb‎

Lines changed: 13 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ def self.run_query(query, req_params = {}, opts = {})
2828

2929
query_args = {}
3030
begin
31-
query_args = query.cast_params req_params
31+
query_args = query.cast_params(req_params, opts)
3232
rescue ValidationError => e
3333
return { error: e, duration_nanos: 0 }
3434
end
@@ -43,17 +43,18 @@ def self.run_query(query, req_params = {}, opts = {})
4343
DB.exec "SET LOCAL statement_timeout = 10000"
4444

4545
# SQL comments are for the benefits of the slow queries log
46-
sql = <<-SQL
47-
/*
48-
* DiscourseDataExplorer Query
49-
* Query: /admin/plugins/explorer/queries/#{query.id}
50-
* Started by: #{opts[:current_user]}
51-
*/
52-
WITH query AS (
53-
#{query.sql}
54-
) SELECT * FROM query
55-
LIMIT #{opts[:limit] || SiteSetting.data_explorer_query_result_limit}
56-
SQL
46+
started_by = opts[:current_user]&.username
47+
sql = <<~SQL
48+
/*
49+
* DiscourseDataExplorer Query
50+
* Query: /admin/plugins/explorer/queries/#{query.id}
51+
#{"* Started by: #{started_by}" if started_by}
52+
*/
53+
WITH query AS (
54+
#{query.sql}
55+
) SELECT * FROM query
56+
LIMIT #{opts[:limit] || SiteSetting.data_explorer_query_result_limit}
57+
SQL
5758

5859
time_start = Time.now
5960

‎plugins/discourse-data-explorer/lib/discourse_data_explorer/parameter.rb‎

Lines changed: 31 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,13 @@ def initialize(identifier, type, default, nullable, validate: true)
3434
end
3535

3636
def to_hash
37-
{ identifier: @identifier, type: @type, default: @default, nullable: @nullable }
37+
{
38+
identifier: @identifier,
39+
type: @type,
40+
default: @default,
41+
nullable: @nullable,
42+
internal: internal?,
43+
}
3844
end
3945

4046
def self.types
@@ -61,9 +67,19 @@ def self.types
6167
:string_list,
6268
:user_list,
6369
:group_list,
70+
# System-injected (value set by server, not user input)
71+
:current_user_id,
6472
)
6573
end
6674

75+
def self.internal_types
76+
@internal_types ||= %i[current_user_id]
77+
end
78+
79+
def internal?
80+
self.class.internal_types.include?(@type)
81+
end
82+
6783
def self.type_aliases
6884
@type_aliases ||= { integer: :int, text: :string, timestamp: :datetime }
6985
end
@@ -108,7 +124,20 @@ def self.create_from_sql(sql, opts = {})
108124
ret_params
109125
end
110126

111-
def cast_to_ruby(string)
127+
def cast_to_ruby(string, opts = {})
128+
# Handle system-injected types first - they ignore user input
129+
if @type == :current_user_id
130+
current_user = opts[:current_user]
131+
if current_user.nil?
132+
if @nullable
133+
return nil
134+
else
135+
raise ValidationError.new("Parameter #{identifier} requires a logged in user")
136+
end
137+
end
138+
return current_user.id
139+
end
140+
112141
string = @default unless string
113142
# Since we allow passing a JSON object for the params, the most straightforward way to
114143
# check that the value is valid is to convert it back to a string.

‎plugins/discourse-data-explorer/spec/data_explorer_spec.rb‎

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -58,6 +58,57 @@
5858
expect(result[:pg_result][0]["id"]).to eq(topic2.id)
5959
end
6060

61+
describe "current_user_id parameter" do
62+
fab!(:user)
63+
64+
it "injects the current user's id, ignoring user-provided values" do
65+
sql = <<~SQL
66+
-- [params]
67+
-- current_user_id :me
68+
SELECT id FROM users WHERE id = :me
69+
SQL
70+
71+
query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)
72+
other_user = Fabricate(:user)
73+
74+
result =
75+
described_class.run_query(query, { "me" => other_user.id.to_s }, { current_user: user })
76+
77+
expect(result[:error]).to eq(nil)
78+
expect(result[:pg_result][0]["id"]).to eq(user.id)
79+
end
80+
81+
it "returns an error when not nullable and no current user" do
82+
sql = <<~SQL
83+
-- [params]
84+
-- current_user_id :me
85+
SELECT id FROM users WHERE id = :me
86+
SQL
87+
88+
query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)
89+
90+
result = described_class.run_query(query, {}, {})
91+
92+
expect(result[:error]).to be_a(DiscourseDataExplorer::ValidationError)
93+
expect(result[:error].message).to include("requires a logged in user")
94+
end
95+
96+
it "allows null when nullable and no current user" do
97+
sql = <<~SQL
98+
-- [params]
99+
-- null current_user_id :me
100+
SELECT COALESCE(:me, -1) AS user_id
101+
SQL
102+
103+
query = DiscourseDataExplorer::Query.create!(name: "test", sql: sql)
104+
105+
result = described_class.run_query(query, {}, {})
106+
107+
expect(result[:error]).to eq(nil)
108+
expect(result[:pg_result][0]["user_id"]).to eq(-1)
109+
end
110+
end
111+
61112
describe ".add_extra_data" do
62113
it "treats any column with payload in the name as 'json'" do
63114
Fabricate(:reviewable_queued_post)

‎plugins/discourse-data-explorer/spec/lib/parameter_spec.rb‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,28 @@ def param(identifier, type, default, nullable)
113113
expect(param("user_id", :user_id, nil, false).cast_to_ruby(user.email)).to eq(user.id)
114114
end
115115
end
116+
117+
describe "current_user_id type" do
118+
fab!(:user)
119+
let(:current_user_param) { param("me", :current_user_id, nil, false) }
120+
121+
it "returns the current user's id, ignoring user-provided values" do
122+
expect(current_user_param.cast_to_ruby(nil, current_user: user)).to eq(user.id)
123+
expect(current_user_param.cast_to_ruby("999999", current_user: user)).to eq(user.id)
124+
end
125+
126+
it "raises an error when not nullable and no current user" do
127+
expect { current_user_param.cast_to_ruby(nil, {}) }.to raise_error(
128+
DiscourseDataExplorer::ValidationError,
129+
/requires a logged in user/,
130+
)
131+
end
132+
133+
it "returns nil when nullable and no current user" do
134+
nullable_param = param("me", :current_user_id, nil, true)
135+
expect(nullable_param.cast_to_ruby(nil, {})).to eq(nil)
136+
end
137+
end
116138
end
117139

118140
describe ".create_from_sql" do

‎plugins/discourse-data-explorer/spec/system/explorer_spec.rb‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,4 +95,23 @@
9595
)
9696
end
9797
end
98+
99+
context "with a current_user_id param" do
100+
fab!(:query) { Fabricate(:query, name: "My current user query", sql: <<~SQL, user: admin) }
101+
-- [params]
102+
-- current_user_id :me
103+
SELECT id, username FROM users WHERE id = :me
104+
SQL
105+
106+
it "auto-injects the current user's id without showing an input field" do
107+
visit("/admin/plugins/explorer/queries/#{query.id}")
108+
109+
expect(page).to have_no_css(".query-params")
110+
find(".query-run .btn-primary").click
111+
112+
expect(page).to have_css(".query-results .result-header")
113+
expect(page).to have_css(".query-results tbody tr", count: 1)
114+
expect(page).to have_css(".query-results tbody td", text: admin.username)
115+
end
116+
end
98117
end

0 commit comments

Comments
 (0)