Thread: SQL Injection possible on custom functions

SQL Injection possible on custom functions

From
"Bogdan Tomchuk"
Date:

I have one question that I cannot figure out 100% sure answer.

 

Lets say that in schema Main I have following table:

 

CREATE TABLE Accounts (

UID           char(43) PRIMARY KEY CHECK ( UID <> '' ),

Login         varchar(320) UNIQUE NOT NULL CHECK ( Login <> '' ),

Password      char(32) NOT NULL,                                                            

Active        boolean DEFAULT FALSE,

Valid         boolean DEFAULT FALSE, 

ExpirationTS  timestamp without time zone DEFAULT 'infinity',

FirstName     varchar(512)  NOT NULL DEFAULT '',

LastName      varchar(512)  NOT NULL DEFAULT '',

DisplayName   varchar(1024) NOT NULL DEFAULT '', 

Mail          varchar(320)  NOT NULL CHECK ( Mail <> '' ),              

Retired       boolean DEFAULT FALSE,                                                          

LastUpdate    timestamp without time zone DEFAULT current_timestamp

);

 

And in Schema Users:

CREATE FUNCTION UpdateAccount(varchar(512),varchar(512),varchar(1024),varchar(320)) RETURNS boolean AS'
    DECLARE
        UserFirstName   ALIAS FOR $1;
        UserLastName    ALIAS FOR $2;
        UserDisplayName ALIAS FOR $3;
        UserMail        ALIAS FOR $4;

        Row RECORD;
    BEGIN
        UPDATE Main.Accounts
            SET   FirstName      = UserFirstName,
                  LastName       = UserLastName,
                  DisplayName    = UserDisplayName,
                  Mail           = UserMail,
                  LastUpdate     = DEFAULT
            WHERE Active         = ''True''  AND
                  Valid          = ''True''  AND
                  Retired        = ''False'' AND
                  ExpirationTS   > now()     AND
                  ''user_''||UID = session_user;
        IF NOT FOUND THEN
            RETURN ''False'';            -- No Accessible Account
        END IF;

        RETURN ''True'';                  -- All ok
    END;
'LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER;

 

 

User has no direct access on schema Main but has right to execute UpdateAccount.

 

So normally user can only modify FirstName, LastName, DisplayName, Mail for single record (''user_''||UID = session_user) of table Accounts.

 

My question is: if we imagine that input of  UpdateAccount has no filtration or this filtration incorrect does exist any way to modify other then authorized parameters of Accounts table or records of other user? Is there any injection technique possible? Should I still do something like quote_literal() systematically on each parameters and reverse conversation each time? Or even replace UPDATE.. by EXECUTE ''Update... ?

 

Thank you in advance, TBP

Re: SQL Injection possible on custom functions

From
Tom Lane
Date:
"Bogdan Tomchuk" <tbp-dsi@poly.polytechnique.fr> writes:
> My question is: if we imagine that input of  UpdateAccount has no =
> filtration or this filtration incorrect does exist any way to modify =
> other then authorized parameters of Accounts table or records of other =
> user? Is there any injection technique possible? Should I still do =
> something like quote_literal() systematically on each parameters and =
> reverse conversation each time? Or even replace UPDATE.. by EXECUTE =

No.  The code as you wrote it is perfectly secure --- plpgsql variables
are placeholders, their values aren't injected literally into the SQL
command.  If you went over to using EXECUTE then you *would* need
quote_literal to be safe, because then you're synthesizing the complete
SQL command as a string.

            regards, tom lane