CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsqlcoalescepostgresql-9.5
Alexander Farber
Alexander Farber

Reputation: 22988

How to ensure that a stored function always returns TRUE or FALSE?

With the following stored function I would like to validate user data:

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;

I am going to call it while looping through a JSON array of objects in another stored functions - and will RAISE EXCEPTION if it returns FALSE for any of the JSON objects (and thus rollback the whole transaction).

Instead of dumping here the source code of my 2nd stored function, I have prepared 3 simple test functions below -

CREATE OR REPLACE FUNCTION test1() RETURNS void AS
$func$
BEGIN
        IF NOT check_user(42, 'user1', '56db1046fa7b664c9b3d05bf7413552a') THEN
                RAISE NOTICE 'invalid user';
        ELSE
                RAISE NOTICE 'valid user';
        END IF;
END
$func$ LANGUAGE plpgsql;

The 1st function works as expected and prints valid user.

CREATE OR REPLACE FUNCTION test2() RETURNS void AS
$func$
BEGIN
        IF NOT check_user(42, 'user2', '56db1046fa7b664c9b3d05bf7413552a') THEN
                RAISE NOTICE 'invalid user';
        ELSE
                RAISE NOTICE 'valid user';
        END IF;
END
$func$ LANGUAGE plpgsql;

The 2nd function works as expected and prints invalid user.

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.

COALESCE could be wrapped around the check_user() call in the IF-statement... but is there maybe a nicer way to solve this problem?

Upvotes: 1

Views: 871

Answers (3)

Alexander Farber
Alexander Farber

Reputation: 22988

Here is what I will probably use, because the function is security related and as thus I don't want having to remember any edge cases (like mandatory use of IF check_user(...) IS NOT TRUE when declaring it as STRICT) when using it in future -

CREATE OR REPLACE FUNCTION check_user(
        in_social integer, 
        in_sid varchar(255), 
        in_auth varchar(32))
        RETURNS boolean AS
$func$
        SELECT CASE 
                WHEN in_social IS NULL THEN FALSE
                WHEN in_sid    IS NULL THEN FALSE
                WHEN in_auth   IS NULL THEN FALSE
                ELSE (MD5('secret word' || in_social || in_sid) = in_auth)
        END;

$func$ LANGUAGE sql IMMUTABLE;

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125304

Check is [not] distinct from

select md5('secret word' || in_social || in_sid) is not distinct from in_auth;

But notice that if both sides evaluate to null the comparison will return true:

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown".

Even simpler is to just declare the function strict and compare the returned value with is not true:

if check_user(42, 'user1', null) is not true then raise notice 'invalid user';

STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.

This has the added benefit of avoiding any cost of executing the function.

Upvotes: 1

ikusimakusi
ikusimakusi

Reputation: 159

I would add the coalesce inside the check_user() function, so it always returns true or false.

    SELECT MD5('secret word' || in_social || in_sid) = coalesce(in_auth,'');

Or even the option below in case the other values could also be NULL :

    SELECT MD5('secret word' || coalesce(in_social,-1)::varchar || coalesce(in_sid,'')) = coalesce(in_auth,'');

being there "-1" a value that will never be assigned to in_social

Upvotes: 1

Related Questions