How secure is format() for dynamic queries inside a function?

Question

After reading Postgres manual and many posts here, I wrote this function tacking in mind all I found regarding security. It works great and does everything I looked for.

• takes a json, each key has an array [visible, filter, arg1, optional arg2]

SELECT public.goods__list_json2('{"name": [true, 7, "Ad%"], "category": [true], "stock": [false, 4, 0]}', 20, 0);

• returns a json array with requested data.

[{"name": "Adventures of TRON", "category": "Atari 2600"}, {"name": "Adventure", "category": "Atari 2600"}]

My question is, how could I really be sure that when I create the query using user input arguments, passing them as %L with format is injection safe? By my db design, all is done through functions, running most of them as security definer only allowing certain roles to execute them.

Being secure, my intention is to convert old functions to this dynamic logic and save myself to write a lot of lines of code creating new or specific queries.

I would really appreciate a experienced Postgres developer could give me an advice on this.

I'm using Postgres 13.

CREATE FUNCTION public.goods__list_json (IN option__j jsonb, IN limit__i integer, IN offset__i integer)
    RETURNS jsonb
    LANGUAGE plpgsql
    VOLATILE 
    STRICT
    SECURITY DEFINER
    COST 1
    AS $$
DECLARE
    table__v varchar := 'public.goods_full';
  column__v varchar[] := ARRAY['id', 'id__category', 'category', 'name', 'barcode', 'price', 'stock', 'sale', 'purchase'];
    filter__v varchar[] := ARRAY['<', '>', '<=', '>=', '=', '<>', 'LIKE', 'NOT LIKE', 'ILIKE', 'NOT ILIKE', 'BETWEEN', 'NOT BETWEEN'];
    select__v varchar[];
    where__v varchar[];
  sql__v varchar;
    key__v varchar;
    format__v varchar;
    temp__v varchar;
    temp__i integer;
    betw__v varchar;
    result__j jsonb;
BEGIN
    FOR key__v IN SELECT jsonb_object_keys(option__j) LOOP
        IF key__v = ANY(column__v) THEN
            IF (option__j->key__v->0)::bool THEN
                select__v := array_append(select__v, key__v);
            END IF;
            temp__i := (option__j->key__v->1)::int;
            IF temp__i > 0 AND temp__i <= array_length(filter__v, 1) THEN
                temp__v := (option__j->key__v->>2)::varchar;
                IF temp__i >= 11 THEN
                    betw__v := (option__j->key__v->>3)::varchar;
                    format__v := format('%I %s %L AND %L', key__v, filter__v[temp__i], temp__v, betw__v);
                ELSE
                    format__v := format('%I %s %L', key__v, filter__v[temp__i], temp__v);
                END IF;
                where__v := array_append(where__v, format__v);
            END IF;
        END IF;
    END LOOP;
    sql__v := 'SELECT jsonb_agg(t) FROM (SELECT '
        || array_to_string(select__v, ', ')
        || format(' FROM %s WHERE ', table__v)
        || array_to_string(where__v, ' AND ')
        || format(' OFFSET %L LIMIT %L', offset__i, limit__i)
        || ') t';
    RAISE NOTICE 'SQL: %', sql__v;
    EXECUTE sql__v INTO result__j;
    RETURN result__j;
END;
$$;

Answer 1

A word of warning: this style with dynamic SQL in SECURITY DEFINER functions can be elegant and convenient. But don't overuse it. Do not nest multiple levels of functions this way:

• The style is much more error prone than plain SQL.
• The context switch with SECURITY DEFINER has a price tag.
• Dynamic SQL with EXECUTE cannot save and reuse query plans.
• No "function inlining".
• And I'd rather not use it for big queries on big tables at all. The added sophistication can be a performance barrier. Like: parallelism is disabled for query plans this way.

That said, your function looks good, I see no way for SQL injection. format() is proven good to concatenate and quote values and identifiers for dynamic SQL. On the contrary, you might remove some redundancy to make it cheaper.

Function parameters offset__i and limit__i are integer. SQL injection is impossible through integer numbers, there is really no need to quote them (even though SQL allows quoted string constants for LIMIT and OFFSET). So just:

format(' OFFSET %s LIMIT %s', offset__i, limit__i)

Also, after verifying that each key__v is among your legal column names - and while those are all legal, unquoted column names - there is no need to run it through %I. Can just be %s

I'd rather use text instead of varchar. Not a big deal, but text is the "preferred" string type.

Related:

• Format specifier for integer variables in format() for EXECUTE?
• Function to return dynamic set of columns for given table

COST 1 seems too low. The manual:

COST execution_cost

A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary.

Unless you know better, leave COST at its default 100.

Single set-based operation instead of all the looping

The whole looping can be replaced with a single SELECT statement. Should be noticeably faster. Assignments are comparatively expensive in PL/pgSQL. Like this:

CREATE OR REPLACE FUNCTION goods__list_json (_options json, _limit int = NULL, _offset int = NULL, OUT _result jsonb)
    RETURNS jsonb
    LANGUAGE plpgsql SECURITY DEFINER AS
$func$
DECLARE
   _tbl  CONSTANT text   := 'public.goods_full';
   _cols CONSTANT text[] := '{id, id__category, category, name, barcode, price, stock, sale, purchase}';   
   _oper CONSTANT text[] := '{<, >, <=, >=, =, <>, LIKE, "NOT LIKE", ILIKE, "NOT ILIKE", BETWEEN, "NOT BETWEEN"}';
   _sql           text;
BEGIN
   SELECT concat('SELECT jsonb_agg(t) FROM ('
           , 'SELECT ' || string_agg(t.col, ', '  ORDER BY ord) FILTER (WHERE t.arr->>0 = 'true')
                                               -- ORDER BY to preserve order of objects in input
           , ' FROM '  || _tbl
           , ' WHERE ' || string_agg (
                             CASE WHEN (t.arr->>1)::int BETWEEN  1 AND 10 THEN
                                format('%s %s %L'       , t.col, _oper[(arr->>1)::int], t.arr->>2)
                                  WHEN (t.arr->>1)::int BETWEEN 11 AND 12 THEN
                                format('%s %s %L AND %L', t.col, _oper[(arr->>1)::int], t.arr->>2, t.arr->>3)
                               -- ELSE NULL  -- = default - or raise exception for illegal operator index?
                             END
                           , ' AND '  ORDER BY ord) -- ORDER BY only cosmetic
           , ' OFFSET ' || _offset  -- SQLi-safe, no quotes required
           , ' LIMIT '  || _limit   -- SQLi-safe, no quotes required
           , ') t'
          )
   FROM   json_each(_options) WITH ORDINALITY t(col, arr, ord)
   WHERE  t.col = ANY(_cols)        -- only allowed column names - or raise exception for illegal column?
   INTO   _sql;

   IF _sql IS NULL THEN
      RAISE EXCEPTION 'Invalid input resulted in empty SQL string! Input: %', _options;
   END IF;
   
   RAISE NOTICE 'SQL: %', _sql;
   EXECUTE _sql INTO _result;
END
$func$;

db<>fiddle here

Shorter, faster and still safe against SQLi.

Quotes are only added where necessary for syntax or to defend against SQL injection. Burns down to filter values only. Column names and operators are verified against the hard-wired list of allowed options.

Input is json instead of jsonb. Order of objects is preserved in json, so you can determine the sequence of columns in the SELECT list (which is meaningful) and WHERE conditions (which is purely cosmetic). The function observes both now.

Output _result is still jsonb. Using an OUT parameter instead of the variable. That's totally optional, just for convenience. (No explicit RETURN statement required.)

Note the strategic use of concat() to silently ignore NULL and the concatenation operator || so that NULL makes the concatenated string NULL. This way, FROM, WHERE, LIMIT, and OFFSET are only inserted where needed. A SELECT statement works without either of those. An empty SELECT list (also legal, but I suppose unwanted) results in a syntax error. All intended.
Using format() only for WHERE filters, for convenience and to quote values. See:

• String concatenation using operator "||" or format() function

The function isn't STRICT anymore. _limit and _offset have default value NULL, so only the first parameter _options is required. _limit and _offset can be NULL or omitted, then each is stripped from the statement.

Using text instead of varchar.

Made constant variables actually CONSTANT (mostly for documentation).

Other than that the function does what your original does.

Answer 2

I tried to put all that I learned here and I came up with this below and new questions =D.

• Is there any advantage declaring _oper this way '{LIKE, "NOT LIKE"}' instead of ARRAY['LIKE', 'NOT LIKE']?
• Casting as int _limit and _offset, I'm assuming no SQLi, right?
• Is it an elegant way for 'IN' and 'NOT IN' CASE? I wonder why string_agg() is allowed nested in concat() but not there where I needed to use a sub query.

This is a naive private function.

Edit: Removed "SECURITY DEFINER" as identified dangerous.

CREATE FUNCTION public.crud__select (IN _tbl text, IN _cols text[], IN _opts json, OUT _data jsonb)
    LANGUAGE plpgsql STRICT AS
$$
DECLARE
    _oper CONSTANT text[] := '{<, >, <=, >=, =, <>, LIKE, "NOT LIKE", ILIKE, "NOT ILIKE", BETWEEN, "NOT BETWEEN", IN, "NOT IN"}';
BEGIN
    EXECUTE (
        SELECT concat('SELECT jsonb_agg(t) FROM ('
                , 'SELECT ' || string_agg(e.col, ', ' ORDER BY ord) FILTER (WHERE e.arr->>0 = 'true')
                , ' FROM ', _tbl
                , ' WHERE ' || string_agg(
                    CASE
                        WHEN (e.arr->>1)::int BETWEEN  1 AND 10 THEN
                            format('%s %s %L', e.col, _oper[(e.arr->>1)::int], e.arr->>2)
                        WHEN (e.arr->>1)::int BETWEEN 11 AND 12 THEN
                            format('%s %s %L AND %L', e.col, _oper[(e.arr->>1)::int], e.arr->>2, e.arr->>3)
                        WHEN (e.arr->>1)::int BETWEEN 13 AND 14 THEN
                            format('%s %s (%s)', e.col, _oper[(e.arr->>1)::int], (
                                SELECT string_agg(format('%L', ee), ',') FROM json_array_elements_text(e.arr->2) ee)
                            )
          END, ' AND ')
                , ' OFFSET ' || (_opts->>'_offset')::int
                , ' LIMIT '  || (_opts->>'_limit')::int
                , ') t'
        )
    FROM json_each(_opts) WITH ORDINALITY e(col, arr, ord)
        WHERE e.col = ANY(_cols)
    ) INTO _data;
END;
$$;

Then for table or view, I create wrapper function executable for some roles.

CREATE FUNCTION public.goods__select (IN _opts json, OUT _data jsonb)
    LANGUAGE sql STRICT SECURITY DEFINER AS
$$
    SELECT public.crud__select(
        'public.goods_full',
        ARRAY['id', 'id__category', 'category', 'name', 'barcode', 'price', 'stock', 'sale', 'purchase'],
        _opts
    );
$$;

SELECT public.goods__select('{"_limit": 10, "name": [true, 9, "a%"], "id__category": [true, 13, [1, 2]], "category": [true]}'::json);

[{"name": "Atlantis II", "category": "Atari 2600", "id__category": 1}, .. , {"name": "Amidar", "category": "Atari 2600", "id__category": 1}]