Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.postgresql > #848

Re: plpgsql function SQL injection vulnerability?

Show all headers | View raw

On Thu, 25 Oct 2018 17:11:17 -0000 (UTC), sten.unto@gmail.com (Unto
Sten) wrote:

>I have a question that is probably easy for the
>PostgreSQL experts. Consider a simple function:
>
>###################################
>
>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.

Static queries that take parameters mostly are immune to injection. It
is possible to inject bogus data which will cause the query to fail or
return the wrong results ... but the query using parameters can't be
rewritten so as to do something completely different.

Injection is much more a concern with dynamic queries: e.g., the query
is provided as a function argument, or is constructed by concatenating
strings that include function arguments, and then is run using
EXECUTE.  Sometimes you have no choice[*] but most queries can be
written safely using parameters.  Dynamic queries more often are the
result of programmer laziness than of real necessity.


[*] some things can't be paramterized in a query: e.g., schema and
table names, operators, filtering conjuctives (AND/OR), etc.


>To be safe, I do input validation before calling
>search_for_address(re TEXT) but I would like to
>know the truth here.
>
>If the function is vulnerable, could you please provide
>an exact string to prove it? Thanks.

Assuming there is a good reason for the function to be a security
definer, it looks fine.

George

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