From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to ensure that a stored function always returns TRUE or FALSE? |
Date: | 2016-03-02 12:39:45 |
Message-ID: | CAADeyWiyye5tfSPQ9enm1WbN3oVLobGxi9df5A5BWYd1B=Oeiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Vitaly, but instead of inverting the IF-condition I would prefer to
make my function more robust, since it is kind of security-related and I
might forget about the special IF-condition later when using it elsewhere...
On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>
wrote:
> On 3/2/16, Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> >
> > CREATE OR REPLACE FUNCTION check_user(
> > in_social integer,
> > in_sid varchar(255),
> > in_auth varchar(32))
> > RETURNS boolean AS
> > $func$
> > SELECT MD5('secret word' || in_social || in_sid) =
> in_auth;
> > $func$ LANGUAGE sql IMMUTABLE;
> >
> >
> > CREATE OR REPLACE FUNCTION test3() RETURNS void AS
> > $func$
> > BEGIN
> > IF NOT check_user(42, 'user1', NULL) THEN
> > RAISE NOTICE 'invalid user';
> > ELSE
> > RAISE NOTICE 'valid user';
> > END IF;
> > END
> > $func$ LANGUAGE plpgsql;
> >
> > The 3rd function does NOT work as expected and prints "valid user".
> >
> > This happens because check_user() returns NULL instead of a boolean
> value.
>
> I guess it is enough to swap blocks inside of IF statement and reverse
> its condition:
>
> CREATE OR REPLACE FUNCTION test3() RETURNS void AS
> $func$
> BEGIN
> IF check_user(42, 'user1', NULL) THEN
> RAISE NOTICE 'valid user';
> ELSE
> RAISE NOTICE 'invalid user';
> END IF;
> END
> $func$ LANGUAGE plpgsql;
>
> would give "invalid user". NULL works as FALSE at the top of IF
> expressions.
>
>
> https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Bennett | 2016-03-02 13:29:44 | Re: Looking for pure C function APIs for server extension: language handler and SPI |
Previous Message | fredrik | 2016-03-02 11:54:38 | "missing chunk number XX for toast value YY in pg_toast ..." after pg_basebackup. |