Function argument not interpreted inside body

Question

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.

Community · Accepted Answer · 2017-05-23 12:07:33Z

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;')

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Apr 6, 2015 at 11:09

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erwin Brandstetter Over a year ago

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. stackoverflow.com/questions/10705616/…

Collectives™ on Stack Overflow

Function argument not interpreted inside body

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related