seeking advices for function
От | Jean-Yves F. Barbier |
---|---|
Тема | seeking advices for function |
Дата | |
Msg-id | 20110622213031.57f5f311@anubis.defcon1 обсуждение исходный текст |
Ответы |
Re: seeking advices for function
|
Список | pgsql-novice |
Hi list, I've got some questions about this function: ============================= DROP FUNCTION tsttst(TEXT, TEXT, BOOLEAN, INT8, INT8, TEXT[]); CREATE FUNCTION tsttst(TEXT, -- FQTN TEXT, -- Ordering column BOOLEAN, -- TRUE=ASC / FALSE=DESC INT8, -- LIMIT INT8, -- OFFSET TEXT[]) -- Columns' names array RETURNS SETOF RECORD AS $$ DECLARE usr TEXT; ord TEXT; collist TEXT; qry TEXT; BEGIN -- Retrieve real caller's name usr := session_user; -- First check for table SELECT privilege IF NOT has_table_privilege(usr, $1, 'SELECT') THEN -- If needed, check SELECT privilege per column FOR i IN 1 .. array_length($6, 1) LOOP IF NOT has_column_privilege(usr, $1, $6[i], 'SELECT') THEN RAISE EXCEPTION 'FNCT: tsttst: Call forbidden'; -- ADD HERE LOGING IN TABLE security.alert -- YEAH, BUT HOW TO AVOID security.alert NEW ROW BEING -- VOIDED FROM A ROLLBACK ??? END IF; END LOOP; END IF; -- Set ordering direction IF $3 THEN ord := 'ASC'; ELSE ord := 'DESC'; END IF; -- Construct columns full list collist := array_to_string($6, ','); -- Build query from input parms qry = 'SELECT ' || collist || ' FROM ' || $1 || ' ORDER BY ' || $2 || ' ' || ord || ' LIMIT ' || $4 || ' OFFSET ' || $5 || ';'; -- Return the whole query RETURN QUERY EXECUTE qry; END; $$ LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER; ============================= * Is it totally non-vulnerable to SQL injection? * I intend to use this kind of function for data I/O, as a replacement for views in an ERP project. Considering that overhead introduced by builtin SELECT privilege check is far from negligible (from 110ns to 800ns, one row select) but on the other hand that it could replace thousands views and that an ERP isn't an application that generates hundreds queries per second. Is it a good idea or not? * A big problem is the implementation of trespassing attempts loging (see comment in function) which shouldn't be subject to the subsequent rollback; how can I do that? Any constructive critics will be welcome. JY -- My doctor told me to stop having intimate dinners for four. Unless there are three other people. -- Orson Welles
В списке pgsql-novice по дате отправления: