On Sat, Feb 17, 2007 at 03:15:25PM +0100, Karsten Hilbert wrote:
> On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote:
> > But if we insert a set schema search_path command in an SQL function,
> > the caller will be affected by it. Doing reset search_path before
> > returning to caller might solve some of problems, but it will not
> > recover caller's special search_path. How do you solve the problem?
>
> Schema-qualifying object accesses would be tedious,
> omission-prone but not liable to the above problem.
If you schema-qualify objects instead of setting search_path then
don't forget about operators. A query like
SELECT col
FROM schemaname.tablename
WHERE othercol = schemaname.funcname(someval)
is vulnerable because the caller might have defined an = operator
for the appropriate data types and set search_path to find it before
the one in pg_catalog. To be safe you'd need to use
SELECT col
FROM schemaname.tablename
WHERE othercol operator(pg_catalog.=) schemaname.funcname(someval)
which is harder to read and, as Karsten mentioned, prone to omission.
Also, this query might still be vulnerable if funcname() isn't
carefully written.
A PL/pgSQL function could save and restore the caller's search_path
with something like
oldpath := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', oldpath, false);
If the function raises an exception then search_path wouldn't be
reset unless you catch exceptions and reset the path in the
exception-handling code.
--
Michael Fuhr
