PostgreSQL : Prevent SQL Injection in pl/pgsql function I am trying to handle sql injection related to startdate and enddate in the below pl/pgsql function. 

Let's say If I provide startdate as '2019-10-02' and end date as the following :

2019-10-16', 'YYYY-MM-DD') AND rid in ('R1857745') group by rid, dcid UNION ALL SELECT 'R1857745'::character(8) , 'D099901'::character(8), 1, 1, 1, 1 FROM pg_shadow --

The above value for enddate creates a serious SQL vulnerability.

create function A(
 in startdate varchar(10),
 in enddate varchar(10))
RETURNS setof AB AS $$
BEGIN
 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent&gt;=to_date(''' || startdate || ''',''YYYY-MM-DD'') and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent&gt;=to_date(''' || startdate || ''',''YYYY-MM-DD'') and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent&lt;=to_date(''' || enddate || ''',''YYYY-MM-DD'') ' 
  group by rid, dcid';
END;
$$ LANGUAGE plpgsql;


As startdate and enddate are of variable type date, so we can do validation with date formats. But, I really don't want to do that because in this scenario I am more concerned about single quote.

I know that we can escape ' by using 2 single quotes i.e. ''

Is there any public function available in postgreSQL which can escape single quote, so that we can use the same for all type of variables in where clause or in other places.

I tried using quote_literal, but it's not doing the job for me
Use parameter placeholders instead of concatenating the input into the SQL string.

create function A(in startdate varchar(10), in enddate varchar(10))
  RETURNS setof AB AS $$
declare
  l_startdate date;
  l_enddate date;
BEGIN
 l_startdate := to_date(startdate,'YYYY-MM-DD');
 l_enddate := to_date(enddate,'YYYY-MM-DD'); 

 return query execute
 'select rid,dcid,
        sum(case when id between 10 and 19 or id between 21 and 26 then noOfcaptured else 0 end) total_tilldate,
        sum(case when dateofevent &gt;= $1 and (id between 10 and 19 or id between 21 and 26) then noofcaptured else 0 end) total_range,
        sum(case when id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_tilldate,
        sum(case when dateofevent &gt;= $2 and id not between 51 and 59 then cast(sizecaptured as double precision)/1048576 else 0 end) totalsize_range
   from repostats
  where dateofevent&lt;= $3
  group by rid, dcid'
    using l_startdate, l_startdate, l_enddate;
END;
$$ LANGUAGE plpgsql;