Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C234413381DB for ; Wed, 15 Sep 2010 15:38:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 91742-02 for ; Wed, 15 Sep 2010 18:38:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 58F9E133829A for ; Wed, 15 Sep 2010 15:28:42 -0300 (ADT) Received: by vws3 with SMTP id 3so265224vws.19 for ; Wed, 15 Sep 2010 11:28:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=TzRi+1OKP2qjdZpNCsmycZ5Hqp1+4W9RNYPjTy6WrS8=; b=VYo5qY3//1VuwvRb/jF0x1r5EI/GtX28n+macRjJC3fOVefxe4rMbNTYKOd0TQ89a/ 5Hpz0lV9dWTYGkQVS1ypVZasOkGChxFGSNZ395pjQXfeaHsvTF7L+Wu0M/V/PSBEFlc6 Ez+/jPLQzfyTkugXDDdNwJtwJo5sBqwJ9jdyk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=DLOtRJ5+hJRNgj4gk9fE/3eQ3jmzAQg5bAd8zBByGCXP1aBDWefHNxst2E+jdSwdGV 6Pm0t5FbrIx7w2zb2u1xxEI3rGU5PqXB+qLckSrdLb5U2kuzanqyQKjjOATX9azNGuNR VmS//KcphIctMt2o1J6BZms093O8HZNNbZJ5I= MIME-Version: 1.0 Received: by 10.220.47.158 with SMTP id n30mr1125325vcf.71.1284575320594; Wed, 15 Sep 2010 11:28:40 -0700 (PDT) Received: by 10.220.163.210 with HTTP; Wed, 15 Sep 2010 11:28:40 -0700 (PDT) In-Reply-To: References: Date: Wed, 15 Sep 2010 21:28:40 +0300 Message-ID: Subject: Re: Use "CREATE USER" in plpgsql function From: Asko Oja To: Igor Neyman Cc: Tatarnikov Alexander , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e64607deabda870490507f9f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/42 X-Sequence-Number: 34573 --0016e64607deabda870490507f9f Content-Type: text/plain; charset=ISO-8859-1 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 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 > --0016e64607deabda870490507f9f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable And dynamic SQL leads easily to SQL injection so quoting is required there.=

=A0=A0=A0=A0=A0=A0=A0 execute 'create user ' || quote_ident= (i_username) || ' password ' || quote_literal(i_password);

<= div class=3D"gmail_quote"> On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman <ineyman@perceptron.com> wr= ote:


> -----Original Message-----
> From: Tatarnikov Alexander [mailto:cankrus@gmail.com]
> Sent: Wednesday, September 15, 2010 12:05 AM
> To: pgsql-sql@postgresql.o= rg
> 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 cre= ds.' ||
userPassword;


Read about "dynamic" sql in PG docs:

http://www.postgresql.org/docs/8.4/interactive/pl= pgsql-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

--0016e64607deabda870490507f9f--