0

I have a situation here, we are migrating from Oracle to EDB POSTGRES ADVANCED SERVER(ORACLE COMPATIBLE). I will share a sample procedure (the same method is used everywhere in our organization procedures).

CREATE OR REPLACE PROCEDURE get_emp(
OUT dataset refcursor,
jb character varying)
LANGUAGE 'edbspl'

AS $BODY$

STR VARCHAR2(32000) ;
BEGIN
STR := 'SELECT * FROM EMP WHERE JOB='''||JB||'''  ';

DBMS_OUTPUT.PUT_LINE ( STR ) ;
OPEN DATASET FOR STR ;

END GET_EMP$BODY$;

This procedure is compiled successfully. but when I call the procedure

SELECT GET_EMP('CLERK');

The result is like 

"<unnamed portal 1>"

after this what I do is

begin;
SELECT GET_EMP('CLERK');
fetch all in "<unnamed portal 1>";

This time I am getting the desired output. Is there any way to get the records just by calling the procedure like follows

SELECT GET_EMP('CLERK');
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
vishnudas
  • 53
  • 1
  • 10
  • With `psql` there is no other way to fetch a ref cursor. Unrelated, but: with Postgres 11 you should use [`call`](https://www.postgresql.org/docs/current/sql-call.html) to run a stored procedure, not `select` –  Dec 10 '18 at 13:40
  • Thank you for your comment..So with psql it cant be done.. – vishnudas Dec 12 '18 at 09:09

1 Answers1

0

Your procedure is vulnerable to SQL injection. Besides, why the mumbo-jumbo with refcursors if all you want is:

CREATE FUNCTION get_emp(jb text) RETURNS SETOF emp
   LANGUAGE sql AS
'SELECT * FROM emp WHERE job = jb';
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • in our case the parameters for the procedure is getting from the user through UI,and many tables involved according to the parameters.In oracle we just need to pass the parameters and click on execute it will return the output.Likewise in postgresql is there any way to execute the procedure..As you said how can avoid SQL INJECTION?? – vishnudas Dec 10 '18 at 13:03
  • Just change your code so that your three lines including the `FETCH` are executed when the user clicks. You avoid SQL injection by not concatenating unescaped user input with your query, like in the example I gave. – Laurenz Albe Dec 10 '18 at 13:08
  • @vishnudas: note that with a function declared as `returns setof` you need to use `select * from get_emp(...)` instead of `select get_emp(...)`. But I agree with Laurenz: there is no need for a refcursor, a procedure or dynamic SQL at all –  Dec 10 '18 at 13:41
  • @Laurenz Albe: i will try your suggestion in our production environment,and thank you for your comment – vishnudas Dec 12 '18 at 09:22