Groups > comp.databases.postgresql > #849

Re: plpgsql function SQL injection vulnerability?

Path	csiph.com!eternal-september.org!feeder.eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From	Laurenz Albe <laurenz@nospam.pn>
Newsgroups	comp.databases.postgresql
Subject	Re: plpgsql function SQL injection vulnerability?
Date	Fri, 26 Oct 2018 12:47:53 -0000 (UTC)
Organization	A noiseless patient Spider
Lines	29
Message-ID	<pqv2dp$pdn$1@dont-email.me> (permalink)
References	<pqstfl$bse$1@dont-email.me> <1a45tdhtfu7cas9hm1em6hgqs47o0afa0v@4ax.com>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
Injection-Date	Fri, 26 Oct 2018 12:47:53 -0000 (UTC)
Injection-Info	reader02.eternal-september.org; posting-host="4bc3fb6e62fdbb9e6682332bf34ec990"; logging-data="26039"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+4JXd2obJUetap0QD+OInQOYNZSqbasj8="
User-Agent	Pan/0.145 (Duplicitous mercenary valetism; d7e168a git.gnome.org/pan2)
Cancel-Lock	sha1:COiyPSba53PvX+nt02p81sz7LWE=
Xref	csiph.com comp.databases.postgresql:849

Show key headers only | View raw

On Fri, 26 Oct 2018 00:47:35 -0400, George Neuner wrote:

>> CREATE OR REPLACE FUNCTION search_for_address(re TEXT)
>> RETURNS TABLE(line VARCHAR) AS $$
>> BEGIN
>> 	RETURN QUERY SELECT k.line FROM kdata k WHERE k.line ~* re ORDER 
BY
>> 	k.line ASC LIMIT 100;
>> END;
>> $$ LANGUAGE plpgsql SECURITY DEFINER;
>>
>> Is this function vulnerable to SQL injection attacks via input 're 
TEXT'
>> or does the PG parser prevent it in these plpgsql functions?
> 
> This particular use is safe: the 're' argument to the function is passed
> as a parameter to the ~* regex operator in a statc query ...
> the contents of the 're' string can't escape the operator's scope.

The function may be safe from SQL injection, but it is vulnerable to
privilege escalation attacks.

The attacker could define a ~* operator in "his" schema, set search_path
to that schema and call the function to execute arbitrary code with
elevated privileges.

That's why you should always define SECURITY DEFINER functions with
  SET search_path=pg_catalog
and schema qualify all access to objects in other schemas.

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

plpgsql function SQL injection vulnerability? sten.unto@gmail.com (Unto Sten) - 2018-10-25 17:11 +0000
  Re: plpgsql function SQL injection vulnerability? George Neuner <gneuner2@comcast.net> - 2018-10-26 00:47 -0400
    Re: plpgsql function SQL injection vulnerability? Laurenz Albe <laurenz@nospam.pn> - 2018-10-26 12:47 +0000
      Re: plpgsql function SQL injection vulnerability? sten.unto@gmail.com (Unto Sten) - 2018-10-26 19:06 +0000
    Re: plpgsql function SQL injection vulnerability? sten.unto@gmail.com (Unto Sten) - 2018-10-26 19:08 +0000

csiph-web