Show table of data where the user selected the schema and table

[beingalink]

Hi,

I'm currently using sqlpage with postgresql. I have following index.sql:

SELECT
	'form' AS component,
	'query_form' as id,
	TRUE AS auto_submit,
	'GET' AS method;

-- topics are the schemata in the db
WITH topics AS (SELECT jsonb_agg(jsonb_build_object('label', schema_name, 'value', schema_name, 'selected', CASE WHEN schema_name = $topicChoice THEN 1 ELSE 0 END)) AS topic FROM information_schema.schemata)
SELECT topic AS options,
	'topicChoice' AS name,
	'select' AS type,
	'Thema' AS label,
	'Wählen Sie ein Bereichsthema' AS empty_option,
	(SELECT $topicChoice) AS description,
	TRUE AS searchable,
	TRUE AS readonly
FROM topics;

WITH tables AS (SELECT jsonb_agg(jsonb_build_object('label', table_name, 'value', table_name, 'selected', CASE WHEN table_name = $tableChoice THEN 1 ELSE 0 END)) AS t FROM information_schema.tables WHERE table_schema = $topicChoice)
SELECT t AS options,
	'tableChoice' AS name,
	'select' AS type,
	'Tabelle' AS label,
	'Wählen Sie eine Tabelle' AS empty_option,
	(SELECT $tableChoice) AS description,
	TRUE AS searchable,
	TRUE AS readonly
FROM tables
WHERE $topicChoice IS NOT NULL;

SELECT 'table' AS component;
-- How do I get the data from table $topicChoice.$tableChoice?

I want to display a table with the data in $topicChoice.$tableChoice which is dynamically generated. Is there a way to do this? Thank you for any help!

[lovasoa]

My recommendation is: don't make the schema and table names dynamic here. Write the allowed tables, and preferably the allowed columns, explicitly in your SQL files instead.

If you do build a generic database browser, think very hard about security first. A website visitor is not the SQLPage database user: the database connection may be able to read much more than that visitor should see. A dropdown is not access control; visitors can change the request parameters themselves.

For example, you can keep your form and write fixed queries for the allowed choices (replace these example names with your own):

SELECT 'table' AS component;

SELECT id, order_date, total
FROM sales.orders
WHERE $topicChoice = 'sales' AND $tableChoice = 'orders';

SELECT id, display_name
FROM sales.customers
WHERE $topicChoice = 'sales' AND $tableChoice = 'customers';

Only the query matching the selected choice returns rows. You still need to check that the visitor is authorized to see those rows.

If you really need dynamic tables

SQLPage binds $variables as values, not SQL identifiers, so FROM $topicChoice.$tableChoice cannot work. Also, sqlpage.run_sql() loads a SQL file; it does not evaluate a SQL string.

With PostgreSQL, one option is a database function that safely quotes the identifiers and returns each row as JSON. Create this once, for example in a migration:

CREATE FUNCTION public.browse_table(p_schema text, p_table text)
RETURNS SETOF json
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
    IF p_schema IS NULL OR p_table IS NULL
       OR p_schema = '' OR p_table = '' THEN
        RETURN;
    END IF;

    -- Keep a server-side allowlist, even with dynamic SQL.
    IF (p_schema, p_table) NOT IN
       (('sales', 'orders'), ('sales', 'customers')) THEN
        RAISE EXCEPTION 'Table not allowed';
    END IF;

    RETURN QUERY EXECUTE format(
        'SELECT row_to_json(r)
         FROM (SELECT * FROM %I.%I LIMIT 100) AS r',
        p_schema, p_table
    );
END;
$$;

Then your final SQLPage statements become:

SELECT 'table' AS component;

SELECT 'dynamic' AS component, r AS properties
FROM public.browse_table($topicChoice, $tableChoice) AS r;

Important details:

• %I.%I quotes the schema and table separately, preventing identifier-based SQL injection. Quoting does not provide authorization.
• Use a restricted, read-only database role and check the visitor's permissions. SECURITY INVOKER uses the SQLPage connection's database permissions, not the visitor's identity; do not change it to SECURITY DEFINER to bypass permission errors.
• Keep limits on result size and query time. Reset or validate $tableChoice when the schema changes.
• This JSON approach assumes the output has no column named component. Rename that field before passing it to dynamic, otherwise SQLPage treats its value as a component declaration rather than table data.

Disclosure: I'm an AI coding agent, posting this reply at a human user's request. I tested the PostgreSQL-function/SQLPage-dynamic approach locally with SQLPage 0.47.0 and PostgreSQL 18, including quoted identifiers, empty selections/tables, injection rejection, and database permission enforcement.

[beingalink]

Thank you very much! That is a very sophisticated answer which I wouldn't have been able to come up with by myself. Yesterday, I started asking an AI this question and came up with sth very close to your answer but had to struggle with some json formatting issues. Your solution is much better.

Regarding security you're right of course. This is nothing I'd have pointing outside into the internet. It's just for some internal demonstration showing the capabilities of sqlpage and being able to quickly switch between tables without having to edit the code. It won't become a product.