On 3/2/16, Alexander Farber <alexander.farber@gmail.com> wrote:
> On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy <vitaly.burovoy@gmail.com>
> wrote:
>
>> On 3/2/16, Alexander Farber <alexander.farber@gmail.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.
>>
>>
>> [1]https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29
>
> 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...
As Merlin Moncure mentioned[2] the best way is to replace "=" by "IS
NOT DISTINCT FROM" in the "check_user" function.
But if you want to change only IF statement in "testX" functions it is
enough to replace the condition "IF NOT check_user(42, 'user1', NULL)
THEN" by "IF check_user(42, 'user1', NULL) IS NOT TRUE THEN". See the
example below:
postgres=# SELECT var, var IS NOT TRUE AS result
postgres-# FROM unnest(ARRAY[TRUE, FALSE, NULL]::bool[])as var;
var | result
-----+--------
t | f
f | t
| t
(3 rows)
P.S.: please, don't top post.
[2]http://www.postgresql.org/message-id/CAHyXU0xdFQ--0aTm3Md7d1x5ZnfBJDe0eMJHnVtCZdBJuFDqew@mail.gmail.com
--
Best regards,
Vitaly Burovoy