From: Asko Oja <ascoja@gmail.com>
To: Igor Neyman <ineyman@perceptron.com>
Cc: Tatarnikov Alexander <cankrus@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Use "CREATE USER" in plpgsql function
Date: Wed, 15 Sep 2010 21:28:40 +0300
Message-ID: <AANLkTimCcR7kCjpUmxVR8kAtOkctJxyYcTtgOsk1JGp2@mail.gmail.com> (raw)
In-Reply-To: <F4C27E77F7A33E4CA98C19A9DC6722A206827C5F@EXCHANGE.corp.perceptron.com>
References: <AANLkTi=u7vWFrgTzzE5zXUoT=4vqQt1tSH-A+s-K=JzU@mail.gmail.com>
<F4C27E77F7A33E4CA98C19A9DC6722A206827C5F@EXCHANGE.corp.perceptron.com>
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@perceptron.com> wrote:
>
>
> > -----Original Message-----
> > From: Tatarnikov Alexander [mailto:cankrus@gmail.com]
> > Sent: Wednesday, September 15, 2010 12:05 AM
> > To: pgsql-sql@postgresql.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ascoja@gmail.com, ineyman@perceptron.com, cankrus@gmail.com
Subject: Re: Use "CREATE USER" in plpgsql function
In-Reply-To: <AANLkTimCcR7kCjpUmxVR8kAtOkctJxyYcTtgOsk1JGp2@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox