From: | "Igor Neyman" <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Asko Oja" <ascoja(at)gmail(dot)com> |
Cc: | "Tatarnikov Alexander" <cankrus(at)gmail(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body |
Date: | 2010-09-15 19:08:06 |
Message-ID: | F4C27E77F7A33E4CA98C19A9DC6722A206827E27@EXCHANGE.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
________________________________
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tatarnikov Alexander | 2010-09-16 02:03:30 | Re: Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body |
Previous Message | Igor Neyman | 2010-09-15 19:06:34 | Re: Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body |