1

I am trying to create a PL/pgSQL function in PostgreSQL 9.3.6, but there is weird behavior when using the passed argument inside function body. Here is the 'very simple' function:

CREATE OR REPLACE FUNCTION myschema.test (myarg text) RETURNS text AS $$
DECLARE
  entity text;
  buffer text;
BEGIN
    CREATE ROLE myarg;
    RETURN myarg;
END;
$$ LANGUAGE plpgsql;

So, if for instance myarg equals 'test':

  • A role named 'myarg' is created (WRONG)
  • 'test' is returned (CORRECT)

I searched for hours why this could be and no clue... security parameter? Why is myarg not interpreted for creating roles?

Testing with phpPgAdmin through sql files if this has any impact.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
KevOu
  • 43
  • 7

1 Answers1

1

You should use:

EXECUTE FORMAT('CREATE ROLE %I', myarg);

Here you can find an explanation (especially read Craig's answer).

As Erwin stated (thanks), %I is safer than %s. Anyway, myarg should be verified before the function call. Try for example

SELECT myschema.test('something; stupid; here;')
Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232
  • Be sure to escape `myarg` as identifier in this context (a role name) or you are open to SQL injection. Use `%I` with `format()` unless the identifier is safely escaped already. http://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349 – Erwin Brandstetter Apr 06 '15 at 11:54