Thread: Pgsql roles, SQL injection, and utility statements

Hi all;

I have a bit of concern about writing applications which use Pgsql roles
for security.

Since the utility statements are not parameterized, the easiest way to
manage the roles in an application is to use stored procedures which
EXECUTE strings to create SQL queries.   These EXECUTE statements
include user-supplied data, and since these would generally run with
some sort of administrative rights, I am worried about people doing
things like:
select * from add_user_to_role('username', 'rolename; drop table foo;');

Is this a problem?  Is there a way to do this safely?

Best Wishes,
Chris Travers

chris@travelamericas.com (Chris Travers) writes:
> Since the utility statements are not parameterized, the easiest way to
> manage the roles in an application is to use stored procedures which
> EXECUTE strings to create SQL queries.   These EXECUTE statements
> include user-supplied data, and since these would generally run with
> some sort of administrative rights, I am worried about people doing
> things like:
> select * from add_user_to_role('username', 'rolename; drop table foo;');
>
> Is this a problem?  Is there a way to do this safely?

Sure - validate that 'rolename; drop table foo;' is the legitimate
name of a role, and raise an exception if it isn't.

And have the stored function use "double quotes" to make sure that the
names are suitably quoted.

That provides a "belt" and a "pair of suspenders" for this case...
--
output = ("cbbrowne" "@" "acm.org")
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
I just removed the instructions in MC:COMMON;LINS > which specify that
it should be installed on AI.  We'll certainly  miss that machine, and
probably spend the rest of our lives fixing programs that mention it.