Dynamic SQL and SQL injection

Question

I have been asked to look into generating a dynamic SQL statement in a function on a Postgres DB. In the past, I have always preferred that the SQL be generated at the app level but in this case, we are trying to do this in the DB itself.

To make matters worse I will basically be given the table name and where clause to use as a filter. I won't know the number of columns since the table could vary.

I have looked at both returning JSON and using a polymorphic type return value. I am trying to avoid any SQL injection on these queries and would like to know from the Postgres gurus if this function is vulnerable to SQL injection in your eyes? This is is really just a dumbed down piece of code to learn how to do things in Postgres since I come from an Oracle background.

Function 1:

CREATE OR REPLACE FUNCTION ksh.get_data_json( p_table text,
    p_column text,
    p_value text)
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
    query TEXT := 'SELECT json_agg(e) FROM (SELECT * FROM ' ||quote_ident(p_table);
BEGIN
    IF p_column IS NOT NULL THEN
        query := query || ' WHERE ' || quote_ident(p_column) || ' = ' ||quote_literal(p_value)||')e';
    END IF;
    
    RETURN QUERY EXECUTE query;

END;
$BODY$;

Function 2:

CREATE OR REPLACE FUNCTION ksh.get_data_poly(_tbl_type anyelement, _col text, _value text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s   WHERE ' || quote_ident(_col) ||' = '|| quote_literal(_value)||
      'ORDER  BY 1'
    , pg_typeof(_tbl_type))
   USING  _col,_value;
END
$func$;

Answer 1

Your attempts are safe against SQL injection. Basics:

• Table name as a PostgreSQL function parameter
• Define table and column names as arguments in a plpgsql function?
• SQL injection in Postgres functions vs prepared queries

Some functional / syntactical issues remain.

Function 1

CREATE OR REPLACE FUNCTION ksh.get_data_json( _table text, _column text, _value text)
  RETURNS SETOF json 
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- These asserts are optional, but recommended.
   -- Makes the rest simpler & safer, too
   IF _table <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_table missing!';
   END IF;
   
   IF _column <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_column missing!';
   END IF;

   IF _value <> '' THEN  -- all good
   ELSE RAISE EXCEPTION '_value missing!';
   END IF;

   RETURN QUERY EXECUTE format(
      'SELECT to_json(%1$I.*) FROM %1$I WHERE %2$I = %3$L'
    , _table, _column, _value
      );
END
$func$;

Call (plain):

SELECT * FROM ksh.get_data_json('tbl', 'col1', '1');

Use format() to make your live easier.

I added some asserts. Alternatively, make the function STRICT - so it returns null on null input.

Since you return SETOF json, don't aggregate all rows. Pass one JSON value per result row instead.

I use to_json(%1$I.*)rather than just to_json(%1$I), so that a column name cannot overrule an identical table name.

Function 2

Looks inspired by my old post:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

But not quite there, yet.

CREATE OR REPLACE FUNCTION ksh.get_data_poly(_tbl_type anyelement, _col text, _val text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT * FROM %1$s WHERE %2$I = %3$L'  --  ORDER BY 1 (???)
    , pg_typeof(_tbl_type), _col, _val
      );
END
$func$;

Call (important!):

SELECT * FROM pg_temp.get_data_poly(NULL::tbl, 'col1', '1');

Since the data type of the passed column is unknown, we cannot pass the value as value safely. Instead, concatenate a string constant.

Related:

• Robust approach for building SQL queries programmatically