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
