Re: Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body

Finally i'm using:

EXECUTE 'CREATE USER '|| quote_ident(creds."userName") ||' WITH PASSWORD '||
quote_literal(creds."userPassword") || ' IN GROUP ta_users';

and it works perfect.

Thanks!

2010/9/16 Igor Neyman <ineyman(at)perceptron(dot)com>

>
>
> ------------------------------
> *From:* Asko Oja [mailto:ascoja(at)gmail(dot)com]
> *Sent:* Wednesday, September 15, 2010 2:29 PM
> *To:* Igor Neyman
> *Cc:* Tatarnikov Alexander; pgsql-sql(at)postgresql(dot)org
> *Subject:* Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s)
> list error in the Text body
>
> And dynamic SQL leads easily to SQL injection so quoting is required there.
>
> execute 'create user ' || quote_ident(i_username) || ' password '
> || quote_literal(i_password);
>
> On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman <ineyman(at)perceptron(dot)com>wrote:
>
>>
>>
>> > -----Original Message-----
>> > From: Tatarnikov Alexander [mailto:cankrus(at)gmail(dot)com]
>> > Sent: Wednesday, September 15, 2010 12:05 AM
>> > To: pgsql-sql(at)postgresql(dot)org
>> > Subject: Use "CREATE USER" in plpgsql function
>> >
>> > Hello!
>> >
>> > I have function wich check user credentials and if test
>> > passed function must create new user with generated username
>> > and password.
>> >
>> > Language is plpgsql.
>> >
>> > For example:
>> >
>> > ....
>> > DECLARE creds RECORD;
>> > ...
>> > SELECT * INTO creds FROM ...
>> >
>> > creds is Record with fields userName(VARCHAR) and
>> > userPassword(VARCHAR)
>> >
>> > so when i use CREATE USER creds."userName" WITH PASSWORD
>> > creds."userPassword"
>> >
>> > i get an error, because creds."userName" is VARCHAR and thus
>> > when function runs it will be look like this:
>> > CREATE USER 'user_1'
>> > but right command is
>> > "CREATE USER user_1" OR " CREATE USER "user_1" "
>> >
>> > so question is how to "unembrace" this parameter (i mean
>> > creds."userName")?
>> >
>> > Thanks
>> > --
>> > ------
>> > Alexander
>> >
>>
>> It is called "dynamic" sql:
>>
>> EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' ||
>> userPassword;
>>
>>
>> Read about "dynamic" sql in PG docs:
>>
>> http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html
>>
>> Specifically: "38.5.4. Executing Dynamic Commands"
>>
>> Regards,
>> Igor Neyman
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
> [I.N.] Opps.
> Missed quote_ident() in your message, sorry.
>
>

--
------
С уважением,
Татарников Александр