postgresqlfunctionstored-procedurespostgresql-16

Execute function from procedure, the name of function stored in table column


Table condition: This table contains function name in the column fn_name

create table condition(id int,name varchar(50),fn_name varchar(50));

Data:

insert into condition values(1,'check id','fn_condition_1');
insert into condition values(2,'check name','fn_condition_2');
insert into condition values(3,'check salary','fn_condition_3');

Function: fn_condition_1

create or replace function fn_condition_1(p_id int)
returns boolean
as 
$$
begin   
    if(select count(1) from emp where id = p_id) > 0 
    then 
        return 'true';
    else
        return 'false';
    end if;
end
$$
language plpgsql;

Procedure: prc_bsn_condition within this procedure calling function.

create or replace procedure prc_bsn_condition(p_id int)
language plpgsql
as
$$
declare v_out varchar(10);
        v_sql varchar(200);
        v_fn varchar(20);
begin
    select * into v_fn from (select fn_name from condition where id = p_id);
    v_sql := 'SELECT * FROM '||v_fn||'('||p_id||')';
    execute v_sql into v_out;
    raise info '%',v_out;
end
$$;

Output:

true;

Note: The above procedure works fine, but I would like to convert the dynamic sql to the static due to the fear of sql injections. Or is there any other method to call or execute those functions which are stored in the table.


Solution

  • A combination of format() to create the SQL statement and USING for getting the content in that statement, will fix the SQL injection risk:

    CREATE OR REPLACE PROCEDURE prc_bsn_condition(p_id INT)
        LANGUAGE plpgsql
    AS
    $$
    DECLARE
        v_out VARCHAR(10);
        v_sql VARCHAR(200);
        v_fn  VARCHAR(20);
    BEGIN
        SELECT fn_name
        INTO v_fn
        FROM condition
        WHERE id = p_id;
    
        v_sql := FORMAT('SELECT * FROM %I($1)', v_fn);
    
        EXECUTE v_sql 
        INTO v_out 
        USING p_id;
        
        RAISE INFO '%',v_out;
    END
    $$;